Kangaroo Court {concept47 blog}

Getting rid of duplicate Entries in your MySql database

Thanks for Justin Cook for doing all the leg work on this one.

All you have to do is create a new table from the old one, which filters out the duplicate entries

1
2
3

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1
GROUP BY [colum_to_remove_duplicates_from];

so as an example

1
2

CREATE TABLE news_new AS
SELECT * FROM news WHERE 1 GROUP BY [title];<!--more-->

Now you just have to go in and rename the news db to news_old (for the newbies to this … don’t drop the table immediately, at least until you’re sure the new one works fine).

Then rename news_new to news

Phpmyadmin should make all this easy (click on the “Operations” tab), but in case you are working from the console, here is the syntax you need to know

1

RENAME TABLE new_table TO old_table;

That isn’t all that you have to do, however. The new table will be missing its primary key and auto increment settings as well as any other indexes that you may have had on the old table.
Go in and set the primary key (first) back on the column that had it, then (secondly … the order matters) clear out the default value on it and set it to auto increment.

You can re-add your indexes later … be sure to put a unique index on the column you were having trouble with.

Add comment March 27th, 2008

Mysql5 runs with twice the memory of Mysql4 on Windows XP

just an interesting side note … stumbled upon this “discovery” by accident earlier today.

Add comment February 13th, 2008

How to run both mysql5 and mysql4 on the same windows Machine.

I finally made the jump from mysql 4 the other day, to take advantage of mysql’s new “INSERT … ON DUPLICATE UPDATE” command (which I think is spectacular by the way).

I didn’t want to actually upgrade from mysql4 to mysql5, just run the two mysqls side by side … so I looked around for a bit and figured out that all you need to do is get both  servers running on different ports. The trick is to remember to reference localhost:port (eg: “localhost:3307″) instead of just “localhost” whenever you connect to it, in php, for example. Below are screenshots of the things you need to watch out for when doing this on a windows machine.

The first part of the install is pretty straight forward, just make sure to install it into a different folder than your current mysql install.

At this screen be sure to tick the “Configure the MySQL Server now” box

At this screen pick “detailed configuration” and continue on …

At this screen be sure to change the port number.

From the dropdown pick a service name that won’t conflict with the name of the service for the current MySQL install.

After that is all done, we want to try and connect to our brand new server. So fire up MySQL Administrator or MySQL query browser and create a new connection, like so … (Yes. I know running as root is bad for you … thanks).

Once you’re done … connect to MySql Query browser and you should go to this screen! The nice thing about this is that you can probably even run mysql 4, 4.1 and 5 all together.

Add comment February 4th, 2008