MySQL Troubleshooting

Status Checks

MySQL Performance Tuning
$ mysqladmin debug - dumps some information about locks in use, used memory and query usage to the MySQL log file. This may help solve some problems. This command also provides some useful information even if you haven’t compiled MySQL for debugging!

show status of InnoDB


show current running processes

unix# mysqladmin processlist status

Corrupt Tables

Sometimes detected by unable to open foo.myi file errors in logs
myisamchk path to something.MYI - check and repair the table file
As described in manual myisamchk must be never used when mysqld is also running.
Note that even myisamchk message contains reference to this issue:
warning: 1 clients is using or hasn’t closed the table properly

A “client using” it in this case usually means mysqld itself which is running.
Please confirm if you have ran myisamchk after shutting down mysqld. If you had both running at the same time this is not a bug, but expected behaviour.

On the contrast mysqlcheck can be used with running mysqld as it sends CHECK or REPAIR statements to mysqld.
mysqlcheck -v -uusername -p -A dbname tablename

A corrupt table can also be seen by trying to do a describe on the table. If it fails: mysql> repair table <tablename>; can be tried Further reading at dev.mysql.com

replication failing

Last_error: there is an unfinished transaction in the relay log (could find neither COMMIT nor ROLLBACK in the relay log); it could be that the master died while writing the transaction to its binary log. Now the slave is rolling back the transaction.: set global sql_slave_skip_counter=1; show slave status \G; stop slave start slave ; show slave status \G; mysql> show master logs; - checks what the log files are called for the replication. +----+ | Log_name | +----+ | CPS_db.001 | | CPS_db.002 |

Checking Errors:

sudo tail -10 /var/db/mysql/mk-nn-radproxy-3.err 080416 5:30:42 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 080416 5:30:42 Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mk-dslmaster-1-bin.004’ position 843665557 080416 6:45:14 Slave I/O thread exiting, read up to log ‘mk-dslmaster-1-bin.004’, position 846015097 080416 6:45:20 Slave I/O thread: connected to master ‘repl@’, replication started in log ‘mk-dslmaster-1-bin.004’ at position 846015097 081028 3:02:38 Note Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘mysql-master-bin.001’ position 73 081028 3:02:38 Note Slave: connected to master ‘tmcscripts@’,replication resumed in log ‘mysql-master-bin.001’ at position 73 081028 3:02:38 Note Slave: received end packet from server, apparent master shutdown: Could be caused by both servers having the same server-id

Client does not support authentication protocol requested by server

consider upgrading MySQL client
unix_cli> mysql -uusername -p -e”SET PASSWORD FOR ‘username’@’host’ = OLD_PASSWORD(‘password’);” database_name

Illegal mix of collations

mysql> ALTER DATABASE problem_db CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.05 sec)

mysql> ALTER TABLE problem_db.problem_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 177 rows affected (0.68 sec)
Records: 177  Duplicates: 0  Warnings: 0