Search code examples
mysqlmysql5

How to force MySQL out of TRADITIONAL mode?

I have an old application that started failing after an upgrade from MySQL 5.0 to 5.1.

A bit of research indicated this is due to "strict mode" which prevents inserting certain types of "invalid" values which previously were just automatically converted to something reasonable.

I tried SET @@SESSION.sql_mode = '' and SET @@GLOBAL.sql_mode = '' but I still get the error.

Also tried commenting out sql_mode in the my.ini.

Is there a stronger, sort of "nuclear" option to fix this?

Solution

  • In my application I usually make sure that the MySQL connection is using traditional mode by issuing

    SET SESSION sql_mode = 'ANSI_QUOTES,TRADITIONAL'

    on each new connection. I presume that if you just issue

    SET SESSION sql_mode = ''

    on each new connection, you will have solved the problem.

    You should be able to change the default SQL mode for new connections by issuing

    SET GLOBAL sql_mode = ''

    but you must use an account with sufficient privileges to do this or it won't work.

    I think that if you want to make sure a particular SQL mode is in operation for your application, the most robust way to do so is to set it for each and every new connection.