Bug #47323 mysqlbinlog --verbose displays bad output when events contain subset of columns
Submitted: 15 Sep 2009 12:27 Modified: 19 Dec 2009 11:36
Reporter: Frazer Clement Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:mysql-5.1-telco-6.2 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any

[15 Sep 2009 12:27] Frazer Clement
Description:
mysqlbinlog --verbose displays row events in the Binlog in a 'SQL style' - as INSERT, UPDATE and DELETE statements.

Write_rows events are displayed as INSERT statements.

By default, Ndb cluster records updates to tables as Write_rows events, with only the primary key and updated columns present.

Using mysqlbinlog --verbose to look at a Binlog containing these 'update mapped to Write_rows' events displays garbage.

This is because the code which determines whether a particular column is null assumes that all columns are present.

How to repeat:
1) Start a cluster with attached binlogging MySQLD
2) RESET MASTER;
3) Create a table with a primary key and multiple columns :
   CREATE TABLE t (a int primary key, b int, c int, d int, e int) engine=ndb;
4) Insert a row into the table
   INSERT INTO t VALUES (1,1,1,1,1);
5) Update the row
   UPDATE t set d=4 WHERE a=1;
6) Look at the Binlog using mysqlbinglog --verbose <binlog file name>

Observe the suggested SQL for the update event.

Suggested fix:
Modify pretty-printing code to check for null columns correctly.  Nulls are only present for columns which are present.

