Fixes for Common Issues and Errors
View All Tags
ERROR 1044 (42000): Access denied for user 'lock_viewer'@'%' to database 'inventory' looks like a login failure, and it isn't one: your password was right. This account just has no rights on the database you named. It's different from ERROR 1045, and has a different fix.
Reading sys.innodb_lock_waits failed with ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. The view isn't broken. The end of that long message is the real reason: the account lacks a right.
GRANT PROCESS ON inventory.* TO ... failed with ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES. The syntax is fine. Some privileges apply to the whole server, and can't be limited to one database.
A ROLLBACK came back with Query OK, 0 rows affected, 1 warning, and the warning was The creation of some temporary tables could not be rolled back. Your data was rolled back correctly, and only the temporary table stayed. Here's how to see it and avoid it.
Two transactions touch only one product, and in the same order, and they still deadlock with ERROR 1213. The hidden lock comes from the foreign key: inserting a child row takes a shared lock on its parent. When both then try to update the parent, neither can go on. The fix is to swap two lines.
"Lock the row if it's there, and insert it if it isn't" sounds like the careful way to do it. On MySQL, two sessions doing that at once deadlock on the INSERT, with ERROR 1213. Asking for a row that doesn't exist locks a gap, and gap locks don't block each other, until both sessions try to insert.
EXPLAIN UPDATE gave me one line: EXPLAIN: -> <not executable by iterator executor>. It isn't an error, and it isn't useful either. In MySQL 9.7, the default output format can't describe this kind of statement, and the older format can.
Connecting to MySQL failed with ERROR 1040 (HY000): Too many connections. The server is up, and there's no room for you. Find out who's holding the connections before you raise the limit, because a higher limit often just delays the same problem.
SET innodb_rollback_on_timeout = ON failed with ERROR 1238 (HY000): Variable 'innodb_rollback_on_timeout' is a read only variable. Some settings can only be chosen when the server starts. With Docker Compose, that takes one line and a restart.
KILL failed with ERROR 1094 (HY000): Unknown thread id: 99999. No connection has that number. Either it's already gone, or the number came from the wrong column, and MySQL has two kinds of ID that are easy to mix up.
