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

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0
Fix Version/s: 10.0.18
Component/s: Partitioning, Storage Engine - Connect
Labels:
- verified
Environment:
Debian 7

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.

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

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

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.

Show

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.

Hide

Permalink

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

Show

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

Hide

Permalink

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.

Show

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.

Hide

Permalink

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

Show

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

Hide

Permalink

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

Thanks! Now I can reproduce it.

Show

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

Hide

Permalink

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)

Show

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)

Hide

Permalink

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

When will it be fixed?

Show

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

People

Assignee:

Olivier Bertrand

Reporter:

tramchamploo

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2015-03-31 13:27

Updated:

2015-04-04 20:33

Resolved:

2015-04-04 20:33

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

Agile

View on Board