=== modified file 'sql/log_event.cc'
--- sql/log_event.cc	2009-08-26 10:59:55 +0000
+++ sql/log_event.cc	2009-09-15 12:02:12 +0000
@@ -1855,6 +1855,7 @@ Rows_log_event::print_verbose_one_row(IO
 {
   const uchar *value0= value;
   const uchar *null_bits= value;
+  uint nullBitIndex= 0;
   char typestr[64]= "";
   
   value+= (m_width + 7) / 8;
@@ -1863,7 +1864,7 @@ Rows_log_event::print_verbose_one_row(IO
   
   for (size_t i= 0; i < td->size(); i ++)
   {
-    int is_null= (null_bits[i / 8] >> (i % 8))  & 0x01;
+    int is_null= (null_bits[nullBitIndex / 8] >> (nullBitIndex % 8))  & 0x01;
 
     if (bitmap_is_set(cols_bitmap, i) == 0)
       continue;
@@ -1900,6 +1901,8 @@ Rows_log_event::print_verbose_one_row(IO
     }
     
     my_b_printf(file, "\n");
+    
+    nullBitIndex++;
   }
   return value - value0;
 }
[22 Sep 2009 17:00] Frazer Clement
New patch with style changes and testcase

Attachment: bug47323.patch (text/x-patch), 9.28 KiB.

[25 Sep 2009 11:47] Frazer Clement
mysqlbinlog --verbose testcase split from Ndb binlog generation tc

Attachment: bug47323-split-tcs.patch (text/x-patch), 18.39 KiB.

[25 Sep 2009 11:49] Frazer Clement
Testing split into 2 facets :

binlog/t/binlog_row_mysqlbinlog_verbose
  Uses captured binlog files to test mysqlbinlog --verbose functionality

ndb_binlog/t/ndb_binlog_variants (telco trees only)
  Uses Ndb to generate binlog variants and check them

Other comments addressed.
[28 Sep 2009 10:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84815

3007 Frazer Clement	2009-09-28
      Bug#47323 : mysqlbinlog --verbose displays bad output when events contain subset of columns
      added:
        mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result
        mysql-test/suite/binlog/std_data/update-full-row.binlog
        mysql-test/suite/binlog/std_data/update-partial-row.binlog
        mysql-test/suite/binlog/std_data/write-full-row.binlog
        mysql-test/suite/binlog/std_data/write-partial-row.binlog
        mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_verbose.test
        mysql-test/suite/ndb_binlog/r/ndb_binlog_variants.result
        mysql-test/suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
        mysql-test/suite/ndb_binlog/t/ndb_binlog_variants.cnf
        mysql-test/suite/ndb_binlog/t/ndb_binlog_variants.test
      modified:
        sql/log_event.cc
[29 Sep 2009 15:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85067

3009 Frazer Clement	2009-09-29
      Bug#47323 : mysqlbinlog --verbose displays bad output when events contain subset of columns
      added:
        mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result
        mysql-test/suite/binlog/std_data/update-full-row.binlog
        mysql-test/suite/binlog/std_data/update-partial-row.binlog
        mysql-test/suite/binlog/std_data/write-full-row.binlog
        mysql-test/suite/binlog/std_data/write-partial-row.binlog
        mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_verbose.test
        mysql-test/suite/ndb_binlog/r/ndb_binlog_variants.result
        mysql-test/suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
        mysql-test/suite/ndb_binlog/t/ndb_binlog_variants.cnf
        mysql-test/suite/ndb_binlog/t/ndb_binlog_variants.test
      modified:
        sql/log_event.cc
[29 Sep 2009 16:01] Frazer Clement
Fix pushed to :
mysql-5.1-telco-6.2.19
mysql-5.1-telco-6.3.28
mysql-5.1-telco-7.0.9
mysql-5.1-telco-7.1.0

Fix *not yet* pushed to 
mysql-5.1.*
[29 Sep 2009 16:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85087

3075 Frazer Clement	2009-09-29
      bug#47323 merge fix, as ndb_log_update* vars become visible in 6.3+, and DELETE logs all columns in 6.3+ when log_update_only=OFF
      modified:
        mysql-test/suite/ndb_binlog/r/ndb_binlog_variants.result
[30 Sep 2009 8:13] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:frazer@mysql.com-20090929142503-sst6g3fs0vx9fgil) (version source revid:frazer@mysql.com-20090929142503-sst6g3fs0vx9fgil) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[30 Sep 2009 8:13] Bugs System
Pushed into 5.1.37-ndb-6.3.28 (revid:jonas@mysql.com-20090930070741-13u316s7s2l7e1ej) (version source revid:frazer@mysql.com-20090929152643-bvia2lagmiu92llz) (merge vers: 5.1.37-ndb-6.3.28) (pib:11)
[30 Sep 2009 8:14] Bugs System
Pushed into 5.1.37-ndb-7.0.9 (revid:jonas@mysql.com-20090930075942-1q6asjcp0gaeynmj) (version source revid:frazer@mysql.com-20090929154320-ae34rrt0glj8prrv) (merge vers: 5.1.37-ndb-7.0.9) (pib:11)
[30 Sep 2009 8:15] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:jonas@mysql.com-20090930080049-1c8a8cio9qgvhq35) (version source revid:frazer@mysql.com-20090929154645-gnwj4pzy86ww8468) (merge vers: 5.1.35-ndb-7.1.0) (pib:11)
[9 Oct 2009 8:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86292

3154 He Zhenxing	2009-10-09
      Bug#47323 : mysqlbinlog --verbose displays bad output when events contain subset of columns
      
      Commit the non-NDB specific part (originated by frazer) to 5.1 mainline.
[9 Oct 2009 8:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86293
[13 Oct 2009 4:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86633
[14 Oct 2009 14:39] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:satya.bn@sun.com-20091013071829-zc4c3go44j6re592) (merge vers: 5.1.40) (pib:13)
[19 Oct 2009 3:20] Zhenxing He
pushed to 5.1-bugteam and pe
[22 Oct 2009 6:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:05] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131708-bc6pv55x6287a0wc) (merge vers: 5.5.0-beta) (pib:13)
[23 Oct 2009 13:30] Jon Stephens
Documented as follows in the NDB-6.2.19, NDB-6.3.28, NDB-7.0.9, mysql-5.1.41, mysql-5.5.0, and mysql-6.0.14 changelogs:

        When mysqlbinlog --verbose was used to read a binary log that
        had been recorded using the row-based format, the output for
        events that updated some but not all columns of tables was not
        correct.

Closed.
[18 Dec 2009 10:26] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:42] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 10:57] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:12] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[19 Dec 2009 11:36] Jon Stephens
No additional changelog entries needed. Setting back to Closed state.