[MDEV-7343] corrupted text using connect - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.13
Fix Version/s: 10.0.16
Component/s: Storage Engine - Connect
Labels:
None
Environment:
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)

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

test_script_server1.log
2 kB
2015-01-20 05:20
test_script_server1.sql
0.5 kB
2015-01-20 05:20
test_script_server2.log
4 kB
2015-01-20 05:20
test_script_server2.sql
1 kB
2015-01-20 05:20

Issue Links

relates to

MDEV-7375 FEDERATED + DISCOVERY can make UTF8 columns to be corrupted

Open

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-12-18 23:44

Hi Olivier,

Could you please take a look – is it a bug, or a known limitation, or is it configurable?

Thanks.

Show

Elena Stepanova added a comment - 2014-12-18 23:44 Hi Olivier, Could you please take a look – is it a bug, or a known limitation, or is it configurable? Thanks.

16 older comments

Hide

Permalink

Takuya Aoki added a comment - 2015-01-20 05:32

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

Show

Takuya Aoki added a comment - 2015-01-20 05:32 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

Hide

Permalink

Olivier Bertrand added a comment - 2015-01-20 15:23

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.

Show

Olivier Bertrand added a comment - 2015-01-20 15:23 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.

Hide

Permalink

Takuya Aoki added a comment - 2015-01-22 10:09

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

Show

Takuya Aoki added a comment - 2015-01-22 10:09 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

Hide

Permalink

Olivier Bertrand added a comment - 2015-01-22 12:07

Indeed the fixed version is here above indicated as 10.0.16. (not yet released)

Show

Olivier Bertrand added a comment - 2015-01-22 12:07 Indeed the fixed version is here above indicated as 10.0.16. (not yet released)

Hide

Permalink

Takuya Aoki added a comment - 2015-01-22 12:12

OK, thank you.

Show

Takuya Aoki added a comment - 2015-01-22 12:12 OK, thank you.

corrupted text using connect

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Time Tracking

Agile