myMYSQL Nightmare Continues

My fight this week with mySQL continues. I started getting really strange errors in the error log, and users couldn’t connect, not even root@localhost.

The error log showed

2016-09-30T15:04:08.790598Z 0 [Warning] System table 'time_zone_leap_second' is expected to be transactional.
2016-09-30T15:04:08.790625Z 0 [Warning] System table 'time_zone_name' is expected to be transactional.
2016-09-30T15:04:08.790628Z 0 [Warning] System table 'time_zone' is expected to be transactional.
2016-09-30T15:04:08.790643Z 0 [Warning] System table 'time_zone_transition_type' is expected to be transactional.
2016-09-30T15:04:08.790646Z 0 [Warning] System table 'time_zone_transition' is expected to be transactional.
2016-09-30T15:04:08.793672Z 0 [Warning] System table 'servers' is expected to be transactional.
2016-09-30T15:04:08.800142Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2016-09-30T15:04:08.803287Z 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.

Followed by logins failing

2016-09-30T15:14:27.945521Z 4 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-30T15:14:35.794051Z 5 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-30T15:14:50.242206Z 6 [Note] Access denied for user 'root'@'localhost' (using password: YES)

What I think had happened, mySQL was upgraded on the OS by the linux admins. It doesn’t look like anything inside the database was upgrade.

The new version of mySQL uses the innodb engine (v5.6.15)

mysql> show create table time_zone\G
*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones'
1 row in set (0.00 sec)

Whereas the older version of mySQL uses MyISAM (from another server v5.5.50)

[mysql]> show create table time_zone\G
*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'

mysql was expecting INNODB and found MyISAM

This should be a simple fix, just run the mysql_upgrade script. However the database is either down, or I can’t log in when the database is up.

So I had to shutdown the database, start it up skipping permissions, and change my root password

mysqld --skip-grant-tables --skip-networking

in another session I was now able to connect to the database

mysql -u root

and update the root password

FLUSH PRIVILEGES;
SET PASSWORD FOR root@'localhost' = PASSWORD('password');

then killed off my instance, and started it back up

service mysqld start

run the mysql_upgrade script

msyql_upgrade -uroot -pPassword

and restart once more

service mysqld restart

I don’t know what wrong with the passwords at this point, but none of the users could log in, so I changed all of their passwords to their current passwords. Just rewriting the password seemed to do the trick.

CREATE USER 'dba_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

Everything seems to be back to normal…