Pythian Group

Overview

We know that having the chance to split a table in different and smaller chunk helps.
It helps because performance searches, insert, index handling and data management as well.
All fine we are all happy, and very often we push on our customer to go for partition in order to solve part of their problems.
What happens quite often is that customer (and sometime MySQL dba as well) see partitions as separate tables.
Unfortunately is not like that, there are some operations that are (still?) creating unexpected results.
One of this is the OPTIMIZATION of a partition.

The case

Some time ago, almost 16 months I was at customer site and we were working with MySQL 5.5 rc.
We were really happy with the new version of MySQL, and being working for MySQL/SUN I was quite proud of it as well.
Then we run the ALTER TABLE X OPTIMIZE PARTITION Y;

Suddenly we realize something was not working as expected because all partitions had temporary tables.
We know and were expecting the LOCK while doing the exercise, but this was not expected at all.

I was attending several internal presentations, and I was in many internal calls,
all stating that the operation should not affect all partitions but only one.
It was not nice, and it was a bug (42822) marked non critical, referring to another one 46158,
which was close because referring to the previous as a duplicate.

Funny thing at the end is … we still have it in 5.5.12.
Read the rest of this entry . . .

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:
Read the rest of this entry . . .

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 | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test_table (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table (id) values (0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

mysql>

Now let’s change our PK and make it auto_increment Read the rest of this entry . . .

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.

Read the rest of this entry . . .