Do you connect/disconnect before/after every insert? -----Original Message----- From: Dallas Engelken [mailto:dallase@;nmgi.com] Sent: Thursday, October 24, 2002 9:52 AM To: [EMAIL PROTECTED] Subject: DBI Connect Fails after 20,000 record inserts/updates
I am trying to parse an email log database with about 900,000 records for records older than 2 days (ie 600,000 records). I make a dbi connection and select all records older than 2 days. It returns the rows. Now I step through each row with a while loop, and if the entry is a virus, i open a new dbi connect and store it in the virus table (update if it's already there). Then I disconnect that dbi call. Continuing on, I take each entry and update the overall_stats table, based on the date the email was received.. So if I receive 100,00 emails on 10-24-2002, then instead of having 100,000 log entries I condense it down to 1 entry in the stats table. Each entry in the overall_stats would be a single day, so that would mean I would have no more than 365 records there a year... month,day,year,total_emails,total_spam,total_virus,total_bytes After updating or inserting into the stats table, I close the dbi call, and process the next email log record. This goes on fine for about 19,000-21,000 records... then all of a sudden it dies.. Output of the perl script is here.... Updating Record 19700 in stats table Update _W32/Klez.H@mm to 1767 Virus table Update _W32/Klez.H@mm to 1768 Virus table Update _W32/Klez.H@mm to 1769 Virus table DBI->connect(logs:mail1:3306) failed: Can't connect to MySQL server on 'mail1' (99) at ./parse_sql line 123 Can't call method "prepare" on an undefined value at ./parse_sql line 124. Cannot connect to database Running this script over and over will produce different results on the record is stops on. line 122 creates the dbi connect, line 123 calls the prepare function on the query, which obviously fails because dbi thinks the database has gone away?? That's what I assume, but I cant figure out how to fix it. As soon as the script dies saying cant connect, I type mysql and get right into the db. I am sth->finish() and dbh->disconnect() on all my queries, except for the first one that pulls all 600,000 rows that i step through, so I dont see that would be a problem. Box is a dual p3, 2gig ram, 72gig SCSI RAID 5. Lookin for ideas here! Thanks for any help. Dallas Engelken NMGI --------------------------------------------------------------------- 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 Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. --------------------------------------------------------------------- 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