MySQL Bugs: #12719: SHOW CREATE PROCEDURE/FUNCTION does obey sql_quote_show

Bug #12719	SHOW CREATE PROCEDURE/FUNCTION does obey sql_quote_show_create
Submitted:	22 Aug 2005 10:51	Modified:	20 May 2009 7:28
Reporter:	Anders Karlsson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.12, 5.0.14-rc, 5.1.34	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The sql_quote_show_create setting works fine with SHOW CREATE TABLE (which is explicitly documented), SHOW CREATE DATABASE (which is not explicitly documented) and SHOW CREATE VIEW (which is not explicitly documented). This seems to imply that this option should be used by all SHOW CREATE commands, which seems reasonable, and as is seen, this really work with more commands than SHOW CREATE TABLE, although this is at this point in time undocumented.

It does NOT work with SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION though, and this is really inconsistent. Now this setting affect some, but not all, SHOW CREATE commands, and the manual does not document which is which.

This is not the only issue with SHOW CREATE PROCEDURE/FUNCTION. Related is also bug #10713.

How to repeat:
mysql test
...
mysql> delimiter //
mysql> create procedure p1() begin end//
Query OK, 0 rows affected (0.00 sec)

mysql> create function f1() returns int begin return 1; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
---------------+
| Table | Create Table
               |
+-------+-----------------------------------------------------------------------
---------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
---------------+
1 row in set (0.00 sec)

mysql> show create procedure p1;
+-----------+-------------------------------------------------------------------
------------------------------------------------------------+-------------------
-----------------------+
| Procedure | sql_mode
                                                            | Create Procedure
                       |
+-----------+-------------------------------------------------------------------
------------------------------------------------------------+-------------------
-----------------------+
| p1        | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | CREATE PROCEDURE `
test`.`p1`()
begin end |
+-----------+-------------------------------------------------------------------
------------------------------------------------------------+-------------------
-----------------------+
1 row in set (0.00 sec)

mysql> show create function f1;
+----------+--------------------------------------------------------------------
-----------------------------------------------------------+--------------------
-----------------------------------------------+
| Function | sql_mode
                                                           | Create Function
                                               |
+----------+--------------------------------------------------------------------
-----------------------------------------------------------+--------------------
-----------------------------------------------+
| f1       | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | CREATE FUNCTION `te
st`.`f1`() RETURNS int(11)
begin return 1; end |
+----------+--------------------------------------------------------------------
-----------------------------------------------------------+--------------------
-----------------------------------------------+
1 row in set (0.00 sec)

mysql> set @@sql_quote_show_create=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
-----------+
| Table | Create Table
           |
+-------+-----------------------------------------------------------------------
-----------+
| t1    | CREATE TABLE t1 (
  c1 int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
-----------+
1 row in set (0.00 sec)

mysql> show create procedure p1;
+-----------+-------------------------------------------------------------------
------------------------------------------------------------+-------------------
-----------------------+
| Procedure | sql_mode
                                                            | Create Procedure
                       |
+-----------+-------------------------------------------------------------------
------------------------------------------------------------+-------------------
-----------------------+
| p1        | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | CREATE PROCEDURE `
test`.`p1`()
begin end |
+-----------+-------------------------------------------------------------------
------------------------------------------------------------+-------------------
-----------------------+
1 row in set (0.00 sec)

mysql> show create function f1;
+----------+--------------------------------------------------------------------
-----------------------------------------------------------+--------------------
-----------------------------------------------+
| Function | sql_mode
                                                           | Create Function
                                               |
+----------+--------------------------------------------------------------------
-----------------------------------------------------------+--------------------
-----------------------------------------------+
| f1       | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | CREATE FUNCTION `te
st`.`f1`() RETURNS int(11)
begin return 1; end |
+----------+--------------------------------------------------------------------
-----------------------------------------------------------+--------------------
-----------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Two suggestions:
1) Document that sql_quote_show_create affect all SHOW CREATE commands, not just SHOW CREATE TABLE. It already does this in most cases, and this is reasonable behaviour, but the documentation is lacking.
2) Fix SHOW CREATE PROCEDURE / FUNCTION to obey this setting, just like the other SHOW CREATE commands.

Problem still exists on today's 5.0.14-rc (ChangeSet@1.1965.2.1, 2005-09-16 09:15:45+03:00, bell@sanja.is.com.ua):

mysql> create procedure p1() begin end//
Query OK, 0 rows affected (0.00 sec)

mysql> create function f1() returns int begin return 1; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
---------------+
| Table | Create Table
               |
+-------+-----------------------------------------------------------------------
---------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
---------------+
1 row in set (0.00 sec)

mysql> show create procedure p1;
+-----------+----------+-----------------------------------+
| Procedure | sql_mode | Create Procedure                  |
+-----------+----------+-----------------------------------+
| p1        |          | CREATE PROCEDURE `p1`()
begin end |
+-----------+----------+-----------------------------------+
1 row in set (0.00 sec)

mysql> show create function f1;
+----------+----------+---------------------------------------------------------
---+
| Function | sql_mode | Create Function
   |
+----------+----------+---------------------------------------------------------
---+
| f1       |          | CREATE FUNCTION `f1`() RETURNS int(11)
begin return 1; end |
+----------+----------+---------------------------------------------------------
---+
1 row in set (0.01 sec)

mysql> set @@sql_quote_show_create=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------
-----------+
| Table | Create Table
           |
+-------+-----------------------------------------------------------------------
-----------+
| t1    | CREATE TABLE t1 (
  c1 int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
-----------+
1 row in set (0.00 sec)

mysql> show create procedure p1;
+-----------+----------+-----------------------------------+
| Procedure | sql_mode | Create Procedure                  |
+-----------+----------+-----------------------------------+
| p1        |          | CREATE PROCEDURE `p1`()
begin end |
+-----------+----------+-----------------------------------+
1 row in set (0.00 sec)

mysql> show create function f1;
+----------+----------+---------------------------------------------------------
---+
| Function | sql_mode | Create Function
   |
+----------+----------+---------------------------------------------------------
---+
| f1       |          | CREATE FUNCTION `f1`() RETURNS int(11)
begin return 1; end |
+----------+----------+---------------------------------------------------------
---+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.14-rc |
+-----------+
1 row in set (0.00 sec)

As Per-Erik states, this cannot be fixed currently.

This is still repeatable with 5.1.34:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.34-community MySQL Community Server (GPL)

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

mysql> delimiter //
mysql> create procedure p1() begin end//
Query OK, 0 rows affected (0.08 sec)

mysql> create function f1() returns int begin return 1; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> create table ttt1(c1 int);
Query OK, 0 rows affected (0.13 sec)

mysql> show create table ttt1\G
*************************** 1. row ***************************
       Table: ttt1
Create Table: CREATE TABLE `ttt1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

mysql> show create procedure p1\G
*************************** 1. row ***************************
           Procedure: p1
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.09 sec)

mysql> show create function f1\G
*************************** 1. row ***************************
            Function: f1
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS
int(11)
begin return 1; end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> set @@sql_quote_show_create=0;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table ttt1\G
*************************** 1. row ***************************
       Table: ttt1
Create Table: CREATE TABLE ttt1 (
  c1 int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create function f1\G
*************************** 1. row ***************************
            Function: f1
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS
int(11)
begin return 1; end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> show create procedure p1\G
*************************** 1. row ***************************
           Procedure: p1
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Seems to be fixed in (or before) 8.0.3.