Mysql can't paste because there is no default value?

Question

I have two tables with exactly the same layout. I can insert one table, but not another. The one that fails complains about the lack of a default value. Here is my create statement for the table

CREATE TABLE `t_product` ( `product_id` varchar(10) NOT NULL, `prod_name` varchar(150) DEFAULT NULL, `price` decimal(6,2) NOT NULL, `prod_date` date NOT NULL, `prod_meta` varchar(250) DEFAULT NULL, `prod_key` varchar(250) DEFAULT NULL, `prod_desc` varchar(150) DEFAULT NULL, `prod_code` varchar(12) DEFAULT NULL, `prod_price` decimal(6,2) NOT NULL, `prod_on_promo` tinyint(1) unsigned NOT NULL, `prod_promo_sdate` date DEFAULT NULL, `prod_promo_edate` date DEFAULT NULL, `prod_promo_price` decimal(6,2) NOT NULL, `prod_discountable` tinyint(1) unsigned NOT NULL, `prod_on_hold` tinyint(1) unsigned NOT NULL, `prod_note` varchar(150) DEFAULT NULL, `prod_alter` varchar(150) DEFAULT NULL, `prod_extdesc` text, `prod_img` varchar(5) NOT NULL, `prod_min_qty` smallint(6) unsigned NOT NULL, `prod_recent` tinyint(1) unsigned NOT NULL, `prod_name_url` varchar(150) NOT NULL, `upc_code` varchar(50) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 

When I run this statement in database1, it successfully inserts:

 insert into t_product (product_id) values ('jlaihello'); 

When I run this exact statement in database2, I get an error:

 ERROR 1364 (HY000): Field 'price' doesn't have a default value 

Why does this error only occur in database2? As far as I can tell, the difference between database1 and database2:

database1 uses mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (i686) using readline 6.3

and

database2 uses mysql Ver 14.14. Distributed on 5.7.16 for Linux (x86_64) using EditLine covers

How to make database2 behave like database1?

EDIT These are hundreds of tables. We basically move the database to a new server. And I made mysqldump from db1 and imported into db2. t_product is just one of the tables affected by this. I would like to avoid manually modifying the schema for hundreds of tables. I prefer a “simple switch” that will make db2 behave like db1.

Answer 1

ERROR 1364 (HY000): Field 'price' doesn't have a default value

price decimal(6,2) NOT NULL,

Set price to zero or set default value

EDIT:

This is called STRICT_TRANS_TABLES SQL STRICT_TRANS_TABLES .

Open phpmyadmin and go to the " More " tab and select the " Variables " submenu. Scroll down to find sql mode. Edit sql mode and delete STRICT_TRANS_TABLES Save it.

OR

You can run the SQL query in your database management tool such as phpMyAdmin:

 -- verify that the mode was previously set: SELECT @@GLOBAL.sql_mode; -- update mode: SET @@GLOBAL.sql_mode= 'YOUR_VALUE'; 

OR

Find the line that looks like this in the mysql conf file:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line and restart MySQL server

Answer 2

Most likely, by default, the price column is not in the second database. To verify this, you should output the table structure:

 describe database2.t_product; 

OR

 show create table database2.t_product; 

and check if a default value is defined.

You can modify the table and add a missing default constraint as follows:

 ALTER TABLE database2.t_product MODIFY COLUMN decimal(6,2) NOT NULL DEFAULT 0 

EDIT

Based on the comments and the specification (default values ​​for the data type), I think there is a difference in sql_mode MySQL:

To enter data in a NOT NULL column that does not have an explicit DEFAULT value, if the INSERT or REPLACE statement does not contain a value for the column, or the UPDATE statement sets the column to NULL, MySQL processes the column according to the current SQL mode:

If strict SQL mode is enabled, an error occurs for the transaction table and the rollback statement. For nontransactional tables, an error occurs, but if this happens for the second or next line of a multiline statement, the previous lines would be inserted.

If strict mode is not enabled, MySQL sets the column to an implicit default value for the column data type.

So, if strict mode is not enabled for the first database, INSERT / UPDATE is enabled and saves the default value of this type (decimal)