mysql can not insert because no default value?

Question

I have two tables with exactly the same schema. I can insert into one table but not another. The one that fails complains about no default value. Here's 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 the error:

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

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

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 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

How do I make database2 behave like database1?

EDIT There are hundreds of tables affected by this. Basically we're moving a database over to a new server. And I did a mysqldump from db1, and imported into db2. t_product is just ONE of the tables affected by this. I'd like to avoid manually modifying the schema for the 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 null or assign a default value

EDIT:

This is caused by the STRICT_TRANS_TABLES SQL mode.

Open phpmyadmin and goto More Tab and select Variables submenu. Scroll down to find sql mode. Edit sql mode and remove STRICT_TRANS_TABLES Save it.

OR

You can run an SQL query within 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 so in the mysql conf file:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line out and restart mysql server

Answer 2

Most probably, the default for column price is missing in the second database. To check this you should output your table structure:

describe database2.t_product;

OR

show create table database2.t_product;

and check if the default is defined.

You can alter your table and add the missing default constraint like this:

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

EDIT

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

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

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

So, if strict mode is not enabled for the first database, INSERT/UPDATE is allowed and storing the default value of that type (a 0 decimal)