MySQL Bugs: #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates

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:	Windows (WinXP)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	5.1, administrator, Backup, databse, mysql administrator, server

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.

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.

**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

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?

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.

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.

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.

standard boring 5.1 my.ini file

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

Pic of bad script

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

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.

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

Chad, you've opened my bug again?

Bug http://bugs.mysql.com/bug.php?id=27890 was marked as duplicate of this
one.

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.

..

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Fixing Bug#8901 would have helped with this issue.

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.

Thanks, just dowloaded 5.1.24 with the fix.  Although appears if backup content has changed not to inlcude "USING BTREE".

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?

And yes, why is there no --compatible=50 switch in mysqldump?

Try loggin new bug in installer section and link this bug back.  5.1.37+installer (latest) works as is should (omits USING BTREE).

same problem

debian:/# mysqldump -V
mysqldump  Ver 10.13 Distrib 5.1.39, for debian-linux-gnu (i486)

Ошибка при переносе с версии 5.1 на версию 5.0