From: Roland Kaber [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2008 11:24 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Erro 1406 Data too long
It looks like it is really a character set conflict. The copyright character © is ascii 169 and is part of latin-1. However, there is a similar character, Ⓒ the circled latin capital letter c which is not in the latin-1 character set. I have found two solutions: 1. setting the column's character set to utf-8 2. keeping the latin 1 character set and changing the INSERT as follows: INSERT INTO text_t (t) VALUES (ASCII(169)) [JS] I have one data feed that (only) sometimes put N-dashes (0x96) in their data. It bedeviled me for a long time. You need to be careful even if you set the character set to UTF-8. You should probably SET NAMES utf8 as well. Our programs are written in PHP, and the ones that cross platforms (Windows client, Linux server) seemed to need mysqli_query($db_conn, $set_names_query) in addition. I think the rules are different for the mysql interface, and possibly for the PDO interface as well. Browsers do something yet again. If you search the message archives for my name, you should find a description of the whole journey I went through. So, thank very much you for your excellent suggestion. Roland Jerry Schwartz wrote: It is a character set conflict between the source of the data and the column. I run into this all of the time when using the CLI. Programmatically it can be avoided. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -----Original Message----- From: Roland Kaber [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2008 4:21 PM To: mysql@lists.mysql.com Subject: Erro 1406 Data too long Hello I recently encountered the following problem. I changed the sql mode to TRADITIONAL recently. Here is a test table for demonstration purposes. CREATE TABLE `text_t` ( `t` text collate latin1_general_cs ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs The following INSERT returns: error 1406 Data too long for column 't'; INSERT INTO text_t (t) values ('©') Why? A single character can't be too long. After executing INSERT IGNORE INTO text_t (t) values ('©'), the special character '©' is inserted with a warning that the string had to be truncated? I then changed sql_mode again: SET sql_mode = ''. Thereafter the initial INSERT worked correctly. A few additional remarks: * The special character seems to be part of the problem. Any normal character works fine. * The problem occured on my local server, MySQL version 5.0.37. * I run a MAC book pro. * I issued the same statements to a MySQL server on a Windows XP machine. The problem simply didn't occur even in traditional sql mode. Do you understand what is going on? Could it be a bug? Thank you in advance for any help you can offer. Roland K
