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: | |
Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
Version: | OS: | Any | |
Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[28 Apr 2009 13:05]
Rafal Somla
[28 Apr 2009 14:18]
Valeriy Kravchuk
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')
[10 Nov 2009 21:55]
Chuck Bell
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.
[13 Nov 2009 16:38]
Chuck Bell
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).
[13 Nov 2009 16:39]
Chuck Bell
Backup file from test noted.
Attachment: db1.bak (application/octet-stream, text), 435 bytes.
[13 Nov 2009 16:39]
Chuck Bell
Mysqldump file.
Attachment: db1.sql (text/x-sql), 2.28 KiB.
[13 Nov 2009 17:21]
Chuck Bell
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.
[13 Nov 2009 17:22]
Chuck Bell
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.
[13 Nov 2009 17:26]
Chuck Bell
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.
[13 Nov 2009 18:38]
Chuck Bell
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.