Bug #17044 FEDERATED storage engine is not UTF8 clean
Submitted: 2 Feb 2006 15:15 Modified: 3 Jan 2007 11:29
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.0.18 (MySQL download) OS:any Linux
Assigned to: Bugs System CPU Architecture:Any

[2 Feb 2006 15:15] Kristian Koehntopp
Description:
The FEDERATED storage engine is not UTF8 clean.

How to repeat:
On one server

root@localhost [(none)]> create database kris default character set utf8;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use kris
Database changed
root@localhost [kris]> create table t ( d varchar(4000) not null );
Query OK, 0 rows affected (0.04 sec)

root@localhost [kris]> insert into t ( d) values ( "äöüß" );
Query OK, 1 row affected (0.02 sec)

root@localhost [kris]> select * from t;
+----------+
| d        |
+----------+
| äöüß     |
+----------+
1 row in set (0.00 sec)

On the other server

root@localhost [(none)]> create database kris default character set utf8;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use kris
Database changed
root@localhost [kris]> create table t ( d varchar(4000) not null ) engine=federated connection="mysql://root:mysql2006@127.0.0.1:3041/kris/t";
Query OK, 0 rows affected (0.01 sec)

root@localhost [kris]> select * from t;
+---+
| d |
+---+
|   |
+---+
1 row in set (0.00 sec)

Suggested fix:
Make FEDERATED use the MySQL internal utf8 support like everything else?
[3 Feb 2006 11:40] Valeriy Kravchuk
Thank you for a problem report. Please, send the my.cnf files content from both servers.
[3 Feb 2006 12:53] Kristian Koehntopp
Data Source:

[client]
port = 3340
socket = /usr/local/mysql/instances/marina_m/mysql.sock

[mysqld]
#read-only
skip-innodb
server-id = 3340
port = 3340
socket = /usr/local/mysql/instances/marina_m/mysql.sock
skip-locking
table_cache = 256
thread_cache = 8

#
# sizing the server (large)
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
#
# values once per server
key_buffer = 256M			### roughly 10% of the total size of data (indices only)
#innodb_buffer_pool_size = 1500M
#innodb_additional_mem_pool_size = 8M
query_cache_size= 20M
# maximum number of concurrent sessions
max_connections = 50
# values used per session
max_allowed_packet = 1M		
join_buffer_size = 1M
sort_buffer_size = 1M
read_buffer_size = 512K
read_rnd_buffer_size = 512K #used for sorting in order by (per session)
# end sizing

#
# sizing the memory: (small)
#
# values once per server

#key_buffer = 16M
#innodb_buffer_pool_size = 150M
#innodb_additional_mem_pool_size = 8M
#query_cache_size= 16M
## number of concurrent sessions
#max_connections = 10
# values per session
#max_allowed_packet = 1M
#join_buffer_size = 128K
#sort_buffer_size = 512K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
# end per session variables
# end sizing

myisam_sort_buffer_size = 64M		##  used only for repairs, creation of indexes 

#Set character set
default-character-set=utf8
lower_case_table_names = 1
#default_table_type = InnoDB			## if not specidied default is MyISAM
#innodb_file_per_table = 1
#

# Try number of CPU's*2 for thread_concurrency
#(InnoDB parameter calculate on the basis of cpu + disk in theory, pratically set to 4 )
thread_concurrency = 4

log_slow_queries = /usr/local/mysql/logs/marina_m/slow.log
log_bin = /usr/local/mysql/logs/marina_m/binlog

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
#no-auto-rehash
auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=utf8

prompt = \u@\h [\d]>\_

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

#Used for hotcopy but if dump is used do not us it
#[mysqlhotcopy]
#interactive-timeout
#
[3 Feb 2006 12:54] Kristian Koehntopp
Sorry, this is the data source. The marina_m instance is the FEDERATED machine.

[client]
port = 3041
socket = /usr/local/mysql/instances/marina_s/mysql.sock

[mysqld]
#read-only
server-id = 3041
port = 3041
socket = /usr/local/mysql/instances/marina_s/mysql.sock
skip-locking
table_cache = 256
thread_cache = 8

#
# sizing the server (large)
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
#
# values once per server
key_buffer = 16M
innodb_buffer_pool_size = 1500M
innodb_additional_mem_pool_size = 8M
query_cache_size= 128M
# maximum number of concurrent sessions
max_connections = 100
# values used per session
max_allowed_packet = 1M
join_buffer_size = 128K
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K #used for sorting in order by (per session)
# end sizing

#
# sizing the memory: (small)
#
# values once per server

#key_buffer = 16M
#innodb_buffer_pool_size = 150M
#innodb_additional_mem_pool_size = 8M
#query_cache_size= 16M
## number of concurrent sessions
#max_connections = 10
# values per session
#max_allowed_packet = 1M
#join_buffer_size = 128K
#sort_buffer_size = 512K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
# end per session variables
# end sizing

myisam_sort_buffer_size = 64M

#Set character set
default-character-set=utf8
lower_case_table_names = 1
default_table_type = InnoDB
innodb_file_per_table = 1
#

# Try number of CPU's*2 for thread_concurrency
#(InnoDB parameter calculate on the basis of cpu + disk in theory, pratically set to 4 )
thread_concurrency = 4

log_slow_queries = /usr/local/mysql/logs/marina_s/slow.log
log_bin = /usr/local/mysql/logs/marina_s/binlog

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
#no-auto-rehash
auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=utf8

prompt = \u@\h [\d]>\_

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

#Used for hotcopy but if dump is used do not us it
#[mysqlhotcopy]
#interactive-timeout
#
[3 Feb 2006 12:56] Kristian Koehntopp
The customer had the idea to use FEDERATED to use a table on a slave server with MyISAM and FULLTEXT and make it available on the master, where is only available with InnoDB and no FULLTEXT.

Since FEDERATED does not support FULLTEXT, also, this is not viable, and the bug does not affect the customer any more as the customer cannot use FEDERATED anyway unless FEDERATED _also_ supports FULLTEXT.
[9 Feb 2006 15:37] Valeriy Kravchuk
Sorry, but I was not able to repeat the problem you described on 5.0.19-BK on Linux, as soon as

set names utf8;

was performed before select from that FEDERATED table. Please, check. utf8 works for me in FEDERATED.

If SHOW VARIABLES like 'char%' shows me dafaul latin1 for all rows but character_set_system, I also got an incorrect "empty" result, but it is reasonable, isn't it?
[14 Feb 2006 11:21] Kristian Koehntopp
This is on the data source, Linux, 5.0.18-max-log:

$ mysql-3333
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-max-log

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

root@localhost [(none)]> create database boom;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use boom
Database changed
root@localhost [boom]> create table t ( data char(20)) character set utf8 engine=myisam;
Query OK, 0 rows affected (0.01 sec)

root@localhost [boom]> insert into t ( data ) values ("äöüß");
Query OK, 1 row affected (0.00 sec)

root@localhost [boom]> select * from t;
+----------+
| data     |
+----------+
| äöüß     |
+----------+
1 row in set (0.00 sec)

root@localhost [boom]> set names utf8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [boom]> select * from t;
+----------+
| data     |
+----------+
| äöüß     |
+----------+
1 row in set (0.00 sec)

This is on the federated machine, same host, same binary, different port:

$ mysql-3340 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.18-max-log

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

root@localhost [(none)]> create database boom;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use boom
Database changed
root@localhost [boom]> create table t ( data char(20)) character set utf8 engine=federated connection="mysql://root@127.0.0.1:3333/boom/t";
Query OK, 0 rows affected (0.05 sec)

root@localhost [boom]> select * from t;
+------+
| data |
+------+
|      |
+------+
1 row in set (0.00 sec)

root@localhost [boom]> set names utf8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [boom]> select * from t;
+------+
| data |
+------+
|      |
+------+
1 row in set (0.00 sec)

The issue still persists. Please try with 5.0.18 and then check against 5.0.19.
[14 Feb 2006 11:23] Kristian Koehntopp
Changed to open, because i still have the problem. Hope this is not considered rude.
[20 Feb 2006 14:14] Kristian Koehntopp
On  data source (3333):

root@localhost [boom]> select * from t;
+----------+
| data     |
+----------+
| äöüß     |
+----------+
1 row in set (0.00 sec)

root@localhost [boom]> select hex(data) from t;
+------------------+
| hex(data)        |
+------------------+
| C3A4C3B6C3BCC39F |
+------------------+
1 row in set (0.00 sec)

On federated (3340):

root@localhost [boom]> select * from t;
+------+
| data |
+------+
|      |
+------+
1 row in set (0.00 sec)

root@localhost [boom]> select hex(data) from t;
+-----------+
| hex(data) |
+-----------+
|           |
+-----------+
1 row in set (0.00 sec)

root@localhost [boom]> insert into t values ( "ascii");
Query OK, 1 row affected (0.00 sec)

root@localhost [boom]> select hex(data) from t;
+------------+
| hex(data)  |
+------------+
|            |
| 6173636969 |
+------------+
2 rows in set (0.00 sec)
[20 Feb 2006 14:17] Kristian Koehntopp
On FEDERATED;

root@localhost [boom]> insert into t values ( "äöüß");;
Query OK, 1 row affected (0.00 sec)

root@localhost [boom]> select hex(data) from t;
+------------------+
| hex(data)        |
+------------------+
|                  |
| 6173636969       |
| C3A4C3B6C3BCC39F |
+------------------+
3 rows in set (0.00 sec)

root@localhost [boom]> select * from t;
+----------+
| data     |
+----------+
|          |
| ascii    |
| äöüß     |
+----------+
3 rows in set (0.00 sec)

root@localhost [boom]> show variables like "%character_set\_%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | utf8  |
| character_set_connection | utf8  |
| character_set_database   | utf8  |
| character_set_results    | utf8  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+
6 rows in set (0.00 sec)

On data source:
root@localhost [boom]> select hex(data) from t;
+----------------------------------+
| hex(data)                        |
+----------------------------------+
| C3A4C3B6C3BCC39F                 |
| 6173636969                       |
| C383C2A4C383C2B6C383C2BCC383C5B8 |
+----------------------------------+
3 rows in set (0.00 sec)

root@localhost [boom]> select * from t;
+------------------+
| data             |
+------------------+
| äöüß             |
| ascii            |
| äöüß         |
+------------------+
3 rows in set (0.00 sec)

root@localhost [boom]> show variables like "%character_set\_%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | utf8  |
| character_set_connection | utf8  |
| character_set_database   | utf8  |
| character_set_results    | utf8  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+
6 rows in set (0.00 sec)
[20 Feb 2006 14:18] Kristian Koehntopp
On FEDERATED:

root@localhost [boom]> select hex('äöüß');
+------------------+
| hex('äöüß')  |
+------------------+
| C3A4C3B6C3BCC39F |
+------------------+
1 row in set (0.00 sec)

On Source:

root@localhost [boom]> select hex('äöüß');
+------------------+
| hex('äöüß')  |
+------------------+
| C3A4C3B6C3BCC39F |
+------------------+
1 row in set (0.00 sec)
[20 Feb 2006 14:40] Valeriy Kravchuk
Verified just as described on 5.0.18 (MySQL-Max-5.0.18-0.glibc23
+ MySQL-server-5.0.18-0.glibc23 RPMs) on SuSE Linux 9.3. The bug is still present if data source is 5.0.19-BK (ChangeSet@1.2064.1.1, 2006-02-20 09:34:02+01:00). So, looks like it is a problem of FEDERATED engine in 5.0.18.
[12 May 2006 2:19] Patrick Galbraith
This one is a bit confusing. See my experience. Note: the server with no poart specified contains the remote myisam table, the one on port 5554 contains the federated table. Both are DBs created with UTF8:

govinda:/usr/local/mysql # mysql foo

mysql> create table t ( d varchar(4000) not null );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t ( d) values ( "äöüß" );
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------+
| d        |
+----------+
| äöüß |
+----------+
1 row in set (0.00 sec)

mysql> \q

govinda:/usr/local/mysql # mysql -S /tmp/mysqld.5554.sock foo

mysql> create table t ( d varchar(4000) not null ) engine=federated connection="mysql://root@localhost/foo/t";
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t;
+------+
| d    |
+------+
| ��|
+------+
1 row in set (0.00 sec)

mysql> select hex(d) from t;
+------------------+
| hex(d)           |
+------------------+
| C3A4C3B6C3BCC39F |
+------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
govinda:/usr/local/mysql # mysql foo

mysql> select hex(d) from t;
+----------------------------------+
| hex(d)                           |
+----------------------------------+
| C383C2A4C383C2B6C383C2BCC383C5B8 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
govinda:/usr/local/mysql # mysql -S /tmp/mysqld.5554.sock foo
mysql> insert into t ( d) values ( "äöüß" );
Query OK, 1 row affected (0.00 sec)

mysql> select hex(d) from t;
+----------------------------------+
| hex(d)                           |
+----------------------------------+
| C3A4C3B6C3BCC39F                 |
| C383C2A4C383C2B6C383C2BCC383C5B8 |
+----------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t;
+----------+
| d        |
+----------+
| ��    |
| äöüß |
+----------+
2 rows in set (0.00 sec)

mysql> \q
Bye
govinda:/usr/local/mysql # mysql foo
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 9 to server version: 5.0.22-debug-log

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

mysql> select * from t;
+------------------+
| d                |
+------------------+
| äöüß         |
| äöüß |
+------------------+
2 rows in set (0.00 sec)

mysql> select hex(d) from t;
+------------------------------------------------------------------+
| hex(d)                                                           |
+------------------------------------------------------------------+
| C383C2A4C383C2B6C383C2BCC383C5B8                                 |
| C383C692C382C2A4C383C692C382C2B6C383C692C382C2BCC383C692C385C2B8 |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> \q
Bye
govinda:/usr/local/mysql # mysql -S /tmp/mysqld.5554.sock foo

mysql> select hex(d) from t;
+----------------------------------+
| hex(d)                           |
+----------------------------------+
| C3A4C3B6C3BCC39F                 |
| C383C2A4C383C2B6C383C2BCC383C5B8 |
+----------------------------------+
2 rows in set (0.00 sec)

mysql> insert into t values (0xC383C2A4C383C2B6C383C2BCC383C5B8);
Query OK, 1 row affected (0.00 sec)

mysql> select hex(d) from t;
+----------------------------------+
| hex(d)                           |
+----------------------------------+
| C3A4C3B6C3BCC39F                 |
| C383C2A4C383C2B6C383C2BCC383C5B8 |
| C383C2A4C383C2B6C383C2BCC383C5B8 |
+----------------------------------+
3 rows in set (0.02 sec)

mysql> select * from t;
+----------+
| d        |
+----------+
| ��    |
| äöüß |
| äöüß |
+----------+
3 rows in set (0.00 sec)

mysql> \q
Bye
govinda:/usr/local/mysql # mysql foo

mysql> select * from t;
+------------------+
| d                |
+------------------+
| äöüß         |
| äöüß |
| äöüß |
+------------------+
3 rows in set (0.00 sec)

mysql> select hex(d) from t;
+------------------------------------------------------------------+
| hex(d)                                                           |
+------------------------------------------------------------------+
| C383C2A4C383C2B6C383C2BCC383C5B8                                 |
| C383C692C382C2A4C383C692C382C2B6C383C692C382C2BCC383C692C385C2B8 |
| C383C692C382C2A4C383C692C382C2B6C383C692C382C2BCC383C692C385C2B8 |
+------------------------------------------------------------------+
3 rows in set (0.00 sec)

