[MDEV-30468] ALTER COLUMN NOT NULL fails with "cannot convert NULL to non-constant DEFAULT" error even if default is constant when using INPLACE algorithm Created: 2023-01-25  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.3, 10.4, 10.3.37, 10.4.27, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Sébastien Volle Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 18.04.1 LTS, Ubuntu 20.04.5 LTS


 Description   

Applying an online change for a column from NULL to NOT NULL using a constant default value fails with a "cannot convert NULL to non-constant DEFAULT".

CREATE TABLE `test_seb` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `created` timestamp NULL DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `created` (`created`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
ALTER TABLE test_seb MODIFY created timestamp NOT NULL DEFAULT '2000-01-01 00:00:00', ALGORITHM=INPLACE;
 
 
 
/* Error:
 
SQL Error [1846] [0A000]: (conn:248597) ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY
 
*/

Trying to apply a constant default before applying the NOT NULL change does not change the behavior.

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

This seems to be a related to timestamp type (and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP):

MariaDB [test]> CREATE TABLE test_seb ( created timestamp NULL DEFAULT NULL) engine=innodb ;
 
Query OK, 0 rows affected (0,052 sec)
 
 
 
MariaDB [test]>  ALTER TABLE test_seb MODIFY created timestamp NOT NULL DEFAULT '2000-01-01 00:00:00', ALGORITHM=INPLACE;
 
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY
 
 
 
MariaDB [test]> CREATE or replace TABLE test_seb ( created datetime NULL DEFAULT NULL) engine=innodb ;
 
Query OK, 0 rows affected (0,036 sec)
 
 
 
MariaDB [test]>  ALTER TABLE test_seb MODIFY created datetime NOT NULL DEFAULT '2000-01-01 00:00:00', ALGORITHM=INPLACE;
 
Query OK, 0 rows affected (0,050 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

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