PHP or MYSQL Converting Empty String to Zero

[Ravenman7]

Any and All,

 

I have a situation which I cannot find an answer for. I'll list the specifics here and state what the issue is.

 

2 configurations, old and new;

 

Old   -    PHP 5.2.6, MySQL 5.1

 

New  -    PHP 5.5.8  MYSQL 5.6

 

On the new setup, the following query throws an error (as it should)

 

INSERT INTO sample set ProjectID='026LC',LabSampleID='1',SampleType='REG',DateReceived='2014-03-05 00:00:00',DateSampled='2014-03-0400:00:00',TimeSampled='0',ClientSampleID='MI1',Media='Water',`Condition`='Intact',

sort=1,ContainerType='',Temperature='',Initial_Amount_Units='',ContainerNum=''

 

ContainerNum is defined as int(10) default null. The error is  - Incorrect integer value: '' for column 'ContainerNum' at row 1

 

On the old setup, no error is thrown. The record is inserted with a zero in the ContainerNum field.

 

INSERT INTO sample set ProjectID='007LC',LabSampleID='1',SampleType='REG',DateReceived='2014-03-05 00:00:00',DateSampled='2014-03-04 00:00:00',TimeSampled='0',ClientSampleID='MI1',Media='Water',`Condition`='Intact',

sort=1,ContainerType='',Temperature='',Initial_Amount_Units='',ContainerNum=''

 

Query Browser shows the ContainerNum field containing a zero as seen in the attached image.

 

 

 

There are no triggers in the database. Any idea on how this was accomplished? The code is broken is several places on the new server.

 

Rick

 

[gizmola]

Your new server is set to SQL Strict mode. Your old server is not, thus it is downgrading the null to a 0.

 

Take a look at this page: http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html

 

As you can see there are quite a lot of different options available to tweak the behavior of the server.

 

Probably the strict server has something like this in its my.cnf file:

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

STRICT_TRANS_TABLES will cause that sort of insert to error out.

 

You will want to diff the mysql configurations on the two servers, and compare the my.cnf for each to pinpoint the specific difference between the two.

 

However, most likely it's the STRICT_TRANS_TABLES setting that explains the difference. On the old server, this setting is not being used, so the server is generating a warning instead, but otherwise creating the row.

[Ravenman7]

Thank you ever so much gizmola, my client and I will be quite relieved. It took care of the issue. Sorry I didn't respond more quickly, Outlook put the reply email in junk.

 

Ravenman7