1

"A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed."

This fact is causing lots of heartache. I am using codeigniter 3 and Mysql RDS. Creating TEMPORARY tables didn't work due to the above quote since my multiuser app creates about 6 regular tables for each user that get deleted (dropped in sql) when they press logoff. But a large number of users will not press logoff in the app, instead pressing the X to close the tab. This leaves 6 extra tables on my RDS server. This is causing a large number of orphan tables (in 24 hours) which is slowing the database server to a crawl.

Is there anyway to "catch" when someone closes the app without pressing logout? I am thinking that if I could keep php from closing sessions constantly, that might work, but that seem pretty far fetched. I was then thinking (outside the box) that perhaps an external service like Redis could hold the temporary tables, but being on AWS I am already at my upper limit of what extra services I can afford.

I have tried taking the TEMPORARY tables and making them regular old mysql tables and deleting them when a user logs off. But the issue is that many users will exit via the X on the tab.

Marcin Orlowski
  • 72,056
  • 11
  • 123
  • 141
richard
  • 31
  • 4
  • 3
    Why do you have separate tables for each user? Why not just create a foreign key user_id in the tables to help you separate the data? – DarkBee Nov 22 '22 at 17:14
  • 3
    Agreed, this sounds like it's probably an XY problem...what is the reason for all these short lived tables? – ADyson Nov 22 '22 at 17:15
  • Also, regardless of the above, surely your title should be how to keep a _mysql_ session from closing? (Or perhaps more accurately, keep a mysql connection alive between requests, and re-use the same connection for each user, so as to avoid the temp tables from being removed). PHP sessions stay alive for as long as the browser is open, generally (or until it times out). – ADyson Nov 22 '22 at 17:20
  • DarkBee, The tables are created fairly complexly. These short lived tables are a 'scratchpad' where I build the data to display on a report using Koolreport. Each table takes into account a bunch of data from other tables. Is is too complex for a single sql statement, IMO. There are a number of sql statements that are run to build each one. – richard Nov 22 '22 at 17:22
  • I doubt any of that would prevent you from storing them in a single, permanent table with a user ID associated to relevant rows? Then you can query the relevant data, restricted by user ID, when needed. And clear old data out via a cron job at your leisure. With proper indexing it shouldn't be a big performance headache I wouldn't have thought, unless you are at a seriously large scale in terms of the numbers of users taking advantage of that feature simultaneously? – ADyson Nov 22 '22 at 17:24
  • ADyson, glad you brought that up. It seems to be a feature of PHP that the session is being closed constantly, even while a single user is attached. Not sure what is an XY problem? I do use $_SESSION and I am thinking that perhaps I can just pass the handle to the TEMPORARY file to the next function, until I don't need the scratchpad anymore. – richard Nov 22 '22 at 17:27
  • 1
    https://xyproblem.info/ - it's basically asking about a solution to an issue which doesn't really need to exist, when there is a real issue underlying it which needs to be addressed instead. Or, asking about how to implement a workaround instead of fixing the original problem. – ADyson Nov 22 '22 at 17:30
  • ADyson, building the minitables ans then transferring to a large permanent is a pretty good idea. I would just create a variable to say which mini table the data came from. Thx – richard Nov 22 '22 at 17:30
  • 3
    `It seems to be a feature of PHP that the session is being closed constantly`...no, PHP doesn't close its own session constantly unless you've configured it that way. But at the end of each HTTP request the PHP script will stop executing, and of course as a result of that it will close the connection to mysql, which will of course cause mysql to remove any temp tables associated with that connection. Don't conflate the PHP and mysql sessions, they are separate. – ADyson Nov 22 '22 at 17:31
  • Thanks Dyson. I will try moving the data to a diary table and immediately deleting the mini tables. On your comment above, when PHP stops executing doesn't matter to me. As long as I can save the handle to the temp file in $_SESSION, that would work. But I am not sure how to save that handle and how to get it to re-open again (from another php script). I guess I don't understand the architecture of php or mysql. – richard Nov 22 '22 at 18:14
  • You can save something to $_SESSION and then retrieve it again in subsequent HTTP requests made by the same user, until such time as the session expires. But if you save something to a temporary file (do you mean file, or table...it's not clear where temp files suddenly came from?), you cannot guarantee the temp file, if it was created by PHP, will last beyond the execution of a single HTTP request. Remember, each request made from the browser to your server causes PHP to execute again, from the beginning, as if it had never run before (aside from state-preserving mechanisms such as Session). – ADyson Nov 22 '22 at 18:26
  • Therefore each time the PHP script runs, it opens a new connection to the mysql database, to run the queries it needs during that run of the script. When the script finishes, the connection variable is removed from memory (along with all the other PHP variables associated with that run of the script), which causes mysql to drop the connection. Mysql considers each open connection a "session", and, as you've noticed, since since temporary tables are associated with a specific connection, it therefore removes them when the connection is ended as a result of the PHP script ending. Does that help? – ADyson Nov 22 '22 at 18:28
  • I call this a bug in codeigniter. – Rick James Nov 23 '22 at 21:21
  • If the network has a hiccup, `TEMPORARY TABLEs` are dropped even if the connection is automatically reestablished. – Rick James Nov 23 '22 at 21:22
  • @RickJames how is that CodeIgniter's fault? The temp table behaviour is part of mysql. – ADyson Nov 23 '22 at 21:22
  • @ADyson - There is no way [that i know of] to retrieve the _same_ MySQL connection after restarting PHP. – Rick James Nov 23 '22 at 21:26
  • "Connection pooling" (if somehow enabled for MySQL) _should_ drop `TEMPORARY` tables, but never 'permanent' tables. – Rick James Nov 23 '22 at 21:29
  • "Session" is ambiguous -- There is `$_SESSION[]`, which can hold _data_ or _objects_ but probably not MySQL connections between "sessions" of PHP. The latter survives only until the page is rendered. – Rick James Nov 23 '22 at 21:31
  • @RickJames Well the dropping of connections is on PHP, again not CodeIgniter, or if we're being specific, the SQL library. A form of connection pooling is used, as I understand it, but you're right you can't pick up the same connection again, and as far as mysql is concerned it's a new connection/session each time you connect to the DB server from PHP anyway. This is logical 99% of the time as it conserves resources and makes connections available to other processes. – ADyson Nov 23 '22 at 21:32
  • @RickJames Interestingly, MS SQL Server has a [more varied set of temp table concepts](https://stackoverflow.com/a/2921091/5947043), with different scopes and lifetimes, some of which might suit the OP better here. MySQL unfortunately does not. – ADyson Nov 23 '22 at 21:34

1 Answers1

1

After trying a few different ways to solve this I ended up creating a log where I log any small temp files created. Then, when any user logs in, I check to see if any tables were created (in the log) more than two hours ago and use drop if exists to delete them. This should work without creating a cron. Is 2 hours enough? I sure hope so.

richard
  • 31
  • 4