Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 20

Displaying posts with tag: alter table (reset)

Why ALTER TABLE runs faster on Percona Server 5.5 vs. MySQL 5.5
+0 Vote Up -0Vote Down

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

  [Read more...]
How to monitor ALTER TABLE progress in MySQL
+1 Vote Up -0Vote Down

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

  [Read more...]
Schema changes – what’s new in MySQL 5.6?
+3 Vote Up -0Vote Down

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.


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

  [Read more...]
Implications of Metadata Locking Changes in MySQL 5.5
+6 Vote Up -0Vote Down

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  [Read more...]

MySQL – Alter table-change datatype of column with foreign key
+1 Vote Up -0Vote Down
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 having foreign key constraints. SQL for tables used in example: create database temp;use temp; drop table if exists city; drop table if exists country; […]
Progress reporting for ALTER TABLE
+3 Vote Up -0Vote Down
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
    • PROGRESS_DONE (within current stage).
  • The client receives out-of-band progress messages which it can display to the

  [Read more...]
Hot Column Addition and Deletion Part II: How it works
+3 Vote Up -0Vote Down

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

  [Read more...]
Tokutek’s Chief Scientist Discusses TokuDB v5.0
+3 Vote Up -0Vote Down

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

  [Read more...]
SQL Oddity: ALTER TABLE and default values
+0 Vote Up -0Vote Down

So, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example:

CREATE TABLE t1 (answer int);

So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy, just ALTER TABLE:

create table t1 (a timestamp);
alter table t1 alter a set default CURRENT_TIMESTAMP;

(This is left as another exercise for the reader as to what this will do – again, maybe not what you expect)

About InnoDB Index Size Limitations
+2 Vote Up -2Vote Down
This is mostly a reflection on a limitation in InnoDB that, in my opinion, has persisted for too long. I founded while reviewing the Amarok media player. The player uses MySQL in the backend, embedded or regular server, so it makes for a great source of real life data.

The Issue

By default, Amarok uses MyISAM tables. This means that if it crashes or stops unexpectedly (a logout while playing music may cause this), the latest updates to the DB are all lost. So I've been looking into using InnoDB instead to avoid loosing my playlists or player statistics.

The Problem

The limitation that bothers me is this one: "Index key prefixes can be up to 767 bytes" which has been in place for several years.
Take this Amarok table for

  [Read more...]
A Replication Surprise
+2 Vote Up -0Vote Down
While working on a deployment we came across a nasty surprise. In hindsight it was avoidable, but it never crossed our minds it could happen. I'll share the experience so when you face a similar situation, you'll know what to expect.


