Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.15
-
None
-
CentOS release 6.5 (X86_64)
Description
Data including ' (single quote) errors when trying to insert to a CONNECT table.
ERROR 1296 (HY000): Got error 122 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'def',250.00000000000000000000)' at line 1 [INSERT INTO `' from CONNECT
Also data including \ (backslash) can be inserted but deletes the backslash automatically.
Below is the log of a test case.
server1
--------------
|
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
|
--------------
|
CREATE OR REPLACE TABLE table_coupon_varchar (
|
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.05 sec)
|
|
|
--------------
|
INSERT INTO table_coupon_varchar VALUES ("1","abc",250),("2","def",200),("3","ghi",300)
|
--------------
|
|
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
--------------
|
SELECT * FROM table_coupon_varchar
|
--------------
|
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
|
--------------
|
GRANT ALL ON test.* TO 'root'@'10.0.1.33'
|
--------------
|
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
Bye
|
server2
--------------
|
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
|
--------------
|
CREATE OR REPLACE TABLE table_coupon_varchar (
|
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.04 sec)
|
|
|
--------------
|
INSERT INTO table_coupon_varchar VALUES ("4","abc'def",250),("5","abc'def",250),("6","gh\\ij",200),("7","gh\\ij",200)
|
--------------
|
|
|
Query OK, 4 rows affected (0.00 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
--------------
|
SELECT * FROM table_coupon_varchar
|
--------------
|
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 4 | abc'def | 250 |
|
| 5 | abc'def | 250 |
|
| 6 | gh\ij | 200 |
|
| 7 | gh\ij | 200 |
|
+-----------+---------+----------+
|
4 rows in set (0.00 sec)
|
|
|
--------------
|
CREATE OR REPLACE TABLE maria_table_coupon_varchar
|
ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon_varchar' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
|
--------------
|
|
|
Query OK, 0 rows affected (0.04 sec)
|
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, memo, discount FROM table_coupon_varchar WHERE coupon_no = "4"
|
--------------
|
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, REPLACE( memo, "'", "''"), discount FROM table_coupon_varchar WHERE coupon_no = "5"
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
| 5 | abc'def | 250 |
|
+-----------+---------+----------+
|
4 rows in set (0.00 sec)
|
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, memo, discount FROM table_coupon_varchar WHERE coupon_no = "6"
|
--------------
|
|
|
Query OK, 1 row affected (0.01 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
| 5 | abc'def | 250 |
|
| 6 | ghij | 200 |
|
+-----------+---------+----------+
|
5 rows in set (0.00 sec)
|
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, REPLACE( memo, "\\", "\\\\"), discount FROM table_coupon_varchar WHERE coupon_no = "7"
|
--------------
|
|
|
Query OK, 1 row affected (0.00 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
| 5 | abc'def | 250 |
|
| 6 | ghij | 200 |
|
| 7 | gh\ij | 200 |
|
+-----------+---------+----------+
|
6 rows in set (0.00 sec)
|
|
|
Bye
|
I made a test case, it can be used with the below command.
server1
mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test4_script_server1.sql;" > /root/test3_script_server1.log
|
server2
mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test4_script_server2.sql;" > /root/test3_script_server2.log
|