DBD::MySQL timeout during query

rcraig has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: DBD::MySQL timeout during query by tachyon (Chancellor) on Sep 08, 2004 at 23:21 UTC
There are many possible reasons. net_read_timeout and net_write_timeout were added in 3.23.20. The read timeout (presumption query is select) default value is 30 seconds and setting it in the my.cnf file requires a restart (of MySQL) to take effect. It seems you know all this. I also assume that you are on Win32 as you are referring to a .INI file? Although you query works off the command line the CGI environment is different. Webservers will abort connections after a period of inactivity, usually in the range 30-60 seconds. It is possible that your command line login runs the query with a higher priority. Establishing the exact time that it takes to timeout may be useful in tracking down the issue. Printing epoch time stamps before/after the query or the total time in the err msg may well provide a useful hint. Not that it is relevant but on *nix tcp(1) has a connect timeout of 75 seconds exact values often indicate the source. This is a very long time for any query. Are you using appropriate primary keys/unique/indexes? You may benefit from having a look at 'DESCRIBE table_name' and 'EXPLAIN query_goes_here' to see if you can speed it up. This of course will not really solve the underlying issue but is worthwihile regardless. If you post the query you may get some useful optimisation suggestions. cheers tachyon	[reply]
Re^2: DBD::MySQL timeout during query by rcraig (Initiate) on Sep 09, 2004 at 15:08 UTC
Thanks. Yes, the query is a select: `SELECT COUNT(DISTINCT seq) FROM Peptide;` [download] Peptide contains almost 2 millions rows (not that big a deal) EXPLAIN AND DESCRIBE: mysql> explain select count(distinct seq) from Peptide; +---------+------+---------------+------+---------+------+---------+-- +-----+ \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| E +xtra \| +---------+------+---------------+------+---------+------+---------+-- +-----+ \| Peptide \| ALL \| NULL \| NULL \| NULL \| NULL \| 1860342 \| + \| +---------+------+---------------+------+---------+------+---------+-- +-----+ 1 row in set (0.02 sec) mysql> desc Peptide; +--------+---------------------------+------+-----+------------+------ +----------+ \| Field \| Type \| Null \| Key \| Default \| Extra + \| +--------+---------------------------+------+-----+------------+------ +----------+ \| pepid \| int(10) unsigned zerofill \| \| PRI \| NULL \| auto_ +increment \| \| proid \| int(10) unsigned zerofill \| \| MUL \| 0000000000 \| + \| \| seq \| tinytext \| \| MUL \| \| + \| \| mh \| double \| \| \| 0 \| + \| \| expect \| double \| \| \| 0 \| + \| \| start \| int(11) \| \| \| 0 \| + \| \| end \| int(11) \| \| \| 0 \| + \| \| charge \| tinyint(1) unsigned \| YES \| \| NULL \| + \| \| delta \| float \| \| \| 0 \| + \| \| dida \| int(11) \| \| \| 0 \| + \| \| didb \| int(11) \| \| \| 0 \| + \| \| didc \| int(11) \| \| \| 0 \| + \| +--------+---------------------------+------+-----+------------+------ +----------+ 12 rows in set (0.00 sec) [download] The index on seq is `index iseq (seq(20))` The average length of seq is 16 and the max is 113. The number with length over 50 is 2400 or so. I set the net_read/write_timeout to 60 and restarted server, but no difference. Also I am using .ini with the settings from my-huge.cnf. When I say that it times out from the perl script, I am only running it from the command line: `C:\testing\scripts>perl stat_tester.pl Connected DBD::mysql::db do failed: Lost connection to MySQL server during query + at stat_tester.pl line 54.` [download] so I don't think that it could be a web server problem. This message comes after about 30 seconds. All this leads me to believe that its an issue with DBD or DBI. Quite possibly the error message is misleading? I will try upping the index length on the seq field, but still, I don't think it's unreasonable to have a query that takes longer than 30 seconds at some point in the life of ones database. Thanks for the help so far!	[reply] [d/l] [select]
Re^3: DBD::MySQL timeout during query by tantarbobus (Hermit) on Sep 09, 2004 at 21:01 UTC
I doubt that it is a problem with DBI/DBD::mysql, mainly because DBD::mysql is reporting that the server closed the connection. It sounds like somesort of timeout, too large of a packect, commands being sent out of order, or something else that causes the server to go away (segfault is a possibility too). You might want to enable tracing on the serverside, to see why the connection is going away. You can try also the DBD::mysql mailing list	[reply]
Re^3: DBD::MySQL timeout during query by tachyon (Chancellor) on Sep 10, 2004 at 00:18 UTC
Here is the MySQL developers section on this http://dev.mysql.com/doc/mysql/en/Gone_away.html Anyway the query will take a long time as it needs a full table scan*. You must have a slow server/disks as it takes me 1.67 seconds to run a similar query on a million row data set. As noted by astroboy forking can cause this issue, but I suspect it is something to do with your MySQL configuration. Here is what we use with 2GB of RAM. It might be worth a try. The extended timeouts discussed before are not included (never needed them) but should be. There is some suggestion the connect timeout you can set here may be important. `[mysqld] datadir=/var/lib/mysql socket=/tmp/mysql.sock set-variable=wait_timeout=200000 set-variable=key_buffer=256M set-variable=max_allowed_packet=32M set-variable=table_cache=512 set-variable=sort_buffer_size=64M set-variable=record_buffer=64M set-variable=read_buffer_size=32M set-variable=myisam_sort_buffer_size=64M set-variable=thread_cache=8 set-variable=query_cache_size=32M set-variable=tmp_table_size=32M set-variable=max_connections=900` [download] As you are on Win32 it is possible that it is a quirk related to the Win32 ports of Perl/DBI/DBD::mysql/MySQL. If you have a Linux box with MySQL it would be interesting to see if the problem is repeatable on the more native platform. cheers tachyon	[reply] [d/l]
Re^4: DBD::MySQL timeout during query by rcraig (Initiate) on Sep 10, 2004 at 15:57 UTC
Re: DBD::MySQL timeout during query by keszler (Priest) on Sep 08, 2004 at 23:07 UTC
The DBD::MySQL docs state: "mysql_connect_timeout If your DSN contains the option ``mysql_connect_timeout=##'', the connect request to the server will timeout if it has not been successful after the given number of seconds. No default value is listed, but I wouldn't be surprised if it was 30 seconds. Try something like: `$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port;mysql_c +onnect_timeout=60"; $dbh = DBI->connect($dsn, $user, $password);` [download]	[reply] [d/l]
Re^2: DBD::MySQL timeout during query by tachyon (Chancellor) on Sep 09, 2004 at 00:37 UTC
The connection timeout would not appear to be relevant in this case as the error (lost connection to database during query) can only occur if you already have a connection. Once you have a connection this timeout is irrelevant. That DBH connection itself will have a life of 8 hours by default (or until you disconnect) cheers tachyon	[reply]
Re^2: DBD::MySQL timeout during query by rcraig (Initiate) on Sep 09, 2004 at 15:11 UTC
Thanks. I pretty sure it's not actually a connect timeout.I did try this tho. The problem is that is loses the connection during the query, and it was certainly connected becuase it executes four queries before it hits the one that takes "too long". Thanks again.	[reply]
Re: DBD::MySQL timeout during query by astroboy (Chaplain) on Sep 09, 2004 at 08:10 UTC
How about turning on tracing and posting the output?	[reply]
Re^2: DBD::MySQL timeout during query by rcraig (Initiate) on Sep 09, 2004 at 15:27 UTC
OK - here it is: DBI::db=HASH(0x198c768) trace level set to 0x2/0 (DBI @ Ox0/0) in +DBI 1.42-ithread (pid 2800) Note: perl is running without the recommended perl -w option -> prepare for DBD::mysql::db (DBI::db=HASH(0x1960e34)~0x198c768 ' +select count() FROM Result;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198c8dc) at stat_test.pl line 21 -> execute for DBD::mysql::st (DBI::st=HASH(0x198c8dc)~0x188a14c) +thr#15d404c -> dbd_st_execute for 0198cfa8 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 22 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198c8dc)~0x18 +8a14c) thr#15d404c -> dbd_st_fetch for 0198cfa8, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '8227' ) [1 items] row1 at stat_test.pl line +23 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1960e34)~0x198c768 ' +select count() FROM Protein;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198d014) at stat_test.pl line 28 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x188a14c)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 29 -> execute for DBD::mysql::st (DBI::st=HASH(0x198d014)~0x198d044) +thr#15d404c -> dbd_st_execute for 018da194 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 29 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198d014)~0x19 +8d044) thr#15d404c -> dbd_st_fetch for 018da194, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '369276' ) [1 items] row1 at stat_test.pl lin +e 30 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1960e34)~0x198c768 ' +select count(DISTINCT label) FROM ProSeq;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198d0ec) at stat_test.pl line 36 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198d044)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 37 -> execute for DBD::mysql::st (DBI::st=HASH(0x198d0ec)~0x198d11c) +thr#15d404c -> dbd_st_execute for 0198d038 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 37 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198d0ec)~0x19 +8d11c) thr#15d404c -> dbd_st_fetch for 0198d038, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '51909' ) [1 items] row1 at stat_test.pl line + 38 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1960e34)~0x198c768 ' +select count() FROM Peptide;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198cff0) at stat_test.pl line 46 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198d11c)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 47 -> execute for DBD::mysql::st (DBI::st=HASH(0x198cff0)~0x198c8e8) +thr#15d404c -> dbd_st_execute for 0198d110 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 47 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198cff0)~0x19 +8c8e8) thr#15d404c -> dbd_st_fetch for 0198d110, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '1860342' ) [1 items] row1 at stat_test.pl li +ne 48 -> execute for DBD::mysql::st (DBI::st=HASH(0x198cff0)~0x198c8e8) +thr#15d404c -> dbd_st_execute for 0198d110 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 54 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198cff0)~0x19 +8c8e8) thr#15d404c -> dbd_st_fetch for 0198d110, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '1860342' ) [1 items] row1 at stat_test.pl li +ne 55 -> finish for DBD::mysql::st (DBI::st=HASH(0x198cff0)~0x198c8e8) t +hr#15d404c <- finish= 1 at stat_test.pl line 57 -> disconnect for DBD::mysql::db (DBI::db=HASH(0x1960e34)~0x198c76 +8) thr#15d404c &imp_dbh->mysql: 198cc94 <- disconnect= 1 at stat_test.pl line 76 ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198c8e8)~INNER) thr# +15d404c ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x198c768)~INNER) thr# +15d404c ! <- DESTROY= undef during global destruction DBI::db=HASH(0x198c1a0) trace level set to 0x2/0 (DBI @ Ox0/0) in +DBI 1.42-ithread (pid 1840) Note: perl is running without the recommended perl -w option -> prepare for DBD::mysql::db (DBI::db=HASH(0x1961304)~0x198c1a0 ' +select count() FROM Result;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198ce90) at stat_test.pl line 21 -> execute for DBD::mysql::st (DBI::st=HASH(0x198ce90)~0x188a37c) +thr#15d404c -> dbd_st_execute for 0198cf38 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 22 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198ce90)~0x18 +8a37c) thr#15d404c -> dbd_st_fetch for 0198cf38, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '8227' ) [1 items] row1 at stat_test.pl line +23 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1961304)~0x198c1a0 ' +select count() FROM Protein;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198cfa4) at stat_test.pl line 28 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x188a37c)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 29 -> execute for DBD::mysql::st (DBI::st=HASH(0x198cfa4)~0x198cfd4) +thr#15d404c -> dbd_st_execute for 018da15c <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 29 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198cfa4)~0x19 +8cfd4) thr#15d404c -> dbd_st_fetch for 018da15c, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '369276' ) [1 items] row1 at stat_test.pl lin +e 30 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1961304)~0x198c1a0 ' +select count(DISTINCT label) FROM ProSeq;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198d07c) at stat_test.pl line 36 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198cfd4)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 37 -> execute for DBD::mysql::st (DBI::st=HASH(0x198d07c)~0x198d0ac) +thr#15d404c -> dbd_st_execute for 0198cfc8 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 37 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198d07c)~0x19 +8d0ac) thr#15d404c -> dbd_st_fetch for 0198cfc8, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '51909' ) [1 items] row1 at stat_test.pl line + 38 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1961304)~0x198c1a0 ' +select count() FROM Peptide;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198cf80) at stat_test.pl line 46 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198d0ac)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 47 -> execute for DBD::mysql::st (DBI::st=HASH(0x198cf80)~0x198ce9c) +thr#15d404c -> dbd_st_execute for 0198d0a0 <- dbd_st_execute 1 rows <- execute= 1 at stat_test.pl line 47 -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x198cf80)~0x19 +8ce9c) thr#15d404c -> dbd_st_fetch for 0198d0a0, chopblanks 0 <- dbd_st_fetch, 1 cols <- fetchrow_array= ( '1860342' ) [1 items] row1 at stat_test.pl li +ne 48 -> prepare for DBD::mysql::db (DBI::db=HASH(0x1961304)~0x198c1a0 ' +select count(DISTINCT seq) FROM Peptide;') thr#15d404c Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x198d058) at stat_test.pl line 54 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198ce9c)~INNER) thr# +15d404c <- DESTROY= undef at stat_test.pl line 55 -> execute for DBD::mysql::st (DBI::st=HASH(0x198d058)~0x198cfb0) +thr#15d404c -> dbd_st_execute for 0198d10c Lost connection to MySQL server during query error 2013 recorded: Lost + connection to MySQL server during query <- dbd_st_execute -2 rows !! ERROR: 2013 'Lost connection to MySQL server during query' (err +#0) <- execute= undef at stat_test.pl line 55 ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x198cfb0)~INNER) thr# +15d404c ERROR: 2013 'Lost connection to MySQL server during query' (err +#0) ! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x198c1a0)~INNER) thr# +15d404c &imp_dbh->mysql: 198cc04 ERROR: 2013 'Lost connection to MySQL server during query' (err +#0) ! <- DESTROY= undef during global destruction [download] Hmmmmm. Does this help? I guess I need to eat more sushi. Thanks.	[reply] [d/l]
Re^3: DBD::MySQL timeout during query by astroboy (Chaplain) on Sep 09, 2004 at 17:00 UTC
A quick search through the DBI lists ... Try here or here. One of these posts may help - the first one may be a likely candidate	[reply]
Re: DBD::MySQL timeout during query by Juerd (Abbot) on Sep 09, 2004 at 06:43 UTC
It's DBD::mysql, not DBD::MySQL. Module names are case sensitive.	[reply]
Re^2: DBD::MySQL timeout during query by rcraig (Initiate) on Sep 09, 2004 at 19:16 UTC
OK - so what's your point. In this context it obviously doesn't matter. If I am having a problem with can't find DBD::MySQL, then let's talk, otherwise .... RC	[reply]
Re^3: DBD::MySQL timeout during query by Juerd (Abbot) on Sep 09, 2004 at 20:04 UTC
what's your point. My point is that it is DBD::mysql, not DBD::MySQL. Nothing more, nothing less. otherwise .... I'm curious. How does this sentence end? Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }	[reply]
Re^4: DBD::MySQL timeout during query by rcraig (Initiate) on Sep 09, 2004 at 21:16 UTC


good chemistry is complicated, and a little bit messy -LW
	PerlMonks