mirror of
https://github.com/MariaDB/server.git
synced 2025-01-16 03:52:35 +01:00
114 lines
3.7 KiB
Text
114 lines
3.7 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
--enable_info
|
|
--error WARN_DATA_TRUNCATED
|
|
ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
|
|
ALGORITHM=INPLACE;
|
|
--error WARN_DATA_TRUNCATED
|
|
ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(),
|
|
ALGORITHM=COPY;
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
|
|
|
|
# ALTER IGNORE cannot create unique or primary key with ALGORITHM=INPLACE.
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
|
|
# Try the same with ALTER TABLE, using non-strict sql_mode.
|
|
--disable_info
|
|
SET @old_sql_mode = @@sql_mode;
|
|
SET sql_mode = '';
|
|
--enable_info
|
|
ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE;
|
|
--disable_info
|
|
SET sql_mode = @old_sql_mode;
|
|
--enable_info
|
|
ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT;
|
|
--disable_info
|
|
SELECT * FROM t1;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (i1 INT UNSIGNED NOT NULL, d1 TIMESTAMP NULL) ENGINE=InnoDB;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
|
|
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
|
|
select * from t1;
|
|
set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
|
|
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:23:45',
|
|
ALGORITHM=INPLACE;
|
|
SELECT DISTINCT d1 FROM t1;
|
|
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NULL DEFAULT '2017-05-08 16:32:45',
|
|
ALGORITHM=COPY;
|
|
SELECT DISTINCT d1 FROM t1;
|
|
--enable_info
|
|
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP DEFAULT '2017-05-08 16:32:54';
|
|
--disable_info
|
|
SELECT COUNT(DISTINCT d1),COUNT(d1),COUNT(*) FROM t1;
|
|
drop table t1;
|
|
|
|
CREATE TABLE t1 (
|
|
`i1` INT(10) UNSIGNED NOT NULL,
|
|
`d1` TIMESTAMP NULL DEFAULT NULL
|
|
) ENGINE=innodb;
|
|
INSERT INTO t1 (i1) VALUES (1), (2), (3), (4), (5);
|
|
--enable_info
|
|
ALTER TABLE t1 CHANGE d1 d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
ALTER TABLE t1 ADD COLUMN w1 varchar(20) NULL DEFAULT USER();
|
|
ALTER TABLE t1 CHANGE w1 u1 varchar(30) NULL DEFAULT substr(USER(),1);
|
|
--disable_info
|
|
SELECT u1, COUNT(DISTINCT d1) FROM t1 GROUP BY u1;
|
|
|
|
--enable_info
|
|
ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45',
|
|
LOCK=NONE;
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED
|
|
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE, LOCK=NONE;
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED
|
|
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1;
|
|
--disable_info
|
|
SELECT d1-d3, d2 FROM t1;
|
|
SHOW CREATE TABLE t1;
|
|
--enable_info
|
|
ALTER TABLE t1 ADD COLUMN d4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
|
|
--disable_info
|
|
SELECT COUNT(DISTINCT d4),COUNT(d4),COUNT(*) FROM t1;
|
|
SELECT DISTINCT (CURRENT_TIMESTAMP()-d4) <= 60 FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
# MDEV-19611 INPLACE ALTER does not fail on bad implicit default value
|
|
|
|
# Empty-table
|
|
CREATE TABLE t1(f1 int) ENGINE=InnoDB;
|
|
INSERT INTO t1 SELECT * FROM seq_1_to_4096;
|
|
connect(purge_control,localhost,root,,);
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
|
|
connection default;
|
|
DELETE FROM t1;
|
|
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';
|
|
ALTER TABLE t1 ADD f2 DATE NOT NULL, ALGORITHM=INPLACE;
|
|
|
|
# Non-empty table
|
|
INSERT INTO t1 VALUES (1, now());
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED
|
|
ALTER TABLE t1 ADD f3 DATE NOT NULL, ALGORITHM=INPLACE;
|
|
DROP TABLE t1;
|
|
disconnect purge_control;
|
|
|
|
--echo #
|
|
--echo # MDEV-26458 SIGSEGV in innobase_table_is_empty() on ALTER TABLE
|
|
--echo #
|
|
CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
ALTER TABLE t DISCARD TABLESPACE;
|
|
SET sql_mode='NO_ZERO_DATE';
|
|
ALTER TABLE t ADD c DATE NOT NULL;
|
|
SET sql_mode=DEFAULT;
|
|
DROP TABLE t;
|
|
|
|
--echo # End of 10.3 tests
|