| Bug #26491 | False 'data truncated' warnings, inserting Unicode data into 8-bit TEXT fields | ||
|---|---|---|---|
| Submitted: | 20 Feb 2007 9:56 | Modified: | 12 Mar 2007 10:01 |
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0-bk, 5.1-bk | OS: | |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | bfsm_2007_03_01, character sets, Unicode, warnings | ||
[20 Feb 2007 10:00]
Domas Mituzas
In strict SQL_MODE (default on Windows) this will result in an error, instead of a warning. Bumping priorities up.
[12 Mar 2007 10:01]
Alexander Barkov
This bug report is a duplicate for http://bugs.mysql.com/bug.php?id=25815, which was pushed into 5.0.38,5.1.17-beta on the 8th of March 2007. It works as expected with the latest version: mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%char%'; +--------------------------+--------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.0/share/mysql/charsets/ | +--------------------------+--------------------------------------------+ 8 rows in set (0.02 sec) mysql> CREATE TABLE `german` ( `a` text collate latin1_german2_ci) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; Query OK, 0 rows affected (0.01 sec) mysql> insert into german values ('ä'),('aaä'),('aa'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 No warnings are generated anymore.
Description: When Unicode data is inserted into 8-bit character set fields (latin1 and cp1257 tested), warning "Data truncated for column 'a' at row 1" is shown, though the data is inserted correctly. This happens just with *TEXT fields, VARCHAR or CHAR types are not affected. How to repeat: mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%char%'; +--------------------------+--------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.0/share/mysql/charsets/ | +--------------------------+--------------------------------------------+ 8 rows in set (0.00 sec) mysql> CREATE TABLE `german` ( -> `a` text collate latin1_german2_ci -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci -> ; Query OK, 0 rows affected (0.00 sec) mysql> insert into german values ('ä'),('aaä'),('aa'); Query OK, 3 rows affected, 2 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 2 mysql> show warnings; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'a' at row 1 | | Warning | 1265 | Data truncated for column 'a' at row 2 | +---------+------+----------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from german; +------+ | a | +------+ | ä | | aaä | | aa | +------+ 3 rows in set (0.00 sec) mysql> create table lithuanian (a text) charset=cp1257; Query OK, 0 rows affected (0.02 sec) mysql> insert into lithuanian values ('ą'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from lithuanian; +------+ | a | +------+ | ą | +------+ 1 row in set (0.00 sec) Suggested fix: don't print warnings, if data was not actually truncated