Get MySQL Duplicate Key Checker
I’ve just released MySQL Duplicate Key Checker on SourceForge. This is a complete rewrite of a tool I initially released under a slightly different name. It is now much more powerful and friendlier to use, especially for scripting, and has many more options.
As with the MySQL Table Checksum tool, it is part of MySQL Toolkit.
What’s new
I’ve redesigned the program from the ground up to be more UNIX-friendly. In particular, the output is suitable for piping to awk or other tools.
The tool does a lot more for you by default. For example, it analyzes all tables in all databases. You can limit and exclude databases and tables, of course.
There are many more options. This tool is now much more general-purpose. Instead of merely finding duplicated or redundant indexes and foreign keys, the approach I took in this rewrite is to find and print information on all indexes and foreign keys — but filter out all but duplicates by default.
This means you can use the tool for other things. For example, you can get a list of all indexes. You could pipe a list of all foreign keys into another program, which describes or analyzes relationships and dependencies among your tables, perhaps to generate schema diagrams. If you want to write a tool for that purpose, please consider adding it to the toolkit. If I don’t beat you to it, that is! It’s on the list.
Sample Output
The output is much more compact now. Here’s a sample:
mysql-duplicate-key-checker -d gamer -t semaphore DATABASE TABLE ENGINE OBJECT TYPE STRUCT PARENT COLUMNS gamer semaphore InnoDB PRIMARY KEY BTREE NULL `i` gamer semaphore InnoDB i KEY BTREE NULL `i` gamer semaphore InnoDB semaphore_ibfk_1 FK NULL `gamer`.`foo` `i` gamer semaphore InnoDB semaphore_ibfk_2 FK NULL `gamer`.`foo` `i`
You only see output if you have duplicated indexes or foreign keys (unless you disable the filtering, in which case you’ll see everything). I deliberately added some duplicate keys to that table.
Redundant indexes aren’t just exact duplicates, as I discussed in the original articles. A redundant index could just be a leftmost prefix of another index. And for foreign keys, keying the same columns to the same referenced table, in any order, suffices.
Summary
Yet another tool! Yay!
Technorati Tags:No Tags
Someone has pointed out a regression bug from the old version… oops. I’ll release a fix soon. In the meantime, please feel free to put the tool through its paces and give me feedback if you have any! Then I can fix more things at once.
Greetings,
I just downloaded both the duplicate keys checker and the queries profiler.
There are 2 issues:
First, it won’t allow you to simply not specify a value for, say, the password to access the database. That is, if you provide -p “” or not a password at all, it will ask you for it. I commented out your checks in the perl code, but I think it needs to be fixed.
As for the duplicate keys checker:
DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘database’ at line 1 at ./mysql-duplicate-key-checker line 185.
Nevertheless, they seem interesting and useful. Thank you for making them available for download. Good luck.
Hi Mark! Re the password: yes, I found that too when I tried to use it without a password. It’s already fixed in Subversion.
The second error is more troublesome. Another user reported problems with it trying to scan the ‘lost+found’ database, which appears because of the ext3 filesystem but is not a database. I have also addressed that, and added quoting so strange names shouldn’t cause troubles. This is also in Subversion, but it may not be the same trouble you’re having.
I will follow up with you over email to see what the issue is. Thanks!
Great job! Have you considered providing a package for the Fink distribution? Erm, Debian would be cool, too. Sorry, I don’t wanna make the impression that I’m too lazy to check for new versions and recompile stuff all the time–but in fact I am ;-)
Thanks! Yes, it would be great to package for distributions, too. Any volunteers?