1

Does anyone know the key things that should be done when moving to MySQL 5.6 to reduce the amount of code changes required? I found that new installations of it have STRICT_TRANS_TABLES set for sql_mode by default.

I plan to go through the release notes and upgrade guides on the MySQL site in more depth but during a past upgrade which was only a point-release change, there was a backwards incompatible change that wasn't called out in the release notes

I wanted to see if anyone had anything noteworthy to mention from experience.

Improve this question

1 Answer 1

Reset to default
1

I ran headlong into this one 6 months

The only sane thing you can do is the following two steps

STEP 01

Add this to my.cnf under the [mysqld] group header

[mysqld]
sql_mode = ''

STEP 02

Login as root@localhost and run this

mysql> SET GLOBAL sql_mode = '';

Restarting mysql is not required.

STEP 03 (Optional)

STEP 02 only affects incoming connections after the change. You may have to kill all current DB Connections. Let the app reconnect and it will begin with sql_mode as blank. While restarting mysql is not needed for incoming connections, you could just run service mysql restart instead of custom scripting the killing of DB Connections.

If you want to custom script the killing of your connections, please see some of my past posts for exmaples on how to kill many connections:

STEP 04 (Optional, Last Resort)

If you would like to make code changes, the only change would be to run

SET sql_mode = '';

as the first command for the connection. Then, you could run your regular queries thereafter.

GIVE IT A TRY !!!

NOTE #1 : The first two steps are more than enough for your app if you close your connection right after running your query. If your connections are persistent, then you will need STEP 03.

NOTE #2 : If you are not allowed to change the configuration, you could skip STEP 01 and STEP 02 and just run STEP 03 and STEP 04.

Improve this answer
2
  • Those are some good options for dealing with sql_mode. I was planning to just deal with sql_mode via my.cnf, but I was interested in anything else you or others have encountered with transitioning to 5.6. Thanks!
    – user101203
    Commented Apr 20, 2015 at 19:04
  • 1
    These were the actual steps I performed for a client months ago in dev and production. Other than having to explicitly set sql_mode to a blank string to make backward compatible with code that worked in MySQL 5.5 now operating in MySQL 5.6, there isn't anything else.
    – RolandoMySQLDBA
    Commented Apr 20, 2015 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.