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

[22 Aug 2005 10:51] Anders Karlsson 
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.
[16 Sep 2005 17:13] Valeriy Kravchuk 
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)
[10 Oct 2005 18:11] Patrick Galbraith 
As Per-Erik states, this cannot be fixed currently.
[20 May 2009 7:28] Valeriy Kravchuk 
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)
[13 Nov 2017 20:47] Federico Razzoli 
Seems to be fixed in (or before) 8.0.3.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.