I have a stored procedure that does the following
BEGIN DROP TEMPORARY TABLE IF EXISTS groupdata;
CREATE TEMPORARY TABLE groupdata
select with a bunch of sum and case statements;
select json_arrayagg from the temp table;
end;
the select is as indexed as I can make it and takes about 1 second to execute. each client will call the stored procedure via a jdbc connection pool.
IT runs about a third of the time and then I get
ERROR 2014 (HY000): Commands out of sync; you can’t run this command now
I thought the temp tables were/are/ session specific and should not cause a issue… honestly I dont care if this select statement is acid compliant as it gets rebuilt every time it runs…
Thoughts on how I should address it?
Thanks
Does this problem only happens with Percona XtraDB Cluster (What version BTW) ? Or do you see it with standalone MySQL Server or Percona Server too ?
Peter:
This is 5.7.28-31-57-log Percona XtraDB Cluster
As I was rebuilding an existing application (new tech new fields new servers ect) previously I was building this table with a external perl script and keeping it updated. Thus I can not answer if it happens on a standalone mysql instance or not… Since this timeframe I have re-wrote the statement to use multiple subselects and am no longer having the issue…
**Note what I do think was going on though is the front end has a connection pool to the DB… and each user that hit the web site was executing procedure, I am thinking that somehow the temp table was attempting to be leveraged by multiple people within the second… but that would not explain why when I ran it via the client it would get the error…