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

Bug #1068562 reported by Simon J Mudd
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
High
Brian Fraser

Bug Description

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.

Related branches

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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

tags: added: pt-online-schema-change risk
summary: - pt-online-schema-change usage when RENAMING columns DROPs the content of
- the column rather then copy it.
+ pt-online-schema-change loses data when renaming columns
Changed in percona-toolkit:
status: New → In Progress
importance: Undecided → High
assignee: nobody → Daniel Nichter (daniel-nichter)
milestone: none → 2.1.6
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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.

Changed in percona-toolkit:
milestone: 2.1.6 → none
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

(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.)

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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?

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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.

Revision history for this message
Simon J Mudd (sjmudd) wrote :

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.

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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).

Changed in percona-toolkit:
assignee: Daniel Nichter (daniel-nichter) → nobody
status: In Progress → Triaged
milestone: none → 2.1.6
Brian Fraser (fraserbn)
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
Brian Fraser (fraserbn)
Changed in percona-toolkit:
status: Triaged → In Progress
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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.

Changed in percona-toolkit:
milestone: 2.1.6 → none
Brian Fraser (fraserbn)
Changed in percona-toolkit:
status: In Progress → Fix Committed
Revision history for this message
Simon J Mudd (sjmudd) wrote :

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

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

Glad it worked out well for everyone.

Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers