Excel VBA MYSQL Query execution was interrupted, maximum statement execution time exceeded

Question

I am trying to operate a SQL query using excel VBA

select count(distinct column_name) from db_name.table_name

but, I end up with the following error:

Run-time error '-2147467259 (80004005)' [MySQL] [ODBC 8.00(w) Driver][mysqld-5. 7.21] Query execution was interrupted, maximum statement execution time exceeded

There are 4 million records in the table with 230 columns. I have tried increasing "max_execution_time" value but it does not help. The error comes up in 30 seconds after execution.

How can I get past this timeout issue?

MYSQL version 5.7.21 (WAMP 32 bit)
Excel - Office 365

I don't have MS Access installed, I guess that's what you meant to use right?! — Viktor Tango
– Viktor Tango, Commented Aug 17, 2018 at 12:38
Have come across it for the first time, let me post back what I get - in some time. — Viktor Tango
– Viktor Tango, Commented Aug 17, 2018 at 12:47
What did you set max_execution_time to? And did you restart the MySQL server after changing the global variable? Please show the actual VBA code block including connection and recordset call (not line snippet of SQL). You can set query time on the ODBC call. — Parfait
– Parfait, Commented Aug 17, 2018 at 15:31

F.Lazarescu · Accepted Answer · 2020-06-15 21:07:52Z

1

I've been struggling for while with a similar problem. I see your post is an old post, but who knows, maybe it can still help you or anyone that comes for a solution to this problem. What I did was divide the SELECT process in batches. So I have a column called Unique (which is the primary key and auto-increasing). So in VBA i do a FOR/NEXT loop, kinda like this:

For i= 0 to 10,000,000
Select * from table where unique>= i and unique< i+1,000,000

Then I copy the query to another cell, compiling all the queries next.

Well, it's not the perfect solution, but it worked for me and hope it helps someone!

edited Jun 15, 2020 at 21:07

F.Lazarescu

1,3852 gold badges18 silver badges35 bronze badges

answered Jun 15, 2020 at 14:14

Felippe Mendonca

364 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA MYSQL Query execution was interrupted, maximum statement execution time exceeded

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related