Sup Monks! I have some question about DBI module, but first lets see what we have
minimally reproducible example:
#!/usr/bin/perl use strict; use warnings; use DBI; # Database connection details my $dsn = 'DBI:mysql:database=testdb;host=mysql-test.local.priv;mysql_ +write_timeout=2;mysql_read_timeout=2;mysql_connect_timeout=2'; my $username = 'test_user'; my $password = '12345'; # Connect to the MySQL database my $dbh = DBI->connect($dsn, $username, $password, { mysql_enable_utf8 => 1, }); $dbh->{mysql_auto_reconnect} = 1; # Check if the connection was successful if (!$dbh) { die "Could not connect to database: $DBI::errstr"; } # Sleep for 40 minutes print "Sleeping for 40 minutes...\n"; sleep(40 * 60); # 40 minutes # Execute the query my $sth = $dbh->prepare('SELECT 1 FROM dual'); $sth->execute(); # Fetch and print the result while (my @row = $sth->fetchrow_array) { print "Result: @row\n"; } # Clean up $sth->finish(); $dbh->disconnect(); print "Done.\n";
Environments:
So-o-o-o, all my live i used centos 7 and there, when i try to execute this script, after 30 minutes of idle, maxscale kills the session cuz it should, BUT the script actually doesn't see it, i don't know how to explain, i just didn't get an error, it reconnects automatically and just work like a charm. Let me show you output of strace:1. Debian 12.6 Perl : 5.036000 (x86_64-linux-gnu-thread-multi) OS : linux (4.19.0) DBI : 1.643 DBD::mysql : 4.050 2. CentOS Linux release 7.9.2009 Perl : 5.016003 (x86_64-linux-thread-multi) OS : linux (3.10.0-957.1.3.el7.x86_64) DBI : 1.627 DBD::mysql : 4.023 3. Mysql Maxscale router
as you can see here, we get an error "Connection killed by MaxScale: Timed out by MaxScale", and then it tries to reconnect and repeat the query. BUT in debian it doesn't. So my question is: Why? Why it returns me an error "Connection killed by MaxScale: Timed out by MaxScale" and didn't even try to reconnect how it is on Centos? How can i keep alive connections?strace: Process 204408 attached + + restart_syscall(<... resuming interrupted read ...>) = 0 + + poll([{fd=3, events=POLLIN|POLLPRI}], 1, 0) = 1 ([{fd=3, revents=POLLI +N}]) + read(3, "=\0\0\0\377\207\7#08S01Connection killed b"..., 8192) = 65 + + poll([{fd=3, events=POLLIN|POLLPRI}], 1, 0) = 1 ([{fd=3, revents=POLLI +N}]) + read(3, "", 8192) = 0 + + shutdown(3, SHUT_RDWR) = 0 + + close(3) = 0 + + socket(AF_INET, SOCK_STREAM, IPPROTO_TCP) = 3 + + fcntl(3, F_SETFL, O_RDONLY) = 0 + + fcntl(3, F_GETFL) = 0x2 (flags O_RDWR) + + fcntl(3, F_GETFL) = 0x2 (flags O_RDWR) + + fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0 + + connect(3, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_add +r("192.168.1.1")}, 16) = -1 EINPROGRESS (Operation now in progress) + fcntl(3, F_SETFL, O_RDWR) = 0 + + poll([{fd=3, events=POLLIN|POLLPRI}], 1, 2000) = 1 ([{fd=3, revents=PO +LLIN}]) + getsockopt(3, SOL_SOCKET, SO_ERROR, [0], [4]) = 0 + + setsockopt(3, SOL_SOCKET, SO_RCVTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\ +0\0\0", 16) = 0 + setsockopt(3, SOL_SOCKET, SO_SNDTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\ +0\0\0", 16) = 0 + setsockopt(3, SOL_IP, IP_TOS, [8], 4) = 0 + + setsockopt(3, SOL_TCP, TCP_NODELAY, [1], 4) = 0 + + setsockopt(3, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0 + + setsockopt(3, SOL_SOCKET, SO_RCVTIMEO, "\2\0\0\0\0\0\0\0\0\0\0\0\0\0\0 +\0", 16) = 0 + setsockopt(3, SOL_SOCKET, SO_SNDTIMEO, "\2\0\0\0\0\0\0\0\0\0\0\0\0\0\0 +\0", 16) = 0 + poll([{fd=3, events=POLLIN}], 1, 2000) = 1 ([{fd=3, revents=POLLIN}]) + + read(3, "S\0\0\0\n5.6.38-83.0-log\0\33P\273\31U\37#KDU?"..., 16384) = +87 + write(3, "a\0\0\1\217\242\16\0\0\0\0@!\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0 +\0\0\0"..., 101) = 101 + read(3, "\7\0\0\2\0\0\0\2\0\0\0", 16384) = 11 + + poll([{fd=3, events=POLLIN|POLLPRI}], 1, 0) = 0 (Timeout) + + write(3, "\23\0\0\0\3SELECT 1 FROM dual", 23) = 23 + + read(3, "\1\0\0\1\1\27\0\0\2\3def\0\0\0\0011\0\f?\0\1\0\0\0\10\201\0\0 +\0\0"..., 16384) = 56 + write(1, "Result: 1\n", 10) = 10 + + write(3, "\1\0\0\0\1", 5) = 5 + + shutdown(3, SHUT_RDWR) = 0 + + close(3) = 0 + + write(1, "Done.\n", 6) = 6 ...
In reply to DBI keep alive by Anonymous Monk
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |