|
Hi Olivier,
Could you please take a look – is it a bug, or a known limitation, or is it configurable?
Thanks.
|
|
Did you try with FEDERATED? With what result?
With CONNECT, as I remember, the charset translation is already done when accessing the remote table. Therefore, this might be fixed by removing the DEFAUT CHARSET from the CONNECT table to avoid a double translation.
MariaDB [test]> CREATE TABLE maria_table_coupon ENGINE=CONNECT table_type=MYSQL dbname=t_00_company_1 tabname=table_coupon option_list='user=root,host=10.0.1.36';
|
Let me know if this work or if I must investigate this as a bug.
|
|
Hello Olivier,
I tried the FEDERATED database, it worked.
With CONNECT though, removing the DEFAULT CHARSET option did not solve it.
[root@kc9001 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 10
|
Server version: 10.0.13-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test;
|
Database changed
|
MariaDB [test]> CREATE TABLE `maria2_table_coupon` ( `coupon_no` varchar(10) NOT NULL, `memo` varchar(30) DEFAULT NULL, `discount` double DEFAULT NULL, `type` int(11) DEFAULT NULL, `delegate_coupon_no` varchar(10) DEFAULT NULL ) ENGINE=FEDERATED CONNECTION='mysql://root@10.0.1.36/t_00_company_1/table_coupon';
|
Query OK, 0 rows affected (0.11 sec)
|
|
MariaDB [test]> SELECT * FROM maria2_table_coupon WHERE coupon_no IN( 7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------------------------------+----------+------+--------------------+
|
| 7175 | [ｶﾀﾛｸﾞ][14年10月]DM | 200 | 2 | 7175 |
|
| 7172 | [ﾊｶﾞｷ]国内[14年10月]|ﾌｫﾛｰ1 | 500 | 6 | 7172 |
|
| 7173 | [ﾊｶﾞｷ]国内[14年10月]|ﾌｫﾛｰ2 | 1000 | 4 | 7173 |
|
| 7174 | [ﾊｶﾞｷ]海外[14年10月] | 1000 | 4 | 7174 |
|
+-----------+------------------------------------------------+----------+------+--------------------+
|
4 rows in set, 2248 warnings (0.07 sec)
|
|
MariaDB [test]> DROP TABLE maria_table_coupon;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE maria_table_coupon ENGINE=CONNECT table_type=MYSQL dbname='t_00_company_1' tabname='table_coupon' option_list='user=root,h
|
ost=10.0.1.36';
|
Query OK, 0 rows affected (0.10 sec)
|
|
MariaDB [test]> SELECT * FROM maria_table_coupon WHERE coupon_no IN( 7172,717
|
3,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------+----------+------+--------------------+
|
| 7175 | [?????][14?10?]DM | 200 | 2 | 7175 |
|
| 7172 | [????]??[14?10?]|????1 | 500 | 6 | 7172 |
|
| 7173 | [????]??[14?10?]|????2 | 1000 | 4 | 7173 |
|
| 7174 | [????]??[14?10?] | 1000 | 4 | 7174 |
|
+-----------+------------------------+----------+------+--------------------+
|
4 rows in set (0.02 sec)
|
|
|
It worked for me. However, I think the difference is that your charset global variables are set to UTF8. Removing the DEFAULT CHARSET option from your table definition is not enough because it is taken as UTF8 by default.
To force the CONNECT table not to do a double translation, try specifying
CREATE TABLE maria_table_coupon ENGINE=CONNECT table_type=MYSQL
|
DEFAULT CHARSET=BINARY dbname='t_00_company_1' tabname='dbo_dmi_vCoupon' option_list='user=root,host=10.0.1.36';
|
After I changed the setting of the charset global variables, I did get wrong results; then specifying the default charset of the CONNECT table as BINARY make me getting good results.
|
|
I don't know why but it doesn't seem to work for me.
[root@kc1060 my.cnf.d]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 3
|
Server version: 10.0.15-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> show variables like "%char%";
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | utf8 |
|
| character_set_connection | utf8 |
|
| character_set_database | utf8 |
|
| character_set_filesystem | binary |
|
| character_set_results | utf8 |
|
| character_set_server | utf8 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE maria_table_coupon ENGINE=CONNECT table_type=MYSQL DEFAULT CHARSET=BINARY dbname='t_00_company_1' tabname='table_coupon' option_list='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.06 sec)
|
|
MariaDB [test]> SELECT * FROM maria_table_coupon WHERE coupon_no IN( 7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------+----------+------+--------------------+
|
| 7175 | [?????][14?10?]DM | 200 | 2 | 7175 |
|
| 7172 | [????]??[14?10?]|????1 | 500 | 6 | 7172 |
|
| 7173 | [????]??[14?10?]|????2 | 1000 | 4 | 7173 |
|
| 7174 | [????]??[14?10?] | 1000 | 4 | 7174 |
|
+-----------+------------------------+----------+------+--------------------+
|
4 rows in set (0.01 sec)
|
|
|
Hmmm... that is puzzling, especially because you get exactly the same result while it did make a difference when I have done some testing. Before I do further investigations could you recreate the CONNECT table explicitely specifying the columns (easy to do from SHOW CREATE TABLE) and setting the charset as binary for each column.
Just to verify they are not set to UTF8 by default despite the table setting.
|
|
I recreated the CONNECT setting charset as binary for each coloumn.
I also tried utf8 and default.
[root@kc1060 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 243
|
Server version: 10.0.15-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> SHOW VARIABLES LIKE "CHAR%";
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | utf8 |
|
| character_set_connection | utf8 |
|
| character_set_database | utf8 |
|
| character_set_filesystem | binary |
|
| character_set_results | utf8 |
|
| character_set_server | utf8 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.00 sec)
|
|
MariaDB [test]> DROP TABLE IF EXISTS `maria_table_coupon_binary`;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE `maria_table_coupon_binary`
|
-> (`coupon_no` varchar(10) CHARACTER SET binary NOT NULL
|
-> ,`memo` varchar(30) CHARACTER SET binary DEFAULT NULL
|
-> ,`discount` double DEFAULT NULL
|
-> ,`type` int(11) DEFAULT NULL
|
-> ,`delegate_coupon_no` varchar(10) CHARACTER SET binary DEFAULT NULL)
|
-> ENGINE=CONNECT DEFAULT CHARSET=binary `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='t_00_company_1' `OPTION_LIST`='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.04 sec)
|
|
MariaDB [test]> SELECT * FROM maria_table_coupon_binary WHERE coupon_no IN( 7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------+----------+------+--------------------+
|
| 7175 | [?????][14?10?]DM | 200 | 2 | 7175 |
|
| 7172 | [????]??[14?10?]|????1 | 500 | 6 | 7172 |
|
| 7173 | [????]??[14?10?]|????2 | 1000 | 4 | 7173 |
|
| 7174 | [????]??[14?10?] | 1000 | 4 | 7174 |
|
+-----------+------------------------+----------+------+--------------------+
|
4 rows in set (0.01 sec)
|
|
MariaDB [test]> DROP TABLE IF EXISTS `maria_table_coupon_utf8`;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE `maria_table_coupon_utf8`
|
-> (`coupon_no` varchar(10) CHARACTER SET utf8 NOT NULL
|
-> ,`memo` varchar(30) CHARACTER SET utf8 DEFAULT NULL
|
-> ,`discount` double DEFAULT NULL
|
-> ,`type` int(11) DEFAULT NULL
|
-> ,`delegate_coupon_no` varchar(10) CHARACTER SET utf8 DEFAULT NULL)
|
-> ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='t_00_company_1' `OPTION_LIST`='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.05 sec)
|
|
MariaDB [test]> SELECT * FROM maria_table_coupon_utf8 WHERE coupon_no IN( 7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------+----------+------+--------------------+
|
| 7175 | [?????][14?10?]DM | 200 | 2 | 7175 |
|
| 7172 | [????]??[14?10?]|????1 | 500 | 6 | 7172 |
|
| 7173 | [????]??[14?10?]|????2 | 1000 | 4 | 7173 |
|
| 7174 | [????]??[14?10?] | 1000 | 4 | 7174 |
|
+-----------+------------------------+----------+------+--------------------+
|
4 rows in set (0.01 sec)
|
|
MariaDB [test]> DROP TABLE IF EXISTS `maria_table_coupon_default`;
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE `maria_table_coupon_default`
|
-> (`coupon_no` varchar(10) NOT NULL
|
-> ,`memo` varchar(30) DEFAULT NULL
|
-> ,`discount` double DEFAULT NULL
|
-> ,`type` int(11) DEFAULT NULL
|
-> ,`delegate_coupon_no` varchar(10) DEFAULT NULL)
|
-> ENGINE=CONNECT DEFAULT CHARSET=default `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='t_00_company_1' `OPTION_LIST`='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.05 sec)
|
|
MariaDB [test]> SELECT * FROM maria_table_coupon_default WHERE coupon_no IN( 7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------+----------+------+--------------------+
|
| 7175 | [?????][14?10?]DM | 200 | 2 | 7175 |
|
| 7172 | [????]??[14?10?]|????1 | 500 | 6 | 7172 |
|
| 7173 | [????]??[14?10?]|????2 | 1000 | 4 | 7173 |
|
| 7174 | [????]??[14?10?] | 1000 | 4 | 7174 |
|
+-----------+------------------------+----------+------+--------------------+
|
4 rows in set (0.01 sec)
|
|
|
Looking into the FEDERATEDX code I found these lines that are executed before calling mysql_real_connect:
/*
|
BUG# 17044 Federated Storage Engine is not UTF8 clean
|
Add set names to whatever charset the table is at open
|
of table
|
*/
|
/* this sets the csname like 'set names utf8' */
|
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, get_charsetname());
|
mysql_options(&mysql, MYSQL_OPT_USE_THREAD_SPECIFIC_MEMORY,
|
(char*) &my_true);
|
I shall try to discover what get_charsetname really does (it seems to get the table charset name) and add the functionality in CONNECT. However, not being able to reproduce your bug, I don't know yet whether this will effectively fix it. Indeed, it seems to force the connection charset name to be the table charset name but this is apparently what you are already doing. Let's hope that if it worked for FEDERATED it will work also for CONNECT.
Meanwhile can you test what happen if you set character_set_connection to binary?
|
|
Finally, I did add the mysql options setting FEDERATED is doing to the CONNECT MYSQL type. The tests I made seem good and the UTF8 CONNECT table returns for me good results whether or not the default charset is specified locally.
However, with an UTF8 table, it is now an error to specify the table default charset as BINARY.
Before I can close this issue, I must wait for you to validate that it fixes your problem. Is there a way this could be done before waiting for the next realease? I you work from a source download and are able to recompile it, I can send you the modified source files. Otherwise, I can only compile for Windows or Linux ubuntu but probably not your specific options.
|
|
I'm sorry for the late reply.
Setting character_set_connection to binary did not make a difference.
When will be the next release?
I'm working on a CentOS and I don't know if I can recompile myself.
|
|
I have pushed the fix (doing like FEDERATED) to the MariaDB CONNECT source repository. It will be included in the next release. However, I don't know when it will be released; this does not depend on me. I am almost sure it will fix your problem because the same fix make FEDERATED work for you.
Normal setting the character_set_connection to anything does not work, it is reset to its default when connecting to the remote server. Only the fix done by FEDERATED and now CONNECT can change it.
|
|
Thank you, I will check the next release.
Can you check the below case and see if it is related.
When I connect to a mysql table and configure the connect table as DATA_CHARSET=cp932, the text is displayed correctly.
When I configure the connect table as DATA_CHARSET=utf8, the text corrupts.
(FROM SERVER 3)
[dbuser@server3 ~]$ mysql -u root
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 21579
|
Server version: 5.0.68-enterprise-gpl-log MySQL Enterprise Server (GPL)
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
|
|
mysql [(none)]>show variables like "%char%";
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | cp932 |
|
| character_set_connection | cp932 |
|
| character_set_database | cp932 |
|
| character_set_filesystem | binary |
|
| character_set_results | cp932 |
|
| character_set_server | cp932 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.00 sec)
|
|
mysql [(none)]>use company;
|
Database changed
|
mysql [company]>show create table table_coupon;
|
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| table_coupon | CREATE TABLE `table_coupon` (
|
`coupon_no` varchar(10) NOT NULL default '',
|
`memo` varchar(30) default NULL,
|
`discount` double default NULL,
|
`type` int(11) default NULL,
|
`delegate_coupon_no` varchar(10) default NULL,
|
PRIMARY KEY (`coupon_no`),
|
KEY `delegate_coupon_no` (`delegate_coupon_no`,`coupon_no`),
|
KEY `coupon_no` (`coupon_no`,`delegate_coupon_no`)
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
|
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.01 sec)
|
(FROM SERVER 2)
[root@kc1060 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 723
|
Server version: 10.0.15-MariaDB-log MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> use test;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> DROP TABLE IF EXISTS mysql_table_coupon;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE mysql_table_coupon ENGINE=CONNECT DEFAULT CHARSET=utf8 table_type=MYSQL dbname=company tabname=table_coupon DATA_CHARSET=cp932 option_list='user=root,host=10.0.10.11';
|
Query OK, 0 rows affected (0.07 sec)
|
|
MariaDB [test]> SELECT * FROM mysql_table_coupon WHERE coupon_no IN(7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+------------------------------------------------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+------------------------------------------------+----------+------+--------------------+
|
| 7175 | [ｶﾀﾛｸﾞ][14年10月]DM | 200 | 2 | 7175 |
|
| 7172 | [ﾊｶﾞｷ]国内[14年10月]|ﾌｫﾛｰ1 | 500 | 6 | 7172 |
|
| 7173 | [ﾊｶﾞｷ]国内[14年10月]|ﾌｫﾛｰ2 | 1000 | 4 | 7173 |
|
| 7174 | [ﾊｶﾞｷ]海外[14年10月] | 1000 | 4 | 7174 |
|
+-----------+------------------------------------------------+----------+------+--------------------+
|
4 rows in set (0.05 sec)
|
|
MariaDB [test]> DROP TABLE IF EXISTS mysql_table_coupon;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE mysql_table_coupon ENGINE=CONNECT DEFAULT CHARSET=utf8 table_type=MYSQL dbname=company tabname=table_coupon DATA_CHARSET=utf8 option_list='user=root,host=10.0.10.11';
|
Query OK, 0 rows affected (0.07 sec)
|
|
MariaDB [test]> SELECT * FROM mysql_table_coupon WHERE coupon_no IN(7172,7173,7174,7175,7215,7216,7234,7235,7236);
|
+-----------+--------+----------+------+--------------------+
|
| coupon_no | memo | discount | type | delegate_coupon_no |
|
+-----------+--------+----------+------+--------------------+
|
| 7175 | [ | 200 | 2 | 7175 |
|
| 7172 | [??] | 500 | 6 | 7172 |
|
| 7173 | [??] | 1000 | 4 | 7173 |
|
| 7174 | [??] | 1000 | 4 | 7174 |
|
+-----------+--------+----------+------+--------------------+
|
4 rows in set, 8 warnings (0.00 sec)
|
|
|
It is difficult for me to reproduce this, not having your table data, and not having the required fonts on my system.
I suppose that perhaps your original table is written using cp932. BTW it seems that you found a way to read your remote table without error!
|
|
I tested using the same character sets on Maria but it didn't work.
Some difference between the below two is causing the problem.
Server version: 5.0.68-enterprise-gpl-log MySQL Enterprise Server (GPL)
Server version: 10.0.15-MariaDB-log MariaDB Server
|
|
I made a test case, so you may be able to reproduce the bug.
ON SERVER 1
[root@kc0022 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 88
|
Server version: 10.0.13-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> SHOW VARIABLES LIKE "%CHAR%";
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | utf8 |
|
| character_set_connection | utf8 |
|
| character_set_database | utf8 |
|
| character_set_filesystem | binary |
|
| character_set_results | utf8 |
|
| character_set_server | utf8 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.01 sec)
|
|
MariaDB [(none)]> USE test;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> CREATE TABLE `table_coupon` (
|
-> `coupon_no` varchar(10) NOT NULL DEFAULT '',
|
-> `memo` varchar(30) DEFAULT NULL,
|
-> `discount` double DEFAULT NULL,
|
-> PRIMARY KEY (`coupon_no`)
|
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.13 sec)
|
|
MariaDB [test]> INSERT INTO `table_coupon` VALUES ('1','ＡＢＣ',250),('2','ＤＥ Ｆ',200),('3','ＧＨＩ',300);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> SELECT * FROM `table_coupon`;
|
+-----------+-----------+----------+
|
| coupon_no | memo | discount |
|
+-----------+-----------+----------+
|
| 1 | ＡＢＣ | 250 |
|
| 2 | ＤＥＦ | 200 |
|
| 3 | ＧＨＩ | 300 |
|
+-----------+-----------+----------+
|
3 rows in set (0.03 sec)
|
ON SERVER 2
[root@kc1060 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 7
|
Server version: 10.0.15-MariaDB-log MariaDB Server
|
|
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [(none)]> SHOW VARIABLES LIKE "%CHAR%";
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | utf8 |
|
| character_set_connection | utf8 |
|
| character_set_database | utf8 |
|
| character_set_filesystem | binary |
|
| character_set_results | utf8 |
|
| character_set_server | utf8 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.00 sec)
|
|
MariaDB [(none)]> use test;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
MariaDB [test]> CREATE TABLE `table_coupon` (
|
-> `coupon_no` varchar(10) NOT NULL DEFAULT '',
|
-> `memo` varchar(30) DEFAULT NULL,
|
-> `discount` double DEFAULT NULL
|
-> ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='test' `OPTION_LIST`='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.09 sec)
|
|
MariaDB [test]> SELECT * FROM `table_coupon`;
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ??? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.04 sec)
|
|
MariaDB [test]> DROP TABLE `table_coupon`;
|
ERROR 2006 (HY000): MySQL server has gone away
|
No connection. Trying to reconnect...
|
Connection id: 8
|
Current database: test
|
|
Query OK, 0 rows affected (0.04 sec)
|
|
MariaDB [test]> CREATE TABLE `table_coupon` (
|
-> `coupon_no` varchar(10) NOT NULL DEFAULT '',
|
-> `memo` varchar(30) DEFAULT NULL,
|
-> `discount` double DEFAULT NULL
|
-> ) ENGINE=CONNECT DEFAULT CHARSET=BINARY `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='test' `OPTION_LIST`='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.06 sec)
|
|
MariaDB [test]> SELECT * FROM `table_coupon`;
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ??? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> DROP TABLE `table_coupon`;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE `table_coupon` (
|
-> `coupon_no` varchar(10) NOT NULL DEFAULT '',
|
-> `memo` varchar(30) DEFAULT NULL,
|
-> `discount` double DEFAULT NULL
|
-> ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='test' DATA_CHARSET=utf8 `OPTION_LIST`='user=root,host=10.0.1.36';
|
ERROR 2006 (HY000): MySQL server has gone away
|
No connection. Trying to reconnect...
|
Connection id: 9
|
Current database: test
|
|
Query OK, 0 rows affected (0.07 sec)
|
|
MariaDB [test]> SELECT * FROM `table_coupon`;
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ??? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> DROP TABLE `table_coupon`;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE `table_coupon` (
|
-> `coupon_no` varchar(10) NOT NULL DEFAULT '',
|
-> `memo` varchar(30) DEFAULT NULL,
|
-> `discount` double DEFAULT NULL
|
-> ) ENGINE=CONNECT DEFAULT CHARSET=binary `TABLE_TYPE`='MYSQL' `TABNAME`='table_coupon' `DBNAME`='test' DATA_CHARSET=utf8 `OPTION_LIST`='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.07 sec)
|
|
MariaDB [test]> SELECT * FROM `table_coupon`;
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ??? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.01 sec)
|
|
|
|
I executed your test case without any error. Indeed my version now includes the fix (doing like FEDERATED)
|
|
Is it possible for you to check what happens in the earlier version without the fix?
The object is to check the FEDERATED fix is the specific reason the error did not occur.
It could be because of other reasons such as system environment.
|
|
I made test scripts because utf8 letters might be converted to latin1 when copying and pasting from the website.
Please fix line 15 on test_script_server1.sql.
Please fix line 10, 18, 26, 34 on test_script_server2.sql.
I used the scripts like below.
ON SERVER 1
mysql --user=root -v -v -v --show-warnings --execute="SOURCE /root/test_script_server1.sql;" > /root/test_script_server1.log
|
ON SERVER 2
mysql --user=root -v -v -v --show-warnings --execute="SOURCE /root/test_script_server2.sql;" > /root/test_script_server2.log
|
|
|
I executed your scripts. Even I don't have the font to display the UTF8 characters, I could check that the display is similar on serv1 and server 2.
DEFAULT CHARSET=utf8: Seems correct, the displays are the same.
DEFAULT CHARSET=BIN: Wrong. This should not be used.
DEFAULT CHARSET=latin1: Also wrong. Unknown characters are displayed as ???
The DATA_CHARSET does nothing and is not applicable in this case.
Of course, my version includes the like FEDERATED fix.
Therefore, I will close this case as fixed. Please re-awake it if it not fixed for you when you get the new version.
|
|
I tested on build-7271 as of 2015-01-20.
http://hasky.askmonty.org/archive/pack/10.0/build-7271/kvm-rpm-centos6-amd64/rpms/
I got the following, so unfortunately the bug is not fixed.
test_script_server2.log
--------------
|
SHOW VARIABLES LIKE "%CHAR%"
|
--------------
|
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | utf8 |
|
| character_set_connection | utf8 |
|
| character_set_database | utf8 |
|
| character_set_filesystem | binary |
|
| character_set_results | utf8 |
|
| character_set_server | utf8 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.00 sec)
|
|
--------------
|
CREATE DATABASE IF NOT EXISTS test
|
--------------
|
|
Query OK, 1 row affected, 1 warning (0.00 sec)
|
|
Note (Code 1007): Can't create database 'test'; database exists
|
--------------
|
DROP TABLE IF EXISTS table_coupon
|
--------------
|
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
Note (Code 1051): Unknown table 'test.table_coupon'
|
--------------
|
CREATE TABLE table_coupon (
|
coupon_no varchar(10) NOT NULL DEFAULT '',
|
memo varchar(30) DEFAULT NULL,
|
discount double DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
|
--------------
|
|
Query OK, 0 rows affected (0.06 sec)
|
|
--------------
|
SELECT * FROM table_coupon
|
--------------
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ?? ? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.29 sec)
|
|
--------------
|
DROP TABLE table_coupon
|
--------------
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
--------------
|
CREATE TABLE table_coupon (
|
coupon_no varchar(10) NOT NULL DEFAULT '',
|
memo varchar(30) DEFAULT NULL,
|
discount double DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=BINARY TABLE_TYPE='MYSQL' TABNAME='table_coupon' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
|
--------------
|
|
Query OK, 0 rows affected (0.05 sec)
|
|
--------------
|
SELECT * FROM table_coupon
|
--------------
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ?? ? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.26 sec)
|
|
--------------
|
DROP TABLE table_coupon
|
--------------
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
--------------
|
CREATE TABLE table_coupon (
|
coupon_no varchar(10) NOT NULL DEFAULT '',
|
memo varchar(30) DEFAULT NULL,
|
discount double DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon' DBNAME='test' DATA_CHARSET=utf8 OPTION_LIST='user=root,host=10.0.1.36'
|
--------------
|
|
Query OK, 0 rows affected (0.04 sec)
|
|
--------------
|
SELECT * FROM table_coupon
|
--------------
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ?? ? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.45 sec)
|
|
--------------
|
DROP TABLE table_coupon
|
--------------
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
--------------
|
CREATE TABLE table_coupon (
|
coupon_no varchar(10) NOT NULL DEFAULT '',
|
memo varchar(30) DEFAULT NULL,
|
discount double DEFAULT NULL
|
) ENGINE=CONNECT DEFAULT CHARSET=binary TABLE_TYPE='MYSQL' TABNAME='table_coupon' DBNAME='test' DATA_CHARSET=utf8 OPTION_LIST='user=root,host=10.0.1.36'
|
--------------
|
|
Query OK, 0 rows affected (0.04 sec)
|
|
--------------
|
SELECT * FROM table_coupon
|
--------------
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | ??? | 250 |
|
| 2 | ?? ? | 200 |
|
| 3 | ??? | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.14 sec)
|
|
Bye
|
|
|
Indeed the fixed version is here above indicated as 10.0.16. (not yet released)
|
|
OK, thank you.
|
test_script_server1.log
test_script_server1.sql