[MDEV-7890] Same query cannot be executed using CONNECT Engine Created: 2015-03-31  Updated: 2015-04-04  Resolved: 2015-04-04

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Storage Engine - Connect
Affects Version/s: 10.0
Fix Version/s: 10.0.18

Type: Bug Priority: Major
Reporter: tramchamploo Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
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

 Comments   
Comment by Elena Stepanova [ 2015-03-31 ]

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.

Comment by tramchamploo [ 2015-04-01 ]

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

Comment by Elena Stepanova [ 2015-04-01 ]

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.

Comment by tramchamploo [ 2015-04-02 ] 

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

Comment by Elena Stepanova [ 2015-04-02 ]

Thanks! Now I can reproduce it.

Comment by Elena Stepanova [ 2015-04-02 ]

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)

Comment by tramchamploo [ 2015-04-03 ]

When will it be fixed?

Generated at Thu Feb 08 07:23:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.