Mysql drop table/create table sequence gives strange error

Question

This situation makes no sense.

I have the following sequence of SQL operations in my php code:

DROP TABLE IF EXISTS tablename;

CREATE TABLE tablename;

Of course the php code does not look like that, but those are the commands being executed.

Every once in a while on the CREATE statement, the system returns "table already exists".

I would not think this could happen, unless it is some kind of delay in the dropping. The table is Innodb and I read that there could be processes using the table. However, the tablename has embedded within it a session_id for the user, because this table is somewhat transient and is dedicated to the specific user only--no other user can be using the table, and not even any other script can be using it. It is a "user-specific, script-specific" table. However, it is possible that the user could execute this script, go away to a different script, then come back to this script.

The describe code is in a routine that decides whether it can re-use the table, or whether it has to be recreated. If it has to be recreated, then the two lines execute.

Any ideas what is causing this error condition?

EDIT:

The problem with "actual code" is that sometimes it just leads to more questions that diverge from the actual point. Neverthess, here is a copy from the actual script:

$query1 = "DROP TABLE IF EXISTS {$_SESSION['tmpContact']}";
SQL($query1);
$memory_table = "CREATE TABLE {$_SESSION['tmpContact']}";

The SQL() function executes the command and has error handling.

It would be easier to comment if we could see whats really going on. Pseudo code can only ever garner guesses — RiggsFolly, Mar 19 '20 at 00:13
Does this error always happen, or do you have more detail about specific scenarios where it happens? Is concurrency a potential issue if multiple requests come in? — totalhack, Mar 19 '20 at 00:14
You shouldn't drop and recreate a table that's hammered. It's easier to move the data to another table then delete the old data if needed. — Alpha2k, Mar 19 '20 at 00:20
@totalhack I was also thinking about concurrency, but he says the tablename is unique for each session. — Barmar, Mar 19 '20 at 00:22
@ totalhack: as it says in the question "Every once in a while ...." Plus this table is specific to the user as stated. — Jeffrey Simon, Mar 21 '20 at 00:33
These comments got me thinking about another possibility. Suppose the user with the dedicated table starts another browser tab. And suppose they have the same session_id() -- this might lead to my problem. I will need to check on this. — Jeffrey Simon, Mar 21 '20 at 00:41

score 0 · Answer 1 · answered Mar 29 '20 at 02:57

0

Plan A: Check for errors after the DROP. There may be a clue there.

Plan B: CREATE TEMPORARY TABLE ... -- That will be local to the connection, so [presumably] you won't need the DROP.

answered Mar 29 '20 at 02:57

Rick James

135,179
13
127
222

The temporary table does not meet the requirements, as it is a table we want to hang around for a while. We could change the design though so as not not have it hang around, which only means it has to be recreated as needed. – Jeffrey Simon Mar 30 '20 at 13:44

score -1 · Answer 2 · answered Mar 19 '20 at 00:19

-1

$a = mysql_query("SELECT TABLE");
if($a != ''){}else{}

try mixing the php with the sql.

answered Mar 19 '20 at 00:19

Whoopix

23
7

Mysql drop table/create table sequence gives strange error

2 Answers2