If in an application, if we execute sql_mode=mysql4.0 as a first query in my application but using MySQL 5.1 in my application then what will happen?
MySQL's sql_mode setting allows you to enforce or relax ANSI standards for SQL processing.
In your case, MySQL will regress in behavior and results. In what sense ? MySQL 5.1 version features will stay, but how the SQL is processing changes. Here is proof:
MySQL 5.0 first introduced Stored Procedures to the MySQL realm.
MySQL 4.x never had Stored Procedures.
Now, let's take a look at the physical home of MySQL 5.x Stored Procedures, mysql.proc:
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| db | char(64) | NO | PRI | | |
| name | char(64) | NO | PRI | | |
| type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| specific_name | char(64) | NO | | | |
| language | enum('SQL') | NO | | SQL | |
| sql_data_access | enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') | NO | | CONTAINS_SQL | |
| is_deterministic | enum('YES','NO') | NO | | NO | |
| security_type | enum('INVOKER','DEFINER') | NO | | DEFINER | |
| param_list | blob | NO | | NULL | |
| returns | longblob | NO | | NULL | |
| body | longblob | NO | | NULL | |
| definer | char(77) | NO | | | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| modified | timestamp | NO | | 0000-00-00 00:00:00 | |
| sql_mode | set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') | NO | | | |
| comment | char(64) | NO | | | |
| character_set_client | char(32) | YES | | NULL | |
| collation_connection | char(32) | YES | | NULL | |
| db_collation | char(32) | YES | | NULL | |
| body_utf8 | longblob | YES | | NULL | |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
20 rows in set (0.02 sec)
Please note column 15 : sql_mode
sql_mode
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED',
'ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL',
'ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS',
'MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES',
'STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE',
'INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',
'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
Look at those modes. Some say
Based on whatever RDBMS you worked with before, you can make the SQL inside the Stored Procedure get up and do any song and dance you like (in other words, you can make the execution of the SQL as strict or as permissive as you wish based on the protocols you chose).
This would allow you to port any code you wrote in MySQL 4.x into a MySQL 5.x Stored Procedure and expect the results in the same order as if you ran it in MySQL 4.x.
I once wrote an answer to a post about relying on undocumented MySQL behavior (What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations?). In that post, I wrote a comment that said:
The fact that there is a local GROUP BY phenomenon and that there is a URL dedicated to it ( dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html ) shows that corner cases should come as no surprise. Notice how this GROUP BY quirk is conveniently called a MySQL Extension. Imagine if Oracle now took MySQL and fixed that GROUP BY quirk. Imagine if the SQL_MODE was defaulted to a strict mode for the GROUP BY : http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_only_full_group_by The possibilities for corner cases can just go on... – RolandoMySQLDBA Nov 26 '12 at 21:47
This gives you time to reflect on how to properly plan the migration of SQL code from MySQL 3.x, MySQL 4.x, or other RDBMS products into MySQL 5.x. It also warns you that result sets from SQL can radically change if you tamper with the sql_mode, so use it with extreme caution.
I hope this answers your question !!!
ORDER BY clause in their SQL call from PHP to get the results the same again.
Commented
Jan 24, 2014 at 20:43