At 08:30 AM 1/27/2005, electroteque wrote:
i have come to realise that temporary tables are only persistent for 30 secs, is there a way to extend that ? My applications is attempting to store data before and entry is made and then insert the data from the temporary table after the entry is inserted then drop the temp table. This should work in theory but even with peristent php connections the tables are lost. Any ideas ? I have had to create heap tables with random named tables for each user, then drop them as i would with temp tables, is this a good idea ?
Double check all of your code. I had a similar problem in the past only to find that at least one call to the database in my script was NOT persistent, so it would drop the temporary database. After making ALL connections in my script persistent, I didn't have the problem any more.
Also:
1. You cannot refer to a TEMPORARY table more than once in the same query AND in different SELECT queries combined by UNION operator.
2.
You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:
mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
quotes from http://dev.mysql.com/doc/mysql/en/temporary-table-problems.html
There are probably other things to check too. Maybe someone more experienced will reply here. To test the 30 second problem, try this. Create a simple script that creates a temporary table, populates it with a couple of things, starts a counter that will count for more than 30 seconds, then read from the table and output the results. Don't have the script doing anything else. This is just to see if your system really does kill the table before 30 seconds. Test it with a counter less than 30 seconds also to make sure that it works for you.
I have two functions that I use to connect to my databases. Not sure where I got them. Probably off of this list or the php list which is what I normally program in. Yes, the functions are php code:
function get_mysql_query($query){
GLOBAL $hostName,$userName,$password,$databaseName;
$connection = @mysql_connect($hostName,$userName,$password);
while($connection == FALSE):
$connection = @mysql_connect($hostName,$userName,$password);
endwhile;
$db = @mysql_select_db($databaseName, $connection);
$result = @mysql($databaseName,$query);
$connection_close = @mysql_close($connection);
return $result;
}
function get_mysql_query_stay_open($query){
GLOBAL $hostName,$userName,$password,$databaseName;
$connection = @mysql_connect($hostName,$userName,$password);
while($connection == FALSE):
$connection = @mysql_connect($hostName,$userName,$password);
endwhile;
$db = @mysql_select_db($databaseName, $connection);
$result = @mysql($databaseName,$query);
return $result;
}Normally when I use the second one in a script, I do NOT use the first one without watching carefully what I am doing. If it is a small script, then I will just use the get_mysql_query_stay_open query if I need to leave the connection open and not even worry about closing the connection. I only worry about it on larger scripts where it might eat up to many resources if I leave it open when it is not needed to be left open.
Hope this might help in some small way. Steve
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
