Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

DBI seems to randomly die

by FitTrend (Pilgrim)
on Jun 06, 2006 at 18:53 UTC ( #553890=perlquestion: print w/replies, xml ) Need Help??
FitTrend has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

Before posting this I've made some effort searching the web and archives for something related to the issue I'm having. This issue is very random. Some times, the user can click link after link after link (web based app) and it works fine. Other times, a few clicks in it fails with no errors from Perl, apache or the mysql log.

To summarize what happens behind the scenes every time a user clicks a link:

  • A scan of available tables takes place (this determine which tables I need for creating a merge table based on what the user is asking for)
  • A drop table of the old merge tables (3 of them) is issued (this is where it randomly dies some times)
  • A merge table is created from the scan list (in the first step) and indexes are applied. It is unique for each user
  • A Temporary table is created to narrow down the records needed (could be 100k+ records)
  • The application queries the results from the temporary table, displays it, and terminates normally

This is the process that is repeated for every user request. It is done this way because the merge table COULD span multiple databases and tables. It all depends on the user. What is frustrating is no errors are generated and it always fails on the first drop table when it occurs (we drop a total of 3 tables).

I've done some testing with just MySQL and cannot reproduce the drop or premature connection termination behavior I believe I'm seeing. I've tested with MySQL 4.1.10a (and the latest), Apache 2.0.52 (and the latest), DBI 1.49 and 1.50, and Perl 5.8.7 and 5.8.8. This is all from a windows system.

Has anyone heard of this type of behavior before? Can any one give me some suggestions on how I should go about resolving my issue? I'm sure I'm missing something.

I've attached the sub I use to create the merge tables using DBI. code here

I appreciate your consideration.


Replies are listed 'Best First'.
Re: DBI seems to randomly die
by dsheroh (Prior) on Jun 06, 2006 at 19:42 UTC
    My gut says that this sounds like a race condition, but you seem to be taking reasonable precautions, such as checking the table exists before dropping it and so forth. I spotted one problem in a quick look over the linked code, which may or may not be connected to this:

    Using ${Database}.(conv|pt|a)_${RemoteAddr} for your temp table names will generate collisions if you have multiple users accessing the app through the same proxy or NATting firewall, since their requests will be coming from the same address. You can avoid this by generating a random cookie and appending it to the address in your table names. (If you already have a session ID independent of the remote address, it would be perfect for this.)

    Back to your original question, how does this fail when it fails? Does it return an error, hang, or what?

      I've tested this in an environment where I am the only user and I am still seeing this. If it fails, it dies at the point I issue a drop table on the merge tables (where I commented it in my code snippet). No errors, hanging, or anything. I just get a blank screen. very frustrating. My gut feeling is also possibility of open tables on windows. But I can't seem to identify it consistantly. Even monitoring "show open tables"

      I appreciate your feedback

        You're not seeing anything at all in your browser, not even the output of the
        print qq {<hr>\n} if ($mysqlDebug); print qq {$DatabaseName, $dbS, $dbU, $dbP, $mergeCount, $RemoteAdd +r, $routers<br>\n} if ($mysqlDebug);
        lines at the top of the sub?

        If you're not even seeing that (and you have $mysqlDebug set, of course), then have you tried using telnet to port 80 and/or a packet sniffer to see the complete document returned by apache (including headers and anything else that your browser may eat without displaying it)?

      I've changed the merge table names to include an EPOCH Time and random number to make sure same NAT'd requests do not use the exact same table. Still no change..

      I moved the drop tables request to the end of the script so users now: Create Merge, Query Merge, Drop Merge each time. Still no change... However, it now dies on the creation of the merge tables.

      I'm leaning away from a perl coding issue and more toward a file in use issue. I just need to find out how to tell if a file is locked in MySQL.

      Thanks for your help guys

Re: DBI seems to randomly die
by UnderMine (Friar) on Jun 06, 2006 at 20:59 UTC
    My initial thoughts would be related to table locking.

    Creation and Dropping is not a trivial database operation as it involves a lot of behind the scenes work (e.g. each merge table creates two files .frm and .MRG and uses lot of open handles). You are trying to monitor what is happening while creating and dropping tables on the fly. This is usually causes issues as the mysql schema is trying to do selects and exclusive locks to create the tables. With DDL statements there is no concept of a transaction they just occur and will break anything that attempts to run at the same time.

    You may be better upgrading to MYSQL 5 and using VIEWS or possibly creating HEAP tables on the fly as these are tempory in nature. However if you want to stay with 4.1 try adding INSERT_METHOD=NO to the end of the create table statement as this prevents some of the locking issues as the merge table can nolonger perform inserts.

    Hope it help

    Update: MERGE tables can be tempory just not in memory only like HEAP tables.

      As it stands, I'm required to use 4.1 at the moment until a planned upgrade to 5 later this year.

      I've played with this a little and it seems to have help the issue a bit, but not completely. I want to say this is helping confirm that files are still in use on the windows server box even though they are not locked (based on "show open tables;")

      thanks for the input. these are great suggestions

Re: DBI seems to randomly die
by derby (Abbot) on Jun 06, 2006 at 20:15 UTC

    Wow ... that's hairy. I would start by setting the RaiseError attribute on and wrapping all the DBI calls in an eval. If that didn't provide any useful info then I would set Tracing on (all of this is documented in DBI.

Re: DBI seems to randomly die
by badaiaqrandista (Pilgrim) on Jun 07, 2006 at 02:34 UTC

    I think you should instead create a normal table and use $RemoteAddr as an attribute. Table records are made to be easily created/updated/deleted, while databases aren't. If you want to clear a table use 'TRUNCATE' command, instead of dropping the whole table.

    Anyway, the problem could also because of the invalid character contained in $RemoteAddr (which I assume containing ip address that contains dots) when dropping tables in 'drop table ${Database}.conv_${RemoteAddr}'


      Also good suggestions. However, I cannot truncate the table because of how the application works. Basically I create the merge based on a date range and their is 1 table per day. So 50% of the time, i have a completely different list of tables to merge.

      As for the IP Address, I'm aware that the dots are an issue so they are changed to underscores before being used in the table name. I'm also trying to avoid changing table structure or references to it since each table can become quite large (100MB+ a day) depending on the end-user's environment. This would make for a messy migration. Also, I want to stick with what the MySQL consultant recommended based on this architecture.

      I do want to point out that since DBI 1.48, this problem is less frequent then prior to that version. Scanning the changelog didn't really point to anything in particular that I could say has fixed my specific issue.

Re: DBI seems to randomly die
by jdtoronto (Prior) on Jun 06, 2006 at 20:04 UTC

    I have a Windows desktop app using Perl and Tk with DBI. Ocassionally we see the thing go "deaf" - I think it is a socket problem but have not yet proven this.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://553890]
Approved by ikegami
Front-paged by Argel
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2018-03-24 14:46 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (299 votes). Check out past polls.