MySQL Bugs: #71179: CREATE TABLE .. SELECT produces invalid structure, breaks RBR

Bug #71179	CREATE TABLE .. SELECT produces invalid structure, breaks RBR
Submitted:	18 Dec 2013 21:00	Modified:	14 May 2014 14:25
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> CREATE TABLE t1 AS SELECT REPEAT('A',1000) DIV 1 AS a;
Query OK, 1 row affected, 1 warning (0.63 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+--------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` bigint(3000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

131218 23:57:36 [ERROR] Slave SQL: Error 'Display width out of range for column 'a' (max = 255)' on query. Default database: 'test'. Query: 'CREATE TABLE `t1` (
  `a` bigint(1000) DEFAULT NULL
)', Error_code: 1439
131218 23:57:36 [Warning] Slave: Display width out of range for column 'a' (max = 255) Error_code: 1439
131218 23:57:36 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 221

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 AS SELECT REPEAT('A',1000) DIV 1 AS a;
SHOW CREATE TABLE t1;

--sync_slave_with_master

Hello Elena,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

based on 5.5.18 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bugfix_71179.diff (application/octet-stream, text), 2.14 KiB.

Posted by developer:
 
Hi Xiobin Lin,

Thank you for the contribution! We will do some changes to it and fix in a coming release.

Sorry for mistyping your name above, Xiaobin Lin

Noted in 5.5.38, 5.6.19, 5.7.5 changelogs.

A statement of the following form broke row-based replication because 
it created a table having a field of data type BIGINT with a display
width of 3000, which is beyond the maximum acceptable value of 255:
    
CREATE TABLE t1 AS SELECT REPEAT('A',1000) DIV 1 AS a;

$ bzr log -r 4626
------------------------------------------------------------
revno: 4626
committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
branch nick: mysql-5.5-17994219
timestamp: Mon 2014-04-28 16:28:09 +0530
message:
  BUG#17994219: CREATE TABLE .. SELECT PRODUCES INVALID STRUCTURE,
                BREAKS RBR
  
  Analysis:
  --------
  A table created using a query of the format:
  CREATE TABLE t1 AS SELECT REPEAT('A',1000) DIV 1 AS a;
  breaks the Row Based Replication.
  
  The query above creates a table having a field of datatype
  'bigint' with a display width of 3000 which is beyond the
  maximum acceptable value of 255.
  
  In the RBR mode, CREATE TABLE SELECT statement is
  replicated as a combination of CREATE TABLE statement
  equivalent to one the returned by SHOW CREATE TABLE and
  row events for rows inserted. When this CREATE TABLE event
  is executed on the slave, an error is reported:
  Display width out of range for column 'a' (max = 255)
  
  The following is the output of 'SHOW CREATE TABLE t1':
  CREATE TABLE t1(`a` bigint(3000) DEFAULT NULL)
                    ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  The problem is due to the combination of two facts:
  
  1) The above CREATE TABLE SELECT statement uses the display
     width of the result of DIV operation as the display width
     of the column created without validating the width for out
     of bound condition.
  2) The DIV operation incorrectly returns the length of its first
     argument as the display width of its result; thus allowing
     creation of a table with an incorrect display width of 3000
     for the field.
  
  Fix:
  ----
  This fix changes the DIV operation implementation to correctly
  evaluate the display width of its result. We check if DIV's
  results estimated width crosses maximum width for integer
  value (21) and if yes set it to this maximum value.
  
  This patch also fixes fixes maximum display width evaluation
  for DIV function when its first argument is in UCS2.