ID #1057

Can I use SQLyog with the various SQL-modes available with MySQL?


Well ... yes. But let us have a look of the concept of the sql-modes.

SQL-modes were introduced with MySQL version 4.0 as a general server setting (the ANSI mode probably is the best known). From MySQL version 4.1 each client can specify its own sql-mode for its connection. From MySQL version 5.0 it is even possible for a client to change its sql-mode in the midst of a connection. MySQL explains here. Basically a sql-mode is a setting that specifies formal rules for the SQL-syntax to be used.

Sql-modes can be 'simple' or 'compound' modes. For instance the ANSI mode is a compound mode that is equivalent to the simple modes REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. And the ORACLE mode is a compound mode that is equivalent to the simple modes PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

SQLyog simply issues the statement set session sql_mode = '' when connecting to a MySQL version 4.1+ server as the very first statement, and the connection will now use '' (empty) SQL-mode. This applies to the connections used by the the main GUI-Window ('connection windows') of the program (where a connection will be created by each).

From the editor you can change sql_mode manually should you want for some reason (example: you want to execute statements using 'ANSI-quotes') and it will work as long as you use the editor. But do not forget to switch back before using the GUI if the sql_mode you have defined has influence on how statements should be written. Note in particular that using strict modes ("STRICT_ALL_TABLES", "STRICT_TRANS_TABLES") will fail to INSERT and UPDATE from the GRIDs if the table has columns defined NOT NULL with no DEFAULT.

For background threads copying data (including Data Sync) we will set the set ‘NO_AUTO_VALUE_ON_ZERO’ sql_mode in order to handle auto_increment columns containing a '0' value. This was implemented in version 8.13.

There is nothing wrong or unsafe in doing this. 'Un-strict' mode is not at all 'unsafe'. It is the basic idea with the sql-modes that each user/application/client could use the most appropriate sql-mode.

You should also note that when executing external files ('SQL scripts') from SQLyog, a seperate connection is created for this. And with such connection SQL_mode is not explicitly set by SQLyog. The script will have to set SQL-mode itself (by including the set sql_mode statement in the beginning) if its content require a sql_mode different from the server default where the script is imported. SQL-scripts created with SQL export/backup functionalities have such statements in the beginning of the file. You will not need to think about this.


Tags: -

Related entries:

You can comment this FAQ