To deploy the changes, we used a pair of servers configured to replicate with each other (master-master replication). There are many articles that describe how to perform an ALTER TABLE with minimum or no downtime using MySQL replication. The simple explanation is:
  • Set up a passive master of the database you want to modify the schema. 
  • Run the schema updates on the passive master.
  • Let replication to catch up once the schema modifications are done.
  • Promote the passive master as the new active master.
  • The details to make this work will depend on each individual situation and are too extensive for

      [Read more...]
    Converting myisam tables to innodb
    +0 Vote Up -2Vote Down
    Why should you convert myisam tables to innodb ? For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks - due to concurrent selects and inserts, it is time for you to covert the table to innodb. The simple query which does the trick is Alter table myisam_table_name engine =
    mysql hack - altering huge tables
    +4 Vote Up -2Vote Down
    You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production. Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack
    When the ALTER TABLE privilege is not enough to run ALTER TABLE
    +4 Vote Up -0Vote Down

    I recently granted ALTER access in MySQL so a user could run the ALTER TABLE command . However after I granted the necessary privileges, the user was still not able to perform the tasks needed. Reproducing the issue using a test instance, I granted a test user the required privileges and MySQL reported no errors or warnings when the ALTER TABLE was run:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 15
    Server version: 5.1.41-log MySQL Community Server (GPL)
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    mysql> grant alter,create,insert on *.* to 'test'@localhost;
    Query OK, 0 rows affected (0.00 sec)
    mysql> show warnings;
    Empty set (0.00 sec)
    mysql> show errors;
    Empty set (0.00 sec)

    The reason I

      [Read more...]
    TYPE= disappears again (MySQL 5.4.4)
    +2 Vote Up -7Vote Down

    I like the 5.4 developments, overall. It has useful stuff and is being developed and released a reasonable pace. Good progress. While perusing the MySQL 5.4.4 changelog, one particular change drew my attention, since it’s been (re)appearing since 2006. It’s the removal of the TYPE= keyword which was obsoleted since MySQL 4.1 in favour of the ENGINE= syntax in CREATE/ALTER TABLE.

    While on the surface it may seem ok to remove the obsolete keyword, there are quite a few apps out there that use it, and that cannot be changed. So these will now be unable to use MySQL 5.4 or beyond. I filed this as a bug in 2006, MySQL bug#17501. If you’re interested in the “history of reappearance”, take a peek at the comments and their timeline. I just put in a

      [Read more...]
    Mind the SQL_MODE when running ALTER TABLE
    +0 Vote Up -0Vote Down

    The other day, a client mentioned they were getting strange results when running ALTER TABLE. The episode involved modifying an existing primary key to add an auto_increment primary key: it was “shifting” values. Say what?!

    As it turns out, it was a very special value getting changed: zero. Some fiddling revealed the underlying reason. Care to join me?

    To understand what’s going on, follow the example below as we start with an empty database, create a table and insert a few rows:

    mysql> use test;
    Database changed
    mysql> show tables;
    Empty set (0.00 sec)
    mysql> create table test_table (id int not null primary key) engine=innodb;
    Query OK, 0 rows affected (0.01 sec)
    mysql> desc test_table;
    | Field |
      [Read more...]
    Recovery features for ALTER TABLE of partitioned tables
    Employee +0 Vote Up -0Vote Down
    A feature which hasn't been so public about the implementation
    of partitioning is the support for atomicity of many ALTER TABLE
    statements using partitioned tables.

    This atomicity exists for

    Given that partitioning often works with very large tables it
    was desirable to have a higher level of security for ALTER TABLE
    of partitioned tables. To support this a DDL log was implemented.
    This DDL log will in future versions be used also for many other
    meta data statements. The DDL log will record all files added,
    renamed and dropped during an ALTER TABLE command as above.

    The design is done in such a way that the ALTER TABLE will either
    fail and then all

      [Read more...]
    Why You Want to Switch to MySQL 5.1
    +0 Vote Up -0Vote Down

    In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

    • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
    • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.
    • (more…)

    ALTER TABLE flakiness and mysql replication
    +0 Vote Up -0Vote Down

    Over the past few years of dealing with mysql in larger environments, one thing I’ve always felt that ALTER TABLE statements are flaky with replication, but could never really prove it. I never had a chance to dig into some of strange things I saw, and would tend to overlook and fix them

    While working for a client, I encountered an issue that I could no longer ignore. In order to reload a small table from a master database to a number of slaves, I simply piped the output of mysqldump into the master and expected everything to flow into the slaves.

    The bug is very specific, and probably not very common. If you send a statement like below, as mysqldump automatically adds, to your master:

    /*!40000 ALTER TABLE table_name DISABLE KEYS */;

    and have configured something like

      [Read more...]
    MySQL Command Line Help
    +0 Vote Up -0Vote Down

    I tend to use MySQL from the interactive command line shell quite a bit (SSH to your db server and type the command mysql to start). I noticed today that there is a pretty extensive built in help system in this command.

    You can access it by typing help COMMAND, so for instance if you type help ALTER TABLE you get a quick reference for constructing an ALTER TABLE statement.

    If you can't remember what command you want to use, then typing help contents is your friend. It will output:

    For more information, type 'help item', where item is
    one of the following categories:
    Column Types
    Data Definition
    Data Manipulation
    Geographic features

    So if your looking for that command that outputs all the columns in a table, but don't know what it's called, you

      [Read more...]
    Showing entries 1 to 20

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.