Re: data truncation warnings by special characters [SOLVED]

[C.R.Vegelin]

Thanks Chris,

Well the script in question is a MySQL script to load a MySQL database.
Currently I use only PHP only for database retrieval.
But after trial and error I managed to find a 'work around', as follows:
a) CREATE TABLE tmp with ENGINE = MyISAM CHARSET = LATIN1;
b) LOAD DATA INFILE statement; =>  no truncation warnings
c) ALTER TABLE tmp CONVERT TO CHARACTER SET UTF8;

Thanks again and a nice weekend.
Cor


----- Original Message ----- 
From: "Chris W" <2wsxdr5@stripped>
To: "MYSQL General List" <mysql@stripped>
Sent: Friday, April 18, 2008 8:38 PM
Subject: Re: data truncation warnings by special characters


>I have some php code I use to import data that is a bit more flexible and 
>robust than the load data statement in MySQL  If you use php I can share 
>the code with you.
>
> C.R.Vegelin wrote:
>> Hi Jerry,
>>
>> Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt.
>> The script giving errors now is used once a year, and last year without 
>> any warning.
>> Since last year all I changed was adding in my.ini:
>> # in [client] part
>> default-character-set=utf8
>> # in [mysqld] part
>> default-character-set=utf8
>> character-set-server = utf8
>> collation-server = utf8_general_ci
>>
>> When I undo these changes, and stop and start mysql, the errors remain.
>> The Data.txt file (from an external source) looks okay with Wordpad.
>>
>> TIA, Cor
>>
>>
>> ----- Original Message ----- From: "Jerry Schwartz" 
>> <jschwartz@stripped>
>> To: "'C.R.Vegelin'" <cr.vegelin@stripped>; <mysql@stripped>
>> Sent: Friday, April 18, 2008 2:30 PM
>> Subject: RE: data truncation warnings by special characters
>>
>>
>>> >-----Original Message-----
>>>> From: C.R.Vegelin [mailto:cr.vegelin@stripped]
>>>> Sent: Friday, April 18, 2008 8:42 AM
>>>> To: mysql@stripped
>>>> Subject: data truncation warnings by special characters
>>>>
>>>> Hi List,
>>>>
>>>> I get strange "Data truncated for column Description" warnings
>>>> when loading a tab separated file with special characters.
>>>
>>> [JS] This sounds like it is related to the problems I've been having. I
>>> think I've come to an understanding of my issues, but I am not using 
>>> LOAD
>>> DATA INFILE. I'll be posting what I have learnt, once I get a chance, 
>>> but I
>>> don't know if it will help you.
>>>
>>> What is the source of your input data? Windows? Linux?
>>>
>>>> The definition of the target table is:
>>>>
>>>> CREATE TEMPORARY TABLE tmp
>>>> ( Code CHAR(8) NOT NULL,
>>>>  Description TEXT NOT NULL,
>>>>  KEY Code (Code)
>>>> ) ENGINE = MyISAM CHARSET=UTF8;
>>>>
>>>> The Load Into statement I use is:
>>>>
>>>> LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp
>>>> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
>>>>
>>>> The truncation warnings are caused by words like PURÉES.
>>>> This leads to a truncted Description field like: "Jams, Jellies, Pur".
>>>>
>>>> Any idea how to solve this ?
>>>>
>>>> Thanks in advance, Cor
>>>
>>>
>>>
>>>
>>
>>
>>
>
> -- 
> Chris W
> KE5GIX
>
> "Protect your digital freedom and privacy, eliminate DRM, learn more at 
> http://www.defectivebydesign.org/what_is_drm"
>
> Ham Radio Repeater Database.
> http://hrrdb.com
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>