MySQL Bugs: #24276: mysql_fix_privilege_tables.sql errors out on 5.1 with strict mode set.

Bug #24276	mysql_fix_privilege_tables.sql errors out on 5.1 with strict mode set.
Submitted:	13 Nov 2006 19:20	Modified:	1 Dec 2006 12:52
Reporter:	Tobias Asplund	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.0.30-BK, 5.1.12	OS:	Linux (Linux, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
The mysql_fix_privilege_tables script errors out on servers with a strict mode set.

The offending part is that the creation of the proc table tries to set a default value for the sql_mode and body columns.

This example is really weird, because it fails when the mode is STRICT_TRANS_TABLES, but the table created is myisam (although the server default is innodb - is the sql for the create table parsed before the engine is assigned?).

My argument here is that:

1. none of our scripts should fail with the default installation my.ini file on windows (nor strict mode since many customers run it).

2. It shouldn't fail with STRICT_TRANS_TABLES when it works with '' as a SQL_MODE if the table is not a transactional engine (such as MyISAM).

How to repeat:
mysql> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS proc (
    ->   db                char(64) collate utf8_bin DEFAULT '' NOT NULL,
    ->   name              char(64) DEFAULT '' NOT NULL,
    ->   type              enum('FUNCTION','PROCEDURE') NOT NULL,
    ->   specific_name     char(64) DEFAULT '' NOT NULL,
    ->   language          enum('SQL') DEFAULT 'SQL' NOT NULL,
    ->   sql_data_access   enum('CONTAINS_SQL',
    ->                          'NO_SQL',
    ->                          'READS_SQL_DATA',
    ->                          'MODIFIES_SQL_DATA'
    ->                     ) DEFAULT 'CONTAINS_SQL' NOT NULL,
    ->   is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,
    ->   security_type     enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,
    ->   param_list        blob DEFAULT '' NOT NULL,
    ->   returns           char(64) DEFAULT '' NOT NULL,
    ->   body              longblob DEFAULT '' NOT NULL,
    ->   definer           char(77) collate utf8_bin DEFAULT '' NOT NULL,
    ->   created           timestamp,
    ->   modified          timestamp,
    ->   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'
                    ) DEFAULT '' NOT NULL,
  comment           char(64) collate  comment           c    ->                         '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'
    ->                     ) DEFAULT '' NOT NULL,
    ->   comment           char(64) collate  comment           char(64) collate ARY KEY (db,name,type)
    -> ) engine=MyISAM
    ->   character set utf8
    ->   comment='Stored Procedures';
ERROR 1101 (42000): BLOB/TEXT column 'param_list' can't have a default value

mysql> SET SQL_MODE = '';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS proc (
    ->   db                char(64) collate utf8_bin DEFAULT '' NOT NULL,
    ->   name              char(64) DEFAULT '' NOT NULL,
    ->   type              enum('FUNCTION','PROCEDURE') NOT NULL,
    ->   specific_name     char(64) DEFAULT '' NOT NULL,
    ->   language          enum('SQL') DEFAULT 'SQL' NOT NULL,
    ->   sql_data_access   enum('CONTAINS_SQL',
    ->                          'NO_SQL',
    ->                          'READS_SQL_DATA',
    ->                          'MODIFIES_SQL_DATA'
    ->                     ) DEFAULT 'CONTAINS_SQL' NOT NULL,
    ->   is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,
    ->   security_type     enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,
    ->   param_list        blob DEFAULT '' NOT NULL,
    ->   returns           char(64) DEFAULT '' NOT NULL,
    ->   body              longblob DEFAULT '' NOT NULL,
    ->   definer           char(77) collate utf8_bin DEFAULT '' NOT NULL,
    ->   created           timestamp,
    ->   modified          timestamp,
    ->   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'
    ->                     ) DEFAULT '' NOT NULL,
    ->   comment           char(64) collate utf8_bin DEFAULT '' NOT NULL,
    ->   PRIMARY KEY (db,name,type)
    -> ) engine=MyISAM
    ->   character set utf8
    ->   comment='Stored Procedures';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'param_list' can't have a default value | 
| Warning | 1101 | BLOB/TEXT column 'body' can't have a default value       | 
+---------+------+----------------------------------------------------------+
2 rows in set (0.00 sec)

Suggested fix:
In addition to fixing this bug, a full testcase using the full mysql_fix_privile_tables under different sql_modes should be added to the test suite, since this has been broken before ( http://bugs.mysql.com/bug.php?id=15760 )

Thank you for a bug report. Verified also with 5.0.30-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb (c1 blob default '') engine=MyISAM;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1101
Message: BLOB/TEXT column 'c1' can't have a default value
1 row in set (0.00 sec)

mysql> show variables like 'sql%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| sql_big_selects | ON    |
| sql_mode        |       |
| sql_notes       | ON    |
| sql_warnings    | OFF   |
+-----------------+-------+
4 rows in set (0.00 sec)

mysql> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.02 sec)

mysql> create table tb2 (c1 blob default '') engine=MyISAM;
ERROR 1101 (42000): BLOB/TEXT column 'c1' can't have a default value

So, I think, it is a general problem (STRICT_TRANS_TABLES affects non-transactional tables). It can be even intended and "documented" (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html):

"STRICT_TRANS_TABLES

Enable strict mode for transactional storage engines, and when possible for non-transactional storage engines."

but as it affects installation, at list more detailed documentation is needed.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicates bug #23669