- developers - lists.mariadb.org

[Maria-developers] New (by Bothorsen): Fix table_cache negative scalability (73)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Fix table_cache negative scalability CREATION DATE..: Fri, 18 Dec 2009, 16:31 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 73 (http://askmonty.org/worklog/?tid=73) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Fix the problem described in this blog entry: http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalabi… You can read the blog, or the text below. --- quoted text --- November 16, 2009 table_cache negative scalability Posted by peter | Vote on Planet MySQL Couple of months ago there was a post by FreshBooks on getting great performance improvements by lowering table_cache variable. So I decided to investigate what is really happening here. The common sense approach to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL common sense however does not always works weve seen performance issues with large query_cache_size also sort_buffer_size and read_buffer_size may not give you better performance if you increase them. I found this also applies to some other buffers. Even though having previous experience of surprised behavior I did not expect such a table_cache issue the LRU for cache management is classics and there are scalable algorithms to deal with it. I would expect Monty to implement one of them. To do the test I have created 100.000 empty tables containing single integer column and no indexes and when ran SELECT * FROM tableN in the loop. Each table in such case is accessed only once and on any but first run each access would require table replacement in table cache based on LRU logic. MySQL Sandbox helped me to test this with different servers easily. I did test on CentOS 5.3, Xeon E5405, 16GB RAM and EXT3 file system on the SATA hard drive. MySQL 5.0.85 Created 100.000 tables in around 3min 40 sec which is about 450 tables/sec This indicates the fsync is lying on this test system as default sync_frm option is used. With default table_cache=64 accessing all tables take 12 sec which is almost 8500 tables/sec which is a great speed. We can note significant writes to the disk during this read-only benchmark. Why ? Because for MyISAM tables table header has to be modified each time the table is opened. In this case the performance was so great because all 100.000 tables data (first block of index) was placed close by on disk as well as fully cached which made updates to headers very slow. In the production systems with table headers not in OS cache you often will see significantly low numbers 100 or less. With significantly larger table_cache=16384 (and appropriately adjusted number of open files) the same operation takes 660 seconds which is 151 tables/sec which is around 50 times slower. Wow. This is the slow down. We can see the load becomes very CPU bound in this case and it looks like some of the table_cache algorithms do not scale well. The absolute numbers are also very interesting 151 tables/sec is not that bad if you look at it as an absolute number. So if you tune table cache is normal case and is able to bring down your miss rate (opened_tables) to 10/sec or less by using large table_cache you should do so. However if you have so many tables you still see 100+ misses/sec while your data (at least table headers) is well cached so the cost of table cache miss is not very high, you may be better of with significantly reduced table cache size. The next step for me was to see if the problem was fixed in MySQL 5.1 in this version table_cache was significantly redone and split in table_open_cache and table_definition_cache and I assumed the behavior may be different as well. MySQL 5.1.40 I started testing with default table_open_cache=64 and table_definition_cache=256 the read took about 12 seconds very close to MySQL 5.0.85. As I increased table_definition_cache to 16384 result remained the same so this variable is not causing the bottleneck. However increasing table_open_cache to 16384 causes scan to take about 780 sec which is a bit worse than MySQL 5.0.85. So the problem is not fixed in MySQL 5.1, lets see how MySQL 5.4 behaves. MySQL 5.4.2 MySQL 5.4.2 has higher default table_open_cache so I took it down to 64 so we can compare apples to apples. It performs same as MySQL 5.0 and MySQL 5.1 with small table cache. With table_open_cache increased to 16384 the test took 750 seconds so the problem exists in MySQL 5.4 as well. So the problem is real and it is not fixed even in Performance focused MySQL 5.4. As we can see large table_cache (or table_open_cache_ values indeed can cause significant performance problems. Interesting enough Innodb has a very similar task of managing its own cache of file descriptors (set by innodb_open_files) As the time allows I should test if Heikki knows how to implement LRU properly so it does not have problem with large number. Well see. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 20 ESTIMATE.......: 35 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:22)=-=- Add estimation time. Worked 5 hours and estimate 35 hours remain (original estimate increased by 5 hours). -=-=(Bothorsen - Fri, 18 Dec 2009, 16:16)=-=- This is the work done on this patch so far. Most of it done by Alex. Worked 15 hours and estimate 035 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Fri, 04 Dec 2009, 13:00)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.14001 2009-12-04 13:00:24.000000000 +0200 +++ /tmp/wklog.47.new.14001 2009-12-04 13:00:24.000000000 +0200 @@ -6,27 +6,27 @@ New server option ~~~~~~~~~~~~~~~~~ - --binlog-annotate-row-events + --binlog-annotate-rows-events -Setting this option makes RBR (row-) events in the binary log to be +Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding -'binlog_annotate_row_events' system variable is dynamic and has both +'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... - SET SESSION binlog_annotate_row_events=ON; + SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... - SET SESSION binlog_annotate_row_events=OFF; + SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] -Describes the query which caused the corresponding row event. In binary log, +Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. @@ -79,6 +79,15 @@ 0000012F | 0F 00 00 00 | table_id = 15 ... +New mysqlbinlog option +~~~~~~~~~~~~~~~~~~~~~~ + --print-annotate-rows-events + +With this option, mysqlbinlog prints the content of Annotate-rows +events (if the binary log does contain them). Without this option +(i.e. by default), mysqlbinlog skips Annotate rows events. + + mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: @@ -109,5 +118,5 @@ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has - both log-slave-updates and binlog-annotate-row-events options set. + both log-slave-updates and binlog-annotate-rows-events options set. -=-=(Alexi - Wed, 02 Dec 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.47.old.17456 2009-12-02 13:32:18.000000000 +0200 +++ /tmp/wklog.47.new.17456 2009-12-02 13:32:18.000000000 +0200 @@ -1,8 +1 @@ -mysql_binlog_send() [sql/sql_repl.cc] -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -1. When sending events to a slave, master should simply skip - Annotate_rows events (they are not needed for replication). - [ ??? Multi-master - currently not clear ] -2. When sending events to mysqlbinlog (remote case), master - must send Annotate_rows events as well. -=-=(Alexi - Wed, 02 Dec 2009, 13:31)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.17414 2009-12-02 11:31:59.000000000 +0000 +++ /tmp/wklog.47.new.17414 2009-12-02 11:31:59.000000000 +0000 @@ -104,3 +104,10 @@ ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... +When master sends Annotate rows events +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. Master always sends Annotate_rows events to mysqlbinlog (in + remote case). +2. Master sends Annotate_rows events to a slave only if the slave has + both log-slave-updates and binlog-annotate-row-events options set. + -=-=(Knielsen - Mon, 30 Nov 2009, 11:21)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.18210 2009-11-30 11:21:33.000000000 +0200 +++ /tmp/wklog.47.new.18210 2009-11-30 11:21:33.000000000 +0200 @@ -28,7 +28,14 @@ Describes the query which caused the corresponding row event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query -text in its data part. Example: +text in its data part. + +The numeric code for this event must be assigned carefully. It should be +coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL +uses the same numeric code for one event that MariaDB uses for +ANNOTATE_ROWS_EVENT, which would make merging the two impossible. + +Example: ... ************************ -=-=(Alexi - Mon, 30 Nov 2009, 10:33)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16188 2009-11-30 10:33:44.000000000 +0200 +++ /tmp/wklog.47.new.16188 2009-11-30 10:33:44.000000000 +0200 @@ -2,6 +2,7 @@ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. When sending events to a slave, master should simply skip Annotate_rows events (they are not needed for replication). + [ ??? Multi-master - currently not clear ] 2. When sending events to mysqlbinlog (remote case), master must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 13:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.32047 2009-11-29 13:00:21.000000000 +0200 +++ /tmp/wklog.47.new.32047 2009-11-29 13:00:21.000000000 +0200 @@ -1 +1,7 @@ +mysql_binlog_send() [sql/sql_repl.cc] +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. When sending events to a slave, master should simply skip + Annotate_rows events (they are not needed for replication). +2. When sending events to mysqlbinlog (remote case), master + must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 09:50)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.24993 2009-11-29 07:50:36.000000000 +0000 +++ /tmp/wklog.47.new.24993 2009-11-29 07:50:36.000000000 +0000 @@ -4,3 +4,96 @@ > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). +New server option +~~~~~~~~~~~~~~~~~ + --binlog-annotate-row-events + +Setting this option makes RBR (row-) events in the binary log to be +preceded by Annotate rows events (see below). The corresponding +'binlog_annotate_row_events' system variable is dynamic and has both +global and session values. Default global value is OFF. + +Note. Session values are usefull to make it possible to annotate only + some selected statements: + + ... + SET SESSION binlog_annotate_row_events=ON; + ... statements to be annotated ... + SET SESSION binlog_annotate_row_events=OFF; + ... statements not to be annotated ... + +New binlog event type +~~~~~~~~~~~~~~~~~~~~~ + Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] + +Describes the query which caused the corresponding row event. In binary log, +precedes each Table_map_log_event. Contains empty post-header and the query +text in its data part. Example: + + ... + ************************ + ANNOTATE_ROWS_EVENT [51] + ************************ + 000000C7 | 54 1B 12 4B | time_when = 1259477844 + 000000CB | 33 | event_type = 51 + 000000CC | 64 00 00 00 | server_id = 100 + 000000D0 | 2C 00 00 00 | event_len = 44 + 000000D4 | F3 00 00 00 | log_pos = 000000F3 + 000000D8 | 00 00 | flags = <none> + ------------------------ + 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" + 000000DE | 72 74 20 69 | + 000000E2 | 6E 74 6F 20 | + 000000E6 | 74 31 20 76 | + 000000EA | 61 6C 75 65 | + 000000EE | 73 20 28 31 | + 000000F2 | 29 | + ************************ + TABLE_MAP_EVENT [19] + ************************ + 000000F3 | 54 1B 12 4B | time_when = 1259477844 + 000000F7 | 13 | event_type = 19 + 000000F8 | 64 00 00 00 | server_id = 100 + 000000FC | 29 00 00 00 | event_len = 41 + 00000100 | 1C 01 00 00 | log_pos = 0000011C + 00000104 | 00 00 | flags = <none> + ------------------------ + ... + ************************ + WRITE_ROWS_EVENT [23] + ************************ + 0000011C | 54 1B 12 4B | time_when = 1259477844 + 00000120 | 17 | event_type = 23 + 00000121 | 64 00 00 00 | server_id = 100 + 00000125 | 22 00 00 00 | event_len = 34 + 00000129 | 3E 01 00 00 | log_pos = 0000013E + 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F + ------------------------ + 0000012F | 0F 00 00 00 | table_id = 15 + ... + +mysqlbinlog output +~~~~~~~~~~~~~~~~~~ +Something like this: + + ... + # at 199 + # at 243 + # at 284 + #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values +(1)` + #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped +to number 15 + #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 +flags: STMT_END_F + + BINLOG ' + VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= + VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= + VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== + '/*!*/; + ### INSERT INTO test.t1 + ### SET + ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ + ... + -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen ------------------------------------------------------------ -=-=(View All Progress Notes, 12 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). New server option ~~~~~~~~~~~~~~~~~ --binlog-annotate-rows-events Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding 'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Example: ... ************************ ANNOTATE_ROWS_EVENT [51] ************************ 000000C7 | 54 1B 12 4B | time_when = 1259477844 000000CB | 33 | event_type = 51 000000CC | 64 00 00 00 | server_id = 100 000000D0 | 2C 00 00 00 | event_len = 44 000000D4 | F3 00 00 00 | log_pos = 000000F3 000000D8 | 00 00 | flags = <none> ------------------------ 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" 000000DE | 72 74 20 69 | 000000E2 | 6E 74 6F 20 | 000000E6 | 74 31 20 76 | 000000EA | 61 6C 75 65 | 000000EE | 73 20 28 31 | 000000F2 | 29 | ************************ TABLE_MAP_EVENT [19] ************************ 000000F3 | 54 1B 12 4B | time_when = 1259477844 000000F7 | 13 | event_type = 19 000000F8 | 64 00 00 00 | server_id = 100 000000FC | 29 00 00 00 | event_len = 41 00000100 | 1C 01 00 00 | log_pos = 0000011C 00000104 | 00 00 | flags = <none> ------------------------ ... ************************ WRITE_ROWS_EVENT [23] ************************ 0000011C | 54 1B 12 4B | time_when = 1259477844 00000120 | 17 | event_type = 23 00000121 | 64 00 00 00 | server_id = 100 00000125 | 22 00 00 00 | event_len = 34 00000129 | 3E 01 00 00 | log_pos = 0000013E 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F ------------------------ 0000012F | 0F 00 00 00 | table_id = 15 ... New mysqlbinlog option ~~~~~~~~~~~~~~~~~~~~~~ --print-annotate-rows-events With this option, mysqlbinlog prints the content of Annotate-rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate rows events. mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: ... # at 199 # at 243 # at 284 #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values (1)` #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped to number 15 #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 flags: STMT_END_F BINLOG ' VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== '/*!*/; ### INSERT INTO test.t1 ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... When master sends Annotate rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has both log-slave-updates and binlog-annotate-rows-events options set. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 20 ESTIMATE.......: 35 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:22)=-=- Add estimation time. Worked 5 hours and estimate 35 hours remain (original estimate increased by 5 hours). -=-=(Bothorsen - Fri, 18 Dec 2009, 16:16)=-=- This is the work done on this patch so far. Most of it done by Alex. Worked 15 hours and estimate 035 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Fri, 04 Dec 2009, 13:00)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.14001 2009-12-04 13:00:24.000000000 +0200 +++ /tmp/wklog.47.new.14001 2009-12-04 13:00:24.000000000 +0200 @@ -6,27 +6,27 @@ New server option ~~~~~~~~~~~~~~~~~ - --binlog-annotate-row-events + --binlog-annotate-rows-events -Setting this option makes RBR (row-) events in the binary log to be +Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding -'binlog_annotate_row_events' system variable is dynamic and has both +'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... - SET SESSION binlog_annotate_row_events=ON; + SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... - SET SESSION binlog_annotate_row_events=OFF; + SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] -Describes the query which caused the corresponding row event. In binary log, +Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. @@ -79,6 +79,15 @@ 0000012F | 0F 00 00 00 | table_id = 15 ... +New mysqlbinlog option +~~~~~~~~~~~~~~~~~~~~~~ + --print-annotate-rows-events + +With this option, mysqlbinlog prints the content of Annotate-rows +events (if the binary log does contain them). Without this option +(i.e. by default), mysqlbinlog skips Annotate rows events. + + mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: @@ -109,5 +118,5 @@ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has - both log-slave-updates and binlog-annotate-row-events options set. + both log-slave-updates and binlog-annotate-rows-events options set. -=-=(Alexi - Wed, 02 Dec 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.47.old.17456 2009-12-02 13:32:18.000000000 +0200 +++ /tmp/wklog.47.new.17456 2009-12-02 13:32:18.000000000 +0200 @@ -1,8 +1 @@ -mysql_binlog_send() [sql/sql_repl.cc] -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -1. When sending events to a slave, master should simply skip - Annotate_rows events (they are not needed for replication). - [ ??? Multi-master - currently not clear ] -2. When sending events to mysqlbinlog (remote case), master - must send Annotate_rows events as well. -=-=(Alexi - Wed, 02 Dec 2009, 13:31)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.17414 2009-12-02 11:31:59.000000000 +0000 +++ /tmp/wklog.47.new.17414 2009-12-02 11:31:59.000000000 +0000 @@ -104,3 +104,10 @@ ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... +When master sends Annotate rows events +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. Master always sends Annotate_rows events to mysqlbinlog (in + remote case). +2. Master sends Annotate_rows events to a slave only if the slave has + both log-slave-updates and binlog-annotate-row-events options set. + -=-=(Knielsen - Mon, 30 Nov 2009, 11:21)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.18210 2009-11-30 11:21:33.000000000 +0200 +++ /tmp/wklog.47.new.18210 2009-11-30 11:21:33.000000000 +0200 @@ -28,7 +28,14 @@ Describes the query which caused the corresponding row event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query -text in its data part. Example: +text in its data part. + +The numeric code for this event must be assigned carefully. It should be +coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL +uses the same numeric code for one event that MariaDB uses for +ANNOTATE_ROWS_EVENT, which would make merging the two impossible. + +Example: ... ************************ -=-=(Alexi - Mon, 30 Nov 2009, 10:33)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16188 2009-11-30 10:33:44.000000000 +0200 +++ /tmp/wklog.47.new.16188 2009-11-30 10:33:44.000000000 +0200 @@ -2,6 +2,7 @@ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. When sending events to a slave, master should simply skip Annotate_rows events (they are not needed for replication). + [ ??? Multi-master - currently not clear ] 2. When sending events to mysqlbinlog (remote case), master must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 13:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.32047 2009-11-29 13:00:21.000000000 +0200 +++ /tmp/wklog.47.new.32047 2009-11-29 13:00:21.000000000 +0200 @@ -1 +1,7 @@ +mysql_binlog_send() [sql/sql_repl.cc] +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. When sending events to a slave, master should simply skip + Annotate_rows events (they are not needed for replication). +2. When sending events to mysqlbinlog (remote case), master + must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 09:50)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.24993 2009-11-29 07:50:36.000000000 +0000 +++ /tmp/wklog.47.new.24993 2009-11-29 07:50:36.000000000 +0000 @@ -4,3 +4,96 @@ > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). +New server option +~~~~~~~~~~~~~~~~~ + --binlog-annotate-row-events + +Setting this option makes RBR (row-) events in the binary log to be +preceded by Annotate rows events (see below). The corresponding +'binlog_annotate_row_events' system variable is dynamic and has both +global and session values. Default global value is OFF. + +Note. Session values are usefull to make it possible to annotate only + some selected statements: + + ... + SET SESSION binlog_annotate_row_events=ON; + ... statements to be annotated ... + SET SESSION binlog_annotate_row_events=OFF; + ... statements not to be annotated ... + +New binlog event type +~~~~~~~~~~~~~~~~~~~~~ + Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] + +Describes the query which caused the corresponding row event. In binary log, +precedes each Table_map_log_event. Contains empty post-header and the query +text in its data part. Example: + + ... + ************************ + ANNOTATE_ROWS_EVENT [51] + ************************ + 000000C7 | 54 1B 12 4B | time_when = 1259477844 + 000000CB | 33 | event_type = 51 + 000000CC | 64 00 00 00 | server_id = 100 + 000000D0 | 2C 00 00 00 | event_len = 44 + 000000D4 | F3 00 00 00 | log_pos = 000000F3 + 000000D8 | 00 00 | flags = <none> + ------------------------ + 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" + 000000DE | 72 74 20 69 | + 000000E2 | 6E 74 6F 20 | + 000000E6 | 74 31 20 76 | + 000000EA | 61 6C 75 65 | + 000000EE | 73 20 28 31 | + 000000F2 | 29 | + ************************ + TABLE_MAP_EVENT [19] + ************************ + 000000F3 | 54 1B 12 4B | time_when = 1259477844 + 000000F7 | 13 | event_type = 19 + 000000F8 | 64 00 00 00 | server_id = 100 + 000000FC | 29 00 00 00 | event_len = 41 + 00000100 | 1C 01 00 00 | log_pos = 0000011C + 00000104 | 00 00 | flags = <none> + ------------------------ + ... + ************************ + WRITE_ROWS_EVENT [23] + ************************ + 0000011C | 54 1B 12 4B | time_when = 1259477844 + 00000120 | 17 | event_type = 23 + 00000121 | 64 00 00 00 | server_id = 100 + 00000125 | 22 00 00 00 | event_len = 34 + 00000129 | 3E 01 00 00 | log_pos = 0000013E + 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F + ------------------------ + 0000012F | 0F 00 00 00 | table_id = 15 + ... + +mysqlbinlog output +~~~~~~~~~~~~~~~~~~ +Something like this: + + ... + # at 199 + # at 243 + # at 284 + #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values +(1)` + #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped +to number 15 + #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 +flags: STMT_END_F + + BINLOG ' + VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= + VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= + VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== + '/*!*/; + ### INSERT INTO test.t1 + ### SET + ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ + ... + -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen ------------------------------------------------------------ -=-=(View All Progress Notes, 12 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). New server option ~~~~~~~~~~~~~~~~~ --binlog-annotate-rows-events Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding 'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Example: ... ************************ ANNOTATE_ROWS_EVENT [51] ************************ 000000C7 | 54 1B 12 4B | time_when = 1259477844 000000CB | 33 | event_type = 51 000000CC | 64 00 00 00 | server_id = 100 000000D0 | 2C 00 00 00 | event_len = 44 000000D4 | F3 00 00 00 | log_pos = 000000F3 000000D8 | 00 00 | flags = <none> ------------------------ 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" 000000DE | 72 74 20 69 | 000000E2 | 6E 74 6F 20 | 000000E6 | 74 31 20 76 | 000000EA | 61 6C 75 65 | 000000EE | 73 20 28 31 | 000000F2 | 29 | ************************ TABLE_MAP_EVENT [19] ************************ 000000F3 | 54 1B 12 4B | time_when = 1259477844 000000F7 | 13 | event_type = 19 000000F8 | 64 00 00 00 | server_id = 100 000000FC | 29 00 00 00 | event_len = 41 00000100 | 1C 01 00 00 | log_pos = 0000011C 00000104 | 00 00 | flags = <none> ------------------------ ... ************************ WRITE_ROWS_EVENT [23] ************************ 0000011C | 54 1B 12 4B | time_when = 1259477844 00000120 | 17 | event_type = 23 00000121 | 64 00 00 00 | server_id = 100 00000125 | 22 00 00 00 | event_len = 34 00000129 | 3E 01 00 00 | log_pos = 0000013E 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F ------------------------ 0000012F | 0F 00 00 00 | table_id = 15 ... New mysqlbinlog option ~~~~~~~~~~~~~~~~~~~~~~ --print-annotate-rows-events With this option, mysqlbinlog prints the content of Annotate-rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate rows events. mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: ... # at 199 # at 243 # at 284 #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values (1)` #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped to number 15 #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 flags: STMT_END_F BINLOG ' VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== '/*!*/; ### INSERT INTO test.t1 ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... When master sends Annotate rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has both log-slave-updates and binlog-annotate-rows-events options set. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 20 ESTIMATE.......: 35 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:22)=-=- Add estimation time. Worked 5 hours and estimate 35 hours remain (original estimate increased by 5 hours). -=-=(Bothorsen - Fri, 18 Dec 2009, 16:16)=-=- This is the work done on this patch so far. Most of it done by Alex. Worked 15 hours and estimate 035 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Fri, 04 Dec 2009, 13:00)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.14001 2009-12-04 13:00:24.000000000 +0200 +++ /tmp/wklog.47.new.14001 2009-12-04 13:00:24.000000000 +0200 @@ -6,27 +6,27 @@ New server option ~~~~~~~~~~~~~~~~~ - --binlog-annotate-row-events + --binlog-annotate-rows-events -Setting this option makes RBR (row-) events in the binary log to be +Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding -'binlog_annotate_row_events' system variable is dynamic and has both +'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... - SET SESSION binlog_annotate_row_events=ON; + SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... - SET SESSION binlog_annotate_row_events=OFF; + SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] -Describes the query which caused the corresponding row event. In binary log, +Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. @@ -79,6 +79,15 @@ 0000012F | 0F 00 00 00 | table_id = 15 ... +New mysqlbinlog option +~~~~~~~~~~~~~~~~~~~~~~ + --print-annotate-rows-events + +With this option, mysqlbinlog prints the content of Annotate-rows +events (if the binary log does contain them). Without this option +(i.e. by default), mysqlbinlog skips Annotate rows events. + + mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: @@ -109,5 +118,5 @@ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has - both log-slave-updates and binlog-annotate-row-events options set. + both log-slave-updates and binlog-annotate-rows-events options set. -=-=(Alexi - Wed, 02 Dec 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.47.old.17456 2009-12-02 13:32:18.000000000 +0200 +++ /tmp/wklog.47.new.17456 2009-12-02 13:32:18.000000000 +0200 @@ -1,8 +1 @@ -mysql_binlog_send() [sql/sql_repl.cc] -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -1. When sending events to a slave, master should simply skip - Annotate_rows events (they are not needed for replication). - [ ??? Multi-master - currently not clear ] -2. When sending events to mysqlbinlog (remote case), master - must send Annotate_rows events as well. -=-=(Alexi - Wed, 02 Dec 2009, 13:31)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.17414 2009-12-02 11:31:59.000000000 +0000 +++ /tmp/wklog.47.new.17414 2009-12-02 11:31:59.000000000 +0000 @@ -104,3 +104,10 @@ ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... +When master sends Annotate rows events +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. Master always sends Annotate_rows events to mysqlbinlog (in + remote case). +2. Master sends Annotate_rows events to a slave only if the slave has + both log-slave-updates and binlog-annotate-row-events options set. + -=-=(Knielsen - Mon, 30 Nov 2009, 11:21)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.18210 2009-11-30 11:21:33.000000000 +0200 +++ /tmp/wklog.47.new.18210 2009-11-30 11:21:33.000000000 +0200 @@ -28,7 +28,14 @@ Describes the query which caused the corresponding row event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query -text in its data part. Example: +text in its data part. + +The numeric code for this event must be assigned carefully. It should be +coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL +uses the same numeric code for one event that MariaDB uses for +ANNOTATE_ROWS_EVENT, which would make merging the two impossible. + +Example: ... ************************ -=-=(Alexi - Mon, 30 Nov 2009, 10:33)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16188 2009-11-30 10:33:44.000000000 +0200 +++ /tmp/wklog.47.new.16188 2009-11-30 10:33:44.000000000 +0200 @@ -2,6 +2,7 @@ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. When sending events to a slave, master should simply skip Annotate_rows events (they are not needed for replication). + [ ??? Multi-master - currently not clear ] 2. When sending events to mysqlbinlog (remote case), master must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 13:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.32047 2009-11-29 13:00:21.000000000 +0200 +++ /tmp/wklog.47.new.32047 2009-11-29 13:00:21.000000000 +0200 @@ -1 +1,7 @@ +mysql_binlog_send() [sql/sql_repl.cc] +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. When sending events to a slave, master should simply skip + Annotate_rows events (they are not needed for replication). +2. When sending events to mysqlbinlog (remote case), master + must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 09:50)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.24993 2009-11-29 07:50:36.000000000 +0000 +++ /tmp/wklog.47.new.24993 2009-11-29 07:50:36.000000000 +0000 @@ -4,3 +4,96 @@ > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). +New server option +~~~~~~~~~~~~~~~~~ + --binlog-annotate-row-events + +Setting this option makes RBR (row-) events in the binary log to be +preceded by Annotate rows events (see below). The corresponding +'binlog_annotate_row_events' system variable is dynamic and has both +global and session values. Default global value is OFF. + +Note. Session values are usefull to make it possible to annotate only + some selected statements: + + ... + SET SESSION binlog_annotate_row_events=ON; + ... statements to be annotated ... + SET SESSION binlog_annotate_row_events=OFF; + ... statements not to be annotated ... + +New binlog event type +~~~~~~~~~~~~~~~~~~~~~ + Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] + +Describes the query which caused the corresponding row event. In binary log, +precedes each Table_map_log_event. Contains empty post-header and the query +text in its data part. Example: + + ... + ************************ + ANNOTATE_ROWS_EVENT [51] + ************************ + 000000C7 | 54 1B 12 4B | time_when = 1259477844 + 000000CB | 33 | event_type = 51 + 000000CC | 64 00 00 00 | server_id = 100 + 000000D0 | 2C 00 00 00 | event_len = 44 + 000000D4 | F3 00 00 00 | log_pos = 000000F3 + 000000D8 | 00 00 | flags = <none> + ------------------------ + 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" + 000000DE | 72 74 20 69 | + 000000E2 | 6E 74 6F 20 | + 000000E6 | 74 31 20 76 | + 000000EA | 61 6C 75 65 | + 000000EE | 73 20 28 31 | + 000000F2 | 29 | + ************************ + TABLE_MAP_EVENT [19] + ************************ + 000000F3 | 54 1B 12 4B | time_when = 1259477844 + 000000F7 | 13 | event_type = 19 + 000000F8 | 64 00 00 00 | server_id = 100 + 000000FC | 29 00 00 00 | event_len = 41 + 00000100 | 1C 01 00 00 | log_pos = 0000011C + 00000104 | 00 00 | flags = <none> + ------------------------ + ... + ************************ + WRITE_ROWS_EVENT [23] + ************************ + 0000011C | 54 1B 12 4B | time_when = 1259477844 + 00000120 | 17 | event_type = 23 + 00000121 | 64 00 00 00 | server_id = 100 + 00000125 | 22 00 00 00 | event_len = 34 + 00000129 | 3E 01 00 00 | log_pos = 0000013E + 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F + ------------------------ + 0000012F | 0F 00 00 00 | table_id = 15 + ... + +mysqlbinlog output +~~~~~~~~~~~~~~~~~~ +Something like this: + + ... + # at 199 + # at 243 + # at 284 + #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values +(1)` + #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped +to number 15 + #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 +flags: STMT_END_F + + BINLOG ' + VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= + VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= + VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== + '/*!*/; + ### INSERT INTO test.t1 + ### SET + ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ + ... + -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen ------------------------------------------------------------ -=-=(View All Progress Notes, 12 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). New server option ~~~~~~~~~~~~~~~~~ --binlog-annotate-rows-events Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding 'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Example: ... ************************ ANNOTATE_ROWS_EVENT [51] ************************ 000000C7 | 54 1B 12 4B | time_when = 1259477844 000000CB | 33 | event_type = 51 000000CC | 64 00 00 00 | server_id = 100 000000D0 | 2C 00 00 00 | event_len = 44 000000D4 | F3 00 00 00 | log_pos = 000000F3 000000D8 | 00 00 | flags = <none> ------------------------ 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" 000000DE | 72 74 20 69 | 000000E2 | 6E 74 6F 20 | 000000E6 | 74 31 20 76 | 000000EA | 61 6C 75 65 | 000000EE | 73 20 28 31 | 000000F2 | 29 | ************************ TABLE_MAP_EVENT [19] ************************ 000000F3 | 54 1B 12 4B | time_when = 1259477844 000000F7 | 13 | event_type = 19 000000F8 | 64 00 00 00 | server_id = 100 000000FC | 29 00 00 00 | event_len = 41 00000100 | 1C 01 00 00 | log_pos = 0000011C 00000104 | 00 00 | flags = <none> ------------------------ ... ************************ WRITE_ROWS_EVENT [23] ************************ 0000011C | 54 1B 12 4B | time_when = 1259477844 00000120 | 17 | event_type = 23 00000121 | 64 00 00 00 | server_id = 100 00000125 | 22 00 00 00 | event_len = 34 00000129 | 3E 01 00 00 | log_pos = 0000013E 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F ------------------------ 0000012F | 0F 00 00 00 | table_id = 15 ... New mysqlbinlog option ~~~~~~~~~~~~~~~~~~~~~~ --print-annotate-rows-events With this option, mysqlbinlog prints the content of Annotate-rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate rows events. mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: ... # at 199 # at 243 # at 284 #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values (1)` #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped to number 15 #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 flags: STMT_END_F BINLOG ' VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== '/*!*/; ### INSERT INTO test.t1 ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... When master sends Annotate rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has both log-slave-updates and binlog-annotate-rows-events options set. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Provide an " ORDER BY FIRST_JOIN.column" feature (59)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Provide an "ORDER BY FIRST_JOIN.column" feature CREATION DATE..: Thu, 22 Oct 2009, 12:33 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 59 (http://askmonty.org/worklog/?tid=59) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 5 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 20 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:21)=-=- Last hours added to the wrong worklog item, sorry. Reported zero hours worked. Estimate unchanged. -=-=(Bothorsen - Fri, 18 Dec 2009, 16:21)=-=- Added the estimation time. Worked 5 hours and estimate 20 hours remain (original estimate increased by 25 hours). DESCRIPTION: Question: How much effort would be required to provide an "ORDER BY FIRST_JOIN.column" feature? We often do self-join queries and want to order them by index, i.e.: create table t1 (a int, b int, unique key 'by_a_b' (a, b))); select * from t1 x inner join t1 y on (x.b = y.b) where x.a=1 and y.a=2 order by (x|y).b desc limit 10; but it is only ordered by index if you order by the first table in the optimized join order, which could be either x or y depending on which is more selective. we don't want to force the join order because often one is far more selective than the other, but the only way to know what table to order by then is to explain the query first. if there was some way to tell mysql to just order by the first join in the optimized order, that would help us. sometimes we do this across tables too. both of these can be solved by rewriting the query to say "using" instead of "on" in which case we don't have to specify the table name of the column, just "order by b desc". but, we also want to be able to do exclusions, in which case an "on" is required and therefore we run into ambiguous column names: Monty answered: MySQL has an optimization where it knows that if x.b = y.b is used then it can replace x.b with y.b and y.b with x.b in the WHERE part MySQL however doesn't do it for the ORDER BY part and I don't think that should be very hard to do. Question continues: create table t2 (c int, b int, unique key 'by_c_b' (c, b))); select * from t1 x inner join t1 y using (b) left join t2 on (t2.c = 3 and t1.b = t2.b) where x.a=1 and y.a=2 and t2.c is null order by (x| y).b desc limit 10; if we were ordering ascending, i think we could just leave off the order by entirely in this case and it would happen to work since it's reading in index order. but the combination of requiring an "on" and descending sort leaves us unable to use these tricks. if we had either an "ORDER BY FIRST_JOIN.column" or some way to tell the server that 'b' is in fact joined as being equivalent across tables (except where it may be null from left joining) and that we shouldn't have to specify the table name at all, that would save us having to figure out the table from the explain. Monty answers: It may be that the eq-replacment we have would solve this. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Provide an " ORDER BY FIRST_JOIN.column" feature (59)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Provide an "ORDER BY FIRST_JOIN.column" feature CREATION DATE..: Thu, 22 Oct 2009, 12:33 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 59 (http://askmonty.org/worklog/?tid=59) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 5 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 20 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:21)=-=- Added the estimation time. Worked 5 hours and estimate 20 hours remain (original estimate increased by 25 hours). DESCRIPTION: Question: How much effort would be required to provide an "ORDER BY FIRST_JOIN.column" feature? We often do self-join queries and want to order them by index, i.e.: create table t1 (a int, b int, unique key 'by_a_b' (a, b))); select * from t1 x inner join t1 y on (x.b = y.b) where x.a=1 and y.a=2 order by (x|y).b desc limit 10; but it is only ordered by index if you order by the first table in the optimized join order, which could be either x or y depending on which is more selective. we don't want to force the join order because often one is far more selective than the other, but the only way to know what table to order by then is to explain the query first. if there was some way to tell mysql to just order by the first join in the optimized order, that would help us. sometimes we do this across tables too. both of these can be solved by rewriting the query to say "using" instead of "on" in which case we don't have to specify the table name of the column, just "order by b desc". but, we also want to be able to do exclusions, in which case an "on" is required and therefore we run into ambiguous column names: Monty answered: MySQL has an optimization where it knows that if x.b = y.b is used then it can replace x.b with y.b and y.b with x.b in the WHERE part MySQL however doesn't do it for the ORDER BY part and I don't think that should be very hard to do. Question continues: create table t2 (c int, b int, unique key 'by_c_b' (c, b))); select * from t1 x inner join t1 y using (b) left join t2 on (t2.c = 3 and t1.b = t2.b) where x.a=1 and y.a=2 and t2.c is null order by (x| y).b desc limit 10; if we were ordering ascending, i think we could just leave off the order by entirely in this case and it would happen to work since it's reading in index order. but the combination of requiring an "on" and descending sort leaves us unable to use these tricks. if we had either an "ORDER BY FIRST_JOIN.column" or some way to tell the server that 'b' is in fact joined as being equivalent across tables (except where it may be null from left joining) and that we shouldn't have to specify the table name at all, that would save us having to figure out the table from the explain. Monty answers: It may be that the eq-replacment we have would solve this. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 15 ESTIMATE.......: 35 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:16)=-=- This is the work done on this patch so far. Most of it done by Alex. Worked 15 hours and estimate 035 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Fri, 04 Dec 2009, 13:00)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.14001 2009-12-04 13:00:24.000000000 +0200 +++ /tmp/wklog.47.new.14001 2009-12-04 13:00:24.000000000 +0200 @@ -6,27 +6,27 @@ New server option ~~~~~~~~~~~~~~~~~ - --binlog-annotate-row-events + --binlog-annotate-rows-events -Setting this option makes RBR (row-) events in the binary log to be +Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding -'binlog_annotate_row_events' system variable is dynamic and has both +'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... - SET SESSION binlog_annotate_row_events=ON; + SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... - SET SESSION binlog_annotate_row_events=OFF; + SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] -Describes the query which caused the corresponding row event. In binary log, +Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. @@ -79,6 +79,15 @@ 0000012F | 0F 00 00 00 | table_id = 15 ... +New mysqlbinlog option +~~~~~~~~~~~~~~~~~~~~~~ + --print-annotate-rows-events + +With this option, mysqlbinlog prints the content of Annotate-rows +events (if the binary log does contain them). Without this option +(i.e. by default), mysqlbinlog skips Annotate rows events. + + mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: @@ -109,5 +118,5 @@ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has - both log-slave-updates and binlog-annotate-row-events options set. + both log-slave-updates and binlog-annotate-rows-events options set. -=-=(Alexi - Wed, 02 Dec 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.47.old.17456 2009-12-02 13:32:18.000000000 +0200 +++ /tmp/wklog.47.new.17456 2009-12-02 13:32:18.000000000 +0200 @@ -1,8 +1 @@ -mysql_binlog_send() [sql/sql_repl.cc] -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -1. When sending events to a slave, master should simply skip - Annotate_rows events (they are not needed for replication). - [ ??? Multi-master - currently not clear ] -2. When sending events to mysqlbinlog (remote case), master - must send Annotate_rows events as well. -=-=(Alexi - Wed, 02 Dec 2009, 13:31)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.17414 2009-12-02 11:31:59.000000000 +0000 +++ /tmp/wklog.47.new.17414 2009-12-02 11:31:59.000000000 +0000 @@ -104,3 +104,10 @@ ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... +When master sends Annotate rows events +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. Master always sends Annotate_rows events to mysqlbinlog (in + remote case). +2. Master sends Annotate_rows events to a slave only if the slave has + both log-slave-updates and binlog-annotate-row-events options set. + -=-=(Knielsen - Mon, 30 Nov 2009, 11:21)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.18210 2009-11-30 11:21:33.000000000 +0200 +++ /tmp/wklog.47.new.18210 2009-11-30 11:21:33.000000000 +0200 @@ -28,7 +28,14 @@ Describes the query which caused the corresponding row event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query -text in its data part. Example: +text in its data part. + +The numeric code for this event must be assigned carefully. It should be +coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL +uses the same numeric code for one event that MariaDB uses for +ANNOTATE_ROWS_EVENT, which would make merging the two impossible. + +Example: ... ************************ -=-=(Alexi - Mon, 30 Nov 2009, 10:33)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16188 2009-11-30 10:33:44.000000000 +0200 +++ /tmp/wklog.47.new.16188 2009-11-30 10:33:44.000000000 +0200 @@ -2,6 +2,7 @@ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. When sending events to a slave, master should simply skip Annotate_rows events (they are not needed for replication). + [ ??? Multi-master - currently not clear ] 2. When sending events to mysqlbinlog (remote case), master must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 13:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.32047 2009-11-29 13:00:21.000000000 +0200 +++ /tmp/wklog.47.new.32047 2009-11-29 13:00:21.000000000 +0200 @@ -1 +1,7 @@ +mysql_binlog_send() [sql/sql_repl.cc] +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. When sending events to a slave, master should simply skip + Annotate_rows events (they are not needed for replication). +2. When sending events to mysqlbinlog (remote case), master + must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 09:50)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.24993 2009-11-29 07:50:36.000000000 +0000 +++ /tmp/wklog.47.new.24993 2009-11-29 07:50:36.000000000 +0000 @@ -4,3 +4,96 @@ > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). +New server option +~~~~~~~~~~~~~~~~~ + --binlog-annotate-row-events + +Setting this option makes RBR (row-) events in the binary log to be +preceded by Annotate rows events (see below). The corresponding +'binlog_annotate_row_events' system variable is dynamic and has both +global and session values. Default global value is OFF. + +Note. Session values are usefull to make it possible to annotate only + some selected statements: + + ... + SET SESSION binlog_annotate_row_events=ON; + ... statements to be annotated ... + SET SESSION binlog_annotate_row_events=OFF; + ... statements not to be annotated ... + +New binlog event type +~~~~~~~~~~~~~~~~~~~~~ + Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] + +Describes the query which caused the corresponding row event. In binary log, +precedes each Table_map_log_event. Contains empty post-header and the query +text in its data part. Example: + + ... + ************************ + ANNOTATE_ROWS_EVENT [51] + ************************ + 000000C7 | 54 1B 12 4B | time_when = 1259477844 + 000000CB | 33 | event_type = 51 + 000000CC | 64 00 00 00 | server_id = 100 + 000000D0 | 2C 00 00 00 | event_len = 44 + 000000D4 | F3 00 00 00 | log_pos = 000000F3 + 000000D8 | 00 00 | flags = <none> + ------------------------ + 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" + 000000DE | 72 74 20 69 | + 000000E2 | 6E 74 6F 20 | + 000000E6 | 74 31 20 76 | + 000000EA | 61 6C 75 65 | + 000000EE | 73 20 28 31 | + 000000F2 | 29 | + ************************ + TABLE_MAP_EVENT [19] + ************************ + 000000F3 | 54 1B 12 4B | time_when = 1259477844 + 000000F7 | 13 | event_type = 19 + 000000F8 | 64 00 00 00 | server_id = 100 + 000000FC | 29 00 00 00 | event_len = 41 + 00000100 | 1C 01 00 00 | log_pos = 0000011C + 00000104 | 00 00 | flags = <none> + ------------------------ + ... + ************************ + WRITE_ROWS_EVENT [23] + ************************ + 0000011C | 54 1B 12 4B | time_when = 1259477844 + 00000120 | 17 | event_type = 23 + 00000121 | 64 00 00 00 | server_id = 100 + 00000125 | 22 00 00 00 | event_len = 34 + 00000129 | 3E 01 00 00 | log_pos = 0000013E + 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F + ------------------------ + 0000012F | 0F 00 00 00 | table_id = 15 + ... + +mysqlbinlog output +~~~~~~~~~~~~~~~~~~ +Something like this: + + ... + # at 199 + # at 243 + # at 284 + #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values +(1)` + #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped +to number 15 + #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 +flags: STMT_END_F + + BINLOG ' + VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= + VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= + VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== + '/*!*/; + ### INSERT INTO test.t1 + ### SET + ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ + ... + -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen -=-=(Psergey - Sun, 16 Aug 2009, 11:08)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.12485 2009-08-16 11:08:33.000000000 +0300 +++ /tmp/wklog.47.new.12485 2009-08-16 11:08:33.000000000 +0300 @@ -1 +1,6 @@ +First suggestion: + +> I think for this we would actually need a new binlog event type +> (Comment_log_event?). Unless we want to log an empty statement Query_log_event +> containing only a comment (a bit of a hack). ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). New server option ~~~~~~~~~~~~~~~~~ --binlog-annotate-rows-events Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding 'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Example: ... ************************ ANNOTATE_ROWS_EVENT [51] ************************ 000000C7 | 54 1B 12 4B | time_when = 1259477844 000000CB | 33 | event_type = 51 000000CC | 64 00 00 00 | server_id = 100 000000D0 | 2C 00 00 00 | event_len = 44 000000D4 | F3 00 00 00 | log_pos = 000000F3 000000D8 | 00 00 | flags = <none> ------------------------ 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" 000000DE | 72 74 20 69 | 000000E2 | 6E 74 6F 20 | 000000E6 | 74 31 20 76 | 000000EA | 61 6C 75 65 | 000000EE | 73 20 28 31 | 000000F2 | 29 | ************************ TABLE_MAP_EVENT [19] ************************ 000000F3 | 54 1B 12 4B | time_when = 1259477844 000000F7 | 13 | event_type = 19 000000F8 | 64 00 00 00 | server_id = 100 000000FC | 29 00 00 00 | event_len = 41 00000100 | 1C 01 00 00 | log_pos = 0000011C 00000104 | 00 00 | flags = <none> ------------------------ ... ************************ WRITE_ROWS_EVENT [23] ************************ 0000011C | 54 1B 12 4B | time_when = 1259477844 00000120 | 17 | event_type = 23 00000121 | 64 00 00 00 | server_id = 100 00000125 | 22 00 00 00 | event_len = 34 00000129 | 3E 01 00 00 | log_pos = 0000013E 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F ------------------------ 0000012F | 0F 00 00 00 | table_id = 15 ... New mysqlbinlog option ~~~~~~~~~~~~~~~~~~~~~~ --print-annotate-rows-events With this option, mysqlbinlog prints the content of Annotate-rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate rows events. mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: ... # at 199 # at 243 # at 284 #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values (1)` #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped to number 15 #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 flags: STMT_END_F BINLOG ' VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== '/*!*/; ### INSERT INTO test.t1 ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... When master sends Annotate rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has both log-slave-updates and binlog-annotate-rows-events options set. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 15 ESTIMATE.......: 35 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:16)=-=- This is the work done on this patch so far. Most of it done by Alex. Worked 15 hours and estimate 035 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Fri, 04 Dec 2009, 13:00)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.14001 2009-12-04 13:00:24.000000000 +0200 +++ /tmp/wklog.47.new.14001 2009-12-04 13:00:24.000000000 +0200 @@ -6,27 +6,27 @@ New server option ~~~~~~~~~~~~~~~~~ - --binlog-annotate-row-events + --binlog-annotate-rows-events -Setting this option makes RBR (row-) events in the binary log to be +Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding -'binlog_annotate_row_events' system variable is dynamic and has both +'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... - SET SESSION binlog_annotate_row_events=ON; + SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... - SET SESSION binlog_annotate_row_events=OFF; + SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] -Describes the query which caused the corresponding row event. In binary log, +Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. @@ -79,6 +79,15 @@ 0000012F | 0F 00 00 00 | table_id = 15 ... +New mysqlbinlog option +~~~~~~~~~~~~~~~~~~~~~~ + --print-annotate-rows-events + +With this option, mysqlbinlog prints the content of Annotate-rows +events (if the binary log does contain them). Without this option +(i.e. by default), mysqlbinlog skips Annotate rows events. + + mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: @@ -109,5 +118,5 @@ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has - both log-slave-updates and binlog-annotate-row-events options set. + both log-slave-updates and binlog-annotate-rows-events options set. -=-=(Alexi - Wed, 02 Dec 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.47.old.17456 2009-12-02 13:32:18.000000000 +0200 +++ /tmp/wklog.47.new.17456 2009-12-02 13:32:18.000000000 +0200 @@ -1,8 +1 @@ -mysql_binlog_send() [sql/sql_repl.cc] -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -1. When sending events to a slave, master should simply skip - Annotate_rows events (they are not needed for replication). - [ ??? Multi-master - currently not clear ] -2. When sending events to mysqlbinlog (remote case), master - must send Annotate_rows events as well. -=-=(Alexi - Wed, 02 Dec 2009, 13:31)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.17414 2009-12-02 11:31:59.000000000 +0000 +++ /tmp/wklog.47.new.17414 2009-12-02 11:31:59.000000000 +0000 @@ -104,3 +104,10 @@ ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... +When master sends Annotate rows events +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. Master always sends Annotate_rows events to mysqlbinlog (in + remote case). +2. Master sends Annotate_rows events to a slave only if the slave has + both log-slave-updates and binlog-annotate-row-events options set. + -=-=(Knielsen - Mon, 30 Nov 2009, 11:21)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.18210 2009-11-30 11:21:33.000000000 +0200 +++ /tmp/wklog.47.new.18210 2009-11-30 11:21:33.000000000 +0200 @@ -28,7 +28,14 @@ Describes the query which caused the corresponding row event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query -text in its data part. Example: +text in its data part. + +The numeric code for this event must be assigned carefully. It should be +coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL +uses the same numeric code for one event that MariaDB uses for +ANNOTATE_ROWS_EVENT, which would make merging the two impossible. + +Example: ... ************************ -=-=(Alexi - Mon, 30 Nov 2009, 10:33)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16188 2009-11-30 10:33:44.000000000 +0200 +++ /tmp/wklog.47.new.16188 2009-11-30 10:33:44.000000000 +0200 @@ -2,6 +2,7 @@ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. When sending events to a slave, master should simply skip Annotate_rows events (they are not needed for replication). + [ ??? Multi-master - currently not clear ] 2. When sending events to mysqlbinlog (remote case), master must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 13:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.32047 2009-11-29 13:00:21.000000000 +0200 +++ /tmp/wklog.47.new.32047 2009-11-29 13:00:21.000000000 +0200 @@ -1 +1,7 @@ +mysql_binlog_send() [sql/sql_repl.cc] +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. When sending events to a slave, master should simply skip + Annotate_rows events (they are not needed for replication). +2. When sending events to mysqlbinlog (remote case), master + must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 09:50)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.24993 2009-11-29 07:50:36.000000000 +0000 +++ /tmp/wklog.47.new.24993 2009-11-29 07:50:36.000000000 +0000 @@ -4,3 +4,96 @@ > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). +New server option +~~~~~~~~~~~~~~~~~ + --binlog-annotate-row-events + +Setting this option makes RBR (row-) events in the binary log to be +preceded by Annotate rows events (see below). The corresponding +'binlog_annotate_row_events' system variable is dynamic and has both +global and session values. Default global value is OFF. + +Note. Session values are usefull to make it possible to annotate only + some selected statements: + + ... + SET SESSION binlog_annotate_row_events=ON; + ... statements to be annotated ... + SET SESSION binlog_annotate_row_events=OFF; + ... statements not to be annotated ... + +New binlog event type +~~~~~~~~~~~~~~~~~~~~~ + Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] + +Describes the query which caused the corresponding row event. In binary log, +precedes each Table_map_log_event. Contains empty post-header and the query +text in its data part. Example: + + ... + ************************ + ANNOTATE_ROWS_EVENT [51] + ************************ + 000000C7 | 54 1B 12 4B | time_when = 1259477844 + 000000CB | 33 | event_type = 51 + 000000CC | 64 00 00 00 | server_id = 100 + 000000D0 | 2C 00 00 00 | event_len = 44 + 000000D4 | F3 00 00 00 | log_pos = 000000F3 + 000000D8 | 00 00 | flags = <none> + ------------------------ + 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" + 000000DE | 72 74 20 69 | + 000000E2 | 6E 74 6F 20 | + 000000E6 | 74 31 20 76 | + 000000EA | 61 6C 75 65 | + 000000EE | 73 20 28 31 | + 000000F2 | 29 | + ************************ + TABLE_MAP_EVENT [19] + ************************ + 000000F3 | 54 1B 12 4B | time_when = 1259477844 + 000000F7 | 13 | event_type = 19 + 000000F8 | 64 00 00 00 | server_id = 100 + 000000FC | 29 00 00 00 | event_len = 41 + 00000100 | 1C 01 00 00 | log_pos = 0000011C + 00000104 | 00 00 | flags = <none> + ------------------------ + ... + ************************ + WRITE_ROWS_EVENT [23] + ************************ + 0000011C | 54 1B 12 4B | time_when = 1259477844 + 00000120 | 17 | event_type = 23 + 00000121 | 64 00 00 00 | server_id = 100 + 00000125 | 22 00 00 00 | event_len = 34 + 00000129 | 3E 01 00 00 | log_pos = 0000013E + 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F + ------------------------ + 0000012F | 0F 00 00 00 | table_id = 15 + ... + +mysqlbinlog output +~~~~~~~~~~~~~~~~~~ +Something like this: + + ... + # at 199 + # at 243 + # at 284 + #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values +(1)` + #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped +to number 15 + #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 +flags: STMT_END_F + + BINLOG ' + VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= + VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= + VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== + '/*!*/; + ### INSERT INTO test.t1 + ### SET + ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ + ... + -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen -=-=(Psergey - Sun, 16 Aug 2009, 11:08)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.12485 2009-08-16 11:08:33.000000000 +0300 +++ /tmp/wklog.47.new.12485 2009-08-16 11:08:33.000000000 +0300 @@ -1 +1,6 @@ +First suggestion: + +> I think for this we would actually need a new binlog event type +> (Comment_log_event?). Unless we want to log an empty statement Query_log_event +> containing only a comment (a bit of a hack). ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). New server option ~~~~~~~~~~~~~~~~~ --binlog-annotate-rows-events Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding 'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Example: ... ************************ ANNOTATE_ROWS_EVENT [51] ************************ 000000C7 | 54 1B 12 4B | time_when = 1259477844 000000CB | 33 | event_type = 51 000000CC | 64 00 00 00 | server_id = 100 000000D0 | 2C 00 00 00 | event_len = 44 000000D4 | F3 00 00 00 | log_pos = 000000F3 000000D8 | 00 00 | flags = <none> ------------------------ 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" 000000DE | 72 74 20 69 | 000000E2 | 6E 74 6F 20 | 000000E6 | 74 31 20 76 | 000000EA | 61 6C 75 65 | 000000EE | 73 20 28 31 | 000000F2 | 29 | ************************ TABLE_MAP_EVENT [19] ************************ 000000F3 | 54 1B 12 4B | time_when = 1259477844 000000F7 | 13 | event_type = 19 000000F8 | 64 00 00 00 | server_id = 100 000000FC | 29 00 00 00 | event_len = 41 00000100 | 1C 01 00 00 | log_pos = 0000011C 00000104 | 00 00 | flags = <none> ------------------------ ... ************************ WRITE_ROWS_EVENT [23] ************************ 0000011C | 54 1B 12 4B | time_when = 1259477844 00000120 | 17 | event_type = 23 00000121 | 64 00 00 00 | server_id = 100 00000125 | 22 00 00 00 | event_len = 34 00000129 | 3E 01 00 00 | log_pos = 0000013E 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F ------------------------ 0000012F | 0F 00 00 00 | table_id = 15 ... New mysqlbinlog option ~~~~~~~~~~~~~~~~~~~~~~ --print-annotate-rows-events With this option, mysqlbinlog prints the content of Annotate-rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate rows events. mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: ... # at 199 # at 243 # at 284 #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values (1)` #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped to number 15 #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 flags: STMT_END_F BINLOG ' VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== '/*!*/; ### INSERT INTO test.t1 ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... When master sends Annotate rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has both log-slave-updates and binlog-annotate-rows-events options set. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Bothorsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 18 Dec '09

18 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen CATEGORY.......: Server-RawIdeaBin TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 15 ESTIMATE.......: 35 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:16)=-=- This is the work done on this patch so far. Most of it done by Alex. Worked 15 hours and estimate 035 hours remain (original estimate increased by 50 hours). -=-=(Alexi - Fri, 04 Dec 2009, 13:00)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.14001 2009-12-04 13:00:24.000000000 +0200 +++ /tmp/wklog.47.new.14001 2009-12-04 13:00:24.000000000 +0200 @@ -6,27 +6,27 @@ New server option ~~~~~~~~~~~~~~~~~ - --binlog-annotate-row-events + --binlog-annotate-rows-events -Setting this option makes RBR (row-) events in the binary log to be +Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding -'binlog_annotate_row_events' system variable is dynamic and has both +'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... - SET SESSION binlog_annotate_row_events=ON; + SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... - SET SESSION binlog_annotate_row_events=OFF; + SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] -Describes the query which caused the corresponding row event. In binary log, +Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. @@ -79,6 +79,15 @@ 0000012F | 0F 00 00 00 | table_id = 15 ... +New mysqlbinlog option +~~~~~~~~~~~~~~~~~~~~~~ + --print-annotate-rows-events + +With this option, mysqlbinlog prints the content of Annotate-rows +events (if the binary log does contain them). Without this option +(i.e. by default), mysqlbinlog skips Annotate rows events. + + mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: @@ -109,5 +118,5 @@ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has - both log-slave-updates and binlog-annotate-row-events options set. + both log-slave-updates and binlog-annotate-rows-events options set. -=-=(Alexi - Wed, 02 Dec 2009, 13:32)=-=- Low Level Design modified. --- /tmp/wklog.47.old.17456 2009-12-02 13:32:18.000000000 +0200 +++ /tmp/wklog.47.new.17456 2009-12-02 13:32:18.000000000 +0200 @@ -1,8 +1 @@ -mysql_binlog_send() [sql/sql_repl.cc] -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -1. When sending events to a slave, master should simply skip - Annotate_rows events (they are not needed for replication). - [ ??? Multi-master - currently not clear ] -2. When sending events to mysqlbinlog (remote case), master - must send Annotate_rows events as well. -=-=(Alexi - Wed, 02 Dec 2009, 13:31)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.17414 2009-12-02 11:31:59.000000000 +0000 +++ /tmp/wklog.47.new.17414 2009-12-02 11:31:59.000000000 +0000 @@ -104,3 +104,10 @@ ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... +When master sends Annotate rows events +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. Master always sends Annotate_rows events to mysqlbinlog (in + remote case). +2. Master sends Annotate_rows events to a slave only if the slave has + both log-slave-updates and binlog-annotate-row-events options set. + -=-=(Knielsen - Mon, 30 Nov 2009, 11:21)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.18210 2009-11-30 11:21:33.000000000 +0200 +++ /tmp/wklog.47.new.18210 2009-11-30 11:21:33.000000000 +0200 @@ -28,7 +28,14 @@ Describes the query which caused the corresponding row event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query -text in its data part. Example: +text in its data part. + +The numeric code for this event must be assigned carefully. It should be +coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL +uses the same numeric code for one event that MariaDB uses for +ANNOTATE_ROWS_EVENT, which would make merging the two impossible. + +Example: ... ************************ -=-=(Alexi - Mon, 30 Nov 2009, 10:33)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16188 2009-11-30 10:33:44.000000000 +0200 +++ /tmp/wklog.47.new.16188 2009-11-30 10:33:44.000000000 +0200 @@ -2,6 +2,7 @@ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. When sending events to a slave, master should simply skip Annotate_rows events (they are not needed for replication). + [ ??? Multi-master - currently not clear ] 2. When sending events to mysqlbinlog (remote case), master must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 13:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.32047 2009-11-29 13:00:21.000000000 +0200 +++ /tmp/wklog.47.new.32047 2009-11-29 13:00:21.000000000 +0200 @@ -1 +1,7 @@ +mysql_binlog_send() [sql/sql_repl.cc] +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +1. When sending events to a slave, master should simply skip + Annotate_rows events (they are not needed for replication). +2. When sending events to mysqlbinlog (remote case), master + must send Annotate_rows events as well. -=-=(Alexi - Sun, 29 Nov 2009, 09:50)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.24993 2009-11-29 07:50:36.000000000 +0000 +++ /tmp/wklog.47.new.24993 2009-11-29 07:50:36.000000000 +0000 @@ -4,3 +4,96 @@ > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). +New server option +~~~~~~~~~~~~~~~~~ + --binlog-annotate-row-events + +Setting this option makes RBR (row-) events in the binary log to be +preceded by Annotate rows events (see below). The corresponding +'binlog_annotate_row_events' system variable is dynamic and has both +global and session values. Default global value is OFF. + +Note. Session values are usefull to make it possible to annotate only + some selected statements: + + ... + SET SESSION binlog_annotate_row_events=ON; + ... statements to be annotated ... + SET SESSION binlog_annotate_row_events=OFF; + ... statements not to be annotated ... + +New binlog event type +~~~~~~~~~~~~~~~~~~~~~ + Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] + +Describes the query which caused the corresponding row event. In binary log, +precedes each Table_map_log_event. Contains empty post-header and the query +text in its data part. Example: + + ... + ************************ + ANNOTATE_ROWS_EVENT [51] + ************************ + 000000C7 | 54 1B 12 4B | time_when = 1259477844 + 000000CB | 33 | event_type = 51 + 000000CC | 64 00 00 00 | server_id = 100 + 000000D0 | 2C 00 00 00 | event_len = 44 + 000000D4 | F3 00 00 00 | log_pos = 000000F3 + 000000D8 | 00 00 | flags = <none> + ------------------------ + 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" + 000000DE | 72 74 20 69 | + 000000E2 | 6E 74 6F 20 | + 000000E6 | 74 31 20 76 | + 000000EA | 61 6C 75 65 | + 000000EE | 73 20 28 31 | + 000000F2 | 29 | + ************************ + TABLE_MAP_EVENT [19] + ************************ + 000000F3 | 54 1B 12 4B | time_when = 1259477844 + 000000F7 | 13 | event_type = 19 + 000000F8 | 64 00 00 00 | server_id = 100 + 000000FC | 29 00 00 00 | event_len = 41 + 00000100 | 1C 01 00 00 | log_pos = 0000011C + 00000104 | 00 00 | flags = <none> + ------------------------ + ... + ************************ + WRITE_ROWS_EVENT [23] + ************************ + 0000011C | 54 1B 12 4B | time_when = 1259477844 + 00000120 | 17 | event_type = 23 + 00000121 | 64 00 00 00 | server_id = 100 + 00000125 | 22 00 00 00 | event_len = 34 + 00000129 | 3E 01 00 00 | log_pos = 0000013E + 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F + ------------------------ + 0000012F | 0F 00 00 00 | table_id = 15 + ... + +mysqlbinlog output +~~~~~~~~~~~~~~~~~~ +Something like this: + + ... + # at 199 + # at 243 + # at 284 + #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values +(1)` + #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped +to number 15 + #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 +flags: STMT_END_F + + BINLOG ' + VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= + VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= + VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== + '/*!*/; + ### INSERT INTO test.t1 + ### SET + ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ + ... + -=-=(Knielsen - Fri, 27 Nov 2009, 13:30)=-=- Observers changed: Knielsen -=-=(Psergey - Sun, 16 Aug 2009, 11:08)=-=- High-Level Specification modified. --- /tmp/wklog.47.old.12485 2009-08-16 11:08:33.000000000 +0300 +++ /tmp/wklog.47.new.12485 2009-08-16 11:08:33.000000000 +0300 @@ -1 +1,6 @@ +First suggestion: + +> I think for this we would actually need a new binlog event type +> (Comment_log_event?). Unless we want to log an empty statement Query_log_event +> containing only a comment (a bit of a hack). ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: First suggestion: > I think for this we would actually need a new binlog event type > (Comment_log_event?). Unless we want to log an empty statement Query_log_event > containing only a comment (a bit of a hack). New server option ~~~~~~~~~~~~~~~~~ --binlog-annotate-rows-events Setting this option makes RBR (rows-) events in the binary log to be preceded by Annotate rows events (see below). The corresponding 'binlog_annotate_rows_events' system variable is dynamic and has both global and session values. Default global value is OFF. Note. Session values are usefull to make it possible to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... New binlog event type ~~~~~~~~~~~~~~~~~~~~~ Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] Describes the query which caused the corresponding rows event. In binary log, precedes each Table_map_log_event. Contains empty post-header and the query text in its data part. The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Example: ... ************************ ANNOTATE_ROWS_EVENT [51] ************************ 000000C7 | 54 1B 12 4B | time_when = 1259477844 000000CB | 33 | event_type = 51 000000CC | 64 00 00 00 | server_id = 100 000000D0 | 2C 00 00 00 | event_len = 44 000000D4 | F3 00 00 00 | log_pos = 000000F3 000000D8 | 00 00 | flags = <none> ------------------------ 000000DA | 69 6E 73 65 | query = "insert into t1 values (1)" 000000DE | 72 74 20 69 | 000000E2 | 6E 74 6F 20 | 000000E6 | 74 31 20 76 | 000000EA | 61 6C 75 65 | 000000EE | 73 20 28 31 | 000000F2 | 29 | ************************ TABLE_MAP_EVENT [19] ************************ 000000F3 | 54 1B 12 4B | time_when = 1259477844 000000F7 | 13 | event_type = 19 000000F8 | 64 00 00 00 | server_id = 100 000000FC | 29 00 00 00 | event_len = 41 00000100 | 1C 01 00 00 | log_pos = 0000011C 00000104 | 00 00 | flags = <none> ------------------------ ... ************************ WRITE_ROWS_EVENT [23] ************************ 0000011C | 54 1B 12 4B | time_when = 1259477844 00000120 | 17 | event_type = 23 00000121 | 64 00 00 00 | server_id = 100 00000125 | 22 00 00 00 | event_len = 34 00000129 | 3E 01 00 00 | log_pos = 0000013E 0000012D | 10 00 | flags = LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F ------------------------ 0000012F | 0F 00 00 00 | table_id = 15 ... New mysqlbinlog option ~~~~~~~~~~~~~~~~~~~~~~ --print-annotate-rows-events With this option, mysqlbinlog prints the content of Annotate-rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate rows events. mysqlbinlog output ~~~~~~~~~~~~~~~~~~ Something like this: ... # at 199 # at 243 # at 284 #091129 9:57:24 server id 100 end_log_pos 243 Query: `insert into t1 values (1)` #091129 9:57:24 server id 100 end_log_pos 284 Table_map: `test`.`t1` mapped to number 15 #091129 9:57:24 server id 100 end_log_pos 318 Write_rows: table id 15 flags: STMT_END_F BINLOG ' VBsSSzNkAAAALAAAAPMAAAAAAGluc2VydCBpbnRvIHQxIHZhbHVlcyAoMSk= VBsSSxNkAAAAKQAAABwBAAAAAA8AAAAAAAAABHRlc3QAAnQxAAEDAAE= VBsSSxdkAAAAIgAAAD4BAAAQAA8AAAAAAAEAAf/+AQAAAA== '/*!*/; ### INSERT INTO test.t1 ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ... When master sends Annotate rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Master always sends Annotate_rows events to mysqlbinlog (in remote case). 2. Master sends Annotate_rows events to a slave only if the slave has both log-slave-updates and binlog-annotate-rows-events options set. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

