CODEWITHSUNDEEP
mysqlerror-handlingbackwards-compatibility
Oliver Williams
Oliver Williams

Reputation: 6334

Query failed with the following: (Error #1366) Incorrect integer value: '' for column; how to set version compatibility

Here is my current version of MySQL:

mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

Here is the previous version:

mysql  Ver 14.14 Distrib 5.5.47, for Linux (x86_64) using readline 5.1

The query (abbereviated) that I'm trying to run is:

INSERT INTO bais_logs SET editor_id = '', type = 'basic'; -- no editor

And I get the error specified. editor_id is a INT(10) UNSIGNED NOT NULL

It worked in the previous version, and it will be difficult and time-consuming to update the related queries. Is there any way to set compatibility with the MySQL 5.5 server so that this would just ignore the '' and proceed?

Upvotes: 0

Views: 1440

Answers (3)

Onome Mine Adamu
Onome Mine Adamu

Reputation: 35

MySQL is not interpreting this action as valid due to its SQL_MODE being in STRICT mode. you can either edit you my.ini file or add this code after running a successful connection to your database.

mysqli_query($connection, "SET GLOBAL sql_mode = ''");

the $connection variable here is assumed to be the variable holding your connection tot he database.

Upvotes: 0

Oliver Williams
Oliver Williams

Reputation: 6334

True to another post, SQL mode had changed (or just started out differently on the new system). I see posts about setting SQL Mode, but I thought I'd share some helpful tips all in one when doing this somewhat compiled from all the advice:

  1. You need root privileges to do the following, but it should work:

    SET GLOBAL sql_mode = '';

  2. Item #1 will fail if the server or mysql restarts. To PERMANENTLY set it you must follow the information here: setting global sql_mode in mysql

  3. IMPORTANTLY, figure out what your existing sql_mode is BEFORE you make changes!!

    SELECT @@GLOBAL.SQL_MODE; -- this will output normally a comma-separated list.

Write this down somewhere in a comment; this configuration is probably the "right" configuration or at least what most developers would be operating under.

  1. Go to the the MySQL docs and study each mode to understand it; they are there for a reason. Setting sql_mode='' is a hack (of which I'm guilty :) But for reference: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-important

Upvotes: 0

mic4ael
mic4ael

Reputation: 8300

I am not sure but I think that the SQL mode has changed on your database. Try to change it accordingly after reading https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-important

Upvotes: 1

Related Questions