Link to home
Start Free TrialLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

asked on

MySQL Drop vs. Temporary Table

Hi Experts,

I am working on a complicated report that have many views, and I need to create a table for the report. What I have done is created a blank MyISAM table that has my structure, and I am doing SELECT INTO report_session id. Then I am populated this table, and resorting for my individual views.

At the beginning and end of my script I am doing DROP TABLE IF EXISTS report_session_id, but I need to grant DROP privileges. All of this I am executing through PHP.

My question is, if there is a way to grant DROP privileges only on tables that begin with report_%?

I also see the privilege to create temporary tables. How would that work, and when are temporary tables destroyed, considering that this is a PHP script? Are temporary tables the same as memory tables?

Any help would be appreciated.
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Are temporary tables the same as memory tables?
Memory tables will persist across user sessions whereas temporary tables are automatically deleted when the session ends.

Personally I would use a temporary table for this.

http://anothermysqldba.blogspot.com/2013/06/memory-and-temporary-tables.html
Avatar of APD Toronto

ASKER

So far I have come up with the following, but I am getting an error

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON 'css_reservations'.'excel_temp_'* TO 'rs_user'@'localhost'

Open in new window


As well am I correct to assume that my previous privileges will remain on the other tables, or do I need to revoke all and define then here. If so, how would I do all except create and drop on all other tables?
whereas temporary tables are automatically deleted when the session ends.

What session? The PHP session?
If the temporary table gets automatically deleted when the PHP session ends, what if in the same PHP session I need to re-create that table, wouldn't I run in the same DROP scenario?

As well, can I create a temporary table based on a MyIsam table or would I need to embed the CREATE TABLE in my PHP script?
What session? The PHP session?
The MySQL session - however when the PHP script ends the MySQL session will terminate as well - sooner if you call mysqli->close();

You have to create the table with your PHP script or you can move all your data processing to a stored procedure and do it there.

CREATE TEMPORARY TABLE __TEMP1 AS (
    ....
)

Open in new window


As to granting privileges you can't use a wildcard on object names you can say

db.*

but db.xyz* will fail.
I am using PDO as follows:

$db = Database::getDB();
         
            $statement = $db->prepare($query);
            $statement->execute();
            $statement->closeCursor();

Open in new window


Does this mean that I will lose my table when I close my cursor?

I ask because in my first execute I am creating my table and inserting values, while in subsequent executes I will be selecting and sorting. In this case should I leave my last line until the very end, and repeat the middle 2 lines for every execution?
Why not CREATE TABLE ... IF NOT EXISTS normally, TRUNCATE the table to remove any leftover data, use the table normally and when your work is done, DROP the table?  Easy and predictable behavior.
Hi Ray,

if you will see my original post, I want to avoid assigning DROP privileges to the application's user.
I don't believe that TRUNCATE requires DROP privileges.  Certainly DELETE FROM ... WHERE 1 does not require DROP.  The data will still be gone, but the table will remain and can be reused.

With respect to the application's user, isn't that user the PHP script?  Or are you going to use separate MySQL identities for each different authenticated PHP client?
But I am creating 1 table per session
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ray in this situation I only needed a table for the lifespan of my script not my session.