Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to get a simple master-slave MySQL replication running, but I have some strange errors when LOAD DATA INFILE is used:

110908 23:59:42 [ERROR] Slave SQL: Error 'Unknown column 'N°_Site' in 'field list'' on query. Default database: 'b4_sites'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-116-4-6441.data' IGNORE INTO  TABLE `cellule_1` FIELDS TERMINATED BY ';' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES  (`Region`, `V2G3G`, `Lac`, `CI`, `bsc_rnc`, `N°_Site`, `Constructeur`)', Error_code: 1054
110908 23:59:42 [Warning] Slave: Unknown column 'N°_Site' in 'field list' Error_code: 1054
110908 23:59:42 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000002' position 68303896

We have Ubuntu Server 10.04 LTS running on both master and slave . Identical MySQL versions mysql-server-core-5.1 - 5.1.41-3ubuntu12.10 . Almost identical MySQL configs (which I can post if needed) with nothing very fancy .

I can see that there is a special character in the name of one of the fields of the table which obviously provokes the error, but the strange thing is that the query is executed without error on the master...

The character sets on both servers - master:

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

slave:

mysql>  show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Any help is welcome, Thanks in advance

share|improve this question
    
And does DESC b4_sites.cellule_1 produce the same output on both machinse? –  symcbean Sep 9 '11 at 8:49
    
Hello, yes, they are the same ... –  Tupak Goliam Sep 9 '11 at 9:50
add comment

1 Answer

up vote 1 down vote accepted

We found a solution:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set=UTF8
...

,on the masters. Just the client section...

Or:

 mysql> SET NAMES UTF8;

So, just the names of the fields are in UTF-8.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.