Error inserting new column with MySQL

Navigation
3

I have a small script in which I want to insert a new column into a table in the database.

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 in the following way: 

mysql -u usuario -pclave nombrebasededatos < myscript_ddl.sql

It throws me the following error and it will not let me add the field:

  

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

I've tried it manually from PhpMyadmin and the exact same thing happens, the same error. It also refers to fecha_inicio which is another column of the database that should have nothing to do with the new entry.

Why is it? How can I solve it?

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');
    
asked by Norak 17.05.2018 в 13:23
source

2 answers

2

The problem you suffer is because the date 0000-00-00 00:00:00 is a "virtual" date that does not cause an error when used in a date field ( TIMESTAMP , DATETIME , DATE , etc):

  

MySQL allows you to store at "zero" value of '0000-00-00' as a "dummy date." This is in some cases more convenient than using% co_of% values, and uses less data and index space. To disallow NULL , enable the '0000-00-00' SQL mode.

In Spanish:

  

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

The default SQL mode has been varying 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 from version 5.7 the SQL modes ( NO_ZERO_DATE ) activate the options sql_mode and NO_ZERO_DATE that prevent the use of dates "zero" in values or in the definition of tables.

Solution altering the definition

Everything points to you creating the table in an earlier 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 of NO_ZERO_IN_DATE with a real one as 0000-00-00 00:00:00 : 

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 this online at this link .

Solution by altering the value of NULL

Another option is to permanently modify the value of sql_mode to work as before adding in sql_mode : 

[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';
    
answered by 17.05.2018 / 16:38
source
3

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

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

I hope you get the solution.

    
answered by 17.05.2018 в 14:40
Error when implementing comparable generic list What is the difference between these two types of pointers?