If you get this in MySQL Lock wait timeout exceeded; try restarting transaction
I ran into this crap when I needed to purge a massive number of records from a database. All you need to do is edit the timeout setting in your MySQL config file.
On my Mac it is located at : /etc/my.cnf
Uncomment the line:
innodb_lock_wait_timeout = 50
And change the setting to 500 (its seconds)
Restart mysql and rerun your query.
sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist
If you make this change on a production server you may want to set it back to the default setting. I don’t know what the consequences of a long timeout setting would be.
Categories: MySQL
Hey thanks for the setting info. I have a lot of long transactions so I will be keeping it at about 100 seconds which shouldnt be an issue on my production server.
Wouldn’t you be better served by reducing the query to several small ones using LIMIT? The timeout is there to stop people from flooding your database with huge queries.