When ALTER TABLE crashes MySQL server it leaves orphaned records in InnoDB dictionary. It is annoying because next time you run the same ALTER TABLE query it will fail with error:

ERROR 1050 (42S01) at line 1: Table 'sakila/#sql-ib712' already exists

The post explains why it happens and how to fix it.

When you run ALTER table InnoDB follows the plan:

1. Block the original table
2. Create an empty temporary table with the new structure. The name of the new table is something like #sql-ib712.
3. Copy all records from the original table to the temporary one
4. Swap the temporary and original tables
5. Unblock the original table

The temporary table is a normal InnoDB table except it’s not visible to a user. InnoDB creates a record in the dictionary for the temporary table as for any other table.

If MySQL crashes in the middle of the …

Some of us Perconians are at OpenStack summit this week in Atlanta. Matt Griffin, our director of product management, tweeted about the turbo-hipster CI talk about their experience of ALTER TABLEs running faster on Percona Server. Oracle’s Morgan Tocker then tweeted in response, asking why this was the case. I decided that the simplest way to answer that was here in this post.

The reason for this is the …

While working on a recent support issue as a Percona Support Engineer,  I got one question from a customer asking how to monitor ALTER TABLE progress. Actually, for MySQL 5.5 and prior versions, it’s quite difficult to ALTER the table in a running production environment especially for large tables (with millions records). Because it will rebuild and lock the table affecting the performance as well as our users. Therefore even if we start ALTER it’s really important to know when it will finish. Even while creating the index, ALTER TABLE will not rebuild the table if fast_index_creation is ON but still it might lock the table.

…

Among many of the improvements you can enjoy in MySQL 5.6, there is one that addresses a huge operational problem that most DBAs and System Administrators encounter in their life: schema changes.

While it is usually not a problem for small tables or those in early stages of product life cycle, schema changes become a huge pain once your tables get a significant amount of data. Planning for maintenance is becoming more and more difficult, and your worldwide users want the service to be up and running 24/7, while on the other hand, your developers desire to introduce schema changes every week.

PITA

But what is the real problem here? Let me illustrate very typical case:

Session1> ALTER TABLE revision ADD COLUMN mycol tinyint;
Query OK, 1611193 rows affected (1 min 5.74 sec)
Records: 1611193  Duplicates: 0 …

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata Locking behavior prior to MySQL 5.5.3

Prior to MySQL 5.5.3 a statement that opened a …

One of my colleague asked me : « Is an ALTER TABLE with many specifications is faster than 1 ALTER TABLE by specification ? »

The answers seems quite intuitive, anyway figures are better than a long speech...

Context :

MySQL Community Server 5.5.22

InnoDB table with 8 539 238 rows.

17 columns with INT, CHAR, VARCHAR, TEXT, DATETIME, … fields

Schema alteration is a big headache especially when it has already reached to production and things get worse when the relations are involved.Below is a short example for altering tables…

The post MySQL – Alter table-change datatype of column with foreign key first appeared on Change Is Inevitable.

I just finished my last MariaDB 5.3 feature before we go beta:

Progress reporting for ALTER TABLE, LOAD DATA INFILE etc.

This is a feature that I think every MySQL user who has ever waited for an ALTER TABLE to finish has wanted for a long time! I know people who have written scripts to monitor the size of the result files to try to estimate how much an ALTER TABLE has progressed.

Progress reporting means that:

• There is a new column Progress in SHOW PROCESSLIST which shows the total progress (0-100 %)
• INFORMATION_SCHEMA.PROCESSLIST has three new columns which allow you to see in which process stage we are and how much of that stage is completed:
  • STAGE
  • MAX_STAGE
  • PROGRESS_DONE (within current stage).
• The client receives out-of-band progress messages which it can display to the user …

Hot Column Addition and Deletion (HCAD)

In the previous HCAD post, I described HCAD and showed that it can reduce the downtime of column addition (or deletion) from 18 hours to 3 seconds. In fact, the downtime of InnoDB is proportional to the size of the database, whereas the downtime for TokuDB 5.0 depends on the time it takes for MySQL to close and reopen a table — a time that’s independent of database size. Go ahead and build bigger tables. The HCAD downtime for TokuDB won’t increase.

You may be wondering how we do HCAD. Here goes:

Under the hood

TokuDB is based on Fractal Tree indexing, one of the cools features of which is that they replace random I/O with sequential I/O. The way this happens has an impact on how HCAD happens, …

Running with Big Data

It’s spring here in Boston, though one could hardly tell (still barely hitting 40°F). So, for those stuck indoors working out on the treadmill, or those lucky enough to do a workout outdoors, we’ve got a great podcast. Our Chief Scientist and co-founder Martín Farach-Colton had the privilege of sitting down with Sheeri Cabral and Sarah Novotny for their weekly MySQL Database Community Podcast (OurSQL Episode 39). In it, he speaks about Tokutek and TokuDB v5.0, which was just released last week (see …