Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.13
-
None
-
CentOS release 6.5 (X86_64)
Description
When I use a connect table to connect to
a MyISAM table on a local server,
the data set at utf-8 charset happens to corrupt.
Is this a bug or can I configure options to fix this?
(FROM SERVER 1)
[root@kc0022 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 19
|
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.00 sec)
|
|
|
MariaDB [(none)]> use t_00_company_1;
|
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 [t_00_company_1]> 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.00 sec)
|
|
|
MariaDB [t_00_company_1]> SELECT * FROM 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.01 sec)
|
(FROM SERVER 2)
[root@kc9001 ~]# mysql -u root
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 13
|
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.00 sec)
|
|
|
MariaDB [(none)]> use test;
|
Database changed
|
|
|
MariaDB [test]> DROP TABLE IF EXISTS maria_table_coupon;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> CREATE TABLE maria_table_coupon ENGINE=CONNECT DEFAULT CHARSET= utf8 table_type=MYSQL dbname=t_00_company_1 tabname=table_coupon option_list='user=root,host=10.0.1.36';
|
Query OK, 0 rows affected (0.05 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)
|
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
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)
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.logON SERVER 2
mysql --user=root -v -v -v --show-warnings --execute="SOURCE /root/test_script_server2.sql;" > /root/test_script_server2.log