ERROR 1292 (22007): Incorrect date value: '0000-00-00' when changing table engine (Mysql)

Asked

Viewed 2,176 times

1

I am trying to change the engine of a table of MyISAM for InnoDB, and it occurs that the column date_install generates errors due to zero dates: 0000-00-00.

In this case, I used the following query:

update `glpidb`.`glpi_computers_softwareversions` set `date_install`='2019-01-01' where `date_install`='0000-00-00';

The error that is returned to me is:

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_install' at row 1

As previously reported, I am also trying to pass the table to Innodb.

Update query for InnoDB:

alter table `glpidb`.`glpi_computers_softwareversions` Engine=InnoDB;

*The error that occurs to me when running the update query for Innodb

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_install' at row 5102

The structure of the table is:

CREATE TABLE `glpi_computers_softwareversions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `computers_id` int(11) NOT NULL DEFAULT '0',
  `softwareversions_id` int(11) NOT NULL DEFAULT '0',
  `is_deleted_computer` tinyint(1) NOT NULL DEFAULT '0',
  `is_template_computer` tinyint(1) NOT NULL DEFAULT '0',
  `entities_id` int(11) NOT NULL DEFAULT '0',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `is_dynamic` tinyint(1) NOT NULL DEFAULT '0',
  `date_install` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unicity` (`computers_id`,`softwareversions_id`),
  KEY `softwareversions_id` (`softwareversions_id`),
  KEY `computers_info` (`entities_id`,`is_template_computer`,`is_deleted_computer`),
  KEY `is_template` (`is_template_computer`),
  KEY `is_deleted` (`is_deleted_computer`),
  KEY `is_dynamic` (`is_dynamic`),
  KEY `date_install` (`date_install`)
) ENGINE=MyISAM AUTO_INCREMENT=2476336 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Some records for test/playback of error:

INSERT INTO `glpi_computers_softwareversions` VALUES (47629, 185, 6448, 0, 0, 18, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (127367, 62, 14113, 0, 0, 18, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (127366, 62, 14112, 0, 0, 18, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (127393, 62, 14144, 0, 0, 18, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (849215, 180, 14113, 0, 0, 18, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (1907659, 367, 29433, 0, 0, 6, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (1907658, 367, 29432, 0, 0, 6, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475628, 34, 24352, 0, 0, 27, 0, 1, '2018-12-04');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475624, 34, 26906, 0, 0, 27, 0, 1, '2018-12-12');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475610, 34, 31514, 0, 0, 27, 0, 1, '2019-04-15');
INSERT INTO `glpi_computers_softwareversions` VALUES (2473876, 118, 12827, 0, 0, 16, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475617, 34, 24283, 0, 0, 27, 0, 1, '2019-02-14');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475009, 34, 18446, 0, 0, 27, 0, 1, '2018-03-06');
INSERT INTO `glpi_computers_softwareversions` VALUES (2473987, 34, 26589, 0, 0, 27, 0, 1, '2018-11-30');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475001, 34, 18438, 0, 0, 27, 0, 1, '2018-03-06');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475603, 34, 27965, 0, 0, 27, 0, 1, '2019-02-07');
INSERT INTO `glpi_computers_softwareversions` VALUES (2474994, 34, 18431, 0, 0, 27, 0, 1, '2018-03-06');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475602, 34, 28292, 0, 0, 27, 0, 1, '2019-02-07');
INSERT INTO `glpi_computers_softwareversions` VALUES (2473821, 31, 5556, 0, 0, 21, 0, 1, '0000-00-00');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475010, 34, 18447, 0, 0, 27, 0, 1, '2018-03-06');
INSERT INTO `glpi_computers_softwareversions` VALUES (2473687, 227, 31518, 0, 0, 16, 0, 1, '2019-04-16');
INSERT INTO `glpi_computers_softwareversions` VALUES (2473686, 227, 31517, 0, 0, 16, 0, 1, '2019-04-16');
INSERT INTO `glpi_computers_softwareversions` VALUES (2472637, 34, 18635, 0, 0, 27, 0, 1, NULL);
INSERT INTO `glpi_computers_softwareversions` VALUES (2475480, 25, 27617, 0, 0, 21, 0, 1, '2019-01-09');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475479, 25, 27634, 0, 0, 21, 0, 1, '2019-01-09');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475478, 25, 26592, 0, 0, 21, 0, 1, '2018-11-30');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475477, 25, 28387, 0, 0, 21, 0, 1, '2019-02-07');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475476, 25, 12202, 0, 0, 21, 0, 1, '2018-07-16');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475006, 34, 18443, 0, 0, 27, 0, 1, '2018-03-06');
INSERT INTO `glpi_computers_softwareversions` VALUES (2475590, 34, 18421, 0, 0, 27, 0, 1, '2018-05-15');
INSERT INTO `glpi_computers_softwareversions` VALUES (2474993, 34, 18430, 0, 0, 27, 0, 1, '2018-03-06');
mysql engine

  • same error, it cannot change by values to be 0000-00-00

  • only that when selecting the records, it returns me +/- 1000 records with 0000-00-00

  • ready :) I tidied up

  • With the data you passed, everything worked fine here: https://i.stack.Imgur.com/8pAxJ.png PS: I reworked the question to make it easier for other colleagues who are going to reproduce the error.

  • here it does not run :/ Can change records 0000-00-00 ?

  • Come here to chat. I’ll try to give you a situation that might result in some.

  • 1

    I managed to solve

  • 1

    Remember formulate a response with the solution of the problem. It can help someone in the future (by the way, I’m curious! ).

  • 1

    is done kkk :D

Show 4 more comments

1 answer

2


After a little more in-depth research, I found a solution to the problem. If needed to temporarily disable the strict mode, using

set session sql_mode = 'No_engine_substitution';

After that, it was necessary to update the values 0000-00-00 for Null, and then make the change to InnoDB.

Browser other questions tagged mysql engine

You are not signed in. Login or sign up in order to post.