Computing.Net > Forums > Programming > Create temp file delete for mysql

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

Create temp file delete for mysql

Reply to Message Icon

Name: ian_ok
Date: March 15, 2006 at 07:06:25 Pacific
OS: win xp 98 & 2k
CPU/Ram: p3 512
Comment:

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



Sponsored Link
Ads by Google

Response Number 1
Name: FishMonger
Date: March 15, 2006 at 23:14:55 Pacific
Reply:

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;


0

Response Number 2
Name: FishMonger
Date: March 15, 2006 at 23:24:48 Pacific
Reply:

Oops, I forgot to quote the drop statement.

$dbh->do(drop $table)

should be

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


0

Response Number 3
Name: ian_ok
Date: March 16, 2006 at 00:36:08 Pacific
Reply:

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


0

Response Number 4
Name: ian_ok
Date: March 17, 2006 at 05:16:02 Pacific
Reply:

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


0

Sponsored Link
Ads by Google
Reply to Message Icon

Related Posts

See More







Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Programming Forum Home


Sponsored links

Ads by Google


Results for: Create temp file delete for mysql

Batch File Delete Temp www.computing.net/answers/programming/batch-file-delete-temp/17530.html

SQl delete temp files code... www.computing.net/answers/programming/sql-delete-temp-files-code/12257.html

creating a temp file in user profil www.computing.net/answers/programming/creating-a-temp-file-in-user-profil/10663.html