Let's assume you have a 512MB table, and you decide to alter the table to add an index to make queries faster.

How long would you expect this alter to take? Hours? Days?

Even with 7200 RPM-slow disks the alter should of finished in less then 1/2 hour.

I ran across an alter that was running for 4 days-on 512MB datasize. The reason why it ran so long is because there was a SELECT that was running preventing mySQL from performing "rename table", the last leg of the ALTER TABLE process.

Killing that SELECT released the shared lock allowing the alter to finish.


Do not KILL THE ALTER when stuck in this SHARED LOCK STATE; Do NOT then remove the temporary tablespace file "#sql-320f_106f99a2.*".

What will happen if you do remove the #sql* file by hand?

Well for one INNODB will crash the mysql instance saying it could not find the temporary table …

My new edition of the MySQL 5.1 Cluster DBA Certification Study Guide arrived today. It features a bigger format, corrections, and much better graphics. And it is $10 less expensive.

The MySQL Cluster is just different enough from the 'old fashioned' server to be confusing to long time MySQL DBAs. This book points out common problems people have getting their clusters configured. So if you are curious about how clusters work and want more than the man pages as a reference, order you copy today!

This is not the first case of open source and life saving tied together. For example, Matthew Swift, one of the …

Andrii Nikitin and son

(Credit: Zack Urlocker)

MySQL has been very good to the open-source world. In turn, I'd like to see if we can give something back.

Andrii Nikitin is one of MySQL's support engineers based in Ukraine. As Zack Urlocker recently posted on his blog, however, Andrii has a major concern right now which has nothing to do with customer support:

Andrii's son Ivan, who is 2 1/2, is in need of a bone marrow transplant operation. This will require going to a clinic in Europe that will not be covered by regular insurance. So Andrii has aksed to see if we could help raise funds. The cost is expected to be $150,000 - $250,000. A huge amount for an engineer from Ukraine to cover. But a small amount by many people could make a big difference.

Consistent with your interest and means, I'd encourage you to help …

Very cool OSS project for tracking and recovering your lost or stolen laptop. It?s a project from the University of Washington. This app sounds like a great way to address the 12,000 laptops lost per week at US airports. The UW website states: "Adeona is the first Open Source system for tracking the location of your lost or stolen laptop that does not rely on a proprietary, central service. This means that you can install Adeona on your laptop and go ? there's no need to rely on a single third party. What's more, Adeona addresses a critical privacy goal... READ MORE

Jennifer Widom just received the ACM SIGMOD Edgar F. Codd Innovations Award, and spoke about the process of database research:
"[I]t's imperative to think about all three of the critical components -- data model, query language, and system -- and in that order! We in research have a rare luxury, compared to those in industry, that we can mull over a data model for a long time before we move on to think about how we'll query it, and we can nail down a solid syntax and semantics for a query language before we implement it."I've designed languages before, and I know how hard it is to do it right, so when I was designing SQLstream's extensions …

I was asked recently by a client to help with providing a history of data in certain tables. Like most problems, there is no one single solution, and in this case there are several possible solutions. I was able to provide a database specific only solution, with just minimal impact to the existing schema.

Here is my approach, your feedback and alternative input as always a welcome.

The problem

Client: I want to keep a history of all changes to two tables, and have a means of viewing this history.

For the purposes of this solution, we will use one table, called ‘customer’ from the Sakila Sample Database.

Solution

For tables to be audited, we will introduce a new column called ‘audit_id’ which is NULLABLE, and hopefully will not affect any existing INSERT statements providing column naming (a Best Practice) is …

I was asked recently by a client to help with providing a history of data in certain tables. Like most problems, there is no one single solution, and in this case there are several possible solutions.

(Reposted from Zack Urlocker’s blog)

Andrii Nikitin, one of the MySQL support engineers located in Ukraine, has asked for help from MySQLers and so I’m sharing this information to the community at large. Andrii’s son Ivan, who is 2 1/2, is in need of a bone marrow transplant operation. This will require going to a clinic in Europe that will not be covered by regular insurance. So Andrii has aksed to see if we could help raise funds. The cost is expected to be 150,000 - 250,000 EUROS. A huge amount for an engineer from Ukraine to cover. But a small amount by many people could make a big difference.

Many MySQLers have kicked in to help out, but more is needed. Ivan’s health has taken a turn for the worse recently and the issue is now quite pressing. Even a small donation could mean the difference between life and death for Ivan.

I hope some of you who use MySQL or have young kids will join me in making a …

Last Friday and Saturday I had the pleasure of presenting DRBD-based high availability at the 2008 Zarafa Summer Camp. What’s this Zarafa thing, you ask? Here’s a quick introduction.

Zarafa is a fully LAMP-based, drop-in replacement for Microsoft Exchange developed by a Delft, Netherlands-based company. Yes, that means that all those Windows desktop users out there can use their beloved Microsoft Outlook to connect to a full-fledged collaboration solution, including email, calendaring, contact management, a global address book, and task sharing. Using native MAPI. And the server components are 100% built on Linux, Apache, MySQL, and PHP. They even use an …