| Bug #78611 | timout query after 99,999 seconds | ||
|---|---|---|---|
| Submitted: | 28 Sep 2015 21:27 | Modified: | 2 Nov 2015 13:39 |
| Reporter: | kate W | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[28 Sep 2015 21:27]
kate W
[30 Sep 2015 17:03]
MySQL Verification Team
Hi, In order to check what is a problem that you have, we need to be able to repeat the problem that is troubling you. That means that we must have CREATE statements for the tables involved with all data for those. We also need to have the queries that give you problems. Usually, we can not progress without those. However, in some rare cases, we can spot the problem if you send us EXPLAIN on the queries that make you problems. We would prefer the EXTENDED version of that command. We need the time that was required, plus number of rows returned. If that is not enough, we shall have to ask you to send us the output of the query with optimizer_trace variable turned on.
[30 Sep 2015 17:18]
kate W
I am using MySql Workbench to join large-ish data sets (anywhere from 5 million to 170 million records per table). The joins are simple, but long. So I changed the preferences on the DBMS read time out from 600 to something larger, like 80,000. I ran the first large join and it timed out at 99,999. I googled and saw a 2013 post on the subject and mysql bug exchange. (http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer...) The recommendation in the 2013 post was "open a feature request to suggest either having 0 disable the timeout entirely or allow bigger values." That bug exchange suggested "Setting the "DBMS connection read time out" field to 0 second should correspond to no time out (if this is possible). Also, the field should accept more figures: 7 or 8 figures should be enough." The response was "Bug was fixed in trunk...fixed as of MySQL Workbench 6.0.3 (BETA 2), and here's the change log entry: The "DBMS connection read time out" user preference was limited to 5 characters in length." So, if I set that "DMBS connection read time out" for, say, 1 million, will it work? What does it mean when the Oracle documentation reads, "Set 0 to not check the read time out"? Does it mean that by using "0" my query won't time out even if it takes 4 days to execute? Below is the query that has timed out (the current query attempt has been running for 35 hours CREATE TABLE adrfil_of SELECT adrfil.courtl, adrfil.courtt, adrfil.casnbr, adrfil.relptysid, adrfil.relptyid, adrfil.adrlin1, adrfil.adrlin2, adrfil.city, adrfil.state, adrfil.zipcde, adrfil.adrid, adrfil.rcdchgdat FROM adrfil JOIN casfil_of ON adrfil.courtl = casfil_of.courtl AND adrfil.courtt = casfil_of.courtt AND adrfil.casnbr = casfil_of.casnbr
[30 Sep 2015 17:25]
MySQL Verification Team
Please, use mysql CLI for running that query. Also send us the output from: EXPLAIN EXTENDED SELECT adrfil.courtl, adrfil.courtt, adrfil.casnbr, adrfil.relptysid, adrfil.relptyid, adrfil.adrlin1, adrfil.adrlin2, adrfil.city, adrfil.state, adrfil.zipcde, adrfil.adrid, adrfil.rcdchgdat FROM adrfil JOIN casfil_of ON adrfil.courtl = casfil_of.courtl AND adrfil.courtt = casfil_of.courtt AND adrfil.casnbr = casfil_of.casnbr Also, send us the EXPLAIN EXTENDED on the above SELECT part of the query. Send us also the structure of those tables, adrfil and casfil_of. If that is not enough, you will have to send us the optimizer trace, just as we wrote to you previously. By using mysql CLI, you shall skip Workbench as a factor in this problem.
[30 Sep 2015 21:44]
kate W
Sinisa, when you say Mysql CLI, does that stand for "Mysql Command Line"? EXPLAIN EXTENDED SELECT: I have about a dozen tables with many fields in each table. I'm trying to select only a few fields from each table and join on the master table (casfil_of) that I've already paired down from a much bigger table (casfil). I hoped that would make future commands and queries move more quickly. STRUCTURE I can't get the structure right now because that query is twiddling its thumbs. The ADRFIL is comprised of: adrfil 1 courtt char adrfil 2 courtl char adrfil 3 casnbr char adrfil 4 relptysid char adrfil 5 relptyid int adrfil 6 adrid int adrfil aracde char adrfil phnnbr char adrfil adrlin1 char adrfil adrlin2 char adrfil city char adrfil state char adrfil zipcde char adrfil rcdchgdat date adrfil rcdchgtim time adrfil usrid char adrfil cmdnam char The CASFIL (and CASFIL_OF) table are comprised of: casfil 1 courtt char 1 casfil 2 courtl char 3 casfil 3 casnbr char 10 casfil 4 rcdtyp char 3 casfil 5 relptysid char 3 casfil 6 relptyid int 5 casfil cascls char 2 casfil castyp char 2 casfil cassts char 4 casfil mstcasnbr char 10 casfil mstcastyp char 3 casfil casfildat date 7 casfil casstrdat date 7 casfil rnsdat date 7 casfil origintyp char 2 casfil strins char 4 casfil dacasnbr char 10 casfil prvcourt char 4 casfil prvcasnbr char 10 casfil prvdcn char 4 casfil trmstg char 4 casfil trmtyp char 4 casfil inrcas char 1 casfil exrcas char 1 casfil amtprdfor float 11.2 casfil rcdchgdat date 7 casfil rcdchgtim time 5 casfil secctl01 char 1 casfil secctl02 char 1 casfil secctl03 char 1 casfil secctl04 char 1 casfil secctl05 char 1 casfil secctl06 char 1 casfil secctl07 char 1 casfil secctl08 char 1 casfil secctl09 char 1 casfil secctl010 char 1 casfil cassecreq char 1 casfil usrid char 10 casfil cmdnam char 10 Finally, I'm really new to this, so I really don't understand enough to send you the trace optimizer. But if you could just clarify for me: In the Oracle documentation it reads, "Set 0 to not check the read time out." Does it mean that by using "0," my query won't time out until I kill it?
[2 Oct 2015 13:39]
MySQL Verification Team
Here are some answers ... MySQL CLI = MySQL Command Line Interface ... that is program named `mysql` that is delivered with each server ... Regarding EXPLAIN EXTENDED send it just for the query that takes most of the time. SHOW CREATE .... will work even when the query is running ... but, in a different thread ... Regarding read_timeout, it is not that simple. There are many timeouts involved when running the query ... But, EXPLAIN and SHOW will work very fast ...
[3 Nov 2015 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