Re: [Maria-developers] bzr repository
by Kristian Nielsen 18 Dec '09

18 Dec '09

Hakan Kuecuekyilmaz <hakan(a)askmonty.org> writes: > I would like to put the scripts to a bzr repository. I guess launchpad > is a good place. A separate repository makes sense to me, or maybe add > them to the MariaDB sources? Yes. I have for some time known we need a public repository with the tools we use for MariaDB development. So now I have created a project for this on Launchpad. Branch to push to: lp:mariadb-tools Project page: https://launchpad.net/mariadb-tools Wiki page: http://askmonty.org/wiki/index.php/Tools_for_MariaDB Everybody, please help by moving things from our internal bzr repo hasky:/usr/local/bzr into this project where relevant. And also please help by updating the wiki page with appropriate documentation. > I have a first working version of the wrapper scripts to automatically > run sql-bench with a set of different configurations. >From this description, it sounds like something that would make sense to include in the MariaDB source tree? If so, I guess just commit them and get a review. Generally, it is an advantage to keep tools that work on a specific version of MariaDB inside the source tree, especially for automation (like Buildbot). Eg. if different variants of the scripts are needed for different MariaDB versions (maybe using a feature that is not present in older versions); keeping the scripts inside the tree trivially ensures that the versions of scripts and of MariaDB matches. It also removes any issues wiht making sure the scripts are available on a given host. On the other hand, if the script needs to work with different versions of MariaDB it probably makes sense to keep it outside of the main source tree. Maybe the script needs to check out the MariaDB source tree (like package scripts); it cannot easily checkout itself... Or maybe it needs to compare two different versions of MariaDB against each other (benchmarks, upgrade testing). For some tools the correct approach would be that part is inside the source tree and part outside. - Kristian.

1 0