Databases, Data Modeling, and Data Access
View All Tags
I ran ROLLBACK, and MySQL answered Query OK, and my UPDATE was still there. A CREATE TABLE in between had quietly committed it. MySQL doesn't warn you, and the ROLLBACK has nothing to report.
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.
