MySQL Bugs: #40021: Renaming view fails, archived .frm for view is missing after downgrade

Bug #40021	Renaming view fails, archived .frm for view is missing after downgrade
Submitted:	14 Oct 2008 14:48	Modified:	8 Dec 2008 17:09
Reporter:	Vemund Østgaard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.1.29	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
There is a problem related to downgrading a 5.1.29 database (to 5.1.28) and later doing RENAME TABLE on a view that existed before the database was downgraded. In a upgrade/downgrade system test, this fails with:

mysqltest: At line 2658: query 'RENAME TABLE upgrade_v70 TO upgrade_v700' failed: 6: Error on delete of './viewdb/arc/upgrade_v700.frm-0001' (Errcode: 2)

The problem does not occur if the database is downgraded using mysqldump, only if it is downgraded by restarting mysqld using the old version (5.1.28) and running the bin/mysql_upgrade script.

The problem is bery likely caused by the changes done in 5.1.29 as part of fixing http://bugs.mysql.com/bug.php?id=17823

Gleb explained on IRC:

<gleb> view's .frm contains "revision" number of that .frm
<gleb> it seems like this revision number is used for backup file number generation
<gleb> so, if I cleanup all [unused obsolete] backups, and if you "downgrade" your base to pre-fix version...
<gleb> old server will start backup generation not from 001, but from 002 for ex.
<gleb> so we'll miss 001

<gleb> BTW it may be a serious problem: we loose our view if RENAME fails
<gleb> it must be documented (at least)

<gleb> BTW the server saves your .frm that was killed by failing RENAME TABLE. it is available with the .frm~ extension

How to repeat:
The problem reproduces every time I do:

1. Create a view upgrade_v70 in 5.1.29
2. Rename view upgrade_v70 to upgrade_v700
3. Rename upgrade_v700 back to upgrade_v70
4. Downgrade database by restarting mysqld with older version (5.1.28) and running mysql_upgrade script.
5. Try to rename view upgrade_v70 to upgrade_v700 again, which fails.

Minimal fix (5.0) is:

=== modified file 'sql/sql_view.cc'
--- old/sql/sql_view.cc	2008-09-30 12:50:28 +0000
+++ new/sql/sql_view.cc	2008-10-14 17:40:58 +0000
@@ -716,9 +716,6 @@ static File_option view_parameters[]=
  {{(char*) STRING_WITH_LEN("with_check_option")},
   my_offsetof(TABLE_LIST, with_check),
   FILE_OPTIONS_ULONGLONG},
- {{(char*) STRING_WITH_LEN("revision")},
-  my_offsetof(TABLE_LIST, revision),
-  FILE_OPTIONS_REV},
  {{(char*) STRING_WITH_LEN("timestamp")},
   my_offsetof(TABLE_LIST, timestamp),
   FILE_OPTIONS_TIMESTAMP},

The same problem appeared in 5.0.72 upon 'live' downgrade to 5.0.70.

The same manual scenario as initially described, with the only correction -- rename starts producing the error upon the 4th view modification, not the 3rd. 

5.0.70 downgrades to 5.0.68 all right.

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/58362

2711 Gleb Shchepa	2008-11-10
      Bug #40021: Renaming view fails, archived .frm for view is
                  missing after downgrade
      
      Obsolete arc/ directory and view .frm file backup support
      has been removed by the patch for bug 17823. However, that
      bugfix caused a problem with "live downgrades" of the
      server: if we rename some view 4 times under 5.1.29/5.0.72
      and then try to rename it under 5.1.28/5.0.70 on the same
      database, the server fails with a error:
      
        query 'RENAME TABLE ... TO ...' failed: 6: Error on
        delete of '....frm-0001' (Errcode: 2)
      
      Also .frm file of that view may be lost (renamed to .frm~).
      
      The server failed because it tried to rename latest 3
      backup .frm files renaming the view: the server used an
      integer value of the "revision" field of .frm file to
      extract those file names. After the fix for bug 17823 those
      files were not created/maintained any more, however the
      "revision" field was incremented as usual. So, the server
      failed renaming non existent files.
      
      This fix solves the problem by removing the support for
      "revision" .frm file field:
      1. New server silently ignores existent "revision" fields
         in old .frm files and never write it down;
      2. Old server assumes, that missing "revision" field in new
         .frm files means default value of 0.
      3. Accordingly to the fix for bug 17823 the new server
         drops arc/ directory on alter/rename view, so after
         "live downgrade" old server begins maintenance of the
         arc/ directory from scratch without conflicts with .frm
         files.

See email

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/58681

2711 Gleb Shchepa	2008-11-13
      Bug #40021: Renaming view fails, archived .frm for view is
                  missing after downgrade
      
      Obsolete arc/ directory and view .frm file backup support
      has been removed by the patch for bug 17823. However, that
      bugfix caused a problem with "live downgrades" of the
      server: if we rename some view 4 times under 5.1.29/5.0.72
      and then try to rename it under 5.1.28/5.0.70 on the same
      database, the server fails with a error:
      
        query 'RENAME TABLE ... TO ...' failed: 6: Error on
        delete of '....frm-0001' (Errcode: 2)
      
      Also .frm file of that view may be lost (renamed to .frm~).
      
      The server failed because it tried to rename latest 3
      backup .frm files renaming the view: the server used an
      integer value of the "revision" field of .frm file to
      extract those file names. After the fix for bug 17823 those
      files were not created/maintained any more, however the
      "revision" field was incremented as usual. So, the server
      failed renaming non existent files.
      
      This fix solves the problem by removing the support for
      "revision" .frm file field:
      1. New server silently ignores existent "revision" fields
         in old .frm files and never write it down;
      2. Old server assumes, that missing "revision" field in new
         .frm files means default value of 0.
      3. Accordingly to the fix for bug 17823 the new server
         drops arc/ directory on alter/rename view, so after
         "live downgrade" old server begins maintenance of the
         arc/ directory from scratch without conflicts with .frm
         files.

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/58767

