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
Triage: Triaged: D2 (Serious)

[14 Oct 2008 14:48] Vemund Østgaard
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.
[14 Oct 2008 17:43] Gleb Shchepa
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},
[28 Oct 2008 1:52] Elena Stepanova
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.
[10 Nov 2008 17:54] Bugs System
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.
[13 Nov 2008 15:33] Ingo Strüwing
See email
[13 Nov 2008 16:50] Bugs System
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.
[14 Nov 2008 11:17] Bugs System
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.
[14 Nov 2008 16:09] Bugs System
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.
[14 Nov 2008 17:59] Bugs System
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.
[2 Dec 2008 13:01] Bugs System
Pushed into 5.0.74  (revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (version source revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (pib:5)
[4 Dec 2008 20:09] Paul Dubois
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.
[8 Dec 2008 10:21] Bugs System
Pushed into 5.1.31  (revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (version source revid:gshchepa@mysql.com-20081114173727-fr0npmcleka1gvlv) (pib:5)
[8 Dec 2008 11:32] Bugs System
Pushed into 6.0.9-alpha  (revid:gshchepa@mysql.com-20081114172557-xh0jlzwal8ze3cy6) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)
[8 Dec 2008 17:09] Paul Dubois
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:25] Bugs System
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)
[19 Jan 2009 13:03] Bugs System
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)
[19 Jan 2009 16:09] Bugs System
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)
[5 Feb 2009 19:53] Paul Dubois
Noted in 5.0.72sp1 changelog.