| Bug #28269 | FEDERATED engine fails to quote reserved words for field names | ||
|---|---|---|---|
| Submitted: | 6 May 2007 17:12 | Modified: | 15 Apr 2008 1:52 |
| Reporter: | Harrison Fisk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Federated storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.17 | OS: | Any |
| Assigned to: | Chad MILLER | CPU Architecture: | Any |
| Tags: | bfsm_2007_05_31 | ||
[6 May 2007 17:12]
Harrison Fisk
[6 May 2007 17:13]
Harrison Fisk
Patch to fix the bug
Attachment: fed_fields.patch (application/octet-stream, text), 1.28 KiB.
[14 Jun 2007 22:06]
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/28811 ChangeSet@1.2489, 2007-06-14 18:06:04-04:00, cmiller@zippy.cornsilk.net +3 -0 Bug#28269: FEDERATED engine fails to quote reserved words for \ field names When you use a federated table with a column name that is a reserved word, the FEDERATED storage engine will fail to quote it properly when sending remote SQL which will cause a syntax error and hence cause an error for FEDERATED. Fix it by quoting all names in federated communication.
[3 Jul 2007 17:45]
Chad MILLER
Patch for test:
===== mysql-test/t/federated.test 1.36 vs edited =====
--- 1.36/mysql-test/t/federated.test 2006-12-01 17:01:28 -05:00
+++ edited/mysql-test/t/federated.test 2007-07-03 13:42:23 -04:00
@@ -6,6 +6,8 @@ connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
+ `group` int NOT NULL default 0,
+ `a\\b` int NOT NULL default 0,
`name` varchar(32) NOT NULL default ''
)
DEFAULT CHARSET=latin1;
@@ -16,6 +18,8 @@ DROP TABLE IF EXISTS federated.t1;
--error 1432
CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
+ `group` int NOT NULL default 0,
+ `a\\b` int NOT NULL default 0,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
@@ -25,6 +29,8 @@ CREATE TABLE federated.t1 (
--error 1432
CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
+ `group` int NOT NULL default 0,
+ `a\\b` int NOT NULL default 0,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
@@ -35,6 +41,8 @@ CREATE TABLE federated.t1 (
--error 1434
eval CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
+ `group` int NOT NULL default 0,
+ `a\\b` int NOT NULL default 0,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
@@ -45,6 +53,8 @@ eval CREATE TABLE federated.t1 (
--error 1429
eval CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
+ `group` int NOT NULL default 0,
+ `a\\b` int NOT NULL default 0,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
@@ -55,6 +65,8 @@ DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
+ `group` int NOT NULL default 0,
+ `a\\b` int NOT NULL default 0,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
@@ -62,6 +74,8 @@ eval CREATE TABLE federated.t1 (
INSERT INTO federated.t1 (id, name) VALUES (1, 'foo');
INSERT INTO federated.t1 (id, name) VALUES (2, 'fee');
+INSERT INTO federated.t1 (id, `group`) VALUES (3, 42);
+INSERT INTO federated.t1 (id, `a\\b`) VALUES (3, 23);
SELECT * FROM federated.t1;
DELETE FROM federated.t1;
[12 Sep 2007 10:55]
Daniel Kinzler
I experienced a simmilar problem, however, quoting the table name ("domain" in my case) with backticks (`) did *not* work, only quoting it with double quotes (") did. I would suggest to look into the cause of this discrepancy. Using the table name "domain" unquoted did not work either, while that was perfectly fine when run directly on the real data.
The error show was: ERROR 1030 (HY000): Got error 1430 from storage engine
As far as I understand the manual, error 1430 should be accompanied by the actuel error message comming from the foreign data source, but no such message was supplied.
The federartion source (read data) was running: mysql Ver 14.13 Distrib 5.1.15-beta-20070124, for pc-solaris2.10 (i386) using readline 5.0
The federation target (federated table) was running: mysql Ver 14.12 Distrib 5.0.41, for pc-solaris2.10 (i386) using EditLine wrappe
The real table uses InnoDB
[6 Mar 2008 1:52]
Patrick Galbraith
This bug is fixed from what I can tell. Why is it not closed?
mysql> create table `domain` (`group` int(3)) ;
Query OK, 0 rows affected (0.05 sec)
mysql> connect localdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 13
Current database: localdb
mysql> create table `domain` (`group` int(3)) engine=federatedx connection='mysql://root@localhost/remotedb/domain';
Query OK, 0 rows affected (0.07 sec)
mysql> insert into `domain` (`group`) values (1), (2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from `domain`;
+-------+
| group |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
mysql> update `domain` set `group`=33 where `group`=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from `domain`;
+-------+
| group |
+-------+
| 1 |
| 2 |
| 33 |
+-------+
3 rows in set (0.00 sec)
mysql> delete from `domain` where `group`=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from `domain`;
+-------+
| group |
+-------+
| 1 |
| 33 |
+-------+
2 rows in set (0.00 sec)
mysql>
This type of code:
append_ident(&insert_string, (*field)->field_name,
strlen((*field)->field_name), ident_quote_char);
Fixed it.
[6 Mar 2008 16:16]
Chad MILLER
Because it's incomplete. Reserved words is not the only problem. Try that test I added earlier.
[6 Mar 2008 19:08]
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/43550 ChangeSet@1.2560, 2008-03-06 14:07:49-05:00, cmiller@zippy.cornsilk.net +2 -0 Bug#28269: FEDERATED engine fails to quote reserved words for \ field names Add a test that proves the bug is fixed. This doesn't add any new server code.
[6 Mar 2008 19:11]
Chad MILLER
My original test failed because "eval" interprets the first level of backslash escaping. This is indeed fixed. Queued to 6.0-build and 5.1-build.
[27 Mar 2008 22:03]
Bugs System
Pushed into 5.1.24-rc
[28 Mar 2008 11:10]
Bugs System
Pushed into 6.0.5-alpha
[15 Apr 2008 1:52]
Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs. The FEDERATED storage engine did not perform identifier quoting for column names that are reserved words when sending statements to the remote server.
