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

/- M a r c -/

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);
[download]

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

/- M a r c -/

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
UnderMine

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

/- M a r c -/

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.

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.

/- M a r c -/

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.

jdtoronto