Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
-
None
Description
I need some help categorizing this issue, as I'm not sure where the "legacy design" ends and the unexpected behavior starts. There can also be more than one problem here.
The results are for 11.0 with
select @@explicit_defaults_for_timestamp; |
@@explicit_defaults_for_timestamp
|
1
|
although it behaves the same way with explicit_defaults_for_timestamp=OFF.
In the test case below, the table t1 is first created with a current_timestamp as a default for a non-null datetime column. It gets created correctly, as expected.
|
11.0 a79abb65 |
CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP); |
show create table t1; |
Table Create Table |
t1 CREATE TABLE `t1` ( |
`a` datetime NOT NULL DEFAULT current_timestamp() |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
Then ALTER TABLE attempts to drop the default. ALTER ends without warnings or errors, but the resulting table still has a default for the column:
ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT; |
show create table t1; |
Table Create Table |
t1 CREATE TABLE `t1` ( |
`a` datetime NOT NULL DEFAULT current_timestamp() |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
I would say that in case of explicit_defaults_for_timestamp=ON it is already a problem, but given MDEV-28983, I'm not completely sure.
The next ALTER adds a column with a CHECK constraint referring to this existing column, and puts the new column before the existing one. It gets added successfully.
ALTER TABLE t1 ADD b DATE CHECK (b > a) FIRST; |
show create table t1; |
Table Create Table |
t1 CREATE TABLE `t1` ( |
`b` date DEFAULT NULL CHECK (`b` > `a`), |
`a` datetime NOT NULL DEFAULT current_timestamp() |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
But the resulting structure is invalid. If we try to create such a table directly, it fails:
query 'CREATE TABLE `t1` ( |
`b` date DEFAULT NULL CHECK (`b` > `a`),
|
`a` datetime NOT NULL DEFAULT current_timestamp()
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
' failed: ER_EXPRESSION_REFERS_TO_UNINIT_FIELD (4029): Expression for field `b` is referring to uninitialized field `a` |
So I'd say it's another problem, either ALTER should not succeed if it creates an invalid structure, or this structure should be valid. But I can't figure out why it is important that the table has a fake current_timestamp, and why it doesn't fail when the table simply does not have a default from the start.
The resulting incorrect structure of course makes any further mysqldump output invalid or, in the test case here, causes a replication failure.
2023-03-29 23:16:29 8 [ERROR] Slave SQL: Error 'Expression for field `b` is referring to uninitialized field `a`' on query. Default database: 'test'. Query: 'CREATE TABLE `t2` ( |
`b` date DEFAULT NULL CHECK (`b` > `a`),
|
`a` datetime NOT NULL DEFAULT current_timestamp()
|
)', Gtid 0-1-4, Internal MariaDB error code: 4029 |
2023-03-29 23:16:29 8 [Warning] Slave: Expression for field `b` is referring to uninitialized field `a` Error_code: 4029 |
But I don't believe that replication is to blame here.
|
Test case |
--source include/have_binlog_format_row.inc
|
--source include/master-slave.inc
|
|
|
CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP); |
ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT; |
ALTER TABLE t1 ADD b DATE CHECK (b > a) FIRST; |
|
|
CREATE TABLE t2 SELECT * FROM t1; |
|
|
--sync_slave_with_master
|
|
|
# Cleanup
|
--connection master
|
DROP TABLE t1, t2; |
--source include/rpl_end.inc |