MySQL Bugs: #35190: Can't upgrade MySQL 6 without mysqldump first

Bug #35190	Can't upgrade MySQL 6 without mysqldump first
Submitted:	10 Mar 2008 19:09	Modified:	6 Oct 2008 16:51
Reporter:	Kevin Benton (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0.4	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	qc triaged

Description:
The following announcement went with the release of 6.0.4:

* Incompatible Change: Because of a change in the format of the
   Falcon pages stored within Falcon database files, Falcon
   databases created in MySQL 6.0.4 (or later) are not compatible
   with previous releases, and existing Falcon databases are
   incompatible with MySQL 6.0.4 (and later versions. You should
   dump Falcon databases using mysqldump before upgrading, and
   then reload them after the upgrade. For more information, see
   Section 4.5.4, "mysqldump --- A Database Backup Program."

Reading this, my *perception* is that by upgrading, MySQL will not only not use existing data after the upgrade, but also potentially destroy that data in the process.  My hope would be that MySQL would either a) upgrade existing databases to the new format, or b) refuse to start without a special startup option acknowledging that the user has taken appropriate steps to prevent data loss.

I understand that this is just Alpha quality software, however, my perspective on it is, if upgrading will cause more problems than not, I'm going to stop upgrading and therefore, depart from the community of Alpha testers.  As I see it, preventing data loss should be the #1 priority of every release even if it requires calling a special program the first time the new code is run from mysqld to deal with incompatible changes in the underlying data structures.

For those that are using MySQL 6.*, it seems critical to me to know that no matter when an upgrade is done, data can't be lost because of a predictable upgrade.  Burying a notice like this multiple pages into release notes to find out that we can't upgrade seems to be a train-wreck waiting to happen.  Even though it is Alpha and it's not "approved" for production use, I'm sure that others want to know that their data will not suddenly disappear because they upgraded.  If nothing else, I feel that this should have been the first release note and further clarified to describe exactly what is expected when users choose to upgrade.

How to repeat:
See above

Suggested fix:
See above

Thank you for the report.

Feature request verified as described:

either a) upgrade existing databases to the new format, or b) refuse to start without a special startup option acknowledging that the user has taken appropriate steps to prevent data loss.

Current 6.0.4 behavior with table t1 created in version 6.0.3 is:

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             | 
+----------------+
1 row in set (0.00 sec)

Sergey, If an upgrade happens without exporting the data first, the data is not lost, it just cannot be found.  So maybe you can find a better error message that we can return.

> [2 Apr 20:20] Kevin Lewis
> Sergey, If an upgrade happens without exporting the data first, the data is
> not lost, it just cannot be found.  So maybe you can find a better error
> message that we can return.

Kevin - if the data cannot be found, it's lost.  Reporting a "better error message" doesn't make the data available.  The fix for this needs to prevent data from becoming inaccessible for any reason (lost or cannot be found).  I completely disagree with the D5/R2/E3 evaluation of this bug.

The perception is that because MySQL reports the data isn't there, that this is truly S1.

The data can not be read by the most recent version of Falcon.  Restoring
the previous version will cause the data to become available again.  The
error message should indicate that access is being attempted by the wrong
version of Falcon.

Ann - again - this is about perception - users often upgrade without reading detailed release notes first.  If the installation specifically warns the user that upgrading will make data unavailable (whether lost or not) and forces the user to ack. the warning, I am as certain as I can be that users will appreciate it.  Users will be upset if they aren't warned and are forced to downgrade so they can grab data that didn't get dealt with in the upgrade, then re-upgrade and manually apply the "old" data back to the "new" table(s).

Without this kind of functionality, I'm less likely to stick with testing MySQL because it's difficult to know if I'm going to run into more of this type of incompatibility and not get warned.

One thing I learned a *long* time ago when asking for volunteers to test - honor the tester's time as much as possible - asking a tester to read pages of release notes to find out that there's an incompatible change made that prevents them from simply upgrading without backing data up first and restoring (but only part of that data because we don't want to overwrite certain types of data) - that seems like a lot of work for a tester to go through.  I want my testers to spend the largest part of their time on testing changes not trying to figure out how to install my latest release without losing data.

If Falcon detects a incompatible data, then it has to refuse to start. Period. No error message on client, no starting at all, no strange ERROR 1146 (42S02): Table 'test.t1' doesn't exist. We have to write into the error log, that the database has to updated.

> [3 Apr 19:10] Hakan Kuecuekyilmaz
> If Falcon detects a incompatible data, then it has to refuse to start. Period. 
...

Hakan, I almost agree with you.  When upgrading, it seems to me that the upgrade process ought to catch this type of problem and self-correct or at least warn the user that continuing beyond a certain point will result in the inability to access data.  Once beyond that point, users ought to be able to start the server and replace tables stored in old formats of Falcon if not self-corrected.  If the upgrade can self-correct, then I agree with you - refuse to start.

In any case, it seems that providing a way to upgrade old format Falcon tables as a part of the installation process without down-grading is necessary.  If that means providing a separate program that can read the old format files and re-write them to the new format, that would be great!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46182

ChangeSet@1.2655, 2008-04-29 15:34:12+05:00, svoj@mysql.com +1 -0
  BUG#35190 - Can't upgrade MySQL 6 without mysqldump first
  
  Falcon silently succeedes to open incompatible data files
  (e.g. created by different version) on server start.
  
  With this fix falcon refuses to start if it mets incompatible
  data files.

Wow, that was easier than we thought!

Was pushed to 6.0.6.

Documented in the 6.0.6 changelog as follows:

        Previously, Falcon failed silently when attempting to read incompatible
        datafiles created by an earlier version of the storage engine. Now, when
        Falcon encounters such datafiles, it refuses to start, and an
        appropriate error is issued instead.

Thanks all - great update. :-)