CiviCRM Community Forums (archive)

I've been struggling with timeouts while trying to run the deduping system. I've been upping limits as suggested in other forum posts. I usually get past that specific limit only to run into another. First it was PHP, then fast-cgi, now mysql. Small progress but frustrating none-the-less. The database is getting bigger for sure but I can't imagine that we're pushing limits of what CiviCRM can dedupe. Finally I asked our server administrator to take a look and below is what they had to say.

In the end his question was whether the deduping uses mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...) or mysql_options(..., or MYSQL_OPT_WRITE_TIMEOUT,...). Does anyone know this off the top of their head or could point me in the right direction to look through the code?

Thanks,
Colin

I first looked at MySQL timeout settings thinking that maybe MySQL is
dropping the connection after a certain amount of time. Once you log
into MySQL with the root account, you can issue the SHOW VARIABLES command
to see the current value for everything. Here are variables with
"timeout" in the name:

connect_timeout=5
delayed_insert_timeout=300
innodb_lock_wait_timeout=50
interactive_timeout=28800
net_read_timeout=30
net_write_timeout=60
slave_net_timeout=3600
table_lock_wait_timeout=50
wait_timeout=28800

and you can get explanations for these variables at
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

The main one's we would be interested in are interactive_timeout and
wait_timeout, which is how long MySQL waits on an idle connection before
terminating it. As you can see, both are set to 28800 (8 hours), so
that's not the problem.

I tried monitoring the process server-side with the SHOW PROCESSLIST
command and it was in a Sleep state for roughly 1500 seconds before
terminating. Memory usage for the php-fcgi process grew quite a bit
above average to 550 MB (size)/330 MB (resident) (or more) according to
`top`.

This page has a great explanation and tons of possible causes for the
"MySQL server has gone away" error:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

I though this in particular was interesting:

"You can get more information about the lost connections by starting
mysqld with the --log-warnings=2 option. This logs some of the
disconnected errors in the hostname.err file."

So I set that variable in MySQL with
mysql> SET log_warnings=2;
and verified that it took with another SHOW VARIABLES command.
Unfortunately, after running the process again, there was nothing added
to the log file.

I thought this was also an interesting item on the same page:
"You got a timeout from the TCP/IP connection on the client side. This
may happen if you have been using the commands: mysql_options(...,
MYSQL_OPT_READ_TIMEOUT,...) or mysql_options(...,
MYSQL_OPT_WRITE_TIMEOUT,...). In this case increasing the timeout may
help solve the problem."

Determining whether or not this is the case would require digging
through the Drupal code.

At this point, I wonder if there is something in the client side of the
MySQL connection that is timing out. Maybe something in a PHP-MySQL
module configuration?

Yeah, we are still on 2.0. Thanks for the info and I'm looking forward to the 2.1 release!