0

I have an application to debug which performs a few dozen operations against a MySQL database. These operations are performed in a single transaction. I need to know the state of the database while debugging at a specific breakpoint.

I can log into the MySQL service using the common mysql CLI tool. I see that a transaction in progress:

mysql> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 325090
                 trx_state: RUNNING
               trx_started: 2022-09-14 12:10:32
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 30
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 10
          trx_lock_structs: 26
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 43
         trx_rows_modified: 4
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.01 sec)

Now, how can I join that transaction to query the state of the database during the transaction?

As a workaround, I could add a query to the application code. However that requires me to know which query I want to run before running the application, and then to recompile the app for each query I want to run. Having an open CLI to debug would be very helpful.

The database is MySQL 8.0 running on Debian.

13
  • 1
    Though I guess the term you're looking for is a "dirty read" and maybe the Read Uncommitted Isolation level is what you're looking for.
    – J.D.
    Sep 14, 2022 at 13:39
  • 1
    Aside from the read-uncommitted isolation level that J.D. mentioned, MySQL has no feature to allow you to inspect data as if you're in the transaction of another session. In other words, MySQL is not a debugger. Sep 14, 2022 at 20:18
  • 1
    @J.D. Thank you, that seems to be exactly what I am looking for. It doesn't let me target a specific transaction, but as this is a dev environment only a single transaction is running anyway. Thank you!
    – dotancohen
    Sep 15, 2022 at 14:29
  • 1
    @dotancohen - If you spend more than innodb_lock_wait_timeout, the query will be rolled back! This could confuse your analysis. Also, if the debugger lets you run SQL, that will probably be a disaster.
    – Rick James
    Sep 15, 2022 at 18:21
  • 1
    @dotancohen - The default is 50 (seconds), but I think that even 5 is too large. That is, hanging around waiting 50 seconds is likely to cause all sorts of other problems with other connections.
    – Rick James
    Sep 16, 2022 at 22:06

1 Answer 1

1

User J.D. answered in a comment, I'm putting the info here for my future self and others. If J.D. posts back with an answer, I'll accept it.

The term for reading uncommited transactions is Dirty Read. To perform a dirty read, the Read Uncommitted Isolation level needs to be enabled.

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1
  • 1
    You said the extent of everything I know about it. This one's all yours. 🙂
    – J.D.
    Sep 15, 2022 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.