Search code examples
mysqlauto-incrementinsert-updatestring

MySQL- Insert a record with empty primary key, works on one server, not the other

I have a table in a MySQL Server (version 5.5.27, installed with EasyPHP for development), it has an ID with Auto_Increment, and it's data type is INT(11).

When i try to insert a record, using this statement, it works. 

insert into factclientes (IDFactClientes, IDTercero, SubTotal, IVA, Total)
        values ('', '3', '2500.00', '400.00', '2900.00')
        ON DUPLICATE KEY UPDATE IDTercero = values(IDTercero),
                SubTotal = values(SubTotal), 
                IVA = values(IVA), 
                Total = values(Total)

But when i try to insert that same record on my production server (version 5.6.17, installed independently on another machine) it throws an error:

 Incorrect integer value: '' for column 'IDFactClientes' at row 1

I know it is because the primary key ID 'IDFactClientes' has an empty value. I do this because i use the same statement to INSERT and to UPDATE. If my program doesn't know and doesn't specify the IDFactClientes, i want a new record, if my program knows the ID already, and it's specified i want the record to be updated.

The weird thing is that it works on my dev machine, but it doesn't on my production server.

Is there a setting im missing?? how could i fix this?? i have the exact same problem with all the tables of my database and i wouldn't want to modify all the statements in my program... if it's possible

Thank you in advance!!

Solution

  • I found it!!! or remembered it... a while ago i heard something about "STRICT MODE", and i suddenly remembered about it!! so i looked for how to turn off the "strict mode" and i found two methods:

    Method 1: Open the "my.ini" file within the MySQL installation directory and look for something like...

    # Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    Replace with:

    # Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    Method 2: You may be able to run an SQL query within your database management tool such as phpMyAdmin which can normally be found from your web hosting control panel:

    SET @@global.sql_mode= '';

    I Think the first method is permanent, and the second one has to be done every connection... i think