Structured Query Language and Relational Queries
View All Tags
An ALTER TABLE on a tiny table was still running minutes later, and the connection's state said Waiting for table metadata lock. The cause was the last thing I'd have looked at: a transaction that ran one SELECT and never ended. This is also what a stuck migration usually looks like.
Another session changed a row and committed, and I could see that it worked. In my transaction, the same SELECT kept returning the old number. Nothing's broken: under MySQL's default isolation level, a transaction keeps reading from the snapshot it took at its first read.
A statement ending in \G works when you type it at the mysql> prompt, and fails with ERROR at line 1: Unknown command '\G'. when it comes from --execute or a script. The command didn't go anywhere. The newer client just turns its own commands off when it isn't interactive.
A SELECT stopped after a second with ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded. Nothing crashed: a time limit did its job. The right fix depends on whether the query is too slow, or the limit too strict.
ROLLBACK TO SAVEPOINT failed with ERROR 1305 (42000): SAVEPOINT a does not exist. A savepoint only lasts as long as you let it: releasing it removes it, and so does the end of the transaction.
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction means the statement tries to change something that a read-only transaction isn't allowed to touch. The straightforward case is an UPDATE, and the less obvious one is creating a temporary table.
Setting the isolation level failed with ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress. A transaction's isolation level is decided before it starts, so this one's a question of order.
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set isn't really a failure. You asked MySQL not to wait for a locked row, and the row was locked. The fix is deciding what your code does in that case.
Looking at MySQL's locks failed with ERROR 1142 (42000): SELECT command denied to user 'inventory_app'@'localhost' for table 'data_locks'. The table's fine, and so is the account: it just has no rights outside its own database. Here's the fix, on a separate account, so the application's account stays as it is.
Changing a MySQL setting failed with ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation. The word GLOBAL in the statement is the cause. Most of the time you don't want the whole server changed anyway, and one word fixes it.
