Temporary table and persistant connection
Posted 14 March 2006 - 07:47 PM
I am using a temporary table in my application and everything is sweet. However, it would be great to use the data in the temp table on the following page as this would make things easier for me. Because the connection is broken as the page transitions to the next page, the temp table gets dropped. So, if I have a persistant connection, am I right in thinking that the temp table won't drop until the connection closes?.....which would mean that I can use the temp table on the next page with it's data intact.
If my thinking is correct and a persistant connection will allow the temp table to remain, the next question is how would I close the persistant connection....and therefore drop the temp table.....later in my application? I notice from the manual that a persistant connection cannot be closed by using mysql_close() function, so is there another way? If not, how long does the persistant connection last ans where is this timeout set? Can I adjust the timeout?
Thanks heaps and sorry to be a pain!
Posted 15 March 2006 - 07:32 AM
You can always add some cleanup somewhere to make sure you delete old temp tables that are accidentally still around.
A persistent connection could indeed extend the life of the table, but I'm not sure if any other thread has permission to access it, and you can't be sure which connection the new page load will get from the persistent pool. You also can't be sure when or if the connection will be regenerated, dropping the table. Not solid enough to depend on.
Posted 15 March 2006 - 08:49 PM
According to the manual, if a persistant connection exists and another connection is started, the original connection....that is the current connection, will over-ride the new connection attempt. As you suggest, this, for some reason isn't reliable. The temporary table behaves as expected, but not all the time. Sometimes it returns the expected records, sometimes it returns nothing at all, leading me to think that the connection timed out and consequently dropped the table....BUT....sometimes after a few hit's of the refresh button on the browser, the records display again as if nothing was wrong!?!?
After leaving the application idle for a while after it decides to return no records at all, I modify the 'Create Temporary Table If Not Exist...' to 'Create Temporary Table....' and I get an error say that the table exists!! So, the table exists and therefore I presume the connection is still alive....yet sometimes the correct records are returned....sometimes nothing is returned (maybe a different thread is created which can't access the table??).
As you can probably imagine....this inconsistancy does nothing to help me sort this out, so I am floundering here. If this is a connection timeout issue I need to extend the connection time, but I'm not sure where it is located or what it is called......php.ini....apache.....mysql?? However, the tests I have done so far seem to suggest that it isn't a timeout issue at all.....sooooo....if the connection hasn't timed out, why won't temporary table return results on a consistant basis?
Or.....should I abandon this idea and try and find another way....I'm confused!!! lol!!
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users