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

I took a very long hiatus (over a year) from posting here. A lot of water has gone under the bridge since January 1st 2007. Most of the people who read this have probably heard me say on occasion that the reason I don’t blog that often is because I don’t have anything useful to contribute. That’s more of an excuse than anything else. I still believe that the contents here should be useful to someone, at least to myself. The problem is that I’m not blogging about that stuff either.

If you are a frequent visitor of this blog, I hope you start to see regular content again. I won’t be posting every day, and maybe not every week - but I should be posting at least a couple of times a month. I’m doing some really interesting things these days, and some of them are definitely worth sharing.

If you’ve been here before, you might have noticed that there is a new tagline above. Since most of my day-to-day revolves around databases, that’s pretty much most of what you’re going to get here. You’ll still get a smattering of GNU/Linux and other code stuff (Perl, PHP, etc) but only to the extend that I stumbled upon something in my job as a DBA.

You’ll also see a new look about the place. It’s pretty much the same template I had before. I moved the sidebar to the right again, and I changed the colors and graphics around. I also edited all the categories and re-categorized all the links.

So welcome (or welcome back) to onefreevoice.com - a blog about databases and stuff.

Until yesterday, my collection of digital photos was a complete mess. We had pictures archived away in multiple tar files on multiple computers, and duplicates everywhere. It was hard to find a picture, and unless you really knew exactly what you were looking for and where it was, it was impossible. Yesterday, I googled for a good solution and I came upon This informative post about photo organization in linux. It was the perfect solution. (more…)

I’m slowly adding computers to my network now, and I’m discovering that not setting up a proper name convention when I only had 2 or 3 PCs is going to now be a problem. I want to think of a standard now while I’m in the process of moving things around and refining the network - before things really get out of hand. (more…)