CODEWITHSUNDEEP
phpmysqlpdo
nigong
nigong

Reputation: 1755

Different PDO behaviours between PHP versions

I have three servers running as my development, staging and production servers. The PHP version running are: 5.5.23, 5.5.9, 5.5.20 respectively.

They are running exactly the same code. But my development server was throwing exceptions whereas the other two works fine. The exception is follow:

Error: [PDOException] SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'comment_id' at row 1
Request URL: /comments/add.json Stack Trace: 0 /Users/me/Sites/MySite/lib/Cake/Model/Datasource/DboSource.php(460): PDOStatement->execute(Array)1
1 /Users/me/Sites/MySite/lib/Cake/Model/Datasource/DboSource.php(426): DboSource->_execute('INSERT INTO `po...', Array)1
2 /Users/me/Sites/MySite/lib/Cake/Model/Datasource/DboSource.php(1008): DboSource->execute('INSERT INTO `po...')1
3 /Users/me/Sites/MySite/lib/Cake/Model/Model.php(1744): DboSource->create(Object(Notification), Array, Array)1
4 /Users/me/Sites/MySite/app/Controller/CommentsController.php(181): Model->save(Array)1
5 [internal function]: CommentsController->add()

The value being inserted is a bool(false) while the database is expecting integers. So I had to do something like to handle this issue for my development server:

//The comment id is set to false. Needs to turn it to NULL as DB can't handle false for integer
if($this->Comment->id === false)
    $notification['Notification']['comment_id'] = NULL;
else
    $notification['Notification']['comment_id'] = $this->Comment->id;

My question is: Why my staging and production server can handle this issue (auto-convert the false value into integer, possibly), but my development server cannot?

As you can see, my servers are running CakePHP. I have been looking at the PDO versions but it seemed like it doesn't give me too much insight. My development server is running on my Mac Book Mac OS X 10.10. The staging and production are running Ubuntu 12.04.

Upvotes: 2

Views: 128

Answers (1)

Martin Konecny
Martin Konecny

Reputation: 59651

You most likely have strict mode for MySQL disabled on your Production and Staging environments, while your development environment has this enabled.

See more here:

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings

You can read on how to change this mode here.

On OS X, the following link has more details:

After a lot of poking around I found out that MySQL for OS X from Oracle ships with a /usr/local/mysql/my.cnf which is loaded on startup. In this file is a sole configuration directive for sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES. Once I commented this out and restarted the server strict mode was off, my ORM worked and I was happy.

Upvotes: 4

Related Questions