| Bug #108466 | "create table ... select" generates columns of type BIGINT with illegal width | ||
|---|---|---|---|
| Submitted: | 13 Sep 2022 6:03 | Modified: | 13 Sep 2022 8:02 |
| Reporter: | lyp tennyson | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7.38, 5.7.39 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Sep 2022 6:05]
lyp tennyson
No special configurations. [mysqld] basedir = /home/rpm/mysql5738 tmpdir = /home/rpm/tmp datadir = /home/rpm/data port = 3306 socket = /home/rpm/tmp/mysql.sock log_error=/home/rpm/error.log
[13 Sep 2022 8:02]
MySQL Verification Team
Hello lyp tennyson, Thank you for the report and test case. regards, Umesh
[24 Sep 2022 12:16]
huahua xu
Hi lyp tennyson, Your case will never occur in mysql 8.0, and the bug will cause the slave to fail.
[24 Sep 2022 12:21]
huahua xu
The patch could solve problems in some scenarios(includes above case).
Attachment: user_var_miss_type_length_bugfix.patch (application/octet-stream, text), 614 bytes.
[24 Sep 2022 12:29]
huahua xu
After apply the patch, the above case will result that: mysql> set names gb18030; mysql> CREATE TABLE dummy_log (id bigint(20) NOT NULL ) ENGINE=InnoDB; mysql> set @rd = 0; mysql> set @all_reduce=0; mysql> CREATE TABLE log_error AS SELECT zzcjf FROM (SELECT @all_reduce := @all_reduce + rd AS zzcjf FROM ( SELECT *, @rd AS rd FROM dummy_log) a) a; mysql> show create table log_error; +-----------+-----------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------+ | log_error | CREATE TABLE `log_error` ( `zzcjf` bigint(84) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+-----------------------------------------------------------------------------------------------------+
Description: "create table ... select" generates columns of type BIGINT with illegal width How to repeat: mysql> set names gb18030; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE dummy_log ( -> id bigint(20) NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> set @rd = 0; Query OK, 0 rows affected (0.00 sec) mysql> set @all_reduce=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE log_error AS -> SELECT -> zzcjf -> FROM -> ( -> SELECT -> @all_reduce := @all_reduce + rd AS zzcjf -> FROM -> ( -> SELECT -> *, -> @rd AS rd -> FROM -> dummy_log -> ) a -> ) a; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table log_error; +-----------+------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------+ | log_error | CREATE TABLE `log_error` ( `zzcjf` bigint(268) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) But, bitint(268) is invalid. mysql> CREATE TABLE `log_error` ( -> `zzcjf` bigint(268) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1439 (42000): Display width out of range for column 'zzcjf' (max = 255)