Wednesday, September 11, 2013

RESOLVED: who has locked mysql table?!

Occasionally appears error message “Lock wait timeout exceeded, INSERT INTO table failed.” between 01am and 05am in one of the applications. Can you imagine DBA the whole night long sitting  and waiting for error message? I also can’t, but need to find the reason of lock. Thus when new record in error log appears it should be raised (automatically) some useful SQL commands. In unix world you can use inotifywait/gamin to watch file changes and mysql client to execute sql commands.
 My biggest problem was the mysql server running on windows platform and absence of nice utilities. On my luck windows has FindFirstChangeNotification WinApi function. I have created small tool which listens a directory for changes and execute

SHOW FULL PROCESSLIST

SHOW OPEN TABLES

SHOW ENGINE INNODB STATUS

 Results will be written to console.

Usage MySql_Monitor.exe -h HOST -u USERNAME -pSOMEPASS -d DBNAME c:\tmp\WATCHED_DIR\

Example: MySql_Monitor.exe -h localhost -u root -p -d somedb c:\tmp\log\ > output.txt

You can take exe file or sources here.
Enjoy!

No comments: