Author: Suresh Kuna

A few days ago, we faced a Duplicate entry problem in the mysqld server on one of our customer’s slave after cold backup. We made some recommendations to the Customer (read them here) in which the option 3 was chosen as the slave was dedicated for backup’s and a quick solution applicable for their data set and timeline for resolution of the issue.

A few days ago, we faced an interesting problem on one of our customer’s slave mysqld servers. An Alter for adding a new column was run on master server took 542 seconds where as it took few hours on the slave server to complete due to a SELECT blocking the Alter was not allowed to complete. What happened in this situation was: ALTER started first, then concurrent SELECT started on the same table, when ALTER finished copy to a temp table it tried to RENAME table, but failed to get global lock on data dictionary. All the threads that started after this point had to wait on ALTER to finish.

Recently, one of our customers had a problem with one of their replication slaves where a sql statement gave an error while executing on the slave which was executed successfully on the Master server. The slave stopped with the error. We found the main difference in MySQL configuration between Master and Slave was the amount of memory allocated to innodb_buffer_pool_size because Slave was running several MySQL instances that were used for backup purposes. We need to increase the memory of innodb_buffer_pool_size variable where the locks table can fit in the configuration file and restart the database. This should fix the problem.