Page MenuHomePhabricator

should not use default value for MySQL BLOB/TEXT columns
Closed, ResolvedPublic

Description

Author: nygreen

Description:
When installing on a system running MySQL 5, the setup script cannot create the
databases.

Steps to reproduce:

  1. Have a system running MySQL 5
  2. Download a fresh copy of MediaWiki (I've tested both 1.8.2 and 1.9-svn)
  3. Try to install as usual (i.e. chmod a+r on the config folder and click the

config link)

The complete output is included below. The essential part is: ( [...] means I've
taken something out)

Query "CREATE TABLE dw_user ( [...], user_password tinyblob NOT NULL default
'', [...] ) TYPE=InnoDB, DEFAULT CHARSET=utf8 " failed with error code
"BLOB/TEXT column 'user_password' can't have a default value (localhost)".

This means the problem is that MediaWiki is trying to set default values for a
blob or text column, but in MySQL 5 "BLOB and TEXT columns cannot have DEFAULT
values." (quoted from http://dev.mysql.com/doc/refman/5.0/en/blob.html)

Complete output:

  • PHP 5.1.6 installed
  • Found database drivers for: MySQL
  • 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.
  • Couldn't find Turck MMCache, eAccelerator or APC. Object caching functions

cannot be used.

  • GNU diff3 not found.
  • Found GD graphics library built-in, image thumbnailing will be enabled if

you enable uploads.

  • Installation directory: C:\Documents and Settings\All

Users\Dokumenter\htdocs\mediawiki-svn\wiki

  • Script URI path: /mediawiki-svn/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
* Loading class: DatabaseMysql
* Attempting to connect to database server as root...success.
* Connected to 5.0.26-community-nt; enabling MySQL 4.1/5.0 charset mode
* Created database devwikidb
* Creating tables... using MySQL 5 table defs...Query "CREATE TABLE

dw_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, DEFAULT CHARSET=utf8 " failed with error code
"BLOB/TEXT column 'user_password' can't have a default value (localhost)".


Version: 1.9.x
Severity: major

Details

Reference
bz7669

Related Objects

StatusSubtypeAssignedTask
ResolvedNone
ResolvedNone

Event Timeline

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

nygreen wrote:

Removes default values from blob and text types in tables.sql

Removes all default values for blob and text data types in
maintenance/mysql5/tables.sql

Attached:

nygreen wrote:

The same in maintenance/archives/

This solves the problem for me, running MySQL 5.0.26. I have not tested it with
MySQL 4.*.

Attached:

Never had such problems with 5.0.19 here. There's rumored to be a bug in 5.0.26.

nygreen wrote:

I'm reopening this.

The reason it seems to work with 5.0.19 is that the error/warning was added in
5.0.25 (and in 5.1.12) (http://bugs.mysql.com/bug.php?id=19498).

However there has never really been support for a DEFAULT attribute for BLOB and
TEXT data types. (Or if it has been, it must have been before 3.23.) But MySQL
has not protested if you have assigned it a DEFAULT value of ''. Not until
5.0.19 / 5.1.12.

The changelog for 5.1.12 states:
"TEXT and BLOB columns do not support DEFAULT values. However, when a default of
'' was specified, the specification was silently ignored. This now results in a
warning, or an error in strict mode."

Until TEXT and BLOB fields have any support for DEFAULT values, I strongly
suggest we don't use DEFAULT on these fields. Any NOT NULL field has a default
value of '' anyway, so we don't need it.

I have also found out this produces only a warning when MySQL is not in strict
mode (se the quote above). But then, I think strict mode is the right mode. Call
me old-fashioned but I like standards.

And as I said: default '' does not add anything to a NOT NULL field.

nygreen wrote:

changed the summary according to last comment

  • Bug 8224 has been marked as a duplicate of this bug. ***

Pretty sure this is a dupe.

Issue is MySQL's 'strict mode' options which break compatibility and may or may
not be set by some people on some versions of the installer. Disable 'strict
mode' options for better compatibility.

Newer schemas on trunk are more compatible with strict mode already; this may or
may not be a moot issue.

Bug should be duped to the original issue when found.

nygreen wrote:

Well I still think it is a problem that MediaWiki uses a syntax that has never
been correct. You cannot call that compatibility. The only reason it used to
work was because MySQL did not cry out about the error. Now it does. Please do
not mistake the lack of an error message as being a proof of successful execution.

As I have shown already, the default='' has no effect combined with Text/Blob.
It is not needed, and never has been. A Text/Blob is empty by default anyway.

I fixed this in my own installation when I first encountered it, so I won't use
any more of my time and energy trying to convince you. But I have one final
wish, Brion: can you please explain why it is so important to you to continue
using an invalid syntax, when I have given you the solution?

If you're going to make further comments, try to make productive ones.
The issue is known, as mentioned above.

robchur wrote:

*** Bug 7722 has been marked as a duplicate of this bug. ***

Fixed in r18480; went through the current schema files and patches and updated
them and the code for bug 7685.

  • Bug 8398 has been marked as a duplicate of this bug. ***
  • Bug 8530 has been marked as a duplicate of this bug. ***