[MDEV-30440] Invalid default value on DEFAULT NULL TIMESTAMP Created: 2023-01-20  Updated: 2023-01-20  Resolved: 2023-01-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.5.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Florent Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 10


 Description   

On mariadb 10.5.4 it seen not possible to create a TIMESTAMP column with null default value.

For exemple :
CREATE TABLE Test (
test TIMESTAMP DEFAULT NULL
);

Return :
Error Code: 1067
Invalid default value for 'test'

 Comments   
Comment by Alice Sherepa [ 2023-01-20 ] 

MariaDB [test]> CREATE TABLE Test1 ( test TIMESTAMP DEFAULT NULL );
 
ERROR 1067 (42000): Invalid default value for 'test'
 
 
 
MariaDB [test]> CREATE TABLE Test2 ( test TIMESTAMP  NULL DEFAULT NULL);
 
Query OK, 0 rows affected (0,054 sec)

It works as expected. KB says: "MariaDB also has special behavior if NULL is assigned to column that uses the TIMESTAMP data type. If the column is assigned the NULL value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time.

This automatic initialization for NULL values can also be explicitly disabled for a column that uses the TIMESTAMP data type by specifying the NULL attribute for the column. In this case, if the column's value is set to NULL, then the column's value will actually be set to NULL." ( https://mariadb.com/kb/en/timestamp/)

So - first set NULL attribute to timestamp column - then set the default (NULL or something else if needed, CURRENT_TIMESTAMP e.g.)

Generated at Thu Feb 08 10:16:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.