MySQL 8: trying to alter two TIMESTAMP tables from NOT NULL to NULL and from '0000-00-00 00:00:00' to NULL as default value

Question

I have this table

CREATE TABLE `acquisition` (
  `id` int NOT NULL,
  `received` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `parsed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I'm trying to change a couple of columns to nullable and assign them NULL as default value.

ALTER TABLE acquisition MODIFY COLUMN received TIMESTAMP  NULL DEFAULT NULL;
ALTER TABLE acquisition MODIFY COLUMN parsed TIMESTAMP  NULL DEFAULT NULL;

I get this:

#1067 - Invalid default value for 'parsed'

so looks like the first one is being executed but not the second one.

Any help?

Answer 1

This is an effect of current SESSION sql_mode server setting. You must clear it before your actions and restore after.

DEMO

SELECT @@sql_mode;
SET @old_sql_mode = @@sql_mode;
SET SESSION sql_mode = '';

@@sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

CREATE TABLE `acquisition` (
  `id` int NOT NULL,
  `received` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `parsed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SHOW CREATE TABLE acquisition;

Table	Create Table
acquisition	CREATE TABLE `acquisition` (   `id` int NOT NULL,   `received` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',   `parsed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO acquisition VALUES 
(1, NOW() - INTERVAL 5 DAY, NOW() - INTERVAL 4 DAY),
(2, NOW() - INTERVAL 3 DAY, DEFAULT),
(3, DEFAULT, NOW() - INTERVAL 2 DAY),
(4, DEFAULT, DEFAULT);
SELECT * FROM acquisition;

id	received	parsed
1	2023-01-20 13:07:12	2023-01-21 13:07:12
2	2023-01-22 13:07:12	0000-00-00 00:00:00
3	0000-00-00 00:00:00	2023-01-23 13:07:12
4	0000-00-00 00:00:00	0000-00-00 00:00:00

ALTER TABLE acquisition MODIFY COLUMN received TIMESTAMP  NULL DEFAULT NULL;
ALTER TABLE acquisition MODIFY COLUMN parsed TIMESTAMP  NULL DEFAULT NULL;
SHOW CREATE TABLE acquisition;
SELECT * FROM acquisition;
UPDATE acquisition
SET received = NULLIF(received, '0000-00-00 00:00:00'),
    parsed = NULLIF(parsed, '0000-00-00 00:00:00');
SET SESSION sql_mode = @old_sql_mode;
SELECT * FROM acquisition;

Table	Create Table
acquisition	CREATE TABLE `acquisition` (   `id` int NOT NULL,   `received` timestamp NULL DEFAULT NULL,   `parsed` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

id	received	parsed
1	2023-01-20 13:07:12	2023-01-21 13:07:12
2	2023-01-22 13:07:12	0000-00-00 00:00:00
3	0000-00-00 00:00:00	2023-01-23 13:07:12
4	0000-00-00 00:00:00	0000-00-00 00:00:00

id	received	parsed
1	2023-01-20 13:07:12	2023-01-21 13:07:12
2	2023-01-22 13:07:12	null
3	null	2023-01-23 13:07:12
4	null	null

fiddle