HowTo Knock it off with crazy MYSQL queries and the qTranslate plugin unistall

We’ve all done it.

Either we learn some tricks in MYSQL, or read a cool post about some obscure programming trick you can do with SQL and decide to try it in a pinch on a live database. Just don’t do it.

9 times out of 10 you have access to SSH, and PHP, and you can write a script that will do what you want. You can also “test” it on a small set of inputs and have it echo out the necessary queries to the console instead of sending them to MYSQL.

This is called the poor man’s unit test, or poor man’s BDD.

Now, on one word press site, before the whole wordpress MU thing, we made the mistake of using qTranslate, well, it’s not evil, it’s just not good, so we wanted to stop using it, however, there’s no real way out of it. Search around and you find this post on their boards DON’T YOU EVEN DARE DO THIS.

It’s just bad programming. You will probably hose your database doing this.

Instead, let’s look at a more sensible solution, using your favorite scripting language, Ruby or PHP will do fine, I’ll choose PHP.

This script isn’t complete, it’s just a proof of concept, or how it could work, notice the generous echo statements? This allows you to run the script as: php script.php > output.txt and then review it every stage of the way, and see where some problem areas are for you.

A few problems we had:

1. Not all posts had the qTranslate tags, weird.
2. Some of them were only in english, but marked as de or fr
3. all of them are huge

Anyway, I hope this helps you to decide to use CLI scripting with some BDD/TDD debugging and going through it in stages, this is stage one, we verify it will work, and then we’d go in and write the actual sql queries, and echo those out. We might try them on a local database, or just run the queries directly.

Sometimes magic tricks and clever bits of code work, and sometimes they don’t, when the don’t, you are shit out of luck.

About Jason

I am a 31 year old programmer living in the south of France. I currently work actively in the fields of Ruby/PHP/Javascript and server/website administration.
This entry was posted in Computer Science, MySQL, PHP Tutorials, PHP/MySQL, Tips and Tricks, Topics and tagged , , , . Bookmark the permalink.