MySQL Bugs: #16097: incorrect processing sql command : <<show triggers;>>

Bug #16097	incorrect processing sql command : <<show triggers;>>
Submitted:	30 Dec 2005 11:25	Modified:	11 Jan 2006 14:28
Reporter:	oktogen oktogen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Linux (linux mandrake 10)
Assigned to:		CPU Architecture:	Any

Description:
description:

Problem in russian coding(charset).
Reveals Itself in triggers ONLY. 
Concluded in :
1) when calling an operator <<show triggers;>>
text of column 'statement' in select torn off on first symbol russian coding(charset).
Looks like that trigger is not saved completely.
2) In spite of this (1), trigger operates all right. 

Remarks:
1) encoding operation system - koi8r
 I use of server MySQL (my.cfg fragment)
[mysqld]
default-character-set=cp1251
default-table-type= InnoDB

2) 
I use russian table names(charset) and columns

How to repeat:
set names = koi8r;
create database 'trigger_test';
use trigger_test; 

CREATE TABLE `проба` (
  `ключ` bigint(20) unsigned NOT NULL auto_increment,
  `название` char(20) default NULL,
  PRIMARY KEY  (`ключ`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

CREATE TRIGGER `проба_before_upd_tr` BEFORE UPDATE ON `проба`
  FOR EACH ROW
BEGIN
# this commend break further text on russian word пример
SET NEW.название = OLD.название;
END;

# cause this problem
show triggers;

# in spite of this, trigger operates all right.
update проба set проба.название = 'new value'; 

Suggested fix:
1) absolutely not to use russian text in triggers. 
such decision unacceptable

this problem discovered for  win2000 and win XP.

Thank you for a problem report. Sorry, but it looks like your fault, not a bug:

mysql> CREATE TABLE `проба` (
    ->   `ключ` bigint(20) unsigned NOT NULL auto_increment,
    ->   `название` char(20) default NULL,
    ->   PRIMARY KEY  (`ключ`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.38 sec)
 
mysql> delimiter $$
mysql> CREATE TRIGGER `проба_before_upd_tr` BEFORE UPDATE ON `проба`
    ->   FOR EACH ROW
    -> BEGIN
    -> # this commend break further text on russian word пример
    -> SET NEW.название = OLD.название;
    -> END;
    -> $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�азвание = OLD.название;
END' at line 4

So, just as you described... But look:

mysql> CREATE TRIGGER `проба_before_upd_tr` BEFORE UPDATE ON `проба`   FOR EACH ROW BEGIN SET NEW.`название` = OLD.`название`; END;$$
Query OK, 0 rows affected (0.00 sec)

The same as for table name:
 
mysql> create table `таблица`(c1 int);
Query OK, 0 rows affected (0.00 sec)
 
mysql> desc таблица;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�аблица' at line 1
mysql> desc `таблица`;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.13 sec)
 
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.08 sec)

Please, send the results of the following statement:

show variables like 'character%';

from the same mysql window where you have problems with trigger.

show variables for character stes

Attachment: show_variables_like_character.htm (text/html), 1.36 KiB.

show triggers

Attachment: show_triggers.htm (text/html), 1.86 KiB.

logs

Attachment: my_log.htm (text/html), 1.16 KiB.

files I has placed here. 
Please, look it.
that this can be?
Thank you.

P.S.
Happy New YEAR!!!

excuses me, forgot to warn.
Coding in you  browser must be set to  cp1251

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/charset-metadata.html) about charcter_set_system system variable (that has value utf8). Because you performed "set names cp1251;", you metadata are not in utf8, and you have to quote all non-ASCII letters, just as I explained before. Look:

mysql> create database trigger_test;
Query OK, 1 row affected (0.01 sec)

mysql> use trigger_test;
Database changed
mysql> set names cp1251;
Query OK, 0 rows affected (0.03 sec)

mysql> create table `таблица`(`бит` bit);
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter //
mysql> CREATE TRIGGER `trigger_test`.`триггер`
    -> BEFORE UPDATE on `trigger_test`.`таблица`
    -> FOR EACH ROW BEGIN
    ->   SET NEW.`бит`=OLD.`бит`;
    -> END;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> show triggers;
+---------+--------+---------+---------------------+--------+---------+---------
-------------------------------------------------------+
| Trigger | Event  | Table   | Statement           | Timing | Created | sql_mode
                                                       |
+---------+--------+---------+---------------------+--------+---------+---------
-------------------------------------------------------+
| триггер       | UPDATE | тавл╕ца       |  BEGIN
  SET NEW.` | BEFORE | NULL    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------+--------+---------+---------------------+--------+---------+---------
-------------------------------------------------------+
1 row in set (0.01 sec)

But:

mysql> create table таблица2 (бит bit);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'аблица2 (бит bit)' at line 1

That was from 5.0.15-nt on Windows. So, it just how it works - you have to quote such identifiers or set names utf8 and provide all of them in utf8. It is not a bug.