| Bug #18908 | ERROR 1406 (22001): Data too long for column :: using utf8 | ||
|---|---|---|---|
| Submitted: | 8 Apr 2006 14:29 | Modified: | 17 Nov 2006 17:56 |
| Reporter: | Alec Doughty | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.19,5.0.21,5.0BK | OS: | Windows (WinXP) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[8 Apr 2006 14:29]
Alec Doughty
[28 Apr 2006 18:00]
Valeriy Kravchuk
Sorry for a long delay with this bug report. Please, try to repeat with a newer version, 5.0.20a, and, in case of same problem, send the SHOW VARIABLES LIKE 'char%'; results from the same mysql client.
[29 Apr 2006 15:05]
zhenkun zhang
I'm just using MySQL 5.0.20a-nt and Apache Struts on WinXP Pro to create Web app. I have configured the DB connection pool by JNDI and I want to insert a record with some Chinese character. When I execute the "insert" statement, I still get the same error which is something like:
Data truncation: Data too long for column...
I exceute the statement:
show characters like 'ch%'
the following is my screen output:
character_set_client gb2312
character_set_connection gb2312
character_set_database utf8
character_set_filesystem binary
character_set_results gb2312
character_set_server utf8
character_set_system utf8
character_sets_dir E:\\mysql\\share\\charsets\\
[27 May 2006 17:13]
MySQL Verification Team
5.0.22 on Windows:
<cut>
mysql> INSERT INTO table1 VALUES ('Côte Rôtie');
ERROR 1406 (22001): Data too long for column 'alty_desc' at row 1
Am trying on linux, then will give feedback.
[27 May 2006 17:30]
Geert Vanderkelen
Works OK on MacOS and Linux using 5.0.21 with test case provided.
[29 May 2006 12:51]
Rene Leonhardt
Does not work on Windows XP and MySQL 5.0.21-community-nt or 5.1.9-beta. Both versions have ENGINE=MyISAM and the same output for SHOW VARIABLES LIKE 'char%': character_set_client | utf8 character_set_connection | utf8 character_set_database | utf8 character_set_filesystem | binary character_set_results | utf8 character_set_server | utf8 character_set_system | utf8 The character_sets_dir values are correct. Both my.ini files have [mysql] default-character-set=utf8 [mysqld] default-character-set=utf8 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Best regards, René
[7 Jun 2006 12:21]
Eamonn Power
I'm encountering the same problems on 5.0.22 - community
[11 Jun 2006 14:06]
Rene Leonhardt
Does not work on Windows XP and MySQL 5.0.22-community-nt or 5.1.11-beta.
[4 Jul 2006 23:28]
Richard Shea
It seems to me that I am experiencing this bug using Gentoo and 5.0.18. My means of replicating the bug are shown below. I'd be grateful if someone could point out whether I'm doing something wrong or whether this is not restricted to Win32 (as the comments seem to suggest). Also it's not clear to me whether there is a version available in which this bug is not seen - does anyone know ?
Here's how I can produce the effect ...
DROP TABLE IF EXISTS TBL_UTF8BUGTEST;
CREATE TABLE `TBL_UTF8BUGTEST` (`alty_desc` varchar(150) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET NAMES utf8;
/*!40000 ALTER TABLE TBL_UTF8BUGTEST DISABLE KEYS */;
INSERT INTO TBL_UTF8BUGTEST VALUES ('Albarino');
INSERT INTO TBL_UTF8BUGTEST VALUES ('Côte Rôtie');
... and the output looks like this ...
localhost ~/src/sql $ mysql --user=wxyzdba --password=123 wxyzlearn
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3393 to server version: 5.0.18-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> source testutf8bug.sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
ERROR 1406 (22001): Data too long for column 'alty_desc' at row 1
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0
Connection id: 3393
Current database: wxyzlearn
Current user: wxyzdba@localhost
SSL: Not in use
Current pager: /usr/bin/less
Using outfile: ''
Using delimiter: ;
Server version: 5.0.18-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 19 days 4 hours 51 min 26 sec
Threads: 4 Questions: 119182 Slow queries: 0 Opens: 53 Flush tables: 1 Open tables: 64 Queries per second avg: 0.072
--------------
[5 Jul 2006 0:29]
MySQL Verification Team
Looks to me this a terminal issue, below I tested with mysql.exe
client and the same server running on Windows but queried using
a Linux terminal:
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS table1;
Query OK, 0 rows affected, 1 warning (0.17 sec)
mysql>
mysql> CREATE TABLE `table1` (
-> `alty_desc` varchar(150) NOT NULL default '') ENGINE=InnoDB DEFAULT
-> CHARSET=utf8;
Query OK, 0 rows affected (0.69 sec)
mysql>
mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> /*!40000 ALTER TABLE table1 DISABLE KEYS */;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> INSERT INTO table1 VALUES ('Albarino');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO table1 VALUES ('C�e R�ie');
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'alty_desc' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.02 sec)
miguel@hegel:~/dbs/5.0> bin/mysql -umiguel -p -h192.168.0.33 test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS table1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TABLE `table1` (
-> `alty_desc` varchar(150) NOT NULL default '') ENGINE=InnoDB DEFAULT
-> CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> /*!40000 ALTER TABLE table1 DISABLE KEYS */;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------+
| Note | 1031 | Table storage engine for 'table1' doesn't have this option |
+-------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO table1 VALUES ('Albarino');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO table1 VALUES ('Côte Rôtie');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM table1;
+--------------+
| alty_desc |
+--------------+
| Albarino |
| Côte Rôtie |
+--------------+
2 rows in set (0.00 sec)
mysql>
[5 Jul 2006 1:08]
Richard Shea
Hi Miguel - Interesting to see your tests. If I understand your correctly the using MySQL.exe on Windows you could see the problem but using it on Linux you could not ? That's interesting, in my test I used MySQL client on linux and the server is on Linux. By the way do you have 'strict' in place on the server you used ? I do in my tests.
[29 Jul 2006 7:44]
Rene Leonhardt
The bug seems to be fixed on Windows XP and MySQL 5.0.23-community-nt, I tried with MyISAM and default-character-set=utf8 or latin1. Can anyone confirm this? Good work, thanks, René
[30 Jul 2006 11:53]
Andriy Vyedyeneyev
I have same problem with mysql.exe, MySql query browser, DbVisualizer client, and java jdbc connector. Can someone provide working case with utf-8 table/fields ? If i take off strict mode in my.ini, then data will be inserted but special characters rendered as question marks.
[30 Jul 2006 14:31]
Andriy Vyedyeneyev
I have finally get it working. At least from DbVisualizer and j/connector. mysql.exe and MySQL Query browser still can't display it properly, but insert's going through. Here is my steps: 1) set my.ini properties to use utf8 [mysql] default-character-set=utf8 character-sets-dir="D:/Programs/MySQL/MySQL Server 5.0/share/charsets/" [mysqld] default-character-set=utf8 character_set_server=utf8 2) Restart server and create new database. Dump your old database DDL, remove from DDL all "character set" settings. One of my problems was charset settings on column level in create table script. 3) Run ddl script in new database. That's it. Now you can try it.
[30 Jul 2006 23:27]
Richard Shea
Hi Andriy - That sounds good (as upgrading beyond 5.0.19 is not an option for me currently). I just wanted to check - in your first post you mentioned turning off strict resolved it but in your second post I assume you've still got strict on ? Could you confirm that please ? thanks richard.
[16 Aug 2006 0:43]
dAniel hAhler
The problems seems to be, if you try to insert "high-ascii" characters, encoded in latin1 (e.g. "äöü") in a connection where you've "SET NAMES utf8". If you test the provided test case (at the top of this report), save it as "latin1"/"iso-8859-1" encoded file and add SET sql_mode = "TRADITIONAL"; at the top of it, to enable "strict mode". I was able to reproduce this problem on 5.0.21-3ubuntu1.
[21 Sep 2006 6:41]
Alexander Barkov
Dear Alec,
It looks like not a bug. The problems is that your script
file is most likely written using notepad or some other
text editor, which means it uses cp1252 (aka latin1) character set,
which is the native character set on a Western WinXP machine.
The scriot file incorrectly specifies "set names utf8" in
the beginning. It should be "set names latin1" instead.
Please check whether it works as expected with "set names latin1".
Also, please add this query into your script and post its results here:
SELECT hex('Côte Rôtie');
I'm expecting you'll get this result:
mysql> SELECT hex('Côte Rôtie');
+----------------------+
| hex('Côte Rôtie') |
+----------------------+
| 43F474652052F4746965 |
+----------------------+
1 row in set (0.00 sec)
Is that true?
Thanks!
[2 Oct 2006 16:43]
Randy Letness
I'm having the same issues. I can reproduce this when I try to insert supplementary characters (the set of characters from U+10000 to U+10FFFF). Characters in the BMP (Basic Multilingual Plane, those from U+0000 to U+FFFF) work fine. My config: OS: Windows XP MySQL Version: 5.0.24a Client: Connector/J 5.0.3 Table SQL: CREATE TABLE `attribute` ( `id` bigint(20) NOT NULL auto_increment, `attributetype` varchar(16) NOT NULL, `attributename` varchar(255) NOT NULL, `itemid` bigint(20) default NULL, `binvalue` longblob, `stringvalue` varchar(1024) default NULL, `datevalue` datetime default NULL, `intvalue` bigint(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
[2 Oct 2006 17:05]
Michael Ekoka
I'm experiencing a similar problem, it's not really affecting my productivity but it's frustrating enough because I'm just getting familiar with character encoding and utf-8 in MySql and this bug (if it is indeed a bug) leaves me with the feeling that I have no idea what I'm doing. This is the report of my tests: /*********/ Using Windows XP sp2 MySQL - 5.0.22-community PhpMyAdmin phpMyAdmin - 2.8.2 I have inserted the following in my.ini: [mysql] default-character-set=utf8 [mysqld] default-character-set=utf8 All my tables and columns have been converted from latin1 to utf8 collation utf8_unicode_ci manually. I'm building a web application in php that uses a class to connect to mysql. The class sets the connection between php and mysql for each object to utf-8 with the query 'SET NAME "utf8";' When I include data from my php application I have no problems. It goes in fine and it displays fine in my application and in PhpMyAdmin. My application sets the browser's header to "Content-Type: text/html; charset=utf-8" so that everything should read fine. On that side all looks ok. Now I have a couple of problems with the mysql command line client: It doesn't display the non ascii characters that I insert in my tables from PMA or from my php application (e.g. é,è,ê,ô,etc). It shows gibberish instead. When I try to insert these characters in a column X with the client, I get error 'Data too long for column X'. this is what 'show variables like "character_set%"' looks like: | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | character_set_filesystem | binary | character_set_results | utf8 | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\chars ets\ it all looks accurate, although i noticed that there's no utf8.xml charset file in the folder specified. I don't know if that should be, I'm not proficient enough in character encoding to know what the scheme is. I went in my.ini and removed the 'default-character-set=utf8' setting from the [mysql] section and launched the command line client again. Now the special characters inserted from PMA and my php application show some new gibberish different from the ones before. When I insert special characters from the command line client it goes in fine and displays fine, only now the ones inserted from it do not show up properly in phpmyadmin or in my php application. Again the result of 'show variables like "character_set%"': | character_set_client | latin1 | character_set_connection | latin1 | character_set_database | utf8 | character_set_filesystem | binary | character_set_results | latin1 | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\chars ets\ Let me know if this is a bug so that I can move on with my life and be happy.
[2 Oct 2006 22:00]
Michael Ekoka
OK I installed MySQL Query Browser and tried to query the db with it and it works fine. So I can rest. The problem must be with either the mysql command line client or the terminal.
[11 Oct 2006 4:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/13439 ChangeSet@1.2282, 2006-10-11 09:00:45+05:00, bar@mysql.com +12 -0 Bug#18908: ERROR 1406 (22001): Data too long for column :: using utf8 Problem: Too confusing error message when cannot convert between string and column character sets on INSERT and UPDATE. Fix: producing a better error message, instead of "Data too long" in such cases
[11 Oct 2006 9:11]
Alexander Barkov
See also bug#9337, which has been marked as duplicate for 18908.
[30 Oct 2006 6:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14531 ChangeSet@1.2282, 2006-10-30 10:14:03+04:00, bar@mysql.com +13 -0 Bug#18908: ERROR 1406 (22001): Data too long for column :: using utf8 Problem: Too confusing error message when cannot convert between string and column character sets on INSERT and UPDATE. Fix: producing a better error message, instead of "Data too long" in such cases Additional changes: Adding "DROP TABLE IF EXISTS" into several tests to be safe against failures in previous tests.
[30 Oct 2006 10:48]
Alexander Barkov
Pushed into 5.0-rpl, marked as 5.0.27
[3 Nov 2006 6:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14785 ChangeSet@1.2327, 2006-11-03 10:18:13+04:00, bar@mysql.com +2 -0 After merge fix for BUG#18908 ERROR 1406 (22001): Data too long for column :: using utf8
[16 Nov 2006 16:24]
Alexander Barkov
Appeared in 5.0.32 common Appeared in 5.1.14 common
[17 Nov 2006 17:56]
Paul DuBois
Noted in 5.0.32, 5.1.14 changelogs. For some problems relating to character set conversion or incorrect string values for INSERT or UPDATE, the server was reporting truncation or length errors instead.
[29 Nov 2006 21:42]
Yoav Shapira
Verified bug exists on 5.0.27 (Linux, Fedora Core 5). Verified workaround suggested by Andriy above works: [mysqld] default-character-set=utf8 character_set_server=utf8 Looking forward to 5.0.32.
[4 Jun 2008 16:17]
Jared Knowlton
That's funny, I'm on 5.0.3.7-community-nt and I still have this problem with Spanish accented letters. I guess it wasn't fixed?
[18 Sep 2008 21:52]
G F
I get the same problem. Win2003 server mysql 5.0.51 innodb charset UTF8 I attempt to insert a large xml string into a blob and get the same error. I know it is no where near the limits of a blob so what gives?
[13 Dec 2008 18:31]
Amit Gupta
Thanks you have Solved my big issue(data too long) to change char set to Utf8
