CODEWITHSUNDEEP
mysql-workbench
Franck Dernoncourt
Franck Dernoncourt

Reputation: 83377

How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench?

UPDATE: the issue is now fixed.

I would like to execute a query that takes more than 99,999 seconds to execute (e.g. SELECT SLEEP(150000);). To change the timeout in MySQL Workbench, we have to go to Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds). However, the DBMS connection read time out field only accept up to 5 figures, and setting the field to 0 is equivalent to the default parameter (600 seconds). If the query takes more time than the timeout, I get the error message: Error Code: 2013. Lost connection to MySQL server during query

Hence my question: is it possible to increase this limit to over 99,999 seconds? I use Windows 7 64-bit Ultimate with MySQL Workbench 5.2.47 CE.

The DBMS connection read time out field: enter image description here

Timeout issue (0 is equivalent to the default parameter (600 seconds)): enter image description here

Upvotes: 8

Views: 28913

Answers (3)

Zsolt Szilagyi
Zsolt Szilagyi

Reputation: 5016

Well, in Europe we consider the comma a decimal-separator. Did you actually mean 100k of seconds? I see in your comments that you are handling 50 GB. Even so, if you need longer than an hour, you missed the Indeces. You must know that they won´t get properly rebuildt in a single query, so if you join on a massive insert, You get the cartesian product of rows scanned - in other words, your query could happen to run for weeks or even months.

Solution:

  1. Fill in basic data, use no joins here.
  2. Alter table to set index.
  3. Run ANALYZE <tablename>
  4. Do everything else.

If you feel that you have trouble following that procedure, prepend your Query with the EXPLAIN keyword and post the results.

(I have a cronjob importing about 80GB every 30 minutes in place - MySQL surely can handle this.)

Upvotes: 1

Franck Dernoncourt
Franck Dernoncourt

Reputation: 83377

This issue is now solved in MySQL Workbench 6.0.3 (2013-07-09): See the bug report and the change log.

Upvotes: 1

Mike Lischke
Mike Lischke

Reputation: 53502

Probably nobody ever thought you'd need such a high timeout, so you are limited to what is settable currently. But open a feature request on http://bugs.mysql.com to suggest either having 0 disable the timeout entirely or allow bigger values.

Upvotes: 6

Related Questions