516157 - MySQL 6.x/5.2/5.4 uses "ENGINE", not "TYPE", for altering tables to InnoDB

Status: RESOLVED FIXED

(Bugzilla :: Database, defect)

Description

[Gerard J. Cerchio]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.2) Gecko/20090729 Firefox/3.5.2 (.NET CLR 3.5.30729)
Build Identifier: Bugzilla 3.4.2 on perl 5.8.8

trying to convert from 3.2.1 to 3.4.2 the database conversion fails because the ALTER TABLE uses TYPE not ENGINE. mySQL 6.0 uses ENGINE.

Reproducible: Always

Steps to Reproduce:
1.install mySQL 6
2.install Bugzilla 3.1.2
3.upgrade to Bugzilla 3.4.2
4../checksetup.pl
Actual Results:  
Converting tables to InnoDB:
Converting table longdescs... DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t
he right syntax to use near 'TYPE = InnoDB' at line 1 [for Statement "ALTER TABLE longdescs TYPE = InnoDB"] at Bugzilla/DB/Mysql.pm line 384
        Bugzilla::DB::Mysql::bz_setup_database('Bugzilla::DB::Mysql=HASH(0x9acef28)') called at ./checksetup.pl line 140
circle# 


Expected Results:  
not an error message

Entire checksetup.pl:

./checksetup.pl
* This is Bugzilla 3.4.2 on perl 5.8.8
* Running on FreeBSD 6.4-RELEASE FreeBSD 6.4-RELEASE #0: Wed Nov 26 12:11:16 UTC 2008     [e-mail]:/usr/obj/usr/src/sys/SMP

Checking perl modules...
Checking for              CGI.pm (v3.21)   ok: found v3.42 
Checking for          Digest-SHA (any)     ok: found v5.47 
Checking for            TimeDate (v2.21)   ok: found v2.22 
Checking for            DateTime (v0.28)   ok: found v0.50 
Checking for   DateTime-TimeZone (v0.71)   ok: found v0.93 
Checking for                 DBI (v1.41)   ok: found v1.607 
Checking for    Template-Toolkit (v2.22)   ok: found v2.22 
Checking for          Email-Send (v2.00)   ok: found v2.192 
Checking for          Email-MIME (v1.861)  ok: found v1.861 
Checking for Email-MIME-Encodings (v1.313)  ok: found v1.313 
Checking for Email-MIME-Modifier (v1.442)  ok: found v1.442 
Checking for                 URI (any)     ok: found v1.37 

Checking available perl DBD modules...
Checking for              DBD-Pg (v1.45)    not found 
Checking for           DBD-mysql (v4.00)   ok: found v4.010 
Checking for          DBD-Oracle (v1.19)    not found 

The following Perl modules are optional:
Checking for                  GD (v1.20)   ok: found v2.41 
Checking for               Chart (v1.0)    ok: found v2.4.1 
Checking for         Template-GD (any)     ok: found v1.56 
Checking for          GDTextUtil (any)     ok: found v0.86 
Checking for             GDGraph (any)     ok: found v1.44 
Checking for            XML-Twig (any)     ok: found v3.32 
Checking for          MIME-tools (v5.406)  ok: found v5.427 
Checking for         libwww-perl (any)     ok: found v5.822 
Checking for         PatchReader (v0.9.4)  ok: found v0.9.5 
Checking for          PerlMagick (any)     ok: found v6.4.3 
Checking for           perl-ldap (any)      not found 
Checking for         Authen-SASL (any)      not found 
Checking for          RadiusPerl (any)      not found 
Checking for           SOAP-Lite (v0.710.06) ok: found v0.710.08 
Checking for         HTML-Parser (v3.40)   ok: found v3.59 
Checking for       HTML-Scrubber (any)      not found 
Checking for Email-MIME-Attachment-Stripper (any)      not found 
Checking for         Email-Reply (any)      not found 
Checking for         TheSchwartz (any)      not found 
Checking for      Daemon-Generic (any)      not found 
Checking for            mod_perl (v1.999022) ok: found v2.000004 
***********************************************************************
* OPTIONAL MODULES                                                    *
***********************************************************************
* Certain Perl modules are not required by Bugzilla, but by           *
* installing the latest version you gain access to additional         *
* features.                                                           *
*                                                                     *
* The optional modules you do not have installed are listed below,    *
* with the name of the feature they enable. Below that table are the  *
* commands to install each module.                                    *
***********************************************************************
*                    MODULE NAME * ENABLES FEATURE(S)                 *
***********************************************************************
*                      perl-ldap * LDAP Authentication                *
*                    Authen-SASL * SMTP Authentication                *
*                     RadiusPerl * RADIUS Authentication              *
*                  HTML-Scrubber * More HTML in Product/Group Descriptions *
* Email-MIME-Attachment-Stripper * Inbound Email                      *
*                    Email-Reply * Inbound Email                      *
*                    TheSchwartz * Mail Queueing                      *
*                 Daemon-Generic * Mail Queueing                      *
***********************************************************************
COMMANDS TO INSTALL OPTIONAL MODULES:

      perl-ldap: /usr/bin/perl install-module.pl Net::LDAP
    Authen-SASL: /usr/bin/perl install-module.pl Authen::SASL
     RadiusPerl: /usr/bin/perl install-module.pl Authen::Radius
  HTML-Scrubber: /usr/bin/perl install-module.pl HTML::Scrubber
Email-MIME-Attachment-Stripper: /usr/bin/perl install-module.pl Email::MIME::Attachment::Stripper
    Email-Reply: /usr/bin/perl install-module.pl Email::Reply
    TheSchwartz: /usr/bin/perl install-module.pl TheSchwartz
 Daemon-Generic: /usr/bin/perl install-module.pl Daemon::Generic


To attempt an automatic install of every required and optional module
with one command, do:

  /usr/bin/perl install-module.pl --all

Reading ./localconfig...

OPTIONAL NOTE: If you want to be able to use the 'difference between two
patches' feature of Bugzilla (which requires the PatchReader Perl module
as well), you should install patchutils from:

    http://cyberelk.net/tim/patchutils/

Checking for           DBD-mysql (v4.00)   ok: found v4.010 
Checking for           MySQL (v4.1.2)  ok: found v6.0.6-alpha

Bugzilla now uses the InnoDB storage engine in MySQL for most tables.
Converting tables to InnoDB:
Converting table longdescs... DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t
he right syntax to use near 'TYPE = InnoDB' at line 1 [for Statement "ALTER TABLE longdescs TYPE = InnoDB"] at Bugzilla/DB/Mysql.pm line 384
        Bugzilla::DB::Mysql::bz_setup_database('Bugzilla::DB::Mysql=HASH(0x9acef28)') called at ./checksetup.pl line 140

Comment 1

[Teemu Mannermaa (:wicked)]

According to http://dev.mysql.com/doc/refman/5.1/en/news-5-1-8.html "ALTER TABLE t TYPE = x" syntax has been deprecated since v4.0 and removed in v5.2.5 in favor of "ALTER TABLE t ENGINE = x" syntax. We should change Bugzilla accordingly. So line 384 and possible line 335 in Bugzilla/DB/Mysql.pm is affected.

Comment 2

[Gerard J. Cerchio]

Attachment: modify ALTER TABLE to use ENGINE not TYPE

as per mozwebtools irc wicked's request
This patch does not add code to test for mySQL version

Comment 3

[Frédéric Buclin]

Increasing the severity to put this bug in our radar. The doc says that MySQL 5.2.5 and higher won't support TYPE at all anymore, and MySQL 5.4.1 is already in beta stage, so we should fix this problem soon (though not urgent).

Comment 4

[Frédéric Buclin]

Bugzilla 3.2 should also be fixed.

Comment 5

[Max Kanat-Alexander]

Not major--MySQL 6/5.2/5.4 (whatever they're going to call it) is not GA. I'm probably going to say stuff like, "If you want 5.4 support, upgrade to 3.4."

Comment 6

[Max Kanat-Alexander]

Comment on attachment 400283 [details] [diff] [review]
modify ALTER TABLE to use ENGINE not TYPE

You need to fix everywhere that TYPE is used, not just in DB::Mysql.

Comment 7

[Gerard J. Cerchio]

The one place I did change it produces a working FreeBSD Bugzilla. I am not familiar enough with your process to efficiently find all the other places this change needs to be applied. I cannot accept assignment of this bug.

Comment 8

[Frédéric Buclin]

(In reply to comment #5)
 probably going to say stuff like, "If you want 5.4 support, upgrade to 3.4."

I don't see why. It's a trivial fix, as far as I can see.


(In reply to comment #6)
 You need to fix everywhere that TYPE is used, not just in DB::Mysql.

Are there really other places to fix besides what wicked mentioned in comment 1? I did a grep on "TYPE =", and only those two places are returned.

Comment 9

[Max Kanat-Alexander]

Comment on attachment 400283 [details] [diff] [review]
modify ALTER TABLE to use ENGINE not TYPE

Oh, nevermind. This is indeed actually correct. The other place that we would have been using TYPE was already changed to ENGINE.

Comment 10

[Max Kanat-Alexander]

LpSolit pointed out that 5.4 will probably reach GA before 3.2 reaches EOL, so we might as well backport this to 3.2.

Comment 11

[Max Kanat-Alexander]

BTW--LpSolit asked why we aren't fixing the ISAM->MyISAM conversion line also. It's because ISAM tables aren't even supported in 5.0 and above, so that line will only work in the rare case that you are upgrading a very old Bugzilla using 4.1.x, and it actually will never be called on 5.0+. We still *could* have fixed it, but it doesn't actually matter whether or not we do so.

tip:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.79; previous revision: 1.78
done

3.4:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.72.2.3; previous revision: 1.72.2.2
done

3.2:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.60.2.9; previous revision: 1.60.2.8
done