Bogus Incorrect date value: '0000-00-00' error

If you want to make the DATE column mandatory in the following table:

CREATE TABLE `altas_talleres` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`fecha` DATE NULL,
`socio_id` INT(10) NOT NULL,
PRIMARY KEY (`id`),
INDEX `FK_altas_talleres_socios` (`socio_id`),
CONSTRAINT `FK_altas_talleres_socios` FOREIGN KEY (`socio_id`) REFERENCES `socios` (`idsocio`) ON UPDATE CASCADE
)
COLLATE='utf8_spanish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8;

... unchecking the "Allow NULL" check-box and hitting "Save" in the table editor throws this error:

SQL Error (1292): Incorrect date value: '0000-00-00' for column 'fecha' at row 1

If you copy the SQL statements from the "ALTER code" tab to a query tab, you get the same result. However, if you go to "Tools-> Launch command line" and run them there, everything goes smoothly:

mysql> ALTER TABLE `altas_talleres`
->  ALTER `fecha` DROP DEFAULT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE `altas_talleres`
->  CHANGE COLUMN `fecha` `fecha` DATE NOT NULL AFTER `id`;
Query OK, 6 rows affected (0.27 sec)
Records: 6  Duplicates: 0  Warnings: 0

It's 100% reproducible here. I'm using latest snapshot and a local 5.5.11 MySQL server on Windows XP.

P.S. Anse, if you forget to type the subject you lose the complete message!

Sorry for that destructive forum bug. Just fixed that, so you get a reasonable error message and the prefilled posting form now.

Trying to reproduce that DATE issue now.

Cannot reproduce here. Do you have data in that table which I don't have? Probably some NULL value is silently converted to '0000-00-00' which is for some reason unsupported or so.

Yes, if I insert a fecha=NULL value while it allows NULL, clicking NOT NULL converts it to '0000-00-00' here, silently.

MySQL tells you that this is an "incorrect date value" because you're running SQL mode "NO_ZERO_DATE".

I got a few NULL dates when I added the column to an existing table, but I took care of typing some valid dates in all columns before editing the table. I've configured HeidiSQL to run this on connect:

SET @@SESSION.sql_mode='TRADITIONAL';

I'm leaving for lunch but I'll try to reproduce it later in another database.

Alright, the root of the problem is the TRADITIONAL SQL mode. Here's a script that reproduces the problem on any client:

SET @@SESSION.sql_mode='TRADITIONAL';
DROP TABLE IF EXISTS altas_talleres;
CREATE TABLE altas_talleres (
id INT(10) NOT NULL AUTO_INCREMENT,
fecha DATE NULL,
PRIMARY KEY (id)
)
COLLATE='utf8_spanish_ci'
ENGINE=InnoDB;
INSERT INTO altas_talleres(fecha) VALUES('2012-10-03');
-- Staments issued by HeidiSQL to make fecha NOT NULL:

ALTER TABLE altas_talleres
ALTER fecha DROP DEFAULT;
ALTER TABLE altas_talleres
CHANGE COLUMN fecha fecha DATE NOT NULL AFTER id;

I think the problem is that I don't want to set a default value, because this does work:

ALTER TABLE altas_talleres
CHANGE COLUMN fecha fecha DATE NOT NULL DEFAULT '2010-01-01' AFTER id;

Is it yet another MySQL limitation?

Hm, I guess the TRADITIONAL mode is more to blame here, instead of MySQL itself. A cascade of limitations lets you stuck here. NOT NULL silently converts NULL dates into '0000-00-00', which is in turn not allowed in TRADITIONAL/NO_ZERO_DATE SQL mode.

Is there any reason for why you set TRADITIONAL mode? Secondly, if you don't want to allow NULLs in that "fecha" column, is it really better to have "0000-00-00" values instead? I would personally prefer NULLs instead. NULLs are easier to compare and handle in an application than a string-compare.

I use TRADITIONAL mode to grant data integrity and reduce surprises as much as MySQL allows.

I guess it's a MySQL bug given that you can have a NOT NULL date column without default value—you just cannot make the switch while in TRADITIONAL mode.

Clarification: there aren't NULL dates anywhere.