Query execution was interrupted, maximum statement execution time exceeded

[Royal_Jain]

Hi,

I am transferring 850 tables from a MySQL database to a database in Oracle SQL developer. I have created a workflow using In-Database tools and Batch macros. All the schema and sizes of the tables are different. It works for smaller size of tables but with larger tables(5 lakhs+), it gives this error - 

(55) Record #1: Source Extraction: Error SQLExecute: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.11]Query execution was interrupted, maximum statement execution time exceeded

I need to do this by using Alteryx only, can't reduce the table size, need to transfer all 850 tables, and in  Mysql SESSION MAX_EXECUTION_TIME=0

Need help with this urgently. 

[FrederikE]

Hey @Royal_Jain,

You could try to create a batch marco. Then you'd transfer the tables individually. This will certainly reduce the execution time by a lot and you can detect problems in the process much better since you'll get error messages for indivdual tables in case smth. goes wrong. 

[Royal_Jain]

Thanks for the reply @FrederikE, This is a Batch Macro, and getting this error for some tables as you mentioned rest all the getting transferred

[danilang]

Hi @Royal_Jain 

One thing you could look at for the larger tables is to batch your extraction steps.  In iteration 1 get the 1st lakh rows, on the next get the second lakh rows, etc.  This will involve some work on your part since batching the rows will involve some changes to your sql.  You'll probably be able to use the LIMIT and OFFSET parameters in MySQL to get this to work.

SELECT * FROM `Table` LIMIT 100000 OFFSET 0;

Your macro would update the OFFSET parameter using something like Engine.IterationNumber*100000-1

Dan