Diary of a mysql database problem in 15 minutes

1:42pm – frantic instant message comes in from a smallish project that their site is down.

1:42pm – I ssh into both the webserver and the database server. Database server takes awhile to actually get a login prompt.

1:43pm – Email containing frantic instant message with importance set to high ( side note- people still use importance in emails? people still email after they’ve contacted you another way? people still email? )

1:44pm – I run “top” on both the webserver and the database server. ( There isn’t any other monitoring since it’s a smallish project. there probably should be. Will have to get client to pay for that.) Webserver looks fine. Database server load is really high, around 5 when it’s usually around 0.30. This is a mysql linux server.

1:45pm – exit “top” and open a mysql command line prompt.

1:46pm – execute “show processlist” in mysql. A ton of queries show up, all behind one sinister looking one on a new table that’s been set up recently.

1:47pm – execute “explain sinister-looking query” in mysql to see how mysql is executing that query.

1:48pm – possible_keys = NULL

1:49pm – Ruh roh. Execute “alter table add index…”

1:50pm – database working.

1:51pm – database working.

1:52pm – database working.

1:53pm – database done working. execute “show processlist” to see 2 queries left on stack.

1:54pm – Execute “top” command, load is down to 2.7 and falling. 1.9 now. 0.70 now.

1:55pm – Declare crisis averted via instant message and email. Wonder again if people still use email.

1:56pm – Assign developer responsible for non-indexed table the task of making sure the schema changes get into source code control.

1:57pm – Go back to regular work.

Continue reading

Write helpful code comments or none at all.

Stumbled onto the following comment in some code I’m working on:

//Code updated by Crappy Developer – 06/22/2011. Fix for Prod problem.

This is a terrible code comment. I would rather you not even put this comment into the code.

First off, I’ve replaced the name of the developer with “Crappy Developer” to protect their public persona and since that’s how they are now known to me.

The comment “Fix for Prod problem” tells me nothing. Aren’t all problems Production problems? Otherwise, they’re not problems. What was the actual bug? Under which circumstances was it reproducible? How did you fix it? Any tricky business logic involved here?

And then there’s the “//Code updated by..” You might think that giving the date of the fix and the developer’s name is at least a little bit helpful, but all of that can be discovered with “git blame.” This dev probably doesn’t know “git blame” exists. I’ll have to send him the man page on that.

So the next time you leave a comment in the code, make sure its worth the time of the next developer that might see it. And make sure they aren’t just going to blog about how terrible your comment was. You’re wasting time with lame code comments, so please stop it.

Continue reading

Using Gmail “Details” link to discover older IP addresses

A project that I’m working on has a strict firewall setup which restricts SSH access to only internal machines and those temporary machines outside the network that need access ( like if you’re working from home ). So as my IP address changes at home, I need to notify the service that my IP address has changed from A to B. The service prefers if I can give them both the old address and the new address because they’ll take out the old address from the firewall rules and add access for the new IP address. I was keeping this list on a pad by my desk but today I found myself downstairs, away from that pad, so I couldn’t find my previous IP address, just the new one.

Fearing that I would have to walk ALL THE WAY upstairs to get that pad, I thought for a few seconds and remembered the “Details” link in the footer of my gmail page. Clicking “Details” in the footer of gmail will show you the last 10 instances of logins/refreshes to your gmail page. Sure enough, I was able to find the number 7 entry was from last night and showed my previous IP address. I copied it into my firewall request and got back to my work.

Continue reading

MySql tip for displaying query results vertically

I have a need at the day job to view mysql records via the mysql command line client. Some of the tables I’m looking at have 100 to 200 columns so it’s pretty annoying to view this data horizontally across the screen. If you end your queries in the mysql command line client with \G instead of a semicolon, then it will display the results vertically down the page. Try it out!

Continue reading

How to drop multiple tables in mysql at once

There are a couple of different ways to remove multiple tables from MySQL at one time. Almost all of the ways of dropping many mysql tables at once are dangerous and that’s probably why the MySQL developers have overlooked this “feature.” You run the risk of dropping tables outside of the ones you meant to drop.

But if you’re dead set on this, here’s a stored procedure for dropping multiple MySQL tables at one time that does the job quick and easy.

First you have to add the procedure to the schema. You can technically add this to any schema that you have access to. The way I use it is from a “global” schema that needs to make changes to other schemas that all have similar names. The MySQL user calling the procedure needs to be able to perform the DROP as well as READ the information schema tables. Don’t mess with your permissions unless you have to however.

Calling the procedure can be done with:

So in this case, I’m dropping all the tables that start with “jjj” in the “test” schema. The “pattern” parameter can be any valid MySQL “LIKE” parameter. In a MySQL client, it looks like this:

Now, obviously, if you put in a pattern that returns no resulting table names, then you’ll get an error. But fixing that error is left to the reader as an exercise ( do feel free to contribute that back to me!)

Any questions or comments? Do you have a better way to do this?

Continue reading

Explode a string with no delimiter in PHP

I needed a way to split a string by the character and put the results in an array. Sounds like the perfect job for the explode function right? Well, according to the manual:

If delimiter is an empty string (“”), explode() will return FALSE

That’s a bummer.

I also tried str_split(‘whatever’) but that has some problems with foreign strings. But we can do something like this:

Enjoy! Let me know what other ways you can come up with to do something similar.

Continue reading

SQLSTATE[HY000]: General error: could not call class constructor

SQLSTATE[HY000]: General error: could not call class constructor

This rather vague error message has been popping up in some of my integration tests lately. It’s a PHP error in PDO querying MySQL that happens when you’re using the setFetchMode method of the PDOStatement. If you use the fetchMode of “FETCH_CLASS” you can get this error in one of three ways:

  • The class you specified has not been included/required so when PDO gets results, it cannot create an object of the class you specified since it doesn’t have that class definition.
  • Properties on the class you specified have been marked as protected or private and there aren’t any setters for them so PDO can’t set the properties on the new object.
  • You used the optional third argument “ctorargs” to give constructor arguments on your new object however your arguments are incorrect and don’t match those on the class.

Hope this helps other poor souls out there trying to figure this error out.

Continue reading

“show tables not like” in mysql

I was looking for the tables in my database not starting with “p_”. MySQL doesn’t let you execute this command:

Which is odd considering it will allow “like” here:

Instead, you have to use the information schema like this:

Continue reading

Eclipse update conflict “Do you want to overwrite the changes made on the file system”

Earlier today I kept running into a problem using Eclipse (really Zend Studio built on Eclipse) with a project running on the network that I accessed via a mapped drive on my Linux machine. The problem occurred during file saving and looked like this:

Eclipse update conflict “do you want to overwrite the changes made on the file system?”

The only way I was really able to fix this problem was to make sure that both my machine and the networked machine in question had the same exact time. So I busted out ntpdate and made sure both machines were updating their time at least once a day using a command something like this:

Continue reading

Export from WordPress blog and Import to Blogger

This script exports your posts and categories from your locally hosted WordPress blog and imports them into a new Blogger blog online. The script is based on the Blogger examples for working with PHP and blog data.

Code to export from hosted WordPress blog and import to Blogger at github

You need to have the Zend Framework up and running on your system as it contains all the GData libraries for working with Google data in PHP. This script doesn’t use any of the MVC, so you just need to have the files somewhere that you can require them.

You need to edit line 8 to point to the path of your Zend Framework. You also need to update the database connection variables right below that to match your wordpress blog.

Some caveats:

  • Blogger can only import 50 blog posts per day before tripping some anti-spam protection. So the SQL “limit” clause around line 322 will need to be adjusted. It is set to get the first 50 posts and publish them.
  • WordPress “categories” become Blogger “labels”
  • Blogger forces any imported comments to the blog posts to originate from you, the author, of the blog. So I have omitted these from my script, figuring it would look crazy to be talking to myself. You could hack this script to add those back in.

What’s great about Blogger is that you can create a new test blog and run this script, which allows you to select which Blogger blog you wish to update and go from there.

The usage is:

php xfer_to_blogger.php –user=email@email.com –pass=password

The email and password here are the ones you used in setting up your Blogger account. Let me know how you make out and if you have any suggestions for the code.

Continue reading