pquery SQL is failing with `ERROR: 2014 - Commands out of sync; you can't run this command now`

[rameshvs02]

pquery SQL is failing with ERROR: 2014 - Commands out of sync; you can't run this command now.

INSERT INTO t1  VALUES(0xAFA4);#ERROR: 2014 - Commands out of sync; you can't run this command now
SELECT MIN(c1) AS value FROM t1  WHERE c1 >= 0;#ERROR: 2014 - Commands out of sync; you can't run this command now
INSERT INTO ti VALUES (9944036836733508078,-6353705187404169189,'R4','OoEhDb3AQMTrXIvno8VVzbj7jXlI7tfIXZnZnzn3bZIOiUrLJnrtd','DsvRF','bKjK3ef7TmWGRYz','K','d',2);#ERROR: 2014 - Commands out of sync; you can't run this command now
select max(x) from (select sum(a) as x FROM t1  group by b) as teeone;#ERROR: 2014 - Commands out of sync; you can't run this command now

SQL file info : https://github.com/Percona-QA/percona-qa/blob/master/pquery/pxc-main-ms-ps-md.sql.tar.xz

[rameshvs02]

@abychko I think mysql server-side error message is printing as ERROR: 2014 - Commands out of sync;.. with pquery.

Sample pquery log

CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);#NOERROR
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;#NOERROR
ALTER TABLE t1 ADD COLUMN c2 INT;#NOERROR
ALTER TABLE t1 ADD COLUMN c3 INT GENERATED ALWAYS AS (c2 + 1) STORED;#NOERROR
CREATE INDEX idx1 ON t1 ((c2 + c3));#ERROR: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(c2 + c3))' at line 1
INSERT INTO t1 (c1,c2) VALUES(1,2),(4,5),(7,8);#NOERROR
TRUNCATE TABLE t1;#NOERROR
INSERT INTO t1 (c1,c2) VALUES(1,2),(4,5),(7,8);#NOERROR
INSERT INTO t1(c2) SELECT (c2) FROM t1;#NOERROR
ANALYZE TABLE t1 UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS;#NOERROR
ANALYZE TABLE t1 UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;#NOERROR
ANALYZE TABLE t1 DROP HISTOGRAM ON c2;#NOERROR
ALTER TABLE t1 DROP INDEX idx1;#ERROR: 2014 - Commands out of sync; you can't run this command now
SELECT c2 INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM t1;#ERROR: 2014 - Commands out of sync; you can't run this command now
LOAD DATA INFILE '/tmp/result.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c2);#ERROR: 2014 - Commands out of sync; you can't run this command now
ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;#ERROR: 2014 - Commands out of sync; you can't run this command now
ALTER TABLE t1 ADD COLUMN c5 INT, ALGORITHM=COPY;#NOERROR
ALTER TABLE t1 ADD COLUMN c6 INT, ALGORITHM=INPLACE;#NOERROR
CHECK TABLE t1 FAST QUICK;#NOERROR
CHECKSUM TABLE t1;#NOERROR
OPTIMIZE TABLE t1;#NOERROR
REPAIR TABLE t1;#NOERROR

Following SQL statements are failed in pquery

ALTER TABLE t1 DROP INDEX idx1;
SELECT c2 INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM t1;]
LOAD DATA INFILE '/tmp/result.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c2);]
ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;

These statements are also failing in mysql client program.

8.0.12>ALTER TABLE t1 DROP INDEX idx1;
ERROR 1091 (42000): Can't DROP 'idx1'; check that column/key exists
8.0.12>SELECT c2 INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM t1;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
8.0.12>LOAD DATA INFILE '/tmp/result.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c2);
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
8.0.12>ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
8.0.12>

It will be great if we can print server-side error messages in pquery sql output.

[abychko]

it looks like issue related to stored procedures. they are returning a set of row sets, so it needs one more loop at top level to handle such complex results