Page MenuHomePhabricator

Delete Pages does not work - database error
Closed, ResolvedPublic

Description

Author: jgolas

Description:
Running MediaWiki 1.8.2, PHP 5.1.6, MySql 5.0.2 and IIS 6 on Windows Server

  1. Client is running Firefox 1.5.0.7 on Windows XP Sp2. Whenever I try to

delete a page in MediaWiki, I get the following database error:

database query syntax error has occurred. This may indicate a bug in the
software. The last attempted database query was:

INSERT INTO `archive`

(ar_namespace,ar_title,ar_comment,ar_user,ar_user_text,ar_timestamp,ar_minor_edit,ar_rev_id,ar_text_id)
SELECT
page_namespace,page_title,rev_comment,rev_user,rev_user_text,rev_timestamp,rev_minor_edit,rev_id,rev_text_id
FROM page,revision WHERE page_id = '1516' AND (page_id = rev_page)

from within function "Article::doDeleteArticle". MySQL returned error "1364:
Field 'ar_text' doesn't have a default value (localhost)".

When I installed MediaWiki, I had a problem generating the tables and found a
fix online that discussed changing many "not null default ''" lines to just "not
null". Anyway because of that it seems ar_text is current'y set to just "not
null" with no default value, and Mysql can't create a new row because of that.
When I try to assign a default value to ar_text (or even ar_flags) using either
MySql Query Browser or phpMyAdmin, I get "ar_text cannot have a default value".
I found a posting describing getting around this using phpMyAdmin but I couldn't
replicate it.

It seems the problem is either a MySQL bug (saw some mention of it), or
MediaWiki needs to write something to ar_text even if it isn't used. I did try
changing ar_text and then ar_flags to support null and that seemed to fix the
problem, although I'm not sure if it affects anything else (not a database
expert by any means). From what I researched ar_text is a legacy field that
still exists for backward compatibility or migration purposes.


Version: 1.8.x
Severity: normal
OS: Windows Server 2003
Platform: PC

Details

Reference
bz7685

Event Timeline

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

Hmm, you should correct the table on your installation to match the definition, or behavior may be incorrect.

If you really do have MySQL 5.0.2, be aware this is a very old alpha release. Upgrade to a current 5.0.x if possible.

jgolas wrote:

I'm sorry its 5.0.26, not 5.0.2.

The standard table definition appears to work fine with MySQL 5.0.26 (tested on Mac OS X).

Can you try dropping the table and recreating it from the definition in tables.sql?

jgolas wrote:

Ok I wiped the database & database user from Mysql. I deleted localsettings.php
from the wiki root folder and from the config folder. I undid all changes I made
to the install scripts that I saw on <a
href="http://www.mwusers.com/forums/showthread.php?t=2889">this posting</a> by
copying the original files overtop them.

I re-ran the install under http://webserver/wiki/config and first tried the
standard "backwards compatible" definition - it errored out with "user_password
can't have a default value".

I then wiped the database, database user, and localsettings file again and tried
it again using the "Experimental Mysql 4/5" definitions. Same problem -
user_password can't have a default value.

Detail:

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

jgolas wrote:

Just to see if it's PHP related - I opened the tables.sql script up in MySql
Query Browser, first created the database manually (create database wikidb) and
then ran the tables.sql script - comes up with the following results:

Script line: 64 BLOB/TEXT column 'user_password' can't have a default value
Script line: 175 BLOB/TEXT column 'page_restrictions' can't have a default value
Script line: 235 BLOB/TEXT column 'rev_comment' can't have a default value
Script line: 287 BLOB/TEXT column 'old_text' can't have a default value
Script line: 320 BLOB/TEXT column 'ar_text' can't have a default value
Script line: 468 BLOB/TEXT column 'el_to' can't have a default value
Script line: 563 BLOB/TEXT column 'ipb_reason' can't have a default value
Script line: 619 BLOB/TEXT column 'img_description' can't have a default value
Script line: 677 BLOB/TEXT column 'oi_description' can't have a default value
Script line: 703 BLOB/TEXT column 'fa_description' can't have a default value
Script line: 870 BLOB/TEXT column 'si_text' can't have a default value
Script line: 949 BLOB/TEXT column 'log_params' can't have a default value
Script line: 991 BLOB/TEXT column 'job_params' can't have a default value

jgolas wrote:

Installed Mysql 4.1.21 on another compuer (under Windows 2000 Pro) and it worked
perfectly...must be something with the Mysql 5 table definitions or Mysql 5.0.26
itself under Windows.

Did you enable a non-default option in MySQL 5's configuration, such as strict mode?

jgolas wrote:

Actually it *was* in strict mode...which was default for Mysql (turned on). If
strict mode is nothing more than SQL making the app adhere to SQL standards...it
should work with it on.

I was able to turn it off and the errors went away.

Strict mode is definitely not the default; you have to manually add it to my.cnf.
Are you running on Windows? Perhaps you chose this mode during the installation?

Anyway, page deletion still seems to work fine when I enable strict mode with 5.0.26.
Can you provide your my.cnf?

jgolas wrote:

Yes I'm on Windows...when doing the Mysql instance config in Windows, the
"strict mode" checkbox is definitely turned on by default. As a first time Mysql
user I left it like that.

I could send you the my.cnf file later today but its basically all defaults - I
didn't modify it at all.

Since your defaults appear to include an override from the Windows installer that's *not* in
the default behavior of MySQL as left in its default configuration, and I don't have any
Windows machines available at present, I'll need to know your configuration to do any further
testing.

edwardsbc wrote:

I've experienced this same problem (MySQL windows 5.0.27), which is indeed
installed with strict mode on by default. I think the real question should be
why does "Article::doDeleteArticle" need a default value for the field "ar_text".

Fixed in r18480. Query now supplies the redundant default values.
(Would be cleaner still to drop the NOT NULL from the columns and just use NULL
legitimately,
but that would require a db change on existing tables.)