Database stored procedures, functions and triggers are source code of your application. Please treat them as such.

As a consultant for the past two years, when I come into a batch of code that has many stored procedures or triggers in the database, the first thing I ask is whether those stored procedures and triggers are under some kind of revision or source code control. If the developers/DBAs look at me like I’ve got two heads, then I know I’m in for a doozy of a gig because those devs are probably not doing the best job they could be doing.

And that makes sense right? You don’t call in a consultant if everything is unicorns and rainbows with your system.

Stored procedures, functions, and triggers in your database are part of your codebase. They could even be the most important and most delicate piece of your codebase.

I’ll repeat this again for total clarity… Stored procedures, functions, and triggers in your database are part of your codebase.

But not managing your codebase with a tool is a huge mistake. And yet, I see this time and time again on my gigs. And if I had a nickel for every time I saw a stored proc named with “_new” or “_new2” or “_use_this_one” in the name, I could invite Warren Buffet over to play cards and he would consider it.

For managing the source code of your stored procedures on SQL server, there’s even some commercial tools available that take your database objects and chucks them into your source code repo. Or you can always just create a “sql” directory in your source code repository and store the objects there.

There are a lot of ways to manage this problem. Ignoring it, is not managing the problem, it’s making it worse.

Continue reading

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

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

“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