Greetings,
We use MySQL 3.23.47 running on 4 Pentium III processors machine with 16GB
of RAM.
The site is hosted on 6 web servers using Alteon for load balancing.
If someone had such problem and knows how to fix it, or any ideas are
welcome regarding the following...
Here's what we know about the two problems:
First Scenario: auction_list.pl
-------------------------------
Problem can be duplicated with every run time
Approximate runtime is 20-40 minutes
Its a standard, non-mod_perl script that has no way of clobbering
a handle due to a memory leak, that can be experienced under
mod_perl.
Program is very database intensive with many multi-table joined
SELECT queries.
All SELECT/INSERT/UPDATE statements occur over one DB handle.
The two handles that fail are prepared at the beginning of
runtime:
my $sth_update = $LIQ::DB->prepare("
UPDATE auction_list SET
auction_count = ?,
auction_data = ?,
last_update_time = NOW()
WHERE
owner_id = ?
AND language_code = ?
");
my $sth_insert = $LIQ::DB->prepare("
INSERT INTO auction_list (
auction_count,
auction_data,
owner_id,
language_code,
last_update_time
) VALUES (
?, ?, ?, ?, NOW()
)
");
mysql> desc auction_list;
+------------------+-------------+------+-----+---------------------+-------
+
| Field | Type | Null | Key | Default | Extra
| |
+------------------+-------------+------+-----+---------------------+-------
+
| owner_id | varchar(32) | | PRI | |
| | language_code | char(2) | | PRI | |
| | auction_count | int(11) | | | 0 |
| | auction_data | mediumblob | | | |
| | last_update_time | datetime | | | 0000-00-00 00:00:00 |
| |
+------------------+-------------+------+-----+---------------------+-------
+ 5 rows in set (0.00 sec)
Program runs for about 20 minutes, then breaks on both queries
sequentially (i.e., if an update fails, it performs an insert),
both fail.
Statement from handle is the following:
MySQL server has gone away
Loop continues and all other subsequent rows are written correctly.
This error occurs on one specific row update/insertion and then
continues. The row this breaks on _can_ change. I ran this script
again, commented out the lengthy part of the initial run time,
and half way through, it broke on a different row than usually
expected, and then merrily continued. However, during a run time,
usually the same row breaks from one run time to the next. I
managed to change the row it breaks on by cutting the amount of
SELECTS it initially does in half.
Neither database handle, nor prepared statement is clobbered,
because subsequent rows are written.
One of the datums inserted with each statement is always a blob.
I have included a Dumper of the bind variables passed to the
statement handles in error.txt.gz, please note it is rather large.
Second scenario: various CGIs, diff machines, same time
-------------------------------------------------------
Run time breakage of following programs:
auction/bid
auction/list
auction/search
During normal execution. Software runs correctly for hours, then
the following message is seen in debug emails:
Lost connection to MySQL server during query
Usually this error is accompanied by multiple CGIs experiencing the
error at the same time (i.e., seconds, or concurrently between each
other). The error then simply goes away and processing returns to
normal.
This error can be seen with any type of query, UPDATE/INSERT/DELETE/
SELECT maybe once or twice a day, from various mod_perl CGIs, at the
same, or close time, for 2-5 separate requests, and goes away. I've
attached example debug output emails with this message.
This message can be encountered on any of our machines, and usually
occurs at the same time from various machines in the loop. It then
simply fixes itself.
My concern is that this behavior has occurring in our bid CGI. The
others are not as important. But I'm scared to see what kind of effect
this may be having on our data.
Thanks.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
