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: | |
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 |
[18 Dec 2006 23:12]
Jared S
[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]
MySQL Verification Team
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]
MySQL Verification Team
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