Debian Codegraph @

mysql-test/suite/innodb/t/innodb-alter-nullable.test

raw
1
2 # Save the initial number of concurrent sessions.
3 --source include/count_sessions.inc
4
5 CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT) ENGINE=InnoDB;
6 INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9);
7
8 --enable_info
9 # This one will be a no-op. Instead of implicitly refusing to modify the
10 # PRIMARY KEY column c1 from NOT NULL to NULL, we now get an error for this
11 # operation with the patch for Bug#16545198.
12 --error ER_PRIMARY_CANT_HAVE_NULL
13 ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
14
15 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
16 --disable_info
17 --enable_info
18 ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
19 --disable_info
20 --enable_info
21
22 # Request some conflicting changes for a single column.
23 --error ER_BAD_FIELD_ERROR
24 ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
25 --error ER_BAD_FIELD_ERROR
26 ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL;
27 --error ER_BAD_FIELD_ERROR
28 ALTER TABLE t MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
29 --error ER_BAD_FIELD_ERROR
30 ALTER TABLE t MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
31
32 # No-ops.
33 ALTER TABLE t CHANGE c2 c2 INT NOT NULL;
34 ALTER TABLE t MODIFY c2 INT NOT NULL;
35 --disable_info
36
37 connect (con1,localhost,root,,);
38 connection con1;
39
40
41 --error ER_BAD_NULL_ERROR
42 UPDATE t SET c2=NULL;
43
44 SELECT * FROM t;
45
46 connection default;
47
48 # This should change the column to NULL.
49 ALTER TABLE t MODIFY c2 INT, ALGORITHM=INPLACE;
50
51 connection con1;
52 BEGIN;
53 UPDATE t SET c2=NULL;
54 SELECT * FROM t;
55 ROLLBACK;
56 SELECT * FROM t;
57
58 disconnect con1;
59 connection default;
60
61 # This should be no-op.
62 ALTER TABLE t MODIFY c2 INT NULL, ALGORITHM=INPLACE;
63
64 --replace_column 1 # 5 #
65 SELECT TABLE_ID, NAME, FLAG, N_COLS, SPACE, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE,
66 INSTANT_COLS, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
67 WHERE NAME='test/t';
68
69 DROP TABLE t;
70
71 CREATE TABLE t1(c1 INT);
72 ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1);
73 ALTER TABLE t1 CHANGE c1 c1 INT SIGNED NOT NULL,ADD KEY(c1);
74 DROP TABLE t1;
75
76 # Check that all connections opened by test cases in this file are really
77 # gone so execution of other tests won't be affected by their presence.
78 --source include/wait_until_count_sessions.inc