MySQL Bugs: #44515: Some properties of stored routine not preserved when re-created using si

Bug #44515	Some properties of stored routine not preserved when re-created using si_objects
Submitted:	28 Apr 2009 13:05	Modified:	13 Nov 2009 18:38
Reporter:	Rafal Somla	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
When function whose return type is ENUM (...) CHARACTER SET ucs2 is serialized and
then re-created from its serialization string, then some of its properties which are listed in information_schema.routines are not restored to the original values. The properties which are not restored are:
- DTD_IDENTIFIER, 
- CHARACTER_MAXIMUM_LENGTH,
- CHARACTER_OCTET_LENGTH.

See also BUG#44514.

Note: it might turn out that these properties do not have to be restored and then it is not a bug. But this should be a conscious decision.

How to repeat:
Run the following test script 

--------------------8<---------------------
--source include/have_innodb.inc

CREATE DATABASE db1 CHARACTER SET utf8;

USE db1;
CREATE FUNCTION f4 ()
RETURNS ENUM( 'w' ) CHARACTER SET ucs2
RETURN 0;

--error 0,1
--remove_file check_objects.sql
--write_file check_objects.sql
USE information_schema;
SELECT * FROM routines WHERE routine_schema LIKE 'db%';
EOF

--exec $MYSQL --vertical <check_objects.sql >footprint.pre

BACKUP DATABASE db1 TO 'db1.bak';
DROP DATABASE db1;
RESTORE FROM 'db1.bak';

--exec $MYSQL --vertical <check_objects.sql >footprint.post

--diff_files footprint.pre footprint.post

DROP DATABASE db1;
--exit
-------------------->8---------------------

and see the differences between footprint.pre and footprint.post.

Thank you for the problem report. Verified just as described with recent 6.0.11 from bzr:

valeriy-kravchuks-macbook-pro:mysql-test openxs$ diff footprint.pre footprint.post 
8,9c8,9
< CHARACTER_MAXIMUM_LENGTH: 2
<   CHARACTER_OCTET_LENGTH: 4
---
> CHARACTER_MAXIMUM_LENGTH: 4
>   CHARACTER_OCTET_LENGTH: 8
14c14
<           DTD_IDENTIFIER: enum('\0w')
---
>           DTD_IDENTIFIER: enum('\0\0\0w')

There is a very good explanation why these columns are not being restored. The si_objects code uses the 'SHOW CREATE PROCEDURE/FUNCTION' SQL command which does not return this data.

force_restart' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: force_restart
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: mtr
            ROUTINE_NAME: force_restart
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN   SELECT 1 INTO OUTFILE 'force_restart'; END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2009-11-11 00:51:42
            LAST_ALTERED: 2009-11-11 00:51:42
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: latin1
    COLLATION_CONNECTION: latin1_swedish_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> show create procedure mtr.force_restart \G*************************** 1. row ***************************
           Procedure: force_restart
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `force_restart`()
BEGIN   SELECT 1 INTO OUTFILE 'force_restart'; END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> 

It is important to note that these columns are NULL by default.

Created database db1 and f4 as shown above.

Performed a backup with MySQL Backup.

Performed a backup with mysqldump.

Looking at routing after initial creation:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'f4' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: f4
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: db1
            ROUTINE_NAME: f4
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: enum
CHARACTER_MAXIMUM_LENGTH: 2
  CHARACTER_OCTET_LENGTH: 4
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: ucs2
          COLLATION_NAME: ucs2_general_ci
          DTD_IDENTIFIER: enum('\0w')
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: RETURN 0
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2009-11-13 18:17:49
            LAST_ALTERED: 2009-11-13 18:17:49
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

The mysqldump:

Sun-Laptop:mysql-6.0-bug-48353 cbell$ ./client/mysqldump -uroot --socket=./mysql-test/var/tmp/mysqld.1.sock --databases db1 --routines 
-- MySQL dump 10.13  Distrib 6.0.14-alpha, for apple-darwin10.0.0 (i386)
--
-- Host: localhost    Database: db1

...

/*!50003 DROP FUNCTION IF EXISTS `f4` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `f4`() RETURNS enum('\0w') CHARSET ucs2
RETURN 0 */;;
DELIMITER ;

...

