I get temp files created in my database and i'd like a quick and easy code to delete these, also a friend who is running the same d/base has never deleted theirs and has over 8000!

Could the code below be changed to a wildcard to delete any tables that are:
default_temp_*

I also have a table called default_temp which cannot be deleted! So it has to be anything called default_temp_

######
DROP TABLE `default_temp_6bb2824c646b02eca19751294f70cabe` , `default_temp_33cdb9e7da8954e74aaefe0ca0669ee6` , `default_temp_86e8a587156040c985c6890055feea4d` , `default_temp_209b67489cfc5f9dc13ebc2c4e77edb6` , `default_temp_84766867ba2a5127c18ba90dff0f9fb4` ;
######

Thanks Ian

Venta Sanlucar Sales Sotogrande

It sounds like your main problem is with your application that creates the temp tables. A temp table should be just that, and be dropped by the app when it's no longer needed.

You didn't say which language you wanted to use. Here's a perl script that builds an array of the tables that need to be dropped and prints the table names. If it outputs the expected names, you can uncomment the drop statement and rerun the script. I normally add additional error handling but this should do the job. Posting formatted code in this forum is a joke, so I'm also going to email you the script.

#!usr/bin/perl -w

use strict;
use DBI;

my $db = 'database_name';
my $server = 'server'; # server name or IP address
my $user = 'user';
my $pass = 'password';
my @tables;

my $dbh = DBI->connect("DBI:mysql:$db:$server", $user, $pass,
{'RaiseError' => 1, 'PrintError' => 0} )
or die "Connection to $db database failed: $DBI::errstr";

my $info = $dbh->table_info();

while ( my ($catalog, $schema, $table, $type) = $info->fetchrow_array ) {
#foreach ( $catalog, $schema, $table, $type ) {$_ = 'N/A' unless defined $_;}
push @tables, $table if $table =~ /^default_temp_/i;
#$dbh->do(drop $table) if $table =~ /^default_temp_/i;
}
print "table to be dropped:$_\n" for @tables;

$dbh->disconnect;

Oops, I forgot to quote the drop statement.

$dbh->do(drop $table)

should be

$dbh->do("drop $table")

Hi Fishmonger,

Thanks for email and the code, will test later on this morning and let you know how I get on.

The code is MySQL - Being an non expert not sure if the .pl will run in conjunction with the database, but it's a back up so no porblems in tested!

The temp files are only created when a user stops a page from loading 100% - guess the info taken from the d/b has to go somewhere after that!

Until later....Thanks Ian

Venta Sanlucar Sales Sotogrande

Thanks for all your help, I couldn't get it to run...mainly because I didn't know how to.....!

Anyhow I've maneged to delete 8000 temp tables for the friend of mine by doing show tables and then copying the data and then with a few find and replace used the drop table....

Thanks once again. Ian

Venta Sanlucar Sales Sotogrande