Re: Tired of MYSQL and Character Set Problems!

[Matt Horton]

Hi, I'm not using Apache here, but this seems like the proper thread. Using Mac OS X (10.4.8) and mysql version:
| 5.0.26-max |

I'm getting similar behavior. I have a list of names, some of which include diacritics. Using the strategy suggested in the post by Markus Bertheau (code below), my values get truncated when 'load data infile' encounters an accent character.

The code is as follows, and thank you to those that have any insights!

mysql> create database test1 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> USE test1;
Database changed
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-max-5.0.26-osx10.4-i686/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> create table sites ( name varchar(100) not null, seedset int(1) not null );
Query OK, 0 rows affected (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '/Users/matt/Desktop/work/chemistry/core1.txt' INTO TABLE sites FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r' IGNORE 1 LINES;
Query OK, 192 rows affected, 12 warnings (0.01 sec)
Records: 192 Deleted: 0 Skipped: 0 Warnings: 12

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 102 |
| Warning | 1265 | Data truncated for column 'name' at row 110 |
| Warning | 1265 | Data truncated for column 'name' at row 165 |
| Warning | 1265 | Data truncated for column 'name' at row 166 |
| Warning | 1265 | Data truncated for column 'name' at row 167 |
| Warning | 1265 | Data truncated for column 'name' at row 169 |
| Warning | 1265 | Data truncated for column 'name' at row 170 |
| Warning | 1265 | Data truncated for column 'name' at row 171 |
| Warning | 1265 | Data truncated for column 'name' at row 172 |
| Warning | 1265 | Data truncated for column 'name' at row 175 |
| Warning | 1265 | Data truncated for column 'name' at row 181 |
| Warning | 1265 | Data truncated for column 'name' at row 183 |
+---------+------+---------------------------------------------+
12 rows in set (0.01 sec)

mysql>