2713 Gleb Shchepa	2008-11-14
      Bug #40021: Renaming view fails, archived .frm for view is
                  missing after downgrade
      
      Obsolete arc/ directory and view .frm file backup support
      has been removed by the patch for bug 17823. However, that
      bugfix caused a problem with "live downgrades" of the
      server: if we rename some view 4 times under 5.1.29/5.0.72
      and then try to rename it under 5.1.28/5.0.70 on the same
      database, the server fails with a error:
      
        query 'RENAME TABLE ... TO ...' failed: 6: Error on
        delete of '....frm-0001' (Errcode: 2)
      
      Also .frm file of that view may be lost (renamed to .frm~).
      
      The server failed because it tried to rename latest 3
      backup .frm files renaming the view: the server used an
      integer value of the "revision" field of .frm file to
      extract those file names. After the fix for bug 17823 those
      files were not created/maintained any more, however the
      "revision" field was incremented as usual. So, the server
      failed renaming non existent files.
      
      This fix solves the problem by removing the support for
      "revision" .frm file field:
      1. New server silently ignores existent "revision" fields
         in old .frm files and never write it down;
      2. Old server assumes, that missing "revision" field in new
         .frm files means default value of 0.
      3. Accordingly to the fix for bug 17823 the new server
         drops arc/ directory on alter/rename view, so after
         "live downgrade" old server begins maintenance of the
         arc/ directory from scratch without conflicts with .frm
         files.

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/58813

2716 Gleb Shchepa	2008-11-14
      Bug #40021: Renaming view fails, archived .frm for view is
                  missing after downgrade
      
      Obsolete arc/ directory and view .frm file backup support
      has been removed by the patch for bug 17823. However, that
      bugfix caused a problem with "live downgrades" of the
      server: if we rename some view 4 times under 5.1.29/5.0.72
      and then try to rename it under 5.1.28/5.0.70 on the same
      database, the server fails with a error:
      
        query 'RENAME TABLE ... TO ...' failed: 6: Error on
        delete of '....frm-0001' (Errcode: 2)
      
      Also .frm file of that view may be lost (renamed to .frm~).
      
      The server failed because it tried to rename latest 3
      backup .frm files renaming the view: the server used an
      integer value of the "revision" field of .frm file to
      extract those file names. After the fix for bug 17823 those
      files were not created/maintained any more, however the
      "revision" field was incremented as usual. So, the server
      failed renaming non existent files.
      
      This fix solves the problem by removing the support for
      "revision" .frm file field:
      1. New server silently ignores existent "revision" fields
         in old .frm files and never write it down;
      2. Old server assumes, that missing "revision" field in new
         .frm files means default value of 0.
      3. Accordingly to the fix for bug 17823 the new server
         drops arc/ directory on alter/rename view, so after
         "live downgrade" old server begins maintenance of the
         arc/ directory from scratch without conflicts with .frm
         files.

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/58822

2716 Gleb Shchepa	2008-11-14
      Bug #40021: Renaming view fails, archived .frm for view is
                  missing after downgrade
      
      Obsolete arc/ directory and view .frm file backup support
      has been removed by the patch for bug 17823. However, that
      bugfix caused a problem with "live downgrades" of the
      server: if we rename some view 4 times under 5.1.29/5.0.72
      and then try to rename it under 5.1.28/5.0.70 on the same
      database, the server fails with a error:
      
        query 'RENAME TABLE ... TO ...' failed: 6: Error on
        delete of '....frm-0001' (Errcode: 2)
      
      Also .frm file of that view may be lost (renamed to .frm~).
      
      The server failed because it tried to rename latest 3
      backup .frm files renaming the view: the server used an
      integer value of the "revision" field of .frm file to
      extract those file names. After the fix for bug 17823 those
      files were not created/maintained any more, however the
      "revision" field was incremented as usual. So, the server
      failed renaming non existent files.
      
      This fix solves the problem by removing the support for
      "revision" .frm file field:
      1. New server silently ignores existent "revision" fields
         in old .frm files and never write it down;
      2. Old server assumes, that missing "revision" field in new
         .frm files means default value of 0.
      3. Accordingly to the fix for bug 17823 the new server
         drops arc/ directory on alter/rename view, so after
         "live downgrade" old server begins maintenance of the
         arc/ directory from scratch without conflicts with .frm
         files.

Pushed into 5.0.74  (revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (version source revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (pib:5)

Noted in 5.0.74 changelog.

Support for the revision field in .frm files has been removed. This
addresses the downgrading problem introduced by the fix for
Bug#17823.  

Setting report to NDI pending push into 5.1.x, 6.0.x.

Pushed into 5.1.31  (revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (version source revid:gshchepa@mysql.com-20081114173727-fr0npmcleka1gvlv) (pib:5)

Pushed into 6.0.9-alpha  (revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Noted in 5.0.72sp1 changelog.