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.
APD TorontoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
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
0
APD TorontoAuthor Commented:
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?
0
APD TorontoAuthor Commented:
whereas temporary tables are automatically deleted when the session ends.

What session? The PHP session?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

APD TorontoAuthor Commented:
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?
0
Julian HansenCommented:
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.
0
APD TorontoAuthor Commented:
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?
0
Ray PaseurCommented:
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.
0
APD TorontoAuthor Commented:
Hi Ray,

if you will see my original post, I want to avoid assigning DROP privileges to the application's user.
0
Ray PaseurCommented:
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?
0
APD TorontoAuthor Commented:
But I am creating 1 table per session
0
Julian HansenCommented:
The closeCursor does not close the session so the temporary table will persist.

The sample code below demonstrates the concept. It creates a temporary table - populates it with data, retrieves the data, closes the cursor retrieves the data again to show the data is still there.
The PDO session is the closed and reopened to show the temporary table no longer exists.
<?php
// PDO Example
// Demonstrates that temporary tables persist after statement closeCursor
// is called but not across PDO sessions

$dsn = 'mysql:host=localhost;dbname=ee';
$user = 'user';
$password = 'password';

// Get a new connection to the database
$dbh = getConnection($dsn, $user, $password);

// Create the temporary table
$sql = "CREATE TEMPORARY TABLE `__temp01` (id int, value varchar(30))";
echo "Creating Temporary Table [{$sql}]<br/>";
$stmt = $dbh->prepare($sql);
$stmt->execute();

// Populate it with some data
$sql = "INSERT INTO `__temp01` (id, value) VALUES (1,'ABC'),(2, 'DEF'),(3,'XYZ')";
echo "Adding data [{$sql}]<br/>";
$stmt = $dbh->prepare($sql);
$stmt->execute();

// Fetch the data
$sql = "SELECT * FROM `__temp01`";
echo "Retieving data [{$sql}]<br/>";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();

// Dump it
echo "Dumping data ...<br/>";
echo "<pre>" . print_r($rows, true) . "</pre>";

echo "Closing cursor ...<br/>";
// Close the cursor
$stmt->closeCursor();


echo "Retieving data [{$sql}]<br/>";
// Fetch the data again to show it is still there
$stmt = $dbh->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();

echo "Dumping data ...<br/>";
echo "<pre>" . print_r($rows, true) . "</pre>";

echo "Closing connection ...<br/>";
// Close the connection	
$dbh = null;

echo "Getting new connection ...<br/>";
// Get another connection
$dbh = getConnection($dsn, $user, $password);

// Fetch the data again to show data is now gone
echo "Retieving data [{$sql}]<br/>";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();
echo "Dumping data ...<br/>";
echo "<pre>" . print_r($rows, true) . "</pre>";

// Function to get a new connection
function getConnection($dsn, $user, $password)
{
	$dbh = null;
	
	try {
		$dbh = new PDO($dsn, $user, $password, array( PDO::ATTR_PERSISTENT => false));			
	}
	catch  (PDOException $e) {
		echo 'Connection failed: ' . $e->getMessage();
	}
	
	return $dbh;
}

Open in new window

Working sample here
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
... creating 1 table per session
In web applications, "session" is a term of art that can mean different things.  It can be a PHP session (data kept in $_SESSION) or a client interaction with a web site, perhaps visiting one or more pages, with or without using the PHP $_SESSION.  Or it can mean an interaction with a MySQL server.

I guess the thing you would want to be clear about is this:  Do you need the table to persist beyond the lifetime of the timeline of a single request / response?  If so, my instinct is to use a permanent table.  I say that because when I show demonstration scripts here at E-E, I use temporary tables with CREATE TEMPORARY TABLE.  The tables are disposed of by the PHP script termination process, so they are not be available to other pages on subsequent HTTP requests (page loads).  In other words, the temporary table exists for the life of the PHP script, but once the script is done, the table and all its data is removed.
0
APD TorontoAuthor Commented:
Ray in this situation I only needed a table for the lifespan of my script not my session.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.