Last modified: 2010-05-15 15:42:44 UTC
Running MediaWiki 1.8.2, PHP 5.1.6, MySql 5.0.2 and IIS 6 on Windows Server 2003. 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.
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.
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?
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)".
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
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?
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?
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.
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.)