And the log from the remote server:

060511 19:16:34       5 Connect     root@localhost on foo
                      5 Query       show databases
                      5 Query       show tables
060511 19:16:42       5 Query       create table t ( d varchar(4000) not null )
060511 19:16:53       5 Query       insert into t ( d) values ( "äöüß" )
060511 19:16:58       5 Query       select * from t
060511 19:17:02       5 Quit
060511 19:17:51       6 Connect     root@localhost on foo
                      6 Query       SELECT *  FROM `t` WHERE  1=0
                      6 Quit
060511 19:18:02       7 Connect     root@localhost on foo
                      7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:18:48       7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:18:57       8 Connect     root@localhost on foo
                      8 Query       show databases
                      8 Query       show tables
                      8 Field List  t
060511 19:19:00       8 Query       select hex(d) from t
060511 19:19:07       8 Quit
060511 19:19:18       7 Query       INSERT INTO `t`  (d) VALUES  ('äöüß')
060511 19:19:20       7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:19:29       7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:20:39       9 Connect     root@localhost on foo
                      9 Query       show databases
                      9 Query       show tables
                      9 Field List  t
060511 19:20:41       9 Query       select * from t
060511 19:20:48       9 Query       select hex(d) from t
060511 19:20:58       9 Quit
060511 19:21:02       7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:24:38       7 Query       INSERT INTO `t`  (d) VALUES  ('äöüß')
060511 19:24:41       7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:24:48       7 Query       SHOW TABLE STATUS LIKE 't'
                      7 Query       SELECT `d` FROM `t`
060511 19:25:24      10 Connect     root@localhost on foo
                     10 Query       show databases
                     10 Query       show tables
                     10 Field List  t
060511 19:25:26      10 Query       select * from t
060511 19:25:39      10 Query       select hex(d) from t
[12 May 2006 2:26] Patrick Galbraith
In the code:

String insert_field_value_string(insert_field_value_buffer,
                                   sizeof(insert_field_value_buffer),
                                   &my_charset_bin);

then:

if ((*field)->is_null())
        insert_field_value_string.append(FEDERATED_NULL);
      else
      {
        (*field)->val_str(&insert_field_value_string);
        /* quote these fields if they require it */
        (*field)->quote_data(&insert_field_value_string);

So, is "val_str" dealing with UTF8? Does "my_charset_bin" set up the string to deal with UTF8 properly? This is where the values are built for the sql to send to the remote server.
[12 May 2006 17:34] Patrick Galbraith
ok, for me, simply connecting with the client and issuing "set names utf8" worked:

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

mysql> insert into t values ('äöüß');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------+
| d        |
+----------+
| äöüß |
| äöüß |
+----------+
2 rows in set (0.00 sec)

mysql> insert into t values ('מחיקה מרשימת הדיוור אנא לחץ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------------------------------------------------+
| d                                                  |
+----------------------------------------------------+
| äöüß                                           |
| äöüß                                           |
| מחיקה מרשימת הדיוור אנא לחץ |
+----------------------------------------------------+
3 rows in set (0.00 sec)

mysql> insert into t values ('尊敬的新老客户');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------------------------------------------------+
| d                                                  |
+----------------------------------------------------+
| äöüß                                           |
| äöüß                                           |
| מחיקה מרשימת הדיוור אנא לחץ |
| 尊敬的新老客户                              |
+----------------------------------------------------+
4 rows in set (0.00 sec)

mysql> insert into t values ('Международная финансовая');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+----------------------------------------------------+
| d                                                  |
+----------------------------------------------------+
| äöüß                                           |
| äöüß                                           |
| מחיקה מרשימת הדיוור אנא לחץ |
| 尊敬的新老客户                              |
| Международная финансовая    |
+----------------------------------------------------+
5 rows in set (0.01 sec)

mysql> show create table t1;
ERROR 1146 (42S02): Table 'foo.t1' doesn't exist
mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `d` varchar(4000) NOT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root@localhost/foo/t' |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql>               
mysql> \q
Bye
govinda:/usr/local/mysql # mysql foo
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 14 to server version: 5.0.22-debug-log

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

mysql> select * from t;
+----------------------------------------------------+
| d                                                  |
+----------------------------------------------------+
| äöüß                                           |
| äöüß                                           |
| מחיקה מרשימת הדיוור אנא לחץ |
| 尊敬的新老客户                              |
| Международная финансовая    |
+----------------------------------------------------+
5 rows in set (0.00 sec)

mysql>                         

LOG:

                     13 Query       INSERT INTO `t`  (d) VALUES  ('äöüß')
060512 10:25:09      13 Query       SHOW TABLE STATUS LIKE 't'
                     13 Query       SELECT `d` FROM `t`
060512 10:26:02      13 Query       INSERT INTO `t`  (d) VALUES  ('מחיקה מרשימת הדיוור אנא לחץ')
060512 10:26:05      13 Query       SHOW TABLE STATUS LIKE 't'
                     13 Query       SELECT `d` FROM `t`
060512 10:27:03      13 Query       INSERT INTO `t`  (d) VALUES  ('尊敬的新老客户')
060512 10:27:05      13 Query       SHOW TABLE STATUS LIKE 't'
                     13 Query       SELECT `d` FROM `t`
060512 10:28:22      13 Query       INSERT INTO `t`  (d) VALUES  ('Международная финансовая')
060512 10:28:24      13 Query       SHOW TABLE STATUS LIKE 't'
                     13 Query       SELECT `d` FROM `t`
060512 10:29:43      14 Connect     root@localhost on foo
                     14 Query       show databases
                     14 Query       show tables
                     14 Field List  t
060512 10:29:46      14 Query       select * from t

Kristian - Could you please try to do what I did and see if it works for you? Thanks!
[12 May 2006 23:59] Patrick Galbraith
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+----------------------------------------------------+
| d                                                  |
+----------------------------------------------------+
| äöüß                                           |
| äöüß                                           |
| מחיקה מרשימת הדיוור אנא לחץ |
| 尊敬的新老客户                              |
| Международная финансовая    |
+----------------------------------------------------+
5 rows in set (0.01 sec)

mysql> insert into t values ('久しぶりのセックスだったので自分');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+----------------------------------------------------+
| d                                                  |
+----------------------------------------------------+
| äöüß                                           |
| äöüß                                           |
| מחיקה מרשימת הדיוור אנא לחץ |
| 尊敬的新老客户                              |
| Международная финансовая    |
| 久しぶりのセックスだったので自分   |
+----------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.19-debug-log |
+------------------+
1 row in set (0.04 sec)

060512 17:07:35       6 Query       INSERT INTO `t` (d) VALUES  ('久しぶりのセックスだったので自分')
[12 Jun 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Aug 2006 9:28] Geert Vanderkelen
Using 5.0.25-bk I have wrong data when selecting from a federated table using UTF8:

-- MySQL instance 1:
CREATE TABLE `fed1` (
  `vc` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- MySQL instance 2:
CREATE TABLE `fed1` (
  `vc` varchar(20) default NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://feder:@127.0.0.1:3306/test/fed1';

-- Test data (Swedish string inserted):
SET NAMES UTF8;
DELETE FROM fed1;
INSERT INTO fed1 (vc) values ('månad');

-- MySQL instance 1 (from MyISAM)
mysql> SET NAMES UTF8; SELECT vc FROM fed1;
Query OK, 0 rows affected (0.00 sec)

+--------+
| vc     |
+--------+
| månad | 
+--------+
1 row in set (0.00 sec)

-- MySQL instance 2 (from FEDERATED)
mysql> SET NAMES UTF8; SELECT vc FROM fed1;
Query OK, 0 rows affected (0.00 sec)

+------+
| vc   |
+------+
| m    | 
+------+
1 row in set (0.00 sec)

Definitly wrong result.
[29 Sep 2006 21:02] Patrick Galbraith
Patch coming. This is evidence of it being fixed:

patg@govinda:~/mysql-build/mysql-5.0-engines-bug17044> mysql -u root -S /tmp/fed_slave.sock test

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

mysql> create table t1 (name varchar(64) default NULL) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('эффективную');
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye
patg@govinda:~/mysql-build/mysql-5.0-engines-bug17044> mysql -u root -S /tmp/fed.sock test

mysql> create table t1 (name varchar(64) default NULL) ENGINE=FEDERATED CONNECTION="mysql://root@localhost:5556/test/t1" DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

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

mysql> select * from t1;
+------------------------+
| name                   |
+------------------------+
| эффективную |
+------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values ('эффективную');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------------------+
| name                   |
+------------------------+
| эффективную |
| эффективную |
+------------------------+
2 rows in set (0.00 sec)

mysql> insert into t1 values ('må'nad');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------------------+
| name                   |
+------------------------+
| эффективную |
| эффективную |
| månad                 |
+------------------------+
3 rows in set (0.01 sec)

mysql> \q
Bye
patg@govinda:~/mysql-build/mysql-5.0-engines-bug17044> mysql -u root -S /tmp/fed_slave.sock test

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

mysql> select * from t1;
+------------------------+
| name                   |
+------------------------+
| эффективную |
| эффективную |
| månad                 |
+------------------------+
3 rows in set (0.00 sec)

mysql>
[29 Sep 2006 21:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12874

ChangeSet@1.2283, 2006-09-29 17:17:58-04:00, patg@govinda.patg.net +3 -0
  BUG #17044 Federated Storage Engine not UTF8 clean
  
  - Added 'SET NAMES <charset>" upon ::open
  - Added test and results for simple UTF test
[29 Sep 2006 21:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12875

ChangeSet@1.2283, 2006-09-29 16:58:43-04:00, patg@govinda.patg.net +3 -0
  BUG# 17044 Federated Storage Engine not UTF8 clean
  
  - Added 'SET NAMES <charset>" upon ::open
  - Added test and results for simple UTF test
[20 Oct 2006 14:26] [ name withheld ]
same problem as described occurres on windows plattform windows xp professional with mysqld-max-nt
[27 Oct 2006 15:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14495

ChangeSet@1.2283, 2006-10-27 11:50:44-04:00, patg@govinda.patg.net +3 -0
  BUG# 17044 Federated Storage Engine not UTF8 clean
    
    - Added 'SET NAMES <charset>" upon ::open
    - Added test and results for simple UTF test
    - Add explicite collation to loop building select
[31 Oct 2006 11:15] Patrick Galbraith
<antony>	patg: about your cset...
[12:50] 	<antony>	+      if ((*field)->charset()->name && (*field)->charset() != &my_charset_bin)
[12:50] 	<antony>	+      {
[12:50] 	<antony>	+        query.append(' ');
[12:50] 	<antony>	+        query.append('_');
[12:50] 	<antony>	+        query.append((*field)->charset()->name);
[12:52] 	<antony>	patg: shouldn't it be csname and not name?
[13:00] 	<patg>	antony: heh, it works. It puts the correct values for collation in that string.
[13:01] 	<antony>	patg: I think it would work better as:
[13:06] 	<antony>	query.append(STRING_WITH_LEN("CONVERT(")); query.append(FEDERATED_BTICK); query.append((*field)->field_name); query.append(FEDERATED_BTICK); query.append(" USING "); query.append((*field)->charset()->csname); query.append(") "); query.append((int)(field - table->field));
[13:08] 	<antony>	patg: your code there, I do not think has quite the effect you think it does.
[13:08] 	<antony>	patg: also, for mbcs, you will probably want to look at hex encoding them...
[13:09] 	<antony>	multi-byte-character-sets
[13:20] 	<antony>	do you want to try what I suggested above? Also, when you have charset()->mbminlen > 1, you should HEX encode the string.
[13:21] 	<antony>	the function octet2hex already exists for doing the hex encoding.
[13:27] 	<antony>	you may want to omit the last append I put there.
[13:29] 	<patg>	antonY; sure, I would like to try it.
[13:30] 	<patg>	I assume octet2hex usage is easy to figure out by looking at code?
[13:30] 	<antony>	should be obvious.
[13:30] 	<patg>	I would really like to get inserts charset 'safe'
[13:31] 	<antony>	look inside of item_strfunc.cc for use example
[13:31] 	<patg>	so, I could insert hex as well with this?
[13:32] 	<antony>	just wanting to make federated safe even with strings with nul characters in it and ucs2 as well.
[15 Nov 2006 3:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15327

ChangeSet@1.2295, 2006-11-14 22:17:54-05:00, patg@radha.local +3 -0
  BUG# 17044 Federated Storage Engine not UTF8 clean
    
  - Added 'SET NAMES <charset>" upon ::open
  - Added test and results for simple UTF test
  
  ha_federated.cc:
    BUG# 17044 Federated Storage Engine not UTF8 clean
    Upon ::open, set names to table's charset. 
  federated.result:
    BUG# 17044 Federated Storage Engine not UTF8 clean
    New test results
  federated.test:
    BUG #17044 Federated Storage Engine not UTF8 clean
    New test. Using hex - pasting various charsets in the terminal doesn't work.
[15 Nov 2006 3:19] Patrick Galbraith
Brian: I would stick to the SET NAMES and document the other for right now. My reason for this is that performance wise sending and quoting all of the additional sql will be overhead (and I am wondering about character set issues...)

Patrick:so, do you want me to give you a whole new patch and let you officially comment?

Brian: Run it by me, but it will be a nod.
[16 Nov 2006 2:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15391

ChangeSet@1.2295, 2006-11-15 21:01:44-05:00, patg@radha.local +3 -0
  BUG# 17044 Federated Storage Engine not UTF8 clean
  - Added 'SET NAMES <charset>" upon ::open
  - Added test and results for simple UTF test
  
  federated.test:
  BUG #17044 Federated Storage Engine not UTF8 clean
    New test. Using hex - pasting various charsets in the terminal doesn't work.
  federated.result:
  BUG# 17044 Federated Storage Engine not UTF8 clean
    New test results
  ha_federated.cc:
    BUG# 17044 Federated Storage Engine not UTF8 clean
    Upon ::open, set names to table's charset.
[16 Nov 2006 2:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15392

ChangeSet@1.2295, 2006-11-15 21:24:58-05:00, patg@radha.local +3 -0
  BUG# 17044 Federated Storage Engine not UTF8 clean
  - Added 'SET NAMES <charset>" upon ::open
  - Added test and results for simple UTF test
  
  federated.test:
    BUG #17044 Federated Storage Engine not UTF8 clean
    New test. Using hex - pasting various charsets in the terminal doesn't work.
  federated.result:
    BUG# 17044 Federated Storage Engine not UTF8 clean
    New test results
  ha_federated.cc:
    BUG# 17044 Federated Storage Engine not UTF8 clean
    Upon ::open, set names to table's charset
[27 Dec 2006 0:18] Antony Curtis
Pushed to 5.1.15-beta repository
[27 Dec 2006 2:27] Antony Curtis
Pushed to 5.0.34 repository
[3 Jan 2007 11:29] MC Brown
A note has been added to the 5.0.34 and 5.1.15 changelogs.
[7 Jul 2014 7:23] MySQL Verification Team
due to this change, you cannot use utf16 tables with federated engine