Bug #8502 load data infile character set problems

• Submitted: 14 Feb 2005 15:37
• Modified: 15 Feb 2005 12:14
• Reporter: Gleb Paharenko
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.9
• OS: Linux (Linux)
• CPU Architecture: Any

[14 Feb 2005 15:37] Gleb Paharenko

Description:
When I try to load into table a file which contains a non-acsii character '£' - phound sign
(0xA3 in latin1 encoding), the value of the column appears 0xC2A3 - the utf8 value of phound sign.
character_set_client     | latin1              |
| character_set_connection | latin1              |
| character_set_database   | latin1              |
| character_set_results    | latin1              |
| character_set_server     | latin1              |
| character_set_system     | utf8              |
| character_sets_dir       | 

But my system's encoding is UTF-8 (default in Fedora Core 3 distribution)

How to repeat:
[gleb@toyou mysql-debug-4.1.9-pc-linux-gnu-i686]$ hexdump data/test/pr.sql
0000000 0aa3
0000002
mysql> load data infile 'pr.sql' into table pr;
Query OK, 1 row affected (0.02 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select hex(a) pr;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
mysql> select hex(a) from pr;
+--------+
| hex(a) |
+--------+
| C2A3   |
+--------+

Suggested fix:
Load a correct value in the table.

[15 Feb 2005 12:14] Aleksey Kishkin

Hi! I created the same file, 

hexdump /tmp/pr.sql
0000000 0aa3                                   
0000002

then run mysql:
bash-2.05b$ /usr/local/mysql/bin/mysql test 
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 3 to server version: 4.1.9-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'char%';
+--------------------------+-------------------------------------------------------------------------+
| Variable_name            | Value                                                                   |
+--------------------------+-------------------------------------------------------------------------+
| character_set_client     | latin1                                                                  |
| character_set_connection | latin1                                                                  |
| character_set_database   | latin1                                                                  |
| character_set_results    | latin1                                                                  |
| character_set_server     | latin1                                                                  |
| character_set_system     | utf8                                                                    |
| character_sets_dir       | /usr/local/mysql-standard-4.1.9-pc-linux-gnu-i686/share/mysql/charsets/ |
+--------------------------+-------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create table pr (a char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table pr;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| pr    | CREATE TABLE `pr` (
  `a` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> load data infile '/tmp/pr.sql' into table pr;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select hex(a) from pr;
+--------+
| hex(a) |
+--------+
| A3     |
+--------+
1 row in set (0.00 sec)

Could you please check your table definition? what is charset for column a? if you have ideas how to reproduce this error, please let us know..

[15 Feb 2005 12:54] Gleb Paharenko

Sorry, I was wrong.

-------+
| Table | Create Table        |
+-------+------------------------------------------------------------------------------------------+
| pr    | CREATE TABLE `pr` (
  `a` text character set utf8
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

There is no any bug :)