Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

DBIx or Catalyst problem: Lost connection to MySQL server during query

by Largo (Novice)
on Jan 16, 2017 at 14:04 UTC ( [id://1179665]=perlquestion: print w/replies, xml ) Need Help??

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

Hi there!

I'm working on a project using the Catalyst framework. The db access is done by DBIx::Class. All works fine until the queries get too complicated and take too long to run.

Therefore I wrote a test script to find out if there is a fix amount of time after which the db crashes and the answer is yes. If a query takes longer than 50 seconds then we get the error msg:

DBI Exception: DBD::mysql::st execute failed: Lost connection to MySQL server during query

For testing I use the query "SELECT SLEEP(n);" which does nothing but to sleep for n seconds and then returns.
Here is my test code:

use encs; my $sto = encs->model("DB::Exset")->new({})->result_source->schema +->storage; printf("start\n"); foreach my $i (49,50,51,52,53,49) { print("wait $i seconds:\n"); my $t1 = time; my $t2 = undef; eval { $sto->dbh_do( sub { my ($storage, $dbh, @cols) = @_; $t2 = time; my $sth = $dbh->prepare("Select sleep($i)") or die $db +h->errstr; $sth->execute or die $sth->errstr; my $data = $sth->fetchrow_hashref; } ); }; if ( $@ ) { printf("ERROR: %s (%d, %d)\n", $@, time - $t1, time - $t2); + } else { printf("Ok: (%d, %d)\n", time - $t1, time - $t2); } } printf("end\n"); exit;
A typical run:
start wait 49 seconds: Ok: (92, 49) wait 50 seconds: Ok: (86, 50) wait 51 seconds: ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::st execute failed: Lost c +onnection to MySQL server during query [for Statement "Select sleep(5 +1)"] at ./script/encs_wetest.pl line 32 (86, 51) wait 52 seconds: ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::st execute failed: Lost c +onnection to MySQL server during query [for Statement "Select sleep(5 +2)"] at ./script/encs_wetest.pl line 32 (51, 51) wait 53 seconds: ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::st execute failed: Lost c +onnection to MySQL server during query [for Statement "Select sleep(5 +3)"] at ./script/encs_wetest.pl line 32 (51, 51) wait 49 seconds: Ok: (49, 49) end

"encs" is my Catalyst class. 50 seconds are running through and 51 seconds are failing. Interestingly the query breaks everey time after 51 seconds, but it's not the mysql server that ends the connection. There ist no error message at the mysql server. If I setup a DBI connection on my own, without Catalyst, then it runs as long as it takes. Therefore I think it's an Catalyst/DBIx issue.

My Catalyst YAML config for db: Model::DB: schema_class: encs::Schema::DB connect_info: dsn: dbi:mysql:encs01_test:encsdb user: uu password: xxxxx options: mysql_connect_timeout: 600 net_read_timeout: 600 net_write_timeout: 600

Does anybody have an idea what is the problem? How I could configure Catalyst to avoid this timeout?

Thx, Lars

Replies are listed 'Best First'.
Re: DBIx or Catalyst problem: Lost connection to MySQL server during query
by huck (Prior) on Jan 17, 2017 at 10:58 UTC

    Via http://search.cpan.org/~michielb/DBD-mysql-4.041/lib/DBD/mysql.pm mysql_read_timeout If your DSN contains the option "mysql_read_timeout=##", the read operation to the server will timeout if it has not been successful after the given number of seconds.

    How this applies to your situation i am not sure, MAYBE

    dsn: dbi:mysql:encs01_test:encsdb:mysql_read_timeout=999

Re: DBIx or Catalyst problem: Lost connection to MySQL server during query
by Anonymous Monk on Jan 16, 2017 at 21:54 UTC
    Idea is grep your DBIx/Catalyst directories for "timeout", then see what the documentation around it says

      No relevant 'timeout' in DBIx or Catalyst to find.

        The issue seems to be related to old versions of ubuntu/mysql/perl where our production servers still running on. A test with the same code and current modules on a ubuntu 16.04 ran without exceptions. Updating of DBI, Catalyst and DBIx did not help on the old server.

        We will now update our production servers to the new ubuntu. Thank you all for helping.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1179665]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (4)
As of 2024-03-29 09:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found