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:
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
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:
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
...
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?
