Bug #24816 Special characters in stored procedures does not restore properly
Submitted: 5 Dec 2006 11:42 Modified: 7 Dec 2006 15:03
Reporter: Pr Gradin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S2 (Serious)
Version:5.1.12 OS:Windows (xp)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: Backup, encoding, restore, stored procedures

[5 Dec 2006 11:42] Pr Gradin
Description:
I have a couple of stored procedures that contains swedish characters. When I restore a backup of my database those characters does not restore properly. The table data (also containing swedish characters) gets restored properly, but not the procedures. I get strange characters within those procedures and not the sweedish characters I want.

The MySQL server was installed using the default settings.
The database was created with MA ver 1.2.6 rc.
Backup/Restore using default options.

As of now I can not do a proper restore of my database.

How to repeat:
1.
CREATE PROCEDURE `MySchema`.`MyProc`()
BEGIN
  SELECT 'ÅÄÖåäö' as mycharacters;
END;
2. Executing the procedure returns the correct characters.
3. Now do a backup of the database (using default options)
4. Do a restore of the backup created (using default options).
5. Have a look at the procedure:
PROCEDURE `MySchema`.`MyProc`()
BEGIN
  SELCT 'ÅÄÖåäö' as mycharacters;
END
6. Executing the procedure returns incorrect characters.

Suggested fix:
Does mysql actually restore stored procedures in UTF-8?
[5 Dec 2006 17:53] Sveta Smirnova
Thank you for the report.

I can not repeat it using mysqldump.

Do you make backup and restore using MySQL Administrator? What is the version of MySQL  Administrator you use?
[5 Dec 2006 21:09] Pr Gradin
Hi Sveta,
I use mysql admin ver 1.2.6 rc.
Below is some output from the command client:

Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.27-community-nt

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

mysql> use myschema;
Database changed
mysql> delimiter //
mysql> CREATE PROCEDURE `MySchema`.`MyProc`()
    -> BEGIN
    ->  SELECT 'ÅÄÖåäö' as mycharacters;
    -> END; //
Query OK, 0 rows affected (0.00 sec)

mysql> call myproc();//
+--------------+
| mycharacters |
+--------------+
| ÅÄÖåäö       |       <-------------Good characters
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

(NOW, DO A BACKUP AND RESTORE IN MYSQL ADMIN VER 1.2.6 RC)

mysql> call myproc();//
+--------------+
| mycharacters |
+--------------+
| Ž™†„” |       <-------------Bad characters
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>
/Pär
[5 Dec 2006 21:16] Sveta Smirnova
Category changed to more appropriate
[5 Dec 2006 21:17] Pr Gradin
The backupfile generated and restored

Attachment: bak_myschema 20061205 2155.sql (application/octet-stream, text), 1.28 KiB.

[5 Dec 2006 21:18] Pr Gradin
I have attached the backupfile.
/Pär
[6 Dec 2006 9:32] Sveta Smirnova
As I can see you used mysql command line client to create procedure. Please provide dump created by mysqldump, not MySQL Administrator.
[6 Dec 2006 11:42] Pr Gradin
Here is the dump from mySQLDump.

Attachment: MySchemaDump.sql (text/plain), 1.76 KiB.

[6 Dec 2006 11:45] Pr Gradin
Hi Sveta,
The dump created from mySQLDump works just fine!
My swedish characters look ok.

Strange that the MySQLAdmin does not work, though...
Thanks. 
/Pär
[6 Dec 2006 12:01] Sveta Smirnova
Thank you for the report.

MySQL Administrator bug verified as described using dump provided.
[7 Dec 2006 15:03] Mike Lischke
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html