Bug #33880 replicated wrong geometry data when update query set another field to NULL

• Submitted: 16 Jan 2008 2:17
• Modified: 21 Oct 2008 3:07
• Reporter: li pickup (OCA)
• Status: Closed
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S2 (Serious)
• Version: 5.1.22-rc-log
• OS: Linux
• Assigned to: Susanne Ebrecht
• CPU Architecture: Any
• Tags: geometry field, row based replication, UPDATE, update set NULL

[16 Jan 2008 2:17] li pickup

Description:
The geometry data is not correct when replicate a update query which set another field to NULL.

How to repeat:
Firstly, we will build a environment of row based replication. The slave gets data from master by replication. There are a table named "testGeo" on each site. The create schema of the table is :
"create table testGeo (id int(11) NOT NULL,  a008 float(12) unsigned, a027 point, PRIMARY KEY (id));"

1.  insert a row to the table:
    insert into testGeo (id, a008, a027) values (1, 23.327, GeomFromText('point(2 2)'));
    you can select from the slave as:
    mysql> select * from king1.testgeo ;
    +----+--------+---------------------------+
    | id | a008   | a027                      |
    +----+--------+---------------------------+
    |  1 | 23.327 |     

          @       @ | 
    +----+--------+---------------------------+
    1 row in set (0.00 sec)

    mysql> select astext(a027) from king1.testgeo where id =1;
    +--------------+
    | astext(a027) |
    +--------------+
    | POINT(2 2)   | 
    +--------------+
    1 row in set (0.00 sec)

2.  update the row:
    update testGeo set a008=NULL where id=1;
    you can select from the slave like:
    mysql> select * from king1.testgeo ;                      
    +----+------+---------------------------+
    | id | a008 | a027                      |
    +----+------+---------------------------+
    |  1 | NULL | 

          @       @   @ | 
    +----+------+---------------------------+
    1 row in set (0.00 sec)

    mysql> select astext(a027) from king1.testgeo where id =1;
    +--------------+
    | astext(a027) |
    +--------------+
    | NULL         | 
    +--------------+
    1 row in set (0.00 sec)

it seems that there are some excursion in the geometry data.
by the way, I have test the case in the version of 5.1.22-rc. It is still the same in the new version.

Suggested fix:
I can not find any solution for it now.

[25 Jan 2008 19:41] Susanne Ebrecht

Many thanks for writing a bug report.
Unfortunately, I can't reproduce your problem.

master> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-debug-log

slave> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-debug

master> show variables like 'binlog_format'\G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW

master> create table testGeo (id int(11) NOT NULL,  a008 float(12) unsigned, a027 point, PRIMARY KEY (id));

master> insert into testGeo (id, a008, a027) values (1, 23.327, GeomFromText('point(2 2)'));

master> select * from testGeo;
+----+--------+---------------------------+
| id | a008   | a027                      |
+----+--------+---------------------------+
|  1 | 23.327 |     

          @       @ | 
+----+--------+---------------------------+

master> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| POINT(2 2)   | 
+--------------+

slave> select * from testGeo;
+----+--------+---------------------------+
| id | a008   | a027                      |
+----+--------+---------------------------+
|  1 | 23.327 |     

          @       @ | 
+----+--------+---------------------------+

slave> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| POINT(2 2)   | 
+--------------+

master> update testGeo set a008=NULL where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master> select * from testGeo;
+----+------+---------------------------+
| id | a008 | a027                      |
+----+------+---------------------------+
|  1 | NULL |     

          @       @ | 
+----+------+---------------------------+

master> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| POINT(2 2)   | 
+--------------+

slave> select * from testGeo;
+----+------+---------------------------+
| id | a008 | a027                      |
+----+------+---------------------------+
|  1 | NULL |     

          @       @ | 
+----+------+---------------------------+

slave> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| POINT(2 2)   | 
+--------------+

In your exampe, you create a table named: testGeo
But your selects are on table king1.testgeo.

MySQL table names are case sensitive, so testgeo and testGeo are not the same tables.

[28 Jan 2008 7:50] li pickup

Thanks very much for quick response.

But I am sorry to tell you that it still can repeat in our environment. 

I have repeat it today. And I find that the version of my MySQL server is 5.1.22-rc-log which is not identified with your MySQL server. 
mysql> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-log
1 row in set (0.00 sec)

It was download from: http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.22-rc-linux-x86_64-glibc23.tar.gz/f....

By the way, I have "lower_case_table_names = 1" in my my.cnf. So testgeo and testGeo is the same in our environment.

follows is my repeat step:
master> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-log
1 row in set (0.00 sec)

master> show variables like 'binlog_format'\G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW
1 row in set (0.00 sec)]

master> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

master> create database king1;
Query OK, 1 row affected (0.00 sec)

master> use king1;
Database changed

master> create table testGeo (id int(11) NOT NULL,  a008 float(12) unsigned, a027 point,PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)

master> show tables;
+-----------------+
| Tables_in_king1 |
+-----------------+
| testgeo         | 
+-----------------+
1 row in set (0.00 sec)

on the slave: 
slave> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-log
1 row in set (0.00 sec)

slave> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)

slave> use king1;
Database changed

slave> show tables;
+-----------------+
| Tables_in_king1 |
+-----------------+
| testgeo         | 
+-----------------+
1 row in set (0.00 sec)

after that we insert a row on the master:
master> insert into testGeo (id, a008, a027) values (1, 23.327, GeomFromText('point(2 2)'));
Query OK, 1 row affected (0.00 sec)

we can find the correct row on the slave:
slave> select * from testGeo;
+----+--------+---------------------------+
| id | a008   | a027                      |
+----+--------+---------------------------+
|  1 | 23.327 |     

          @       @ | 
+----+--------+---------------------------+
1 row in set (0.00 sec)

but after update a field to NULL on the master, you can see that:
master> update testGeo set a008=NULL where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master> select * from testGeo;
+----+------+---------------------------+
| id | a008 | a027                      |
+----+------+---------------------------+
|  1 | NULL |     

          @       @ | 
+----+------+---------------------------+
1 row in set (0.00 sec)

the slave shows a wrong row values.
slave> select * from testGeo;
+----+------+---------------------------+
| id | a008 | a027                      |
+----+------+---------------------------+
|  1 | NULL | 

          @       @   @ | 
+----+------+---------------------------+
1 row in set (0.00 sec)

slave> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| NULL         | 
+--------------+
1 row in set (0.00 sec)

The bug maybe fixed in your version, I think. But I believe you can also repeat it on version 5.1.22-rc-log.  Many thanks for your attention and kind help.

[30 Jan 2008 8:02] li pickup

I found another problem about MySQL row based replication. It is similar to this bug, I think. It can repeat on 

Here is the repeat step.
the row based environment is set as: 
master (10.0.1.26)
slave  (10.0.1.25)
it all set lower_case_table_names = 1 in my.cnf.

master> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-log
1 row in set (0.00 sec)

master> show variables like 'binlog_format'\G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW
1 row in set (0.00 sec)

master> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 |     3106 |              |                  | 
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

slave> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-log
1 row in set (0.00 sec)

slave> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.26
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 3106
               Relay_Log_File: localhost-relay-bin.000049
                Relay_Log_Pos: 248
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: pdnstest
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3106
              Relay_Log_Space: 549
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

we create table in database king1:
master> use king1;
Database changed
master> create table table1(id int (100) primary key, uid int (100) unique key, bit_a001 bit(48), tinyint_a002 tinyint(5), boolean_a003 boolean, smallint_a004 smallint(15) unsigned, mediumint_a005 mediumint(11) zerofill,  bigint_a006 bigint(8), floatunsigned_a007 float(12) unsigned, floatzerofill_a008 float(64,5) zerofill, doublezerofill_a009 double(64,14) zerofill, decimal_a010 decimal(14,3), date_a011 date, datetime_a012 datetime, time_a013 time, year_a014 year, char_a015 char(30), vchar_a016 varchar(100), binary_a017 binary(14), varbinary_a018 varbinary(23), tinyblob_a019 tinyblob, text_a020 text(2048), mediumblob_a021 mediumblob, logtext_a022 longtext, emum_a023 enum('e1', 'e2', 'e3'), set_a024 set('s1','s2','s3','s4'));
Query OK, 0 rows affected (0.05 sec)

and insert a row to the table:
master> insert into table1 (id, uid, bit_a001, tinyint_a002, boolean_a003, smallint_a004, mediumint_a005, bigint_a006, floatunsigned_a007, floatzerofill_a008, doublezerofill_a009, decimal_a010, date_a011, datetime_a012, time_a013, year_a014, char_a015, vchar_a016, binary_a017, varbinary_a018, tinyblob_a019, text_a020, mediumblob_a021, logtext_a022, emum_a023, set_a024) values (1, 1, 0x01020506, -34, true, 123, 12, -1024, 3.1415926535897932384626433832795, 3.1415926535897932384626433832795, 3.1415926535897932384626433832795, -3.1415926535897932384626433832795, 19691231, 20050408220408, -220408, 2005, '214', 'bcde', 0x00010204, 0x010402, '214', '124', '14\02', '21\04', 'e2', 's1,s4');
Query OK, 1 row affected, 1 warning (0.03 sec)

the row was successfully replicated to slave:
slave> use king1;
Database changed
slave> select * from table1\G
*************************** 1. row ***************************
                 id: 1
                uid: 1
           bit_a001: 
       tinyint_a002: -34
       boolean_a003: 1
      smallint_a004: 123
     mediumint_a005: 00000000012
        bigint_a006: -1024
 floatunsigned_a007: 3.14159
 floatzerofill_a008: 0000000000000000000000000000000000000000000000000000000003.14159
doublezerofill_a009: 0000000000000000000000000000000000000000000000003.14159265358979
       decimal_a010: -3.142
          date_a011: 1969-12-31
      datetime_a012: 2005-04-08 22:04:08
          time_a013: -22:04:08
          year_a014: 2005
          char_a015: 214
         vchar_a016: bcde
        binary_a017: 
     varbinary_a018: 

      tinyblob_a019: 214
          text_a020: 124
    mediumblob_a021: 14
       logtext_a022: 21
          emum_a023: e2
           set_a024: s1,s4
1 row in set (0.00 sec)

after that we update the row like this:
master> update table1 set vchar_a016="" where id=1;           
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master> select * from table1\G                      
*************************** 1. row ***************************
                 id: 1
                uid: 1
           bit_a001: 
       tinyint_a002: -34
       boolean_a003: 1
      smallint_a004: 123
     mediumint_a005: 00000000012
        bigint_a006: -1024
 floatunsigned_a007: 3.14159
 floatzerofill_a008: 0000000000000000000000000000000000000000000000000000000003.14159
doublezerofill_a009: 0000000000000000000000000000000000000000000000003.14159265358979
       decimal_a010: -3.142
          date_a011: 1969-12-31
      datetime_a012: 2005-04-08 22:04:08
          time_a013: -22:04:08
          year_a014: 2005
          char_a015: 214
         vchar_a016: 
        binary_a017: 
     varbinary_a018: 

      tinyblob_a019: 214
          text_a020: 124
    mediumblob_a021: 14
       logtext_a022: 21
          emum_a023: e2
           set_a024: s1,s4
1 row in set (0.00 sec)

but the tinyblob_a19, text_a020, mediumblob_a021 and logtext_a022 are not the same in the slave:
slave> select * from table1\G
*************************** 1. row ***************************
                 id: 1
                uid: 1
           bit_a001: 
       tinyint_a002: -34
       boolean_a003: 1
      smallint_a004: 123
     mediumint_a005: 00000000012
        bigint_a006: -1024
 floatunsigned_a007: 3.14159
 floatzerofill_a008: 0000000000000000000000000000000000000000000000000000000003.14159
doublezerofill_a009: 0000000000000000000000000000000000000000000000003.14159265358979
       decimal_a010: -3.142
          date_a011: 1969-12-31
      datetime_a012: 2005-04-08 22:04:08
          time_a013: -22:04:08
          year_a014: 2005
          char_a015: 214
         vchar_a016: 
        binary_a017: 
     varbinary_a018: 

      tinyblob_a019: 241
          text_a020: 4
    mediumblob_a021: 21
       logtext_a022: 
          emum_a023: e2
           set_a024: s1,s4
1 row in set (0.00 sec)

I hope it was also fixed in the version of  5.1.22-rc-debug-log. Thanks very much.

[31 Jan 2008 16:36] Susanne Ebrecht

FYI: 5.1.22-rc-log and 5.1.22-rc-debug-log are the same versions.
The relevant part is 5.1.22-rc
the "log" only means, that you enabled log files, and the "debug" just means, that you compiled the source with debug informations.

This issue could be OS related.

I need the exact Linux version from you.
Means: the distributor: like: Debian, Ubuntu, RedHat, Suse, ...
If it is 32 or 64 bit
and the result of:
$ uname -a

[1 Feb 2008 1:53] li pickup

Many thanks for your quick response. I am sorry to hear that you can not repeat it.

My system is centos 4.4 (Redhat linux). It is a 64 bit version.

master:
[root@host26 ~]# uname -a
Linux host26 2.6.16-xen #1 SMP Fri Jul 14 10:02:29 CST 2006 x86_64 x86_64 x86_64 GNU/Linux

slave:
[root@host25 ~]# uname -a
Linux host25 2.6.16-xen #1 SMP Fri Jul 14 10:02:29 CST 2006 x86_64 x86_64 x86_64 GNU/Linux

[18 Feb 2008 4:55] Valeriy Kravchuk

Please, try to repeat with a newer version, 5.1.23-rc, and inform about the results.

[19 Feb 2008 7:15] li pickup

