Bug #57098 RBR breaks on changing user password on 5.1 master -> 5.5 slave
Submitted: 29 Sep 2010 12:34 Modified: 15 Nov 2010 19:40
Reporter: Nidhi Shrotriya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.6-rc, 5.1 OS:Any
Assigned to: Alfranio Correia
Triage: Triaged: D2 (Serious)

[29 Sep 2010 12:34] Nidhi Shrotriya
Description:
ROW based Replication breaks between 5.1 master and 5.5 slave on 'SET PASSWORD' statement 
SET PASSWORD FOR 'create_rout_db'@'localhost' = PASSWORD('create_rout_db');

with error as below:
100929 14:17:17 [ERROR] Slave SQL: Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'', Error_code: 1677

How to repeat:
Start 5.1 (5.1.50)master: 
----------------------------
-bash-3.2$ bin/mysqld_safe --no-defaults --server-id=1 --socket=/tmp/mysql1.sock --port=3308 --log-bin=mysql-bin --binlog-format=row &

Start 5.5 (5.5.6-rc)slave:
--------------------------
-bash-3.2$ bin/mysqld_safe --no-defaults --server-id=2 --socket=/tmp/mysql2.sock --port=3309 &

Connect to slave:
--------------------
-bash-3.2$ bin/mysql --no-defaults --user=root --socket=/tmp/mysql2.sock
mysql> CHANGE MASTER TO MASTER_HOST='localhost',MASTER_PORT=3308,MASTER_USER='root',MASTER_LOG_FILE='mysql-bin.000001',  MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.09 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Connect to master:
------------------
-bash-3.2$ bin/mysql --no-defaults --user=root --socket=/tmp/mysql1.sock
Execute:
DROP DATABASE IF EXISTS privdb;
CREATE DATABASE privdb;
USE privdb;
GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost';
SET PASSWORD FOR 'create_rout_db'@'localhost' = PASSWORD('create_rout_db');

On master:
------------
mysql> show binlog events;
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                    |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |    4 | Format_desc |         1 |         106 | Server ver: 5.1.50-enterprise-commercial-advanced-log, Binlog ver: 4                                    |
| mysql-bin.000001 |  106 | Query       |         1 |         201 | DROP DATABASE IF EXISTS privdb                                                                          |
| mysql-bin.000001 |  201 | Query       |         1 |         288 | CREATE DATABASE privdb                                                                                  |
| mysql-bin.000001 |  288 | Query       |         1 |         417 | use `privdb`; GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost'                          |
| mysql-bin.000001 |  417 | Query       |         1 |         487 | BEGIN                                                                                                   |
| mysql-bin.000001 |  487 | Table_map   |         1 |         640 | table_id: 2 (mysql.user)                                                                                |
| mysql-bin.000001 |  640 | Update_rows |         1 |         883 | table_id: 2 flags: STMT_END_F                                                                           |
| mysql-bin.000001 |  883 | Query       |         1 |        1037 | use `privdb`; SET PASSWORD FOR 'create_rout_db'@'localhost'='*08792480350CBA057BDE781B9DF183B263934601' |
| mysql-bin.000001 | 1037 | Query       |         1 |        1108 | COMMIT                                                                                                  |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

Check status on slave:
-----------------------
mysql> show slave status;

| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File         | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error                                                                                                          | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error                                                                                                      | Replicate_Ignore_Server_Ids | Master_Server_Id |

| Waiting for master to send event | localhost   | root        |        3308 |            60 | mysql-bin.000001 |                1108 | siv35-relay-bin.000002 |           563 | mysql-bin.000001      | Yes              | No                |                 |                     |                    |                        |                         |                             |       1677 | Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'' |            0 |                 417 |            1410 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |           1677 | Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'' |                             |                1 |

1 row in set (0.00 sec)
[5 Oct 2010 23:38] Alfranio Correia
The problem happens because the mysql.user's definition in the master is different from the slave. There is an additional field in the slave's mysql.user

   ...
   `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
   `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
   `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
+  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
   `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ssl_cipher` blob NOT NULL,
   `x509_issuer` blob NOT NULL,
   ...

And in RBR, the SET PASSWORD is replicated in row format and triggers the following error: "Column 32 of table 'mysql.user' cannot be converted from type 'tinyblob' to type 'enum('','ANY','X509','SPECIFIED'':

MASTER.Trigger_priv -ok-> SLAVE.Trigger_priv
MASTER.ssl_type     -ok-> SLAVE.tablespave_priv
MASTER.ssl_cipher   -XX-> SLAVE.slave_type
[6 Oct 2010 10:21] 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/120084

3523 Alfranio Correia	2010-10-06
      BUG#57098 RBR breaks on changing user password on 5.1 master -> 5.5 slave
      
      Backported the patch for BUG#55452.
[10 Oct 2010 20:27] Alfranio Correia
Patch queued to 5.1-bugteam --> 5.5-bugteam --> trunk-merge
[10 Oct 2010 21:33] Alfranio Correia
This patch enables to replicate the SET PASSWORD from >=5.1.53 to 5.5.
See BUG#57357 for information on <5.1.53 to 5.5.
[13 Oct 2010 13:07] Jon Stephens
Documented bugfix in the 5.5.7 changelog, as follows:

      SET PASSWORD caused row-based replication to fail between a MySQL 5.1
      master and a MySQL 5.5 slave.      

      This fix makes it possible to replicate SET PASSWORD correctly from a
      master running MySQL 5.1.53 or a later MySQL 5.1 release to a slave
      running MySQL 5.5.7 or a later MySQL 5.5 release.

      See also BUG#57357 and BUG#55452.

Set NM status, waiting for push to 5.6.
[13 Oct 2010 13:47] Jon Stephens
Discussed with Alfranio, no new merges expected, closing.
[13 Oct 2010 14:59] Alfranio Correia
Note that backport is backward compatible.
[13 Oct 2010 15:02] Jon Stephens
Disregard previous comment, closed in error. returned to NM status.
[29 Oct 2010 17:10] Jon Stephens
Already documented in the 5.1.53, 5.57, and 5.6.1 changelogs. Closing.
[9 Nov 2010 19:48] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:20] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:39] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[15 Nov 2010 19:40] Jon Stephens
No new changelogs entries required. Closed.
[18 Nov 2010 15:55] Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)