Level 14
Instead of
DB::unprepared(
DB::raw(" ... "))
Can you try to use
DB::statement('CREATE ...')
DB::statement('DROP ...')
Feb 21, 2022
4
Level 39
Dropping Temporary MySQL Table
I have created and populated a temporary MySQL table with the following:
$tbl = DB::unprepared(
DB::raw("CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS (".$sql.")")
);
I have confirmed that it exists and contains the expected data by querying it like this:
DB::table('temp1')->first();
However, when I try to drop it at the end of the script using this:
$drop = DB::unprepared(
DB::raw("DROP TEMPORARY TABLE temp1;")
);
... I get the error:
local.ERROR: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'j2.temp1' doesn't exist
I know that MySQL removes temporary tables, so perhaps I don't need to worry about attempting to remove it manually, but I just can't understand why the query to drop the temp table returns an error saying that it doesn't exist when a select query at the same point in the script returns data.
Should I skip any attempt to drop it and just move on with my life? Would doing this present any risk?
Level 39
@manojo123 Same result, I'm afraid.
Level 51
Temporary tables only exist during the connection. If you create it with one query and then do a separate query on a new connection it is already gone.
Level 39
@MohamedTammam Yes, I understand this aspect of temporary tables. I guess what I am apparently confused by is how the query builder connections work. I create the temp table with one statement and I query it with another and everything works. In fact, if I create the table with one and immediately drop it with another, that works too. What doesn't seem to work, however, is a third statement to drop it after creating and also querying.
I think it's clear that the temp table is gone, which is great, and it's also not limiting me because I'm able to successfully reference the temp table when joining it to another query (my ultimate objective). I'm really just trying to gain understanding of why the database connection has dropped before the script has completed. If the execution of the query builder statement closes the connection, then I'm failing to understand how the temp table would even be available for querying... which it clearly is.
Please or to participate in this conversation.