Snip from result of my MTR test including comments
|
CREATE TABLE t1 (
|
col1 VARCHAR(7961) , col2 CHAR(10)
|
) ENGINE=InnoDB ;
|
ALTER TABLE `t1` ADD IF NOT EXISTS ( `col1` SERIAL ) ;
|
ERROR 42000: Specified key was too long; max key length is 3072 bytes
|
### Why that?
|
### It was asked to add a column of data type SERIAL which is according the manual
|
### BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE only if it does not already exist.
|
### And when making a KEY at all than it should be on a SERIAL --> BIGINT
|
### and not VARCHAR(7961)!
|
DROP TABLE t1;
|
CREATE TABLE t1 (
|
col1 VARCHAR(30) , col2 CHAR(10)
|
) ENGINE=InnoDB ;
|
ALTER TABLE `t1` ADD IF NOT EXISTS ( `col1` SERIAL ) ;
|
Warnings:
|
Note 1060 Duplicate column name 'col1'
|
### Looks nice.
|
layout_before and layout_after are not equal
|
layout_before : -->t1 CREATE TABLE `t1` (
|
`col1` varchar(30) DEFAULT NULL,
|
`col2` char(10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1<--
|
layout_after : -->t1 CREATE TABLE `t1` (
|
`col1` varchar(30) DEFAULT NULL,
|
`col2` char(10) DEFAULT NULL,
|
UNIQUE KEY `col1` (`col1`)
|
### So a failing DDL has just silent
|
### - created a KEY (consumes resources)
|
### - including a UNIQUE constraint which changes the properties of the column
|
### 'col1' when being affected by SQL.
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1<--
|
DROP TABLE t1;
|
CREATE TABLE t1 (
|
col1 VARCHAR(30) , col2 CHAR(10)
|
) ENGINE=InnoDB ;
|
INSERT INTO t1 (col1, col2) VALUES ('ã','ã'),('ã','ã');
|
SELECT * FROM t1;
|
col1 col2
|
ã ã
|
ã ã
|
ALTER IGNORE TABLE `t1` ADD IF NOT EXISTS ( `col1` SERIAL ) ;
|
Warnings:
|
Note 1060 Duplicate column name 'col1'
|
layout_before and layout_after are not equal
|
layout_before : -->t1 CREATE TABLE `t1` (
|
`col1` varchar(30) DEFAULT NULL,
|
`col2` char(10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1<--
|
layout_after : -->t1 CREATE TABLE `t1` (
|
`col1` varchar(30) DEFAULT NULL,
|
`col2` char(10) DEFAULT NULL,
|
UNIQUE KEY `col1` (`col1`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1<--
|
SELECT * FROM t1;
|
col1 col2
|
ã ã
|
### So a failing DDL with IGNORE has just removed a row.
|
DROP TABLE t1;
|
CREATE TABLE t1 (
|
col1 VARCHAR(7961) , col2 CHAR(10)
|
) ENGINE=InnoDB ;
|
ALTER TABLE `t1` ADD IF NOT EXISTS `col1` SERIAL ;
|
Warnings:
|
Note 1060 Duplicate column name 'col1'
|
### Looks nice and the table definition is correct too.
|
DROP TABLE t1;
|
|
The problem above is NOT InnoDB specific. I get it with MyISAM too.
|
|
10.2.23 commit d3afdb1e8f0ab5d2d5257ee931fe5d4d2ed82ced 2019-03-14
|
10.3.14 commit 51e48b9f8981986257a1cfbdf75e4fc29a5959c1 2018-12-24
|
source was pulled 2019-03-14
|
10.4.4 commit e3ebeebe3a4c14293a48971e0a062008a1473140 2019-03-14
|
Here I get some a bit different but not sufficient better result.
|
|
Bug
Major