Home / Questions / 165195
Accepted
Norak
Asked: 2020-05-18 03:23:42 +0800 CST 2020-05-18 03:23:42 +0800 CST

Error inserting new column with MySQL

  • 772

I have a small script where I want to insert a new column to a database table.

This is the content of the file myscript_ddl.sql:

START TRANSACTION;
ALTER TABLE e1qxp_productos ADD COLUMN `cierre_venta` TINYINT(4) NULL DEFAULT NULL;
COMMIT;

And to execute it I do it by command line as follows:

mysql -u usuario -pclave nombrebasededatos < myscript_ddl.sql

It throws me the following error and it won't let me add the field:

ERROR 1067 (42000) at line 2: Invalid default value for 'start_date'

I have tried it manually from PhpMyadmin and the exact same thing happens, the same error. It also refers to the fact fecha_iniciothat it is another column in the database that should not have anything to do with the new insertion.

For what is this? How can I solve that?

Edit: I add the definition of the table

CREATE TABLE `e1qxp_productos` (
  `id` int(11) UNSIGNED NOT NULL,
  `state` tinyint(1) NOT NULL,
  `nombre` varchar(255) NOT NULL,
  `apellidos` varchar(255) NOT NULL,
  `nif_cif` char(9) NOT NULL,
  `direccion` varchar(100) NOT NULL,
  `ciudad` varchar(100) NOT NULL,
  `provincia` varchar(100) NOT NULL,
  `cp` varchar(100) NOT NULL,
  `tipo_cliente` enum('persona','empresa') DEFAULT 'persona',
  `tipo_producto` tinyint(1) DEFAULT '-1',
  `fecha_inicio` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `fecha_fin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `precio` float DEFAULT NULL,
  `marca` varchar(255) DEFAULT NULL,
  `enviado` tinyint(1) NOT NULL DEFAULT '0',
  `process` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Indices de la tabla `e1qxp_productos`
--
ALTER TABLE `e1qxp_productos`
  ADD PRIMARY KEY (`id`);
mysql

2 Answers

  1. the problem you have is the values ​​you use by default for the dates. Specifically the two values ​​that you set to '0000-00-00 00:00:00'

    I recommend you to use CURRENT_TIMESTAMP as default value. You can also put the smallest possible valid date '1971-01-01 00:00:00', if you need that value for your algorithmic logic in other sections.

    I hope the solution works for you.

    • 3
  2. Best Answer

    The problem you're experiencing is because the date 0000-00-00 00:00:00is a "virtual" date that doesn't cause an error when used in a date field ( TIMESTAMP, DATETIME, DATE, etc):

    MySQL permits you to store a "zero" value of '0000-00-00'as a "dummy date." This is in some cases more convenient than using NULLvalues, and uses less data and index space. To disable '0000-00-00', enable the NO_ZERO_DATESQL mode.

    In Spanish:

    MySQL allows you to store a "zero" date with a value of '0000-00-00'as a "dummy date." In some cases this is more convenient than using values NULL​​and uses less data and index space. To '0000-00-00'disallow , enable SQL mode NO_ZERO_DATE.

    The default SQL mode has been changing throughout the versions:

    Version  sql_mode
8.0      ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,
         NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,
         NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
5.7      ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,
         NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,
         NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
5.6      NO_ENGINE_SUBSTITUTION
5.5      (nada)

    It means that as of version 5.7 the SQL modes ( sql_mode) activate the options NO_ZERO_DATEand NO_ZERO_IN_DATEprevent the use of "zero" dates in values ​​or in the definition of tables.

    Solution by altering the definition

    Everything points to the fact that you created the table in a previous version of MySQL and after the update you are working with a version that does not allow you to modify the definition without replacing that "virtual" value 0000-00-00 00:00:00with a real one like NULL:

    ALTER TABLE e1qxp_productos
 MODIFY fecha_inicio timestamp NULL DEFAULT NULL,
 MODIFY fecha_fin timestamp NULL DEFAULT NULL;

    The definition would be:

    CREATE TABLE `e1qxp_productos` (
  `id` int(11) UNSIGNED NOT NULL,
  `state` tinyint(1) NOT NULL,
  `nombre` varchar(255) NOT NULL,
  `apellidos` varchar(255) NOT NULL,
  `nif_cif` char(9) NOT NULL,
  `direccion` varchar(100) NOT NULL,
  `ciudad` varchar(100) NOT NULL,
  `provincia` varchar(100) NOT NULL,
  `cp` varchar(100) NOT NULL,
  `tipo_cliente` enum('persona','empresa') DEFAULT 'persona',
  `tipo_producto` tinyint(1) DEFAULT '-1',
  `fecha_inicio` timestamp NULL DEFAULT NULL,
  `fecha_fin` timestamp NULL DEFAULT NULL,
  `precio` float DEFAULT NULL,
  `marca` varchar(255) DEFAULT NULL,
  `enviado` tinyint(1) NOT NULL DEFAULT '0',
  `process` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    You can try all of this online at this link .

    Solution altering the value ofsql_mode

    Another option is to permanently modify the value of sql_modeso that it works as before by adding in my.cnf:

    [mysqld]
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    Or temporarily:

    SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    • 3