Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

  • Will my system use MySQL 4.0 or MySQL 5.1 version features? What is
  • SQL mode in a RDBMS? Why we should use this?
  • On which basis, we should set its value?
share|improve this question
add comment

1 Answer

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

  • 'POSTGRESQL'
  • 'ORACLE'
  • 'MSSQL'
  • 'DB2'
  • 'MAXDB'
  • 'MYSQL323'
  • 'MYSQL40'

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 !!!

share|improve this answer
    
what i can understand from your answer is " my application will use the features of mysl 5.1 while it support mysql 4.x as backwardd compatibility. sice way of execution will change, so, will it effect the processing speed/ performance? –  ursitesion Jan 24 at 4:16
    
You are right in mentioning the backward compatibility issue. I was emphasizing the consequences of not setting sql_mode when porting MySQL 4.x code. I stressed this point in my answer because a hosting client had seen a query result set change its order when I was tasked with upgrading them from MySQL 5.0 to MySQL 5.1. The client had to change an ORDER BY clause in their SQL call from PHP to get the results the same again. –  RolandoMySQLDBA Jan 24 at 20:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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