Bug #66773 a simple case that may break replication

• Submitted: 11 Sep 2012 16:09
• Modified: 12 Sep 2012 17:15
• Reporter: zhai weixiang (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.5.27, 5.5.29
• OS: Any
• CPU Architecture: Any

[11 Sep 2012 16:09] zhai weixiang

Description:
see below 

"create table select..." was converted to "create table ()", but the table's alias was not converted in binlog.

How to repeat:
create table t1 (a bigint(20), feature varchar(4000));
create table t2 (a bigint(20), feature varchar(4000));
create table tmpp as select b.a, cast(substr(a.feature,15) as signed integer) from t1 a, t2 b;

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                                                         |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |     17989 |         107 | Server ver: 5.5.27-log, Binlog ver: 4                                                                                                        |
| mysql-bin.000001 | 107 | Query       |     17989 |         188 | create database xxx                                                                                                                          |
| mysql-bin.000001 | 188 | Query       |     17989 |         303 | use `xxx`; create table t1 (a bigint(20), feature varchar(4000))                                                                             |
| mysql-bin.000001 | 303 | Query       |     17989 |         418 | use `xxx`; create table t2 (a bigint(20), feature varchar(4000))                                                                             |
| mysql-bin.000001 | 418 | Query       |     17989 |         485 | BEGIN                                                                                                                                        |
| mysql-bin.000001 | 485 | Query       |     17989 |         676 | use `xxx`; CREATE TABLE `tmpp` (
  `a` bigint(20) DEFAULT NULL,
  `cast(substr(a.feature,15) as signed integer)` bigint(7986) DEFAULT NULL
) |
| mysql-bin.000001 | 676 | Xid         |     17989 |         703 | COMMIT /* xid=27 */                                                                                                                          |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------+

Suggested fix:
still gdb.

[11 Sep 2012 19:01] Sveta Smirnova

Thank you for the report.

I see no problem here.

Please send us output of show create table tmpp;

[12 Sep 2012 1:42] zhai weixiang

mysql> show create table tmpp\G
*************************** 1. row ***************************
       Table: tmpp
Create Table: CREATE TABLE `tmpp` (
  `a` bigint(20) DEFAULT NULL,
  `cast(substr(a.feature,15) as signed integer)` bigint(7986) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

Sorry, I had a mistake of creating table . But I tried to  execute the statement in binlog  to create another table, and got an error

mysql> CREATE TABLE `tmpq` (   `a` bigint(20) DEFAULT NULL,   `cast(substr(a.feature,15) as signed integer)` bigint(7986) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
ERROR 1439 (42000): Display width out of range for column 'cast(substr(a.feature,15) as signed integer)' (max = 255)

So ,is it possible that this error can  break replication in slave ?

[12 Sep 2012 17:15] Sveta Smirnova

Thank you for the feedback.

Actually this is not replication-related problem: slave replicates all changes just fine. This is issue with how table is created: it is nonsense to have bigint(7986) column.

And this is fixed in version 5.7 already, but since there is no public bug report for 5.5 I set this as "Verified".

[13 Sep 2012 3:58] liu hickey

Any backport plan for 5.5?