[MDEV-7890] Same query cannot be executed using CONNECT Engine - Jira

tramchamploo created issue - 2015-03-31 13:27

Elena Stepanova made changes - 2015-03-31 15:26

Field	Original Value	New Value
Description	CREATE TABLE `forum_main_post` ( `id` bigint(20) NOT NULL, `group_id` int(11) DEFAULT NULL, `user_id` bigint(20) DEFAULT NULL, `title` varchar(300) DEFAULT NULL, `level` int(11) NOT NULL DEFAULT '0' COMMENT '??', `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `type` int(11) DEFAULT '0' COMMENT '1?? 2?? 4??', `order_weight` int(11) DEFAULT '0' COMMENT '????', `beauty` tinyint(4) NOT NULL DEFAULT '0' COMMENT '???????1??????,2????', `score` double DEFAULT '0' COMMENT '排序score', PRIMARY KEY (`id`), KEY `user_id` (`user_id`,`type`), KEY `post_list_idx` (`score`,`group_id`,`type`), KEY `post_list_idx2` (`type`,`score`), KEY `post_list_idx3` (`id`,`type`,`score`), KEY `post_list_idx4` (`id`,`score`,`user_id`,`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `forum_post_maria` ( `id` int(20) NOT NULL, `root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID', `user_id` int(20) NOT NULL, `parent_id` int(20) NOT NULL DEFAULT '0' COMMENT '????????id', `level` int(11) NOT NULL DEFAULT '0' COMMENT '??', `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????', `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???', `create_time` datetime NOT NULL DEFAULT '2014-01-01 00:00:00' COMMENT '????', `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????', `audit_time` datetime DEFAULT NULL COMMENT '????', `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`root_id`), KEY `id` (`id`), KEY `create_time` (`create_time`), KEY `root_id_uid` (`root_id`,`status`,`user_id`), KEY `status` (`status`), KEY `user_id` (`user_id`) ) ENGINE=CONNECT `table_type`=MYSQL `dbname`='medusa' `tabname`='forum_post_%s' `option_list`='user=medusa,host=localhost,password=medusa123,quoted=1' /!50100 PARTITION BY HASH (root_id) PARTITIONS 10 /; CREATE TABLE `forum_post` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `root_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '??ID', `user_id` bigint(20) DEFAULT NULL, `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '????????id', `level` int(11) NOT NULL DEFAULT '0' COMMENT '??', `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????', `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???', `create_time` datetime DEFAULT NULL COMMENT '????', `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????', `audit_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `create_time` (`create_time`), KEY `root_id_uid` (`root_id`,`status`,`user_id`), KEY `status` (`status`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=99314692 DEFAULT CHARSET=utf8 /!50100 PARTITION BY HASH (id) PARTITIONS 100 /; SELECT fmp.id, fmp.user_id, fmp.title, fmp.last_update_time, fmp.type, fmp.order_weight, fmp.beauty, fmp.score FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2 ORDER BY fmp.score DESC LIMIT 0, 1000; When I execute the query, it says 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 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT; But if I change the forum_post_maria to forum_post it works fine.The definitions of two tables are almost the same.	{code:sql} CREATE TABLE `forum_main_post` ( `id` bigint(20) NOT NULL, `group_id` int(11) DEFAULT NULL, `user_id` bigint(20) DEFAULT NULL, `title` varchar(300) DEFAULT NULL, `level` int(11) NOT NULL DEFAULT '0' COMMENT '??', `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `type` int(11) DEFAULT '0' COMMENT '1?? 2?? 4??', `order_weight` int(11) DEFAULT '0' COMMENT '????', `beauty` tinyint(4) NOT NULL DEFAULT '0' COMMENT '???????1??????,2????', `score` double DEFAULT '0' COMMENT '排序score', PRIMARY KEY (`id`), KEY `user_id` (`user_id`,`type`), KEY `post_list_idx` (`score`,`group_id`,`type`), KEY `post_list_idx2` (`type`,`score`), KEY `post_list_idx3` (`id`,`type`,`score`), KEY `post_list_idx4` (`id`,`score`,`user_id`,`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; {code} {code:sql} CREATE TABLE `forum_post_maria` ( `id` int(20) NOT NULL, `root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID', `user_id` int(20) NOT NULL, `parent_id` int(20) NOT NULL DEFAULT '0' COMMENT '????????id', `level` int(11) NOT NULL DEFAULT '0' COMMENT '??', `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????', `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???', `create_time` datetime NOT NULL DEFAULT '2014-01-01 00:00:00' COMMENT '????', `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????', `audit_time` datetime DEFAULT NULL COMMENT '????', `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`root_id`), KEY `id` (`id`), KEY `create_time` (`create_time`), KEY `root_id_uid` (`root_id`,`status`,`user_id`), KEY `status` (`status`), KEY `user_id` (`user_id`) ) ENGINE=CONNECT `table_type`=MYSQL `dbname`='medusa' `tabname`='forum_post_%s' `option_list`='user=medusa,host=localhost,password=medusa123,quoted=1' /!50100 PARTITION BY HASH (root_id) PARTITIONS 10 /; {code} {code:sql} CREATE TABLE `forum_post` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `root_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '??ID', `user_id` bigint(20) DEFAULT NULL, `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '????????id', `level` int(11) NOT NULL DEFAULT '0' COMMENT '??', `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????', `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???', `create_time` datetime DEFAULT NULL COMMENT '????', `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????', `audit_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `create_time` (`create_time`), KEY `root_id_uid` (`root_id`,`status`,`user_id`), KEY `status` (`status`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=99314692 DEFAULT CHARSET=utf8 /!50100 PARTITION BY HASH (id) PARTITIONS 100 /; {code} {code:sql} SELECT fmp.id, fmp.user_id, fmp.title, fmp.last_update_time, fmp.type, fmp.order_weight, fmp.beauty, fmp.score FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2 ORDER BY fmp.score DESC LIMIT 0, 1000; {code} When I execute the query, it says 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 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT; But if I change the forum_post_maria to forum_post it works fine.The definitions of two tables are almost the same.

Elena Stepanova added a comment - 2015-03-31 15:33

Works for me.
Please paste the exact fragment from your client – the query you run and the error you get. Not separately, but all together at once, unabridged.
Thanks.

Elena Stepanova added a comment - 2015-03-31 15:33 Works for me. Please paste the exact fragment from your client – the query you run and the error you get. Not separately, but all together at once, unabridged. Thanks.

Elena Stepanova made changes - 2015-03-31 15:33

Due Date

2015-04-30

tramchamploo added a comment - 2015-04-01 11:01 - edited

Yes, sometimes it works fine. It depends on data. The data set is too big that I can't upload. How can I see error log from connect? I tried to just keep the row where error occur, that is `id` = 6621139. And it works good. Client log has been uploaded

tramchamploo added a comment - 2015-04-01 11:01 - edited Yes, sometimes it works fine. It depends on data. The data set is too big that I can't upload. How can I see error log from connect? I tried to just keep the row where error occur, that is `id` = 6621139. And it works good. Client log has been uploaded

tramchamploo made changes - 2015-04-01 11:16

Description

{code:sql}
CREATE TABLE `forum_main_post` (
  `id` bigint(20) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` int(11) DEFAULT '0' COMMENT '1?? 2?? 4??',
  `order_weight` int(11) DEFAULT '0' COMMENT '????',
  `beauty` tinyint(4) NOT NULL DEFAULT '0' COMMENT '???????1??????,2????',
  `score` double DEFAULT '0' COMMENT '排序score',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`type`),
  KEY `post_list_idx` (`score`,`group_id`,`type`),
  KEY `post_list_idx2` (`type`,`score`),
  KEY `post_list_idx3` (`id`,`type`,`score`),
  KEY `post_list_idx4` (`id`,`score`,`user_id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
{code}
{code:sql}
CREATE TABLE `forum_post_maria` (
  `id` int(20) NOT NULL,
  `root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID',
  `user_id` int(20) NOT NULL,
  `parent_id` int(20) NOT NULL DEFAULT '0' COMMENT '????????id',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????',
  `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???',
  `create_time` datetime NOT NULL DEFAULT '2014-01-01 00:00:00' COMMENT '????',
  `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????',
  `audit_time` datetime DEFAULT NULL COMMENT '????',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`root_id`),
  KEY `id` (`id`),
  KEY `create_time` (`create_time`),
  KEY `root_id_uid` (`root_id`,`status`,`user_id`),
  KEY `status` (`status`),
  KEY `user_id` (`user_id`)
) ENGINE=CONNECT `table_type`=MYSQL `dbname`='medusa' `tabname`='forum_post_%s' `option_list`='user=medusa,host=localhost,password=medusa123,quoted=1'
/*!50100 PARTITION BY HASH (root_id)
PARTITIONS 10 */;
{code}
{code:sql}
CREATE TABLE `forum_post` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `root_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '??ID',
  `user_id` bigint(20) DEFAULT NULL,
  `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '????????id',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????',
  `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???',
  `create_time` datetime DEFAULT NULL COMMENT '????',
  `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????',
  `audit_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `root_id_uid` (`root_id`,`status`,`user_id`),
  KEY `status` (`status`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=99314692 DEFAULT CHARSET=utf8
  /*!50100 PARTITION BY HASH (id)
PARTITIONS 100 */;
{code}
{code:sql}
SELECT
  fmp.id,
  fmp.user_id,
  fmp.title,
  fmp.last_update_time,
  fmp.type,
  fmp.order_weight,
  fmp.beauty,
  fmp.score
FROM forum_main_post fmp
  LEFT JOIN forum_post_maria fp ON fp.id = fmp.id
WHERE fmp.type = 2
ORDER BY fmp.score DESC
LIMIT 0, 1000;
{code}
When I execute the query, it says 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 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT;
But if I change the forum_post_maria to forum_post it works fine.The definitions of two tables are almost the same.

{code:sql}
CREATE TABLE `forum_main_post` (
  `id` bigint(20) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` int(11) DEFAULT '0' COMMENT '1?? 2?? 4??',
  `order_weight` int(11) DEFAULT '0' COMMENT '????',
  `beauty` tinyint(4) NOT NULL DEFAULT '0' COMMENT '???????1??????,2????',
  `score` double DEFAULT '0' COMMENT '排序score',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`type`),
  KEY `post_list_idx` (`score`,`group_id`,`type`),
  KEY `post_list_idx2` (`type`,`score`),
  KEY `post_list_idx3` (`id`,`type`,`score`),
  KEY `post_list_idx4` (`id`,`score`,`user_id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
{code}
{code:sql}
CREATE TABLE `forum_post_maria` (
  `id` int(20) NOT NULL,
  `root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID',
  `user_id` int(20) NOT NULL,
  `parent_id` int(20) NOT NULL DEFAULT '0' COMMENT '????????id',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????',
  `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???',
  `create_time` datetime NOT NULL DEFAULT '2014-01-01 00:00:00' COMMENT '????',
  `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????',
  `audit_time` datetime DEFAULT NULL COMMENT '????',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`root_id`),
  KEY `id` (`id`),
  KEY `create_time` (`create_time`),
  KEY `root_id_uid` (`root_id`,`status`,`user_id`),
  KEY `status` (`status`),
  KEY `user_id` (`user_id`)
) ENGINE=CONNECT `table_type`=MYSQL `dbname`='medusa' `tabname`='forum_post_%s' `option_list`='user=medusa,host=localhost,password=medusa123,quoted=1'
/*!50100 PARTITION BY HASH (root_id)
PARTITIONS 10 */;
{code}
{code:sql}
CREATE TABLE `forum_post` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `root_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '??ID',
  `user_id` bigint(20) DEFAULT NULL,
  `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '????????id',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '??',
  `status` int(11) NOT NULL DEFAULT '1' COMMENT '0?????1:????;2:?????,?????-1??????-2??????-3????;-4 ???;-5 ????',
  `path` varchar(4096) NOT NULL DEFAULT '' COMMENT '???????pid???',
  `create_time` datetime DEFAULT NULL COMMENT '????',
  `auditor` int(11) NOT NULL DEFAULT '0' COMMENT '?????',
  `audit_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `root_id_uid` (`root_id`,`status`,`user_id`),
  KEY `status` (`status`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=99314692 DEFAULT CHARSET=utf8
  /*!50100 PARTITION BY HASH (id)
PARTITIONS 100 */;
{code}
{code:sql}
SELECT
  fmp.id,
  fmp.user_id,
  fmp.title,
  fmp.last_update_time,
  fmp.type,
  fmp.order_weight,
  fmp.beauty,
  fmp.score
FROM forum_main_post fmp
  LEFT JOIN forum_post_maria fp ON fp.id = fmp.id
WHERE fmp.type = 2
ORDER BY fmp.score DESC
LIMIT 0, 1000;
{code}
When I execute the query, it says 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 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT;
But if I change the forum_post_maria to forum_post it works fine.The definitions of two tables are almost the same.

MariaDB [medusa]> SELECT fmp.id, fmp.user_id, fmp.title, fmp.last_update_time, fmp.type, fmp.order_weight, fmp.beauty, fmp.score FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2 ORDER BY fmp.score DESC LIMIT 0, 1000;
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 'WHERE (`id` = 6621139)' at line 1 [SELECT `id` FROM `for' from CONNECT

Elena Stepanova made changes - 2015-04-01 21:33

Due Date

2015-04-30

Elena Stepanova added a comment - 2015-04-01 21:44

Client log has been uploaded

Uploaded where?

Could you please enable general log (a.k.a. query log) on the server and reproduce the problem again?
When it happens, we'll hopefully see the broken query that gets sent.

To enable general log, run

set global general_log=1;

and optionally, also set general_log_file variable to the path where you want the log to be created.

Elena Stepanova added a comment - 2015-04-01 21:44 Client log has been uploaded Uploaded where? Could you please enable general log (a.k.a. query log) on the server and reproduce the problem again? When it happens, we'll hopefully see the broken query that gets sent. To enable general log, run set global general_log=1; and optionally, also set general_log_file variable to the path where you want the log to be created.

Elena Stepanova made changes - 2015-04-01 21:44

Due Date

2015-04-29

tramchamploo added a comment - 2015-04-02 05:59 - edited

150402 10:58:11	13950 Query	SELECT fmp.id, fmp.user_id, fmp.title, fmp.last_update_time, fmp.type, fmp.order_weight, fmp.beauty, fmp.score FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2 ORDER BY fmp.score DESC LIMIT 0, 1000

		13961 Connect	medusa@localhost as anonymous on medusa

		13962 Connect	medusa@localhost as anonymous on medusa

		13963 Connect	medusa@localhost as anonymous on medusa

		13964 Connect	medusa@localhost as anonymous on medusa

		13965 Connect	medusa@localhost as anonymous on medusa

		13966 Connect	medusa@localhost as anonymous on medusa

		13967 Connect	medusa@localhost as anonymous on medusa

		13968 Connect	medusa@localhost as anonymous on medusa

		13969 Connect	medusa@localhost as anonymous on medusa

		13970 Connect	medusa@localhost as anonymous on medusa

		13961 Query	SELECT `id` FROM `forum_post_p0` WHERE (`id` = 12478895)

		13962 Query	SELECT `id` FROM `forum_post_p1` WHERE (`id` = 12478895)

		13963 Query	SELECT `id` FROM `forum_post_p2` WHERE (`id` = 12478895)

		13964 Query	SELECT `id` FROM `forum_post_p3` WHERE (`id` = 12478895)

		13965 Query	SELECT `id` FROM `forum_post_p4` WHERE (`id` = 12478895)

		13966 Query	SELECT `id` FROM `forum_post_p5` WHERE (`id` = 12478895)

		13967 Query	SELECT `id` FROM `forum_post_p6` WHERE (`id` = 12478895)

		13968 Query	SELECT `id` FROM `forum_post_p7` WHERE (`id` = 12478895)

		13969 Query	SELECT `id` FROM `forum_post_p8` WHERE (`id` = 12478895)

		13970 Query	SELECT `id` FROM `forum_post_p9` WHERE (`id` = 12478895)

		13961 Query	SELECT `id` FROM `forum_post_p0` WHERE (`id` = 12478895) WHERE (`id` = 6621139)

		13961 Quit

		13962 Quit

		13964 Quit

		13966 Quit

		13970 Quit

		13969 Quit

		13967 Quit

		13965 Quit

		13963 Quit

		13968 Quit

seem like the last one failed

tramchamploo added a comment - 2015-04-02 05:59 - edited 150402 10:58:11 13950 Query SELECT fmp.id, fmp.user_id, fmp.title, fmp.last_update_time, fmp.type, fmp.order_weight, fmp.beauty, fmp.score FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2 ORDER BY fmp.score DESC LIMIT 0, 1000 13961 Connect medusa@localhost as anonymous on medusa 13962 Connect medusa@localhost as anonymous on medusa 13963 Connect medusa@localhost as anonymous on medusa 13964 Connect medusa@localhost as anonymous on medusa 13965 Connect medusa@localhost as anonymous on medusa 13966 Connect medusa@localhost as anonymous on medusa 13967 Connect medusa@localhost as anonymous on medusa 13968 Connect medusa@localhost as anonymous on medusa 13969 Connect medusa@localhost as anonymous on medusa 13970 Connect medusa@localhost as anonymous on medusa 13961 Query SELECT `id` FROM `forum_post_p0` WHERE (`id` = 12478895) 13962 Query SELECT `id` FROM `forum_post_p1` WHERE (`id` = 12478895) 13963 Query SELECT `id` FROM `forum_post_p2` WHERE (`id` = 12478895) 13964 Query SELECT `id` FROM `forum_post_p3` WHERE (`id` = 12478895) 13965 Query SELECT `id` FROM `forum_post_p4` WHERE (`id` = 12478895) 13966 Query SELECT `id` FROM `forum_post_p5` WHERE (`id` = 12478895) 13967 Query SELECT `id` FROM `forum_post_p6` WHERE (`id` = 12478895) 13968 Query SELECT `id` FROM `forum_post_p7` WHERE (`id` = 12478895) 13969 Query SELECT `id` FROM `forum_post_p8` WHERE (`id` = 12478895) 13970 Query SELECT `id` FROM `forum_post_p9` WHERE (`id` = 12478895) 13961 Query SELECT `id` FROM `forum_post_p0` WHERE (`id` = 12478895) WHERE (`id` = 6621139) 13961 Quit 13962 Quit 13964 Quit 13966 Quit 13970 Quit 13969 Quit 13967 Quit 13965 Quit 13963 Quit 13968 Quit seem like the last one failed

Elena Stepanova made changes - 2015-04-02 15:19

Due Date

2015-04-29

Elena Stepanova added a comment - 2015-04-02 15:33

Thanks! Now I can reproduce it.

Elena Stepanova added a comment - 2015-04-02 15:33 Thanks! Now I can reproduce it.

Elena Stepanova added a comment - 2015-04-02 15:56

Test case
INSTALL SONAME 'ha_connect';

DROP DATABASE IF EXISTS db;
CREATE DATABASE db;
USE db;

GRANT ALL ON db.* TO connect@localhost IDENTIFIED BY 'connect';

CREATE TABLE `forum_main_post` (
`id` bigint(20) NOT NULL,
`type` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `forum_post_p0` (
`id` int(20) NOT NULL,
`root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID',
PRIMARY KEY (`root_id`),
KEY `id` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `forum_post_p1` (
`id` int(20) NOT NULL,
`root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID',
PRIMARY KEY (`root_id`),
KEY `id` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `forum_post_maria` (
`id` int(20) NOT NULL,
`root_id` int(20) NOT NULL DEFAULT '0' COMMENT '??ID',
PRIMARY KEY (`root_id`),
KEY `id` (`id`)
) ENGINE=CONNECT `table_type`=MYSQL `dbname`='db' `tabname`='forum_post_%s' `option_list`='user=connect,host=localhost,password=connect,quoted=1'
/*!50100 PARTITION BY HASH (root_id)
PARTITIONS 2 */;

insert into forum_main_post values (1,2),(2,2);

SELECT * FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2;

It produces two valid queries from Connect, and one with the duplicate WHERE clause:

                   69 Connect   connect@localhost as anonymous on db

                   70 Connect   connect@localhost as anonymous on db

                   69 Query     SELECT `id`, `root_id` FROM `forum_post_p0` WHERE (`id` = 1)

                   70 Query     SELECT `id`, `root_id` FROM `forum_post_p1` WHERE (`id` = 1)

                   69 Query     SELECT `id`, `root_id` FROM `forum_post_p0` WHERE (`id` = 1) WHERE (`id` = 2)

Elena Stepanova added a comment - 2015-04-02 15:56 Test case INSTALL SONAME 'ha_connect' ; DROP DATABASE IF EXISTS db; CREATE DATABASE db; USE db; GRANT ALL ON db.* TO connect @localhost IDENTIFIED BY 'connect' ; CREATE TABLE `forum_main_post` ( `id` bigint (20) NOT NULL , `type` int (11) DEFAULT '0' , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `forum_post_p0` ( `id` int (20) NOT NULL , `root_id` int (20) NOT NULL DEFAULT '0' COMMENT '??ID' , PRIMARY KEY (`root_id`), KEY `id` (`id`) ) ENGINE=InnoDB; CREATE TABLE `forum_post_p1` ( `id` int (20) NOT NULL , `root_id` int (20) NOT NULL DEFAULT '0' COMMENT '??ID' , PRIMARY KEY (`root_id`), KEY `id` (`id`) ) ENGINE=InnoDB; CREATE TABLE `forum_post_maria` ( `id` int (20) NOT NULL , `root_id` int (20) NOT NULL DEFAULT '0' COMMENT '??ID' , PRIMARY KEY (`root_id`), KEY `id` (`id`) ) ENGINE= CONNECT `table_type`=MYSQL `dbname`= 'db' `tabname`= 'forum_post_%s' `option_list`= 'user=connect,host=localhost,password=connect,quoted=1' /*!50100 PARTITION BY HASH (root_id) PARTITIONS 2 */ ; insert into forum_main_post values (1,2),(2,2); SELECT * FROM forum_main_post fmp LEFT JOIN forum_post_maria fp ON fp.id = fmp.id WHERE fmp.type = 2; It produces two valid queries from Connect, and one with the duplicate WHERE clause: 69 Connect connect @localhost as anonymous on db 70 Connect connect @localhost as anonymous on db 69 Query SELECT `id`, `root_id` FROM `forum_post_p0` WHERE (`id` = 1) 70 Query SELECT `id`, `root_id` FROM `forum_post_p1` WHERE (`id` = 1) 69 Query SELECT `id`, `root_id` FROM `forum_post_p0` WHERE (`id` = 1) WHERE (`id` = 2)

Elena Stepanova made changes - 2015-04-02 15:56

Component/s		Storage Engine - Connect [ 10128 ]
Fix Version/s		10.0 [ 16000 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s	10.0.17-galera [ 18701 ]
Assignee		Olivier Bertrand [ bertrandop ]
Labels		verified

tramchamploo added a comment - 2015-04-03 05:59

When will it be fixed?

tramchamploo added a comment - 2015-04-03 05:59 When will it be fixed?

Olivier Bertrand made changes - 2015-04-04 20:33

Fix Version/s		10.0.18 [ 18702 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 60311 ]

MariaDB v3 [ 66720 ]

Sergei Golubchik made changes - 2021-12-06 21:41

Workflow

MariaDB v3 [ 66720 ]

MariaDB v4 [ 148984 ]

MariaDB Server

Same query cannot be executed using CONNECT Engine

Details

Description

Attachments

Activity

People

Dates

Git Integration