How to Clear Your Old Database Tables
10th of February, 2010 in the year of our Lord
Oh noz!
To someone who hasn’t dealt with this problem before, this issue probably seems pretty intimidating (at least I think it did to me when I first got it). However, this is most likely a simple fix.
Arriving at the Problem
First, let’s talk about how I came about this problem today so you can avoid it in the future.
To speed things up, I like to keep a copy of the most recent version of WordPress on my computer with a few modifications. One thing I modify is the wp-config.php file; here are some of the changes:
/** MY CHANGES **/
// creates fewer database queries, thus improving performance
define('WP_HOME', 'http://URL.com');
define('WP_SITEURL', 'http://URL.com');
This will screw things up if I don’t replace 'http://URL.com' with the correct URL. More specifically, I get the “clear your old database tables first” message from WordPress. This is what happened today when I transferred a theme from my server to a client’s.
The Solution
<!-- EDIT -->
Quickest and easiest way in the comments. I haven’t tested it yet, but I know and trust the source.
-
Log in to phpMyAdmin (or other database management tool).
You should be able to find phpMyAdmin in the control panel of your hosting account.
With lunarpages, you need to click on MySQL Databases and scroll to the very bottom and click the phpMyAdmin link.
With AT&T, you get to the control panel by adding /ControlPanel/ to the end of your URL.
- Once you log in, click on databases in the left column.
- If you haven’t already enabled MySQL, click MySQL under databases,
- create a password,
- and click the enable button on the right.
- Right beneath MySQL, click phpMyAdmin
- and enable it by clicking the button on the right.
- Then click the link that reads “Administer MySQL with phpMyAdmin.” Log in and you’ll be in phpMyAdmin.
I haven’t had to do this with any other hosts so if you need help, contact your host, leave a comment, or send me an email and I’ll do my best to help you figure it out.
- After logging in, you should be on a page that has a sidebar on the left with a list of all your databases. Click on the name of the database from which you need to clear tables.
- Now you just need to select all the tables that begin with the “wp_” prefix and choose “Drop” from the drop-down menu.
- Make sure your wp-config.php file has all the correct information in it, and go to http://example.com/wp-admin/install.php/.
<!-- I’m pretty sure this next step is irreversible, so make sure you’re on the right database -->
You should be good to go now. If you’re still having trouble, leave a comment and I’ll see what I can do. Also, if you have anything to add, feel free to do so in the comments.
22nd of March, 2010
it’s way easier from the server side via the terminal with a little SQL:
YAY all done!
I found the web gui to be a little easier to accidentally drop a table or whole database you didn’t really want to… however your shared hosting might not have shell access (which is lame if true…). Writing a deliberate SQL statment to drop the tables/whole database is a little safer in my humble opinion.
22nd of March, 2010
yeah… i don’t use the terminal for anything (i don’t know how)… but i will for this in the future. thanks, man.
22nd of March, 2010
It’s super easy. typing three lines of SQL is just quicker than going through a gui on the web.IMO… give your terminal some love sometime with Secure Shell!
22nd of March, 2010
right, sounds easy, and i’m sure i can do it. i’m just sayin’ this is now the one thing i can do in the terminal. but i don’t understand how it works or anything.
i take it the brunyon and ***** should be replaced with username and password associated with the db?
23rd of March, 2010
I’m leaving out some details maybe, sorry:
-You need to log into your hosted server first with ssh from the terminal.
-Then log into mysql from inside ssh session and drop the target database, the create a new one of the same name.
The questions you need to ask yourself now are:
-Does my hosting give ssh access?
-Does my hosting give DROP privileges to my user?
-Does my hosting give CREATE privileges to my user?
-do all the tables in my wordpress database have wp_ as a prefix?
If you meet all those requirements, then you can just drop the database real quick in one statement. If you own the server or have a dedicated hosting then you can do that too probably.
I don’t use WP but I’ve used elgg which has a similar database scheme and this typically worked for me.
To drop a database from mysql server:
go to your terminal and type
$ ssh username@host_domainname.com
-(curtis@curtisblackwell.com maybe?)
-(you’ll be asked to add private kerbros key, type ‘Y’)
-it will ask you for your username again
-then enter your password (invisible input buffer for passwords)
Now you need to know your database username and password
(next line may vary depending on your hosting… it’s easy to find out)
$ mysql -u curtis -p (now press enter to type your password)
(type password)
-now you should be in your mysql command line
//query below drops whole database from server
mysql> DROP DATABASE wordpress (or whatever it’s named);
//query below creates a new clean database with same name
mysql> Create DATABASE wordpress;
mysql> EXIT
$ exit (to leave your ssh session)
now you have done the same amount of work in 2 queries
I should not have assumed you can read my mind… hope this is useful, you may be better off using phpmyadmin if that is what your hosting wants it’s users to use…
23rd of March, 2010
I tested dropping the wordpress database and then creating a new one then ‘upgrading’ (basically leaving the same installation on the server) and on my server it went right to the installation screen where I got a admin name and password.
Then it seems to have recovered all the original start up tables without data in them.
Hopefully this is what you are going for. I don’t keep a local server going on my laptop… only on my LAN and a shared hosting.
23rd of March, 2010
feel free to edit down my comments… eloquence isn’t a strong suit of mine :-/