Re: data truncation warnings by special characters [SOLVED]

Re: data truncation warnings by special characters [SOLVED]

am 18.04.2008 23:04:33 von cr.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@cox.net>
To: "MYSQL General List"
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"
>>
>> To: "'C.R.Vegelin'" ;
>> 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@hetnet.nl]
>>>> Sent: Friday, April 18, 2008 8:42 AM
>>>> To: mysql@lists.mysql.com
>>>> 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=cr.vegelin@hetnet.nl
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org