After dropping database and restoring with mysqldump:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'f4' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: f4
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: db1
            ROUTINE_NAME: f4
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: enum
CHARACTER_MAXIMUM_LENGTH: 4
  CHARACTER_OCTET_LENGTH: 8
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: ucs2
          COLLATION_NAME: ucs2_general_ci
          DTD_IDENTIFIER: enum('\0\0\0w')
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: RETURN 0
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2009-11-13 19:30:41
            LAST_ALTERED: 2009-11-13 19:30:41
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

After dropping database and restoring with backup:

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

mysql> restore from 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 552       |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'f4' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: f4
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: db1
            ROUTINE_NAME: f4
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: enum
CHARACTER_MAXIMUM_LENGTH: 4
  CHARACTER_OCTET_LENGTH: 8
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: ucs2
          COLLATION_NAME: ucs2_general_ci
          DTD_IDENTIFIER: enum('\0\0\0w')
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: RETURN 0
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2009-11-13 19:33:05
            LAST_ALTERED: 2009-11-13 19:33:05
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

mysql> 

Let's look at the output from mysqlbackup:

Sun-Laptop:mysql-6.0-bug-48353 cbell$ ./client/mysqlbackup --all ./mysql-test/var/mysqld.1/data/db1.bak

Image path:          './mysql-test/var/mysqld.1/data/db1.bak'
Image size:          435 bytes
Image compression:   none
Image version:       1
Creation time:       2009-11-13 16:26:09 UTC
Server version:      6.0.14 (6.0.14-alpha)
Server byte order:   little-endian
Server charset:      'utf8'

Catalog summary:

  Databases:              1
  Non-table db objects:   1

Catalog details:

  Database  'db1'
    Sfunc     'db1'.'f4'

Meta data:

  Database 'db1' statement: '60 CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */
'

    Sfunc 'db1'.'f4' statement: '9 USE `db1`
31 SET character_set_client = utf8
42 SET collation_connection = utf8_general_ci
40 SET collation_database = utf8_general_ci
17 SET sql_mode = ''
91 CREATE DEFINER=`root`@`localhost` FUNCTION `f4`() RETURNS enum('\0w') CHARSET ucs2
RETURN 0
'

Data totals:

Summary:

Creation time:       2009-11-13 16:26:09 UTC
Validity time:       2009-11-13 16:26:09 UTC
Finish   time:       2009-11-13 16:26:09 UTC
No binlog information

CONCLUSION
----------
Clearly, mysqldump does not preserve the fields mentioned. Clearly, something odd happens to the DTD_IDENTIFIER field during restore using MySQL Backup but does *not* happen when restoring with mysqldump.

Therefore, this is a problem in si_objects code specifically in the object recreation code (materialize).

Backup file from test noted.

Attachment: db1.bak (application/octet-stream, text), 435 bytes.

Mysqldump file.

Attachment: db1.sql (text/x-sql), 2.28 KiB.

Test to show problem is in the extra \0 placed in the string during backup by si_objects serialiation methods.

Attachment: bug44515.test (application/test, text), 1.89 KiB.

I see the problem here. This was in the backup image:

CREATE DEFINER=`root`@`localhost` FUNCTION `f4`() RETURNS enum('\0w') CHARSET ucs2
RETURN 0

Which is clearly wrong because it includes and extra \0 in the return clause.

The problem has been traced to the SHOW CREATE FUNCTION which returns the extra \0. So the problem is in the server code someplace.

mysql> CREATE DATABASE db1 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> USE db1;
Database changed
mysql> CREATE FUNCTION f4 ()
    -> RETURNS ENUM( 'w' ) CHARACTER SET ucs2
    -> RETURN 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE FUNCTION f4 \G
*************************** 1. row ***************************
            Function: f4
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `f4`() RETURNS enum('\0w') CHARSET ucs2
RETURN 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> 

Notice the extra \0.

The initial problem reported concerns extra columns that are not included in the SHOW CREATE FUNCTION method which is what MySQL Backup uses. Thus, there is no bug in the system -- these fields are not supported by SHOW CREATE FUNCTION.

There is, however, a bug in the SHOW CREATE FUNCTION method. A new bug report has been opened to address this separate issue. See BUG#48766.