DBA reported a bug. An online DDL statement causes the master and slave to stop. The problem is described as follows. Description: The procedure is as follows: 1. Createtabletb (cvarchar (1000) engineinnodb; 2.3.Createtabletcasselectcast (cassignedinteger) fromtb; 4.5.Showcreatetabletc; 6.7
DBA reported a bug. An online DDL statement causes the master and slave to stop. The problem is described as follows. Description: The procedure is as follows: 1. create table tb (c varchar (1000) engine = innodb; 2. 3. create table tc as select cast (c as signed integer) from tb; 4. 5. show create table tc; 6. 7
DBA reported a bug. An online DDL statement causes the master and slave to stop. The problem is described as follows.
Description:
The procedure is as follows:
1. Create table tb (c varchar (1000) engine = innodb;
2.
3. Create table tc as select cast (c as signed integer) from tb; 4.
5. Show create table tc; 6.
7. create table 'tc '8. ('Cast (c as signed integer) 'bigint (1000) default null) ENGINE = InnoDB default charset = gbk as shown in the preceding results, this field in the tc table is defined as bigint (1000 ). This operation is normal on the master database, but when binlog applies from the slave database, it will find that synchronization is stopped, errno 1439.
Analysis
In fact, we found that this statement is executed independently in the Master.
Create table 'tc '(
'Cast (c as signed integer) 'bigint (1000) DEFAULT NULL
) ENGINE = InnoDB default charset = gbk,
The reason is that the display width of bigint and int defined in MySQL cannot exceed 255, but this limit is determined during parsing.
In this example, the tc table does not explicitly indicate the display width of bigint, but it is calculated as long as it is during internal conversion. The binlog records the result after calculation. As a result, when the statement is executed directly from the database, an error is returned.
Simple Solution
In fact, there is no need to use as much as 1000 for the display width, so there is no improper restriction for MySQL. Therefore, we only need to make a judgment when executing the statement (instead of parsing it). When the display width of an integer field exceeds 255, set it to 255.
In this way, the statement executed from the database is bigint (255), and this statement can be correctly executed.
Of course, this error message is displayed in warning.
