MySQL Bugs: #25217: Bug latin1 charset

Bug #25217	Bug latin1 charset
Submitted:	20 Dec 2006 13:28	Modified:	12 Mar 2007 15:43
Reporter:	Danilo Pimentel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.0.22 and 5.1.14	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
I have an error ocurred in my server when I execute an UPDATE in a TABLE named 'táblé1'. 

Follow script for demonstrate the problem. 

/* BEGIN OF SCRIPT */ 

/* works With quotes */ 

SET sql_mode = 'ANSI_QUOTES'; 

/* Drop tables If exists */ 

DROP Table If EXISTS "táblé1"; 
DROP Table If EXISTS "táblé2"; 

/* Create new tables */ 

CREATE Table "táblé1" 
( 
"code1" varchar (4) Default '' NOT NULL, 
"description1" varchar (80) Default '' NOT NULL 
); 

CREATE Table "táblé2" 
( 
"code2" varchar (4) Default '' NOT NULL, 
"description2" varchar (80) Default '' NOT NULL, 
"code1" varchar (4) Default '' NOT NULL 
); 

/* Create trigger */ 

DELIMITER ^ 
CREATE TRIGGER "table1_trg_upd" BEFORE Update On "táblé1" For Each Row 
Begin 
Declare i INT; 
If (OLD."code1" <> NEW."code1") Then 
UPDATE "táblé2" 
SET "code1" = NEW."code1" 
WHERE "code1" = OLD."code1"; 
End If; 
End ^ 
DELIMITER ; 

DELIMITER ^ 
CREATE TRIGGER "table2_trg_upd" BEFORE UPDATE ON "táblé2" FOR EACH ROW 
BEGIN 
DECLARE i INT; 
IF (OLD."code1" <> NEW."code1") THEN 
SET i = 0; 
SELECT COUNT(*) INTO i FROM "táblé1"; 
IF (i = 0) THEN 

/* Any code here... */ 
SET i = 2; 
/* Any code here... */ 

END IF; 
END IF; 
END ^ 
DELIMITER ; 

/* END OF SCRIPT */ 

How to repeat:
To reproduce the error just add a new row in table 'táblé1' and after update this data with any diferent values. 

The server will response with message: "Table 'teste.tÃ!@AS!@#2` doesn't exist', I don't see any relation to this problem. 

The error occur because the trigger "table2_trg_upd" include the select expression: 

SELECT COUNT(*) INTO i FROM "táblé1"; 

If I remove this select of the trigger, execution occur whithout any problem. 

The characterset of database and connection is 'latin1', and error occurr just in cases of tables with "accent" characters, like 'táblé1' or 'táblé2'.

PS: This problem just occurs when adding the first record using ODBC or .NET drivers to connect to database. If we add the first record using query browser, other rows can be added sucessfuly using ODBC and .NET drivers or query browser.

Thank you for a problem report. Please, send the results of:

show variables like 'char%';

from the same mysql command line window where you tested these.

show variable like 'char%'

Attachment: mysql.JPG (image/pjpeg, text), 31.55 KiB.

Hi Valeriy Kravchuk,

Follow the image with the results requested.

Thanks

Danilo Pimentel

Please, try to repeat with a newer version, 5.0.27 and 5.1.15, and execute 'set names latin1" before testing. Look:

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP Table If EXISTS "táblé1";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP Table If EXISTS "táblé2";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE Table "táblé1"
    -> (
    -> "code1" varchar (4) Default '' NOT NULL,
    -> "description1" varchar (80) Default '' NOT NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE Table "táblé2"
    -> (
    -> "code2" varchar (4) Default '' NOT NULL,
    -> "description2" varchar (80) Default '' NOT NULL,
    -> "code1" varchar (4) Default '' NOT NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter //
mysql> CREATE TRIGGER "table1_trg_upd" BEFORE Update On "táblé1" For Each Row
    -> Begin
    -> Declare i INT;
    -> If (OLD."code1" <> NEW."code1") Then
    -> UPDATE "táblé2"
    -> SET "code1" = NEW."code1"
    -> WHERE "code1" = OLD."code1";
    -> End If;
    -> End//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> insert into "táblé1" values('abc', 'def');
Query OK, 1 row affected (0.08 sec)

mysql> update "táblé1" set "description1" = 'WOW';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select version();
Field   1:  `version()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     19
Max_length: 19
Decimals:   31
Flags:      NOT_NULL

+---------------------+
| version()           |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'char%';
Field   1:  `Variable_name`
Catalog:    `def`
Database:   ``
Table:      `VARIABLES`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     80
Max_length: 24
Decimals:   0
Flags:      NOT_NULL

Field   2:  `Value`
Catalog:    `def`
Database:   ``
Table:      `VARIABLES`
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     255
Max_length: 55
Decimals:   0
Flags:      NOT_NULL

+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | latin1
     |
| character_set_connection | latin1
     |
| character_set_database   | latin1
     |
| character_set_filesystem | binary
     |
| character_set_results    | latin1
     |
| character_set_server     | latin1
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".