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…