Page MenuHomePhabricator

TYPE= will generate fatal error in MySQL 5.2
Closed, ResolvedPublic

Description

Author: Ben_Allen

Description:
Hello

I am installing MediaWiki 1.6.6 (not yet an option in the version picklist) on
WindowsXP using Apache 2.2.2, PHP 5.2, and MySQL 5.0.21

When the wiki tries to install, I get the following:

  • PHP 5.2.0-dev installed
  • PHP server API is apache2handler; ok, using pretty URLs (index.php/Page_Title)
  • Have XML / Latin1-UTF-8 conversion support.
  • PHP is configured with no memory_limit.
  • Have zlib support; enabling output compression.
  • Neither Turck MMCache nor eAccelerator are installed, can't use object

caching functions

  • GNU diff3 not found.
  • Couldn't find GD library or ImageMagick; image thumbnailing disabled.
  • Installation directory: D:\Program Files\Apache Software

Foundation\Apache2.2\htdocs\wiki

  • Script URI path: /wiki
  • Environment checked. You can install MediaWiki.
  • Warning: $wgSecretKey key is insecure, generated with mt_rand(). Consider

changing it manually.

  Generating configuration file...
* Database type: mysql
* Attempting to connect to database server as wikiuser...success.
* Connected to 5.0.21-community-nt
* Database wikidb exists
* Creating tables... using MySQL 4 table defs...Query "CREATE TABLE `user` (

user_id int(5) unsigned NOT NULL auto_increment, user_name varchar(255) binary
NOT NULL default '', user_real_name varchar(255) binary NOT NULL default '',
user_password tinyblob NOT NULL default '', user_newpassword tinyblob NOT NULL
default '', user_email tinytext NOT NULL default '', user_options blob NOT NULL
default '', user_touched char(14) binary NOT NULL default '', user_token
char(32) binary NOT NULL default '', user_email_authenticated CHAR(14) BINARY,
user_email_token CHAR(32) BINARY, user_email_token_expires CHAR(14) BINARY,
user_registration CHAR(14) BINARY, PRIMARY KEY user_id (user_id), UNIQUE INDEX
user_name (user_name), INDEX (user_email_token) ) TYPE=InnoDB " failed with
error code "You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1 (localhost)".


It appears that even though the install recognized the MySQL 5 server, it tries
to use MySQL4 command syntax. Manually running that query through the MySQL
Queryt Browser application returned an error indicating that the
"TYPE=database_type" syntax was deprecated and should be changed to
"ENGINE=database_type". I made that change to the command and the Query Browser
app was able to run it sucessfully.

While that change fixes the one error, perhaps this might be an indicator of a
larger problem? The installer noticed that I was using MySQL5 but used MySQL4
syntax instead. Is this something that I can change (or find a list of the SQL
commands run and create all the tables manually)? Thank you!


Version: unspecified
Severity: normal
URL: http://bugs.mysql.com/bug.php?id=17501

Details

Reference
bz6085

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 9:21 PM
bzimport set Reference to bz6085.
bzimport added a subscriber: Unknown Object (MLST).

TYPE works fine with MySQL 5.0.21. It is possible you have broken
your configuration somehow by switching it into an incompatible
mode? Please consult your MySQL configuration file.

Ben_Allen wrote:

Apparently this is related to an issue with recent versions of MySQL
(http://bugs.mysql.com/bug.php?id=17501) where using TYPE generates an error
instead of a warning. My MySQL was a fresh, unmodified installation, so I doubt
it is a configuration issue. I will find an older version of MySQL and try again.

Just to warn you though, the MySQL developers report that as of version 5.2,
TYPE will no longer be supported. The installer for future version of MediaWiki
should probably detect MySQL versions 5+ and use ENGINE instead of TYPE to avoid
breaking the installer completely.

Updated summary + URL to bug report.

ezyang wrote:

If this is a bug with MySQL, then perhaps this should be WONTFIX, and we open a
new bug for TYPE information. By the way, Wikimedia is still using MySQL 4.

robchur wrote:

However, if it's the case that MySQL is changing syntax, again, then I suppose
we need to change our table definitions or our method of using them, so that we
won't break stuff for people running newer versions later on.

ezyang wrote:

I don't think this a blocker (yet), but here are links to the docs:

MySQL 4 http://dev.mysql.com/doc/refman/4.1/en/storage-engines.html
MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

TYPE is supported for backwards compatibility but is deprecated. Maybe the call
is unnecessary?

Replace TYPE= by ENGINE= is a quick and minor change.

What's the problem updating install script ?

robchur wrote:

(In reply to comment #7)

Replace TYPE= by ENGINE= is a quick and minor change.

Yes, but I bet you forgot to do maintenance/archives/*.sql.

What's the problem updating install script ?

It's not supported in MySQL 4.x, at least, and so isn't compatible with the
version most of our users are going to use.

Something like /*ENGINE*/ could be handled in Database::sourceFile(), similar to
the current handling of /*$wgDBprefix*/ et al.

MySQL 4.0.18+ and 4.1.2+ supports it, according
http://dev.mysql.com/doc/refman/4.1/en/create-table.html :

The ENGINE and TYPE options specify the storage engine for the table. ENGINE
was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred
option name as of those versions, and TYPE has become deprecated. TYPE is
supported throughout the 4.x series, but likely will be removed in the future.

Decreasing severity. Posting a support bug with a severity of BLOCKER is just
arrogant.

robchur wrote:

That's what blocker means, although we're not targeting MySQL 5.2.x at present.

As of r17492, using ENGINE on the experimental MySQL 4.1/5 UTF-8 schema.

Main schema still uses TYPE for compatibility with older releases of MySQL 4.0.

MySQL's continued lack of full UTF-8 support means MediaWiki may continue to default to the old schema for the forseeable
future, or else switch to an explicit binary-friendly MySQL 5 schema. >:|

The new installer code should be automatically using ENGINE where needed, so I'd consider this issue fixed.