A notes taking when upgrade from MySQL 5.5 to MySQL 5.6

Spread the love
After having upgraded the current system from
  •  PHP 5.3.19 to PHP 5.6.36
  • MySQL from 5.5.29 to MySQL 5.6
We encounter the strange problem as below
HTTP エラーコード 500
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column {column_name} at row 1; CLASS=[PDOException]
This problem occurred in a program that insert a new record to database, but some of its parameters were blank. It executed no problem in the old system but not the new one.
After carefully look around current code, it turned out that the problem wasn’t with PHP side, but MySQL side.
So I compared the former MySQL and the latter MySQL
  1. Connect to MySQL
  2. Type this command in-side the terminal :
    `show variables;`

    (this command shows what is the current settings is )

Now differences were shown up:
  • MySQL 5.5 :

    sql-mode : ''
  • MySQL 5.6 :

    sql-mode : STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
It seemed that the newer DB engine is stricter than the old one.
So I ran the below commands :
  • `set sql-mode = '' `
  • re-run the program again
  • oops.. nothing changed.
Re-check the manual from MySQL, I figured out that my settings only applied to my own connection. Fair enough.
Let’s make this change global and consistent.
  • ssh to MySQL server
  • change the directory to MySQL configuration file (my case was : /etc/mysql/my.cnf)
  • add this line inside this file
    • sql-mode=""
  • restart MySQL server
  • re-run the program, and the problem was gone.
More to read :
Another way that I tried to circumvent this problem is to add the code below before execute any PDO commands
$pdo->exec('set session sql_mode = ""');

Leave a Reply

Your email address will not be published. Required fields are marked *