MySQL Bugs: #99512: ALTER TABLE ... ADD generates ERROR while MODIFY and CREATE TABLE doesn't

Bug #99512	ALTER TABLE ... ADD generates ERROR while MODIFY and CREATE TABLE doesn't
Submitted:	11 May 2020 12:09	Modified:	4 Jun 2020 17:41
Reporter:	Wim Goedertier	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
"ALTER TABLE ... ADD ... DATETIME (sysdate());"
generates
"ERROR 1674 (HY000) at line 13: Statement is unsafe because it uses a system function that may return a different value on the slave."
while
"ALTER TABLE ... MODIFY ... DATETIME (sysdate());"
and
"CREATE TABLE ... ( ... DATETIME (sysdate()) );"
doesn't generate any error or warning.

This behavior seems inconsistent to me.
Remark that the error doesn't pop up when using "now()" instead of "sysdate()".

How to repeat:
mysql> DROP TABLE IF EXISTS tst1;
mysql> CREATE TABLE tst1 ( id DECIMAL(3) );
mysql> ALTER TABLE tst1 ADD mydate DATETIME DEFAULT (sysdate());
ERROR 1674 (HY000) at line 13: Statement is unsafe because it uses a system function that may return a different value on the slave.

mysql> DROP TABLE IF EXISTS tst1;
mysql> CREATE TABLE tst1 ( id DECIMAL(3), mydate DATETIME DEFAULT (sysdate()) );
mysql> DESCRIBE tst1;
+--------+--------------+------+-----+-----------+-------------------+
| Field  | Type         | Null | Key | Default   | Extra             |
+--------+--------------+------+-----+-----------+-------------------+
| id     | decimal(3,0) | YES  |     | NULL      |                   |
| mydate | datetime     | YES  |     | sysdate() | DEFAULT_GENERATED |
+--------+--------------+------+-----+-----------+-------------------+

mysql> DROP TABLE IF EXISTS tst1;
mysql> CREATE TABLE tst1 ( id DECIMAL(3), mydate DATETIME );
mysql> ALTER TABLE tst1 MODIFY mydate DATETIME DEFAULT (sysdate());
mysql> DESCRIBE tst1;
+--------+--------------+------+-----+-----------+-------------------+
| Field  | Type         | Null | Key | Default   | Extra             |
+--------+--------------+------+-----+-----------+-------------------+
| id     | decimal(3,0) | YES  |     | NULL      |                   |
| mydate | datetime     | YES  |     | sysdate() | DEFAULT_GENERATED |
+--------+--------------+------+-----+-----------+-------------------+

Suggested fix:
Do not generate the error for ALTER TABLE ... ADD
(or generate it for all 3: CREATE TABLE, ALTER TABLE MODIFY, ALTER TABLE ADD)

Might (or might not) be related to bug #99513

Hello Mr. Goedertier,

Thank you for your bug report.

I managed to repeat it with mysql-8.0.20:

ERROR 1674 (HY000) at line 3: Statement is unsafe because it uses a system function that may return a different value on the slave.
Field	Type	Null	Key	Default	Extra
id	decimal(3,0)	YES		NULL
Field	Type	Null	Key	Default	Extra
id	decimal(3,0)	YES		NULL
mydate	datetime	YES		sysdate()	DEFAULT_GENERATED
Field	Type	Null	Key	Default	Extra
id	decimal(3,0)	YES		NULL
mydate	datetime	YES		sysdate()	DEFAULT_GENERATED

Verified as reported.

This is expected behavior. See: bug#92794

Regarding NOW vs SYSDATE, the manual (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_sysdate) states that:

 "In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is nondeterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging.

Alternatively, you can use the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). This works if the option is used on both the master and the slave.

The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it. "

Posted by developer:
 
mysql> DROP TABLE IF EXISTS tst1;
mysql> CREATE TABLE tst1 ( id DECIMAL(3) );
mysql> ALTER TABLE tst1 ADD mydate DATETIME DEFAULT (sysdate());
ERROR 1674 (HY000) at line 13: Statement is unsafe because it uses a system
function that may return a different value on the slave.

The ALTER TABLE statement generates the warning because it changes existing row values using a nondeterministic function.

mysql> DROP TABLE IF EXISTS tst1;
mysql> CREATE TABLE tst1 ( id DECIMAL(3), mydate DATETIME DEFAULT (sysdate()) );

The CREATE TABLE statement generates no warning because it does not change any rows.

mysql> DROP TABLE IF EXISTS tst1;
mysql> CREATE TABLE tst1 ( id DECIMAL(3), mydate DATETIME );
mysql> ALTER TABLE tst1 MODIFY mydate DATETIME DEFAULT (sysdate());

The ALTER TABLE statement generates no warning because it is a metadata changes only and does not change any rows.

Additional information:
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html
(look for "nondeterministic")

Thank you, Paul.