MySQL Bugs: #27844: Query Browser saves the SP with UTF8 format. Can't be run from CLI.

Bug #27844	Query Browser saves the SP with UTF8 format. Can't be run from CLI.
Submitted:	16 Apr 2007 9:33	Modified:	19 Jul 2007 10:26
Reporter:	Niklas Larsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any (reported on Win XP SP2)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	stored proc

Description:
If i make a SP with non-us fieldnames, it's only possible to call/run them from QB - It's all OK. But then when i use the CLI or PHP it fails, becouse the fieldname are converted to UTF8 - but the server is using latin.

mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2
[...]
Server version:         5.0.32-Debian_7etch1-log Debian etch distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1

If I do the create sp from the CLI it works as it should.

How to repeat:
DELIMITER $$

DROP PROCEDURE IF EXISTS `Accenta`.`sp_test_nick` $$
CREATE DEFINER=`root`@`192.168.%.%` PROCEDURE `sp_test_nick`()
BEGIN
  INSERT INTO test_sp
  SELECT null, P.NamnPåAvtal, P.Anställningsnr FROM Personal P;
  END $$

DELIMITER ;

Executing this in QB gives in mysql.proc:

mysql> select body from mysql.proc where name = 'sp_test_nick'\G
*************************** 1. row ***************************
body: BEGIN
  INSERT INTO test_sp
  SELECT null, P.NamnPÃ¥Avtal, P.AnstÃ¤llningsnr FROM Personal P;
  END
1 row in set (0.00 sec)

And:

mysql> call sp_test_nick();
ERROR 1457 (HY000): Failed to load routine Accenta.sp_test_nick. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

PS. Using ` around the column names gives me another error: Errormessage: Unknown column 'P.NamnPÃÂ¥Avtal' in 'field list'

Thank you for the report.

Please indicate version of MySQL server you use.

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

Connection id:          542140
Current database:       Accenta
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.32-Debian_7etch1-log Debian etch distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 9 days 16 hours 35 min 58 sec

Threads: 52  Questions: 37923857  Slow queries: 696036  Opens: 1357  Flush tables: 1  Open tables: 472  Queries per second avg: 45.290
--------------

Thank you for the feedback.

I believe cause of the problem can be server: important character set bug has been fixed after 5.0.32. Also I can not repeat it with in my environment. Please upgrade MySQL server to current 5.0.37 version, try with it and say us result.

I have now set up an mysql server 5.0.37, and are it's behaving the same way. QB is saving the SP in UTF8 and i can't run it from the CLI or PHP - only from QB.

mysql> select * from proc\G
*************************** 1. row ***************************
              db: Nicks
            name: sp_nick_test
            type: PROCEDURE
   specific_name: sp_nick_test
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: NO
   security_type: DEFINER
      param_list:
         returns:
            body: BEGIN
 INSERT INTO test_sp
  SELECT null, t.AnstÃ¤llning, t.LÃ¶n FROM test_sp_2 t;
END
         definer: root@%
         created: 2007-04-17 13:05:16
        modified: 2007-04-17 13:05:16
        sql_mode:
         comment:
1 row in set (0,00 sec)

mysql> \s
--------------
bin/mysql  Ver 14.12 Distrib 5.0.37, for pc-linux-gnu (i686) using  EditLine wrapper

Connection id:          5
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.37 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld5.sock
Uptime:                 1 min 33 sec

Threads: 1  Questions: 8  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6  Queries per second avg: 0.086
--------------

test case

Attachment: bug27844.test (application/octet-stream, text), 516 bytes.

Thank you for the feedback.

Verified as described on Linux using last development sources.

I reclassified it as server bug, because client (Query Browser in your case) settings should not affect important tables.

Workaround:

SET NAMES utf8;
CALL sp_test_nick();

Test case:
set names utf8;

DELIMITER |;

DROP PROCEDURE IF EXISTS `sp_test_nick` |
CREATE PROCEDURE `sp_test_nick`()
BEGIN
  INSERT INTO test_sp
  SELECT null, P.NamnPåAvtal, P.Anställningsnr FROM dunno P;
  END |

DELIMITER ;|

connect (addcon, localhost, root,,);
connection addcon;

show variables like '%char%';

--vertical
select body from mysql.proc where name = 'sp_test_nick';

--error 1457
call sp_test_nick();

set names utf8;
--error 1146
call sp_test_nick();
set names latin1;
--error 1146
call sp_test_nick();

Sveta,
what is the expected result?

Kostja,

I don't want to get error:

mysql> call sp_test_nick();
ERROR 1457 (HY000): Failed to load routine test.sp_test_nick. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

Why mysql.proc is reporting as corrupted?

This seems to be a duplicate of BUG#11986.
At least, this bug is fixed by the patch for BUG#11986.

Moving it to "In progress" to ensure having test case for it.

asdasd

Attachment: superball560.jpg (image/jpeg, text), 74.54 KiB.

The bug was fixed by the patch for BUG#11986.
The behaviour is tested in ddl_i18n_utf8.test and
ddl_i18n_koi8r.test.