Upgrading to MySQL 8? Think of the default authentication plugin

As explained in a post at mysqlserverteam.com the default authentication plugin has been changed from mysql_native_password to caching_sha2_password. And that would certainly break all PHP-based applications because at the time of writing PHP doesn’t support caching_sha2_password. Please, keep an eye on the related request #76243. Once it’s implemented it would be possible to switch to caching_sha2_password but till then use “default_authentication_plugin = mysql_native_password” in your my.cnf file or start mysqld with –default-authentication-plugin= mysql_native_password.

“Change master” to the rescue or how to revive MySQL replication

Sometime ago had an issue with MySQL replication that consecutively faulted with two different errors. Initially with this one:

Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.

Thankfully, this error was easy to fix with the help of change master SQL statement:

change master to master_log_file='mysql-bin.000047', master_log_pos=152667618;

However, the second error kicked in immediately right after that:

Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from impossible position; the first event ‘mysql-bin.000048’ at 223481321, the last event read from ‘/var/log/mysql/mysql-bin.000048’ at 4, the last byte read from ‘/var/log/mysql/mysql-bin.000048′ at 4.’

This one was also a no-brainer if you know how to fix them

Armed with mysqlbinlog it was easy to verify that there were no logs past 223481321 position:

mysqlbinlog --base64-output=decode-rows --verbose --start-position=223481321 ./mysql-bin.000048 
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

Thus the proper solution in that case was to manually point the slave to the next available bin log:

change master to master_log_file='mysql-bin.000049', master_log_pos=4;

mysqldump causes error 2006

Hot on the heels of the recent mysqldump I’ve been running on MySQL 5.5 which ended with the following error:

Got error: 2006: MySQL server has gone away when selecting the database 

Turned out that this error is quite common and there is a quick work around for that – increase max_allowed_packet. In my case going from 16M to 128M was enough.

P.S. There is a helpful thread at DBA Stackexchange which is worth reading too.

What unites DEFINER, “show table status” and ERROR 1143 (42000) in MySQL

For the very first time I had seen this error a couple of days ago:

mysql> show table status;
ERROR 1143 (42000): SELECT command denied to user ''@'some_host_name_here' for column 'sid' in table 'masking'

What the heck was that?!
Turned out that a colleague of mine was doing a cleanup in mysql.user table a day before and deleted a number of non-existent users. So what? Well, the database I was working with didn’t have only “plain” tables:

mysql> show full tables in database_name_here where table_type not like '%table%';
| Tables_in_infra | Table_type |
| v_pool          | VIEW       |
| v_pool_pivot    | VIEW       |
2 rows in set (0.00 sec)

Aha moment! So we had two views. Checked if there was a proper“DEFINER”:

mysql> select TABLE_NAME, DEFINER from information_schema.views;
| TABLE_NAME   | DEFINER                                    |
| v_pool       | some_user@some_host                        |
| v_pool_pivot | some_user@some_host                        |
10 rows in set (0.00 sec)

Of course, that was exactly the user which was deleted and thankfully, that was very easy to fix.
Just had to run “show create view” query to figure out how the view was created in the first place:

mysql> show create view v_pool_pivot\G
*************************** 1. row ***************************
                View: v_pool_pivot
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`some_user@some_host` SQL SECURITY DEFINER VIEW `v_pool_pivot` AS (select followed by several lines of spaghetti SQL)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set, 1 warning (0.00 sec)

And after that just altered it:

mysql> alter DEFINER=CURRENT_USER view v_pool_pivot AS (select ...)

Puzzle solved.