I am sorry to tell you that I can repeat this bug on version 5.1.23-rc-debug in our environment.

the slave is not identified with master:

with the table testGeo:
slave> select * from testGeo;
+----+------+------+
| id | a008 | a027 |
+----+------+------+
|  1 | NULL |      | 
+----+------+------+
1 row in set (0.00 sec)

slave> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| NULL         | 
+--------------+
1 row in set (0.00 sec)

with the table table1:
mysql> select * from table1\G 
*************************** 1. row ***************************
                 id: 1
                uid: 1
           bit_a001: 
       tinyint_a002: -34
       boolean_a003: 1
      smallint_a004: 123
     mediumint_a005: 00000000012
        bigint_a006: -1024
 floatunsigned_a007: 3.14159
 floatzerofill_a008: 0000000000000000000000000000000000000000000000000000000003.14159
doublezerofill_a009: 0000000000000000000000000000000000000000000000003.14159265358979
       decimal_a010: -3.142
          date_a011: 1969-12-31
      datetime_a012: 2005-04-08 22:04:08
          time_a013: -22:04:08
          year_a014: 2005
          char_a015: 214
         vchar_a016: 
        binary_a017: 
     varbinary_a018: 

      tinyblob_a019: 241
          text_a020: 4
    mediumblob_a021: 21
       logtext_a022: 
          emum_a023: e2
           set_a024: s1,s4
1 row in set (0.00 sec)

[19 Feb 2008 7:15] li pickup

I am sorry to tell you that I can repeat this bug on version 5.1.23-rc-debug in our environment.

the slave is not identified with master:

with the table testGeo:
slave> select * from testGeo;
+----+------+------+
| id | a008 | a027 |
+----+------+------+
|  1 | NULL |      | 
+----+------+------+
1 row in set (0.00 sec)

slave> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| NULL         | 
+--------------+
1 row in set (0.00 sec)

with the table table1:
slave> select * from table1\G 
*************************** 1. row ***************************
                 id: 1
                uid: 1
           bit_a001: 
       tinyint_a002: -34
       boolean_a003: 1
      smallint_a004: 123
     mediumint_a005: 00000000012
        bigint_a006: -1024
 floatunsigned_a007: 3.14159
 floatzerofill_a008: 0000000000000000000000000000000000000000000000000000000003.14159
doublezerofill_a009: 0000000000000000000000000000000000000000000000003.14159265358979
       decimal_a010: -3.142
          date_a011: 1969-12-31
      datetime_a012: 2005-04-08 22:04:08
          time_a013: -22:04:08
          year_a014: 2005
          char_a015: 214
         vchar_a016: 
        binary_a017: 
     varbinary_a018: 

      tinyblob_a019: 241
          text_a020: 4
    mediumblob_a021: 21
       logtext_a022: 
          emum_a023: e2
           set_a024: s1,s4
1 row in set (0.00 sec)

[19 Feb 2008 7:15] li pickup

I am sorry to tell you that I can repeat this bug on version 5.1.23-rc-debug in our environment.

the slave is not identified with master:

with the table testGeo:
slave> select * from testGeo;
+----+------+------+
| id | a008 | a027 |
+----+------+------+
|  1 | NULL |      | 
+----+------+------+
1 row in set (0.00 sec)

slave> select astext(a027) from testGeo where id =1;
+--------------+
| astext(a027) |
+--------------+
| NULL         | 
+--------------+
1 row in set (0.00 sec)

with the table table1:
slave> select * from table1\G 
*************************** 1. row ***************************
                 id: 1
                uid: 1
           bit_a001: 
       tinyint_a002: -34
       boolean_a003: 1
      smallint_a004: 123
     mediumint_a005: 00000000012
        bigint_a006: -1024
 floatunsigned_a007: 3.14159
 floatzerofill_a008: 0000000000000000000000000000000000000000000000000000000003.14159
doublezerofill_a009: 0000000000000000000000000000000000000000000000003.14159265358979
       decimal_a010: -3.142
          date_a011: 1969-12-31
      datetime_a012: 2005-04-08 22:04:08
          time_a013: -22:04:08
          year_a014: 2005
          char_a015: 214
         vchar_a016: 
        binary_a017: 
     varbinary_a018: 

      tinyblob_a019: 241
          text_a020: 4
    mediumblob_a021: 21
       logtext_a022: 
          emum_a023: e2
           set_a024: s1,s4
1 row in set (0.00 sec)

[10 Mar 2008 5:49] li pickup

my.cnf of my environment

Attachment: my.cnf.5.1.23 (text/plain), 1.99 KiB.

[21 Oct 2008 3:07] li pickup

I am glad to find the bug can not repeat in the new version 5.1.28.
Congratulations!