Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.1, 10.0
-
Labels:
-
Environment:Linux
-
Sprint:10.1.6-2
Description
Reproduction:
1) Create table
CREATE TABLE `table2` ( `i1` INT(10) UNSIGNED NOT NULL, `d1` TIMESTAMP NULL DEFAULT NULL ) ENGINE=INNODB;
2) Fill table
INSERT INTO table2 (i1) VALUES (1), (2), (3), (4), (5);
3) Try Alter table with making field `d1` not nullable with default value CURRENT_TIMESTAMP
ALTER TABLE `table2` CHANGE `d1` `d1` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
Error happens here:
Error Code: 1138 Invalid use of NULL value
Expected that column `d1` will be filled with NOW() value
SELECT @@sql_mode
@@sql_mode --------------------------------------------------------------------------------------------------------------------------- NO_BACKSLASH_ESCAPES,STRICT_ALL_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
In continue bug https://mariadb.atlassian.net/browse/MDEV-6880 but also needed with already created fields.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Based on manual about alter table syntax it should be:
ALTER TABLE `table2` CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
But at least on 10.1 both are accepted and result is correct i.e. d1 has value NOW().
Yes, I tried to switch 'NOT NULL' and 'DEFAULT', it does not make a difference.
Regarding it working on 10.1, please note the non-empty sql_mode – that makes a difference.
MariaDB [test]> CREATE TABLE `table2` (
-> `i1` INT(10) UNSIGNED NOT NULL,
-> `d1` TIMESTAMP NULL DEFAULT NULL
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (2.32 sec)
MariaDB [test]> INSERT INTO table2 (i1) VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.20 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [test]> set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> ALTER TABLE `table2` CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ERROR 1138 (22004): Invalid use of NULL value
MariaDB [test]> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> ALTER TABLE `table2` CHANGE `d1` `d1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
Query OK, 5 rows affected (2.24 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [test]> select @@version;
+----------------------+
| @@version |
+----------------------+
| 10.1.5-MariaDB-debug |
+----------------------+
1 row in set (0.00 sec)
commit 1a8cf15d63230a84e6d4dfac8011008e1331994f
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Jun 30 22:24:37 2015 +0300
MDEV-8392: Couldn't alter field with default value for make it not nullable.
Analysis; Problem is that InnoDB does not have support for generating
CURRENT_TIMESTAMP or constant default.
Fix: Add additional check if column has changed from NULL -> NOT NULL
and column default has changed. If this is is first column definition
whose SQL type is TIMESTAMP and it is defined as NOT NULL and
it has either constant default or function default we must use
"Copy" method for alter table.
Thanks for the report.
Same happens on MySQL 5.6/5.7; only with InnoDB.
CURRENT_TIMESTAMP is not important, there could be a constant instead, the result is the same.
Apparently, it's caused by online alter table. If I force ALGORITHM=COPY in the alter, it works all right.
Jan Lindström,
Do you think it's supposed to be like that? I doubt that, but I would like to get the 2nd opinion. If it's a bug, we should probably re-report it at bugs.mysql.com.