My web site uses Mysql and PHP3. I'm using HEAP tables for rapid access. A HEAP table is created for each user as they log in, and then dropped when they log out. The problem is that some users will leave the site without logging out, and I need to drop those orphaned tables so they don't choke my RAM. One idea is to make the HEAP tables Temporary, hoping that they stay alive until the user leaves the site. But I can't find any good documentation on the behavior of Temporary tables. Specifically, I need to know whether a PHP persistent connection [mysql_pconnect()] will maintain the tables alive as the user travels from page to page and script to script, and then will die when the person logs out. Or does anyone have a better idea for handling this problem?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Will PHP persistent connections maintain temporary tables?
- Thread starter robelius
- Start date
As I understand it, persistent connections are simply left in a "pool", waiting for the next request that needs the table, and that request could be from any process or user. Temp tables can only be accessed from the same connection that created them. This means that the next person to access the database might be the one to use that connection, not the original 'owner' of that table. Unfortunately, mysql connections are not something that can be handled on a 'user' basis, like sessions. See
And see
See about halfway down the page:
In MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).
In MySQL Version 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.
It would be nice if we could just use the link identifier ( to pick up that specific connection later, but it seems there is no guarantee that someone else won't pick it up first.
I would say that your best bet is to have some sort of cron job running that regularly deletes tables where there has been no activity for a certain amount of time.
And see
See about halfway down the page:
In MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).
In MySQL Version 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.
It would be nice if we could just use the link identifier ( to pick up that specific connection later, but it seems there is no guarantee that someone else won't pick it up first.
I would say that your best bet is to have some sort of cron job running that regularly deletes tables where there has been no activity for a certain amount of time.
KamranFeroz
Programmer
Hi,
Instead of using this hard work and making lot of imlicit temporary tables. Try to use cookies to maintain the users loggins or even the best one, Try to upgrade to PHP4 and use it's session option to maintain the session.
That's the best possibility for your scenarion as I think.
Good luck.
Instead of using this hard work and making lot of imlicit temporary tables. Try to use cookies to maintain the users loggins or even the best one, Try to upgrade to PHP4 and use it's session option to maintain the session.
That's the best possibility for your scenarion as I think.
Good luck.
Kamranferoz is right, if you are not storing large amounts of data per user. If you are storing large amounts of data per user access, then you should probably re-think your overall application design.
The cool thing is you can place arrays or even PHP objects into sessions, rather than just tote around a lot of scalar variables.
Also, as I see above, you want your data in RAM for fast access. Well, there is even a method to place session data into shared memory, if you enabled shared memory in your PHP install. See the session.save_handler directive in php.ini. (more in this if you are interested)
Or, for a simpler way to keep your session data in RAM, you could make the values be stored in a RAMdrive. In php.ini, you can change
to any other directory you want. So if you mounted a RAMdrive at /sessions, then you would just enter
. This means all session stored variables are now in RAM.
The cool thing is you can place arrays or even PHP objects into sessions, rather than just tote around a lot of scalar variables.
Also, as I see above, you want your data in RAM for fast access. Well, there is even a method to place session data into shared memory, if you enabled shared memory in your PHP install. See the session.save_handler directive in php.ini. (more in this if you are interested)
Or, for a simpler way to keep your session data in RAM, you could make the values be stored in a RAMdrive. In php.ini, you can change
Code:
session.save_path = /tmp
Code:
session.save_path = /sessions- Status
Similar threads
