MySQL Forums
Forum List  »  Stored Procedures

Stored procedure runs indefinitely, but the steps within can be run individually with no issue (5.7)
Posted by: Eric Jones
Date: February 15, 2019 01:33PM

I have a small database I'm using for a Power BI project. For the sake of simplicity, let's say that I have separate procedures to load my dimensions and facts called LoadDimensions and LoadFacts. LoadDimensions is a sequence of calls to load 19 dimension tables, and LoadFacts is the same but with 5 fact tables.

If I run the following...

CALL LoadDimensions();
CALL LoadFacts();

...the dimensions complete in about 40 seconds, and the facts complete in about 270 seconds, so everything together runs in a little over 5 minutes.

I created one master procedure to load everything called LoadAllTables. This procedure is exactly what you see above; it calls the dimensions procedure followed by the facts procedure. If I call LoadAllTables in Workbench, however, it runs indefinitely until I cancel it.

What is causing such a basic procedure--one whose individual steps can be executed with absolutely no issues--to hang up indefinitely?

I will say that the 5th fact table in LoadFacts was recently added, and before that was done, LoadFacts would run in around 130 seconds. The 5th fact table pushed the entire process just over the 5-minute mark, so I thought there might be something to that, but I've disabled the read timeout in Workbench, so I don' think that's it. Aside from that, the procedure to load this 5th fact table completes on its own with no issues, and again, LoadFacts by itself completes with no issues.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.