I’ve been coding up a storm in the last couple days and have just about completed coding on three new INFORMATION_SCHEMA views which allow anyone to query the new Drizzle transaction log for information about its contents. I’ve also finished a new UDF for Drizzle called PRINT_TRANSACTION_MESSAGE() that prints out the Transaction message‘s contents in a easy-to-read format.
I don’t have time for a full walk-through blog entry about it, so I’ll just paste some output below and let y’all take a looksie. A later blog entry will feature lots of source code explaining how you, too, can easily add INFORMATION_SCHEMA views to your Drizzle plugins.
Below is the results of the following sequence of actions:
- Start up a Drizzle server with the transaction log enabled, checksumming enabled, and the default replicator enabled.
- Open a Drizzle client
- Create a sample table, insert some data into it, do an update to that table, then drop the table
- Query the INFORMATION_SCHEMA views and take a look at the transaction messages and information the transaction log now contains
Enjoy! ๐
jpipes@serialcoder:~/repos/drizzle/replication-group-commit/tests$ ./dtr --mysqld="--default-replicator-enable"\ --mysqld="--transaction-log-enable"\ --mysqld="--transaction-log-enable-checksum"\ --start-and-exit ... Servers started, exiting jpipes@serialcoder:~/repos/drizzle/replication-group-commit/tests$ ../client/drizzle --port=9306 Welcome to the Drizzle client.. Commands end with ; or \g. Your Drizzle connection id is 2 Server version: 2009.11.1181 Source distribution (replication-group-commit) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. drizzle> use test Database changed drizzle> CREATE TABLE t1 ( id INT NOT NULL PRIMARY KEY , padding VARCHAR(200) NOT NULL ); Query OK, 0 rows affected (0.01 sec) drizzle> INSERT INTO t1 VALUES (1, "I love testing."); Query OK, 1 row affected (0.01 sec) drizzle> INSERT INTO t1 VALUES (2, "I hate testing."); Query OK, 1 row affected (0.01 sec) drizzle> UPDATE t1 SET padding="I love it when a plan comes together" WHERE id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 drizzle> DROP TABLE t1; Query OK, 0 rows affected (0.17 sec) drizzle> SELECT * FROM INFORMATION_SCHEMA.TRANSACTION_LOG\G *************************** 1. row *************************** FILE_NAME: transaction.log FILE_LENGTH: 639 NUM_LOG_ENTRIES: 5 NUM_TRANSACTIONS: 5 MIN_TRANSACTION_ID: 0 MAX_TRANSACTION_ID: 9 MIN_END_TIMESTAMP: 1257888458463696 MAX_END_TIMESTAMP: 1257888473929116 1 row in set (0 sec) drizzle> SELECT * FROM INFORMATION_SCHEMA.TRANSACTION_LOG_ENTRIES; +--------------+-------------+--------------+ | ENTRY_OFFSET | ENTRY_TYPE | ENTRY_LENGTH | +--------------+-------------+--------------+ | 0 | TRANSACTION | 141 | | 141 | TRANSACTION | 121 | | 262 | TRANSACTION | 121 | | 383 | TRANSACTION | 181 | | 564 | TRANSACTION | 75 | +--------------+-------------+--------------+ 5 rows in set (0 sec) drizzle> SELECT * FROM INFORMATION_SCHEMA.TRANSACTION_LOG_TRANSACTIONS; +--------------+----------------+-----------+------------------+------------------+----------------+------------+ | ENTRY_OFFSET | TRANSACTION_ID | SERVER_ID | START_TIMESTAMP | END_TIMESTAMP | NUM_STATEMENTS | CHECKSUM | +--------------+----------------+-----------+------------------+------------------+----------------+------------+ | 0 | 0 | 1 | 1257888458463668 | 1257888458463696 | 1 | 3275955647 | | 141 | 7 | 1 | 1257888462222183 | 1257888462226990 | 1 | 407829420 | | 262 | 8 | 1 | 1257888465371330 | 1257888465378423 | 1 | 4073072174 | | 383 | 9 | 1 | 1257888470209443 | 1257888470215165 | 1 | 92884681 | | 564 | 9 | 1 | 1257888473929111 | 1257888473929116 | 1 | 2850269133 | +--------------+----------------+-----------+------------------+------------------+----------------+------------+ 5 rows in set (0 sec) drizzle> SELECT PRINT_TRANSACTION_MESSAGE("transaction.log", ENTRY_OFFSET) as trx > FROM INFORMATION_SCHEMA.TRANSACTION_LOG_ENTRIES\G *************************** 1. row *************************** trx: transaction_context { server_id: 1 transaction_id: 0 start_timestamp: 1257888458463668 end_timestamp: 1257888458463696 } statement { type: RAW_SQL start_timestamp: 1257888458463676 end_timestamp: 1257888458463694 sql: "CREATE TABLE t1 ( id INT NOT NULL PRIMARY KEY , padding VARCHAR(200) NOT NULL )" } *************************** 2. row *************************** trx: transaction_context { server_id: 1 transaction_id: 7 start_timestamp: 1257888462222183 end_timestamp: 1257888462226990 } statement { type: INSERT start_timestamp: 1257888462222185 end_timestamp: 1257888462226989 insert_header { table_metadata { schema_name: "test" table_name: "t1" } field_metadata { type: INTEGER name: "id" } field_metadata { type: VARCHAR name: "padding" } } insert_data { segment_id: 1 end_segment: true record { insert_value: "1" insert_value: "I love testing." } } } *************************** 3. row *************************** trx: transaction_context { server_id: 1 transaction_id: 8 start_timestamp: 1257888465371330 end_timestamp: 1257888465378423 } statement { type: INSERT start_timestamp: 1257888465371332 end_timestamp: 1257888465378422 insert_header { table_metadata { schema_name: "test" table_name: "t1" } field_metadata { type: INTEGER name: "id" } field_metadata { type: VARCHAR name: "padding" } } insert_data { segment_id: 1 end_segment: true record { insert_value: "2" insert_value: "I hate testing." } } } *************************** 4. row *************************** trx: transaction_context { server_id: 1 transaction_id: 9 start_timestamp: 1257888470209443 end_timestamp: 1257888470215165 } statement { type: UPDATE start_timestamp: 1257888470209446 end_timestamp: 1257888470215163 update_header { table_metadata { schema_name: "test" table_name: "t1" } key_field_metadata { type: INTEGER name: "id" } set_field_metadata { type: VARCHAR name: "padding" } } update_data { segment_id: 1 end_segment: true record { key_value: "2" key_value: "I love it when a plan comes together" after_value: "I love it when a plan comes together" } } } *************************** 5. row *************************** trx: transaction_context { server_id: 1 transaction_id: 9 start_timestamp: 1257888473929111 end_timestamp: 1257888473929116 } statement { type: RAW_SQL start_timestamp: 1257888473929113 end_timestamp: 1257888473929115 sql: "DROP TABLE `t1`" } 5 rows in set (0.06 sec)
FYI, if you look closely, you’ll see some odd things รขโฌโ namely that there is a transaction with an ID of zero. I’m aware of this and am working on fixing it ๐ Like I said, I’m almost done coding…