How To

MySQL – my.cnf

Saving some manual labor with cron, bash scripting, and a config file.

This is a fairly rare file, and one I never would have found had I not needed to run a standard SQL process via cron.

Names have been changed to protect the innocent.

As the story goes, no matter what I did, I could not get this one app to stop spewing out ‘smart’ quotes. You know the fancy apostrophes and quotes that curl? Well, that’s not normally a problem, like in WordPress I’d just filter it out, but in this locked down system, I didn’t have that option. I called the vendor, and they said “Make sure you don’t paste in smart quotes.”

mysqlThat was all fine and dandy for me but I’m not the master of the universe like that. Well, not all the time. I had people to input data for me! They were going to have to manually take the forms (Word Docs), filled in by non-techs, and copy the data into the right places in the app. And you want me to tell them they have to fix this for the non-techs? I thought about how much time that would take, and decided the best fix was to change the forms! Right?

If you’ve ever worked for a major company, you know why this was about as effective as aspirin for a root canal. No deal. So I decided to get inventive.

The only time this was a problem, these ugly quotes, was when we ran our weekly reports. This was how I found out about it, a manager complained that there was garbage instead of quotes on the form titles. Ergo: All I need to do is script something to clean them out!

Enter SQL!

# FIRST, REPLACE UTF-8 characters.
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, 0xE2809C, '"');
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, 0xE2809D, '"');
# NEXT, REPLACE their Windows-1252 equivalents.
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, CHAR(147), '"');
UPDATE `secretapp_table` SET `formtitle` = REPLACE(`formtitle`, CHAR(148), '"');

In my testing, if I ran that on formtitle, it cleaned it up for the report. This was a default report in the app, by the way, not something I had any control to change. And you wonder why I love open source? Anyhow, once I knew how this would work, I sent about scripting it. I couldn’t hook into any triggers on the app, though, because they don’t like to make it easy.

Fine, I decided. A crontab time it is! I made this simple script to run at midnight, every night, and clean up the DB:

#! /bin/bash

mysql -h "dbname-secretapp" "secretapp_db" < "quotecleaner.sql"

It worked when I ran it by hand, but it failed when cron’d. This took me some headbanging, but after reading up on how SQL works, I realized it worked when I ran it as me because I’m me! But cron is not me. I have permissions to run whatever I want in my database. Cron does not. Nor should it! So how do I script it? I don’t want the passwords sitting in that file, which would be accessible by anyone with the CMS to update it.

I went around the corner to my buddy who was a DB expert, and after explaining my situation (and him agreeing that the cron/sql mashup was the best), he asked a simple question. “Who has access to log in as you?” The answer? Just me and the admins. The updating tool for our scripts was all stuff we ran on our PCs that pushed out to the servers, so no one but an admin (me) ever logged in directly.

He grinned and wrote down this on a sticky “.my.cnf”

Google and a Drupal site told me that it was a file that was used to give the mysql command line tools extra information. You shove it in the home directory of the account, and, well, here’s ours:

# Secret App user and password

The only reason I even remembered all this was because an ex-coworker said he ran into the documentation I left explaining all of this, and was thankful. He had to have it scan the body of the form now, because the managers wanted that in the report too!