I have been taking a serious look at the event scheduler in MySQL 5.1, and I thought I would share some of my initial impressions. There is no doubt that having an event scheduler is a great bonus for the database. It’s usefulness in the enterprise, however, may be limited. While we are preparing to move forward with it, it hasn’t been without struggle. I will describe the pros and cons we’ve encountered so far. (more…)

I prefer to install mySQL using the pre-compiled binaries. Depending on the environment, these usually go in either /opt or /usr/local. When you choose this type of install, chances are you are going to need to ensure that you somehow configure your system so that the mysql binaries end up in your PATH.

If you are using RedHat or CentOS, a good way to do this is to add a custom file inside of the /etc/profile.d/ directory. I create one called “mysql.sh”:

if ! echo ${PATH} | /bin/grep -q /opt/mysql/bin ; then
        PATH=/opt/mysql/bin:${PATH}
fi

This script simply checks you PATH for mysql and adds it if it’s not already there.

If you are using Ubuntu, you can edit /etc/environment:

PATH="/usr/local/bin:/usr/bin:/bin:/opt/mysql/bin"
LANG="en_US.UTF-8"

You can see that I’ve just added “:/opt/mysql/bin” to the end of the PATH line.

There are lots of other ways to do this, and I’m sure some distributions not covered here do it differently. If you know of a different or better way, I look forward to your comments.

Back when I was doing a lot of work with Oracle, I learned to lean heavily on the SYSTEM views - Oracle’s equivalent to the INFORMATION_SCHEMA database. These views can really help you when it comes to writing dynamic SQL in stored procedures, or just taking quick shortcuts while you’re writing code. Or, if you’re like me, using the information schema can really help you limit the number of types you make.

Let’s take a simple case. You want to insert a row into a table that has multiple columns. You can hand type all those columns, but for me, that’s takes time and by the time I’m done debugging all the typos, it takes a really long time. Instead I just fire up a separate mysql client in silent mode and run a simple query:

SELECT concat(column_name,', ') 
  FROM information_schema COLUMNS 
 WHERE table_schema = "myschema" 
   AND table_name = "mytable";

(more…)

Today we were doing some basic scaling exercises on some code, which resulted in several million extra rows in one of our tables in one of the development environments. Rather than refresh the data, we decided just to prune those new records and reset the auto-increment value.

Imagine my surprise when executing the following code hadn’t returned in more than 30 seconds:

ALTER TABLE sometable AUTO_INCREMENT = 1;

Imagine my further surprise when my query returned the following:

Query OK, 434866 rows affected (4 min 58.41 sec)
Records: 434866  Duplicates: 0  Warnings: 0

My only experience with this sort of operation is with renumbering Oracle sequences, so I was initially a bit baffled why resetting this number takes longer than a few milliseconds. I was even more concerned as to why every single row in the table was affected.

It didn’t take me long to figure out what was happening though. It has to do with the way mySQL alters tables. According to the reference manual:

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed.

One would hope that changing the auto_increment value was one of those few cases where this occur. I re-executed the query and checked the file system and sure enough the temporary files were there:

bash-3.1$ ls *sql*
#sql-810_876d.frm
#sql-810_876d.ibd