Infrastructure at your Service

Category

MySQL

Elisa Usai

The evolution of MySQL authentication mechanism

By | Database Administration & Monitoring, Database management, MySQL, Security | No Comments

The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network. In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.

Read More
Elisa Usai

My first presentation at the DOAG – “MySQL 8.0 Community: Ready for GDPR?”

By | Database Administration & Monitoring, MySQL, Oracle | 4 Comments

This year I participated for the first time to the DOAG, the conference which takes place in November in Nuremberg. Here some key words about this event: Oracle and other technologies, 2000 visitors, more than 400 sessions, more than 800 abstracts sent, exhibitors… And for me everything started when in June I decided to send an abstract for a MySQL session.

Read More
Elisa Usai

MySQL 8.0 – Roles are finally there

By | Database Administration & Monitoring, MySQL, Security | No Comments

Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL. The most important advantage is to define only once a role that includes a “set of permissions”, then assign it to each user, avoiding wasting time declaring them individually. In MySQL, a role can be created like a user, but without the “identified by” clause and without login: mysqld2-([email protected]) [(none)]> CREATE…

Read More
Elisa Usai

MySQL – Time mismatch between system and log files

By | Database Administration & Monitoring, MySQL | No Comments

I was working on MySQL upgrades to version 5.7 at a customer, when I found out that for my instance, time in the error log file didn’t correspond to local system time: [email protected]:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) [email protected]:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] ls -ltr mysqld1.err -rw-r—–. 1 mysql mysql 13323 Apr 12 10:54 mysqld1.err [email protected]:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] tail -1 mysqld1.err 2018-04-12T08:54:55.300633Z 67 [Note] Access denied for…

Read More
Elisa Usai

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

By | Database Administration & Monitoring, MySQL | No Comments

As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys. In our example, we have the following parent table in a MySQL 5.7.21 server: mysqld3-([email protected]) [sakila]> show create table actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON…

Read More