Last modified: 2007-01-08 11:02:06 UTC

Wikimedia Bugzilla is closed!

Wikimedia migrated from Bugzilla to Phabricator. Bug reports are handled in Wikimedia Phabricator.
This static website is read-only and for historical purposes. It is not possible to log in and except for displaying bug reports and their history, links might be broken. See T9669, the corresponding Phabricator task for complete and up-to-date bug report information.
Bug 7669 - should not use default value for MySQL BLOB/TEXT columns
should not use default value for MySQL BLOB/TEXT columns
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
All All
: Normal major with 1 vote (vote)
: ---
Assigned To: Nobody - You can work on this!
: patch, patch-need-review
: 7722 8224 8398 8530 (view as bug list)
Depends on:
Blocks: 3738
  Show dependency treegraph
Reported: 2006-10-22 23:39 UTC by tiny_green
Modified: 2007-01-08 11:02 UTC (History)
4 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---

Removes default values from blob and text types in tables.sql (6.20 KB, patch)
2006-10-23 01:33 UTC, tiny_green
The same in maintenance/archives/ (1.66 KB, patch)
2006-10-23 02:08 UTC, tiny_green

Description tiny_green 2006-10-22 23:39:31 UTC
When installing on a system running MySQL 5, the setup script cannot create the

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

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
    * 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)".
Comment 1 tiny_green 2006-10-23 01:33:36 UTC
Created attachment 2539 [details]
Removes default values from blob and text types in tables.sql

Removes all default values for blob and text data types in
Comment 2 tiny_green 2006-10-23 02:08:42 UTC
Created attachment 2540 [details]
The same in maintenance/archives/

This solves the problem for me, running MySQL 5.0.26. I have not tested it with
MySQL 4.*.
Comment 3 Brion Vibber 2006-10-24 12:18:53 UTC
Never had such problems with 5.0.19 here. There's rumored to be a bug in 5.0.26.
Comment 4 tiny_green 2006-10-30 22:55:45 UTC
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) (

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.
Comment 5 tiny_green 2006-10-30 23:01:11 UTC
changed the summary according to last comment
Comment 6 Brion Vibber 2006-12-12 02:29:56 UTC
*** Bug 8224 has been marked as a duplicate of this bug. ***
Comment 7 Brion Vibber 2006-12-12 02:31:38 UTC
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.
Comment 8 tiny_green 2006-12-12 13:58:36 UTC
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?
Comment 9 Brion Vibber 2006-12-12 19:35:41 UTC
If you're going to make further comments, try to make productive ones.
The issue is known, as mentioned above.
Comment 10 Rob Church 2006-12-21 20:03:06 UTC
*** Bug 7722 has been marked as a duplicate of this bug. ***
Comment 11 Brion Vibber 2006-12-21 21:47:03 UTC
Fixed in r18480; went through the current schema files and patches and updated
them and the code for bug 7685.
Comment 12 Brion Vibber 2006-12-27 09:38:07 UTC
*** Bug 8398 has been marked as a duplicate of this bug. ***
Comment 13 Brion Vibber 2007-01-08 11:02:06 UTC
*** Bug 8530 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.