Bug #25162 Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates
Submitted: 18 Dec 2006 23:12 Modified: 6 Aug 2009 22:58
Reporter: Jared S (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.1.14 OS:Microsoft Windows (WinXP)
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: 5.1, administrator, Backup, databse, mysql administrator, server
Triage: D2 (Serious)

[18 Dec 2006 23:12] Jared S
Description:
Hi.  Recently backed up couple of DBs from 5.1 while downgrading to 5.0 due to antoher bug in 5.1.  One DB worked and One DB failed to restore due to fact that it backed uped with USING BTREE on most table, but some are OK.  The only thing I can think of that is causing this is my UNION views.

I can attach my script from after the problem occured.  TThis may help you indetify how the bug is effecting backup but may not give you cause.  So I may also backup latest DB before it was contaminated by 5.1 for you to backup yourself.

How to repeat:
1. Load db1 into MySQL server 5.1
2. Backup using Administrator 1.2.8
3. Now take you backup copy and load into Query Browser
4. ** This fails cause of all the USING BTREE on key for most tables

Suggested fix:
Remove this BTREE stuff that is effecting Restoring through Query Browser.
[18 Dec 2006 23:21] Jared S
With my before and after DBs I could not get an exact copy of 'DB before' so I just stripped USING BTREE with a file search and replace tool of my 'DB After'.  Should have no impact on the bug submission.
[19 Dec 2006 1:34] Jared S
**NEW How to repeat:

1. Load db1 into MySQL server 5.1.14+
2. Backup using Administrator 1.2.8
3. Switch over to MySQL Server 5.0.27** 
4. Now take you backup copy and load into Query Browser
5. ** This fails cause of all the USING BTREE on key for most tables
[19 Dec 2006 5:55] Valeriy Kravchuk
Thank you for a problem report. Looks more like MySQL Administrator-related bug to me. Can you try to use mysqldump and check if it gives the same results?
[19 Dec 2006 21:52] Jared S
NO! not a problem with MySQL Administrator - problem still exist when dumped performed with MySQL Server 5.1 using mysqldump.exe from bin folder.

Basically at this point it would appear that MySQL 5.1 DB's are not compatable with 5.0.  I would of thought this goes against the SQL standard.
[21 Dec 2006 23:19] Miguel Solorzano
Thank you for the feedback. I was unable to repeat with 5.1.14[15] and
5.0.27 server. What I did is:

- Restored db1,sql on 5.1.14[15]
- Created a backup with Administrator and mysqldump.
- Restored the db in 5.0.27.

Since I started the 5.1.XX servers with the default start option, then
I would like to ask you to provide the my.ini files that you are using
on your environment. Thanks in advance.
[26 Dec 2006 23:29] Jared S
Hi Miguel, I cannot repeat this problem on my Vista computer at home but I can repeat this problem on my XP computer at Work.  Both are 5.1.14.  I will upload the my.ini file from work(fauly server) although the only differences I saw from faulty server and good server was beefeir memory buffers on my Core 2 DUO box.

Tell me what else I can do to assist you.  I think this 'USING BTREE' thing is major incompatibility and maybe this bug should goto verified state since I have supplied the fualty ouput file also.  This bug maybe fualt with Query Browser.  Really need advice on 'USING BTREE' since reproducing 1 of the steps involved in this bug is not an easy task.
[26 Dec 2006 23:30] Jared S
standard boring 5.1 my.ini file

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

[26 Dec 2006 23:30] Jared S
standard boring 5.1 my.ini file

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

[26 Dec 2006 23:47] Jared S
Pic of bad script

Attachment: 5.1 script error.PNG (image/x-png, text), 77.55 KiB.

[26 Dec 2006 23:47] Jared S
Pic of bad restore on 5.0 server using 5.1 backup

Attachment: 5.1 restore to 5.0 server using admin.PNG (image/x-png, text), 24.07 KiB.

[28 Dec 2006 10:11] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources.

Workaround: use mysqldump --compatible=mysql40 if possible

"Compatibility mode" option of MySQL Administrator works too
[3 Jan 2007 22:27] Jared S
Chad, you've opened my bug again?
[19 Sep 2007 21:32] Miguel Solorzano
Bug http://bugs.mysql.com/bug.php?id=27890 was marked as duplicate of this
one.
[1 Feb 2008 8:00] 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/41544

ChangeSet@1.2593, 2008-02-01 13:00:40+05:00, kaa@mbp.local +3 -0
  Fix for bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs 
                      on table creates
  
  The problem was in incompatible syntax for key definition in CREATE
  TABLE.
  
  5.0 supports only the following syntax for key definition (see "CREATE
  TABLE syntax" in the manual):
  
  {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
  
  While 5.1 parser supports the above syntax, the "preferred" syntax was
  changed to:
  
  {INDEX|KEY} [index_name] (index_col_name,...) [index_type]
  
  The above syntax is used in 5.1 for the SHOW CREATE TABLE output, which
  led to dumps generated by 5.1 being incompatible with 5.0.
  
  Fixed by changing the parser in 5.0 to support both 5.0 and 5.1 syntax
  for key definition.
[5 Mar 2008 3:27] Jared S
..
[13 Mar 2008 19:27] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:34] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:42] Bugs System
Pushed into 5.0.60
[16 Apr 2008 16:19] Paul Dubois
Fixing Bug#8901 would have helped with this issue.
[16 Apr 2008 16:24] Paul Dubois
Noted in 5.0.60 changelogs.

Using mysqldump in MySQL 5.1 resulted in dump files that could not be
loaded in MySQL 5.0 because USING type_name options in index
definitions appeared after the index column list, whereas 5.0
accepted only the old syntax that has USING before the column list.
The parser in 5.0 now accepts USING following the column list.

Also updated the descriptions for CREATE INDEX, CREATE TABLE, and
ALTER TABLE to indicate the change in allowable syntax.
[18 Apr 2008 20:34] Jared S
Thanks, just dowloaded 5.1.24 with the fix.  Although appears if backup content has changed not to inlcude "USING BTREE".
[5 Aug 2009 14:59] Neven Jacmenovic
I just updated MySQL on my Vista workstation (running 5.1.33) with latest Windows x86 version AND x64 version - v5.1.37 Without installer (http://dev.mysql.com/downloads/mysql/5.1.html#win32) and fix is not present in any of those versions. mysqldump  says: Ver 10.13 Distrib 5.1.37, for Win32 (ia32) and MySQL dump 10.13  Distrib 5.1.37, for Win64 (unknown).

I cannot export dumps from Windows and import them to server running mysql version: 5.0.51a-3ubuntu5.4 (Ubuntu). 

When do you think that fix will be available?
[5 Aug 2009 15:04] Neven Jacmenovic
And yes, why is there no --compatible=50 switch in mysqldump?
[6 Aug 2009 22:58] Jared S
Try loggin new bug in installer section and link this bug back.  5.1.37+installer (latest) works as is should (omits USING BTREE).
[6 Oct 2009 2:27] PANEL Martin
same problem

debian:/# mysqldump -V
mysqldump  Ver 10.13 Distrib 5.1.39, for debian-linux-gnu (i486)
[11 Aug 2010 9:25] djazz cool
Ошибка при переносе с версии 5.1 на версию 5.0