OneFreeVoice

March 8, 2008

Using information_schema to write code for you

Filed under: Code, MySQL — Tags: , — Gregory Haase @ 9:15 pm

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…)

March 1, 2008

Resetting auto_increment in MySQL

Filed under: MySQL — Tags: , — Gregory Haase @ 11:01 pm

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

Back to Blogging

Filed under: Personal — Gregory Haase @ 11:01 pm

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.

Theme: Silver is the New Black. Get a blog at WordPress.com

Follow

Get every new post delivered to your Inbox.