Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Stored Procedure/Temp Table/WSREP error

meyerdermeyerder EntrantCurrent User Role Supporter
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

Answers

  • PeterPeter Percona CEO Percona Moderator Role
    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 ? 
  • meyerdermeyerder Entrant Current User Role Supporter
    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..
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.