MySql 5.7: General error: 1366 Incorrect integer value: '' for TinyInt(1) field

1
votes

After upgrading MySql 5.5 to 5.7 I faced with issue of fail insert query in my database schema, using php 7.1.

Query worked fine on 5.5 version but 5.7 throws exception:

message:protected: (string) SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'is_on_demand' at row 1

is_on_demand is tinyint(1) type

Why is 5.7 disallowing inserting empty string as false value, considering that 5.5 allowed this?

2
phpmysql
I can't find a specific reference to the change but it is certainly a logical one. A false value is typically a zero not an empty string. Furthermore an empty string is not an integer so making sure an integer column has an integer value makes sense (unless of course it is defined as being able to be null ... which is not the same as an empty string of course). – Dave
@Dave I've succeeded adding sql_mode = "NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" into my mysql configuration under debian. – Lululu

2 Answers

0
votes

Add:

sql_mode = "NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

into mysql config file.

0
votes

To hide the error (not recommended, but you may have to do this until you fix the root cause)

Disable mysql strict mode:

SHOW VARIABLES LIKE 'sql_mode';

+--------------+------------------------------------------+ 
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

sudo nano /etc/mysql/my.cnf

add the following

[mysqld]
sql_mode= <previous modes list but exclude STRICT_TRANS_TABLES>

sudo service mysql restart

Note: Previous steps will show warnings of enabled mysql modes instead of producing errors, to hide warning also, you can remove the related mode as done in previous steps.

Use the documentation: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html