pt-online-schema-change loses data when renaming columns

[Simon J Mudd]

root@server [db]> show create table simon\G
*************************** 1. row ***************************
       Table: simon
Create Table: CREATE TABLE `simon` (
  `id` int(11) NOT NULL,
  `old_column_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root@server [db]> select * from simon;
+----+-----------------+
| id | old_column_name |
+----+-----------------+
| 1 | old data |
| 2 | old data |
| 3 | old data |
+----+-----------------+
3 rows in set (0.00 sec)

root@server [db]> exit
Bye
[root@server ~]# pt-online-schema-change F=/root/.my.cnf,D=db,t=simon --alter "change old_column_name new_column_name varchar(255) NULL" --execute
Altering `db`.`simon`...
Creating new table...
Created new table db._simon_new OK.
Altering new table...
Altered `db`.`_simon_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 3 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `db`.`_simon_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `db`.`simon`.
[root@server ~]# mysql db
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16106
Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@server [db]> select * from simon;
+----+-----------------+
| id | new_column_name |
+----+-----------------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+----+-----------------+
3 rows in set (0.01 sec)

root@server [db]>

Expected behaviour: new_column_name should contain "old data".

This behaviour IS dangerous and at least in the rpm: percona-toolkit-2.1.2-1 there is NO mention about this dangerous and unexpected behaviour.

[Daniel Nichter (daniel-nichter)]

Naturally, "it's not supposed to do that." Have you tried with 2.1.5? I'll look at this in any case...

[Daniel Nichter (daniel-nichter)]

Indeed, this happens as described, and the test case re-confirms it, but it is documented under --alter:

"""
Columns cannot be renamed by dropping and re-adding with the new name.
The tool will not copy the original column's data to the new column.
"""

The tool does not parse the --alter statement because that would be too complicated and error-prone. Doing such would allow us to detect this kind of operation and potential data loss, so it's a toss-up. I'll thinking more about how to help prevent this kind of surprise.

[Daniel Nichter (daniel-nichter)]

(Btw, another complication in detecting this case is that a user might intend to do this--seems odd but there's a lot of use-cases out there.)

[Daniel Nichter (daniel-nichter)]

I just had an idea: a post-alter or -copy plugin. Frankly, only the user knows what the intended outcome should be. We'll never get it right for the myriad cases in which the tool must function. But we could implement a post-alter or post-copy plugin that returns lets the user's code check things and if all is OK then the tables are swapped, else the tool bails out. What do you think about that?

[Daniel Nichter (daniel-nichter)]

Clarification: when I first said "it's not supposed to do that", that was before I understood the specific issue and that this is a documented limitation. The tool isn't supposed to lose data in normal cases, and so far no one has ever reported that it has.

[Simon J Mudd (sjmudd)]

I'm a little disappointed.
I'm sure it can't be too hard to figure out a column name is changed (yes by parsing the syntax), having to do anything else seems rather lame.

The syntax for changing a column is reasonably simple:

ALTER TABLE .... CHANGE [COLUMN] old_column new_column other stuff , .....

If old_column == new_column then there's no column change at all, this is an alternative form to MODIFY COLUMN xxxxx other stuff.

If we need to say: yes "parse this better please..." then fine, but the point is columns do change name. mysqld can do this fine, so a tool which is trying to mimic the behaviour "online" should I believe try to do the same. (even if that's a bit harder). I'm sure I'm not the only user of pt-online-schema-change who would find this helpful.

And why not while you think about this, consider that if the ALTER statement matches /CHANGE\s+(COLUMN\s+)?(\S+)\s+(\S+)/i (or similar) that you perhaps issue a warning that the user may be about to shoot himself in the foot (and lose data) if lc($3) ne lc($4).

All that said, I've already asked Oracle to implement this inside mysqld. Yes, that's the ideal place to put it and a good tool like this would then be unnecessary. That said I can hope, but I'm also realistic that this may never happen so pt-osc is likely to be the best alternative about.

[Daniel Nichter (daniel-nichter)]

Checking the --alter for patterns like /CHANGE\s+(COLUMN\s+)?(\S+)\s+(\S+)/i sounds like a good idea given that the user could turn it off if 1) they're aware of the consequences or 2) the match is a false-positive so they file a bug and ignore the warning. I think we'll implement this, but we may not have time to make it into 2.1.6 (depends on when 2.1.6 releases and what comes up between now and then).

[Daniel Nichter (daniel-nichter)]

Since this wasn't a bug strictly speaking, but rather a documented limitation, I made https://blueprints.launchpad.net/percona-toolkit/+spec/pt-osc-rename-columns It's fixed and will be in 2.1.6 all the same; this is just housekeeping.

[Simon J Mudd (sjmudd)]

Thanks guys. This is really a nice fix that will make people's life much better. Thanks for taking the time to do that.

[Daniel Nichter (daniel-nichter)]

Glad it worked out well for everyone.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-334