Wikimedia Bugzilla is closed!

Need to check that this isn't in 1.18, too, so setting as blocker just in case.

per irc conversation with Chad, changing priority but leaving as blocker since this probably only reflects wikis that have been upgraded from mysql4

removing blocker on tarball, workaround (upgrade to 1.16 and/or
mysql5) is available

Need clarification on workaround.  I am having this issue exists with an upgrade from 1.17.0 to 1.18.0-beta1.

Environment Details:
-Apache:  2.2.21
-PHP:  5.3.8, suhosin 0.9.32.1
-MySQL:  5.0.86-enterprise-gpl
-SSL:  openssl-1.0.0e
-OS:  SLES 10 64-bit

Error Details:...

Creating msg_resource table...
An error occured:
A database query syntax error has occurred.
The last attempted database query was:
"CREATE TABLE `msg_resource` (
 mr_resource varbinary(255) NOT NULL,
 mr_lang varbinary(32) NOT NULL,
 mr_blob mediumblob NOT NULL,
 mr_timestamp binary(14) NOT NULL
 ) ENGINE=InnoDB, DEFAULT CHARSET=mysql4
"
from within function "DatabaseBase::sourceFile( /data/www/t/maintenance/archives/patch-msg_resource.sql )".
Database returned error "1115: Unknown character set: 'mysql4' (localhost)"
Backtrace:
#0 /data/www/t/includes/db/Database.php(814): DatabaseBase->reportQueryError('Unknown charact...', 1115, 'CREATE TABLE `m...', 'DatabaseBase::s...', false)
#1 /data/www/t/includes/db/Database.php(3013): DatabaseBase->query('CREATE TABLE `m...', 'DatabaseBase::s...')
#2 /data/www/t/includes/db/Database.php(2911): DatabaseBase->sourceStream(Resource id #286, false, false, 'DatabaseBase::s...')
#3 /data/www/t/includes/installer/DatabaseUpdater.php(359): DatabaseBase->sourceFile('/data/www/t/mai...')
#4 /data/www/t/includes/installer/DatabaseUpdater.php(374): DatabaseUpdater->applyPatch('patch-msg_resou...', false)
#5 [internal function]: DatabaseUpdater->addTable('msg_resource', 'patch-msg_resou...')
#6 /data/www/t/includes/installer/DatabaseUpdater.php(228): call_user_func_array(Array, Array)
#7 /data/www/t/includes/installer/DatabaseUpdater.php(196): DatabaseUpdater->runUpdates(Array, false)
#8 /data/www/t/includes/installer/DatabaseInstaller.php(248): DatabaseUpdater->doUpdates()
#9 /data/www/t/includes/installer/WebInstallerPage.php(513): DatabaseInstaller->doUpgrade()
#10 /data/www/t/includes/installer/WebInstaller.php(254): WebInstaller_Upgrade->execute()
#11 /data/www/t/mw-config/index.php(50): WebInstaller->execute(Array)
#12 /data/www/t/mw-config/index.php(18): wfInstallerMain()
#13 {main}

(In reply to comment #4)
> Need clarification on workaround.  I am having this issue exists with an
> upgrade from 1.17.0 to 1.18.0-beta1.

In your case, you can probably edit $wgDBTableOptions so that it doesn't include "charset=mysql4".

r102488

Even though https://www.mediawiki.org/wiki/Special:Code/MediaWiki/102488 made it into the mediawiki 1.18.0 release I'm still hitting a very similar issue when upgrading from 1.17.1 to 1.18.0:

[...]

...eu_wiki_id already renamed to eu_local_id.
...*_mime_minor fields are already long enough.
...rev_len column already populated.
Creating iwlinks table...
An error occured:
A database query syntax error has occurred.
The last attempted database query was:
"CREATE TABLE `iwlinks` (
 iwl_from int unsigned NOT NULL default 0,
 iwl_prefix varbinary(20) NOT NULL default '',
 iwl_title varchar(255) binary NOT NULL default ''
 ) ENGINE=InnoDB, DEFAULT CHARSET=mysql4
"
from within function "DatabaseBase::sourceFile( /var/www/www.mybrokensite.org/mediawiki/maintenance/archives/patch-iwlinks.sql )".
Database returned error "1115: Unknown character set: 'mysql4' (localhost)"
Backtrace:
#0 /var/www/www.mybrokensite.org/mediawiki/includes/db/Database.php(814): DatabaseBase->reportQueryError('Unknown charact...', 1115, 'CREATE TABLE `i...', 'DatabaseBase::s...', false)
#1 /var/www/www.mybrokensite.org/mediawiki/includes/db/Database.php(3013): DatabaseBase->query('CREATE TABLE `i...', 'DatabaseBase::s...')
#2 /var/www/www.mybrokensite.org/mediawiki/includes/db/Database.php(2911): DatabaseBase->sourceStream(Resource id #253, false, false, 'DatabaseBase::s...')
#3 /var/www/www.mybrokensite.org/mediawiki/includes/installer/DatabaseUpdater.php(364): DatabaseBase->sourceFile('/var/www/www.my...')
#4 /var/www/www.mybrokensite.org/mediawiki/includes/installer/DatabaseUpdater.php(379): DatabaseUpdater->applyPatch('patch-iwlinks.s...', false)
#5 [internal function]: DatabaseUpdater->addTable('iwlinks', 'patch-iwlinks.s...')
#6 /var/www/www.mybrokensite.org/mediawiki/includes/installer/DatabaseUpdater.php(233): call_user_func_array(Array, Array)
#7 /var/www/www.mybrokensite.org/mediawiki/includes/installer/DatabaseUpdater.php(197): DatabaseUpdater->runUpdates(Array, false)
#8 /var/www/www.mybrokensite.org/mediawiki/includes/installer/DatabaseInstaller.php(248): DatabaseUpdater->doUpdates()
#9 /var/www/www.mybrokensite.org/mediawiki/includes/installer/WebInstallerPage.php(513): DatabaseInstaller->doUpgrade()
#10 /var/www/www.mybrokensite.org/mediawiki/includes/installer/WebInstaller.php(254): WebInstaller_Upgrade->execute()
#11 /var/www/www.mybrokensite.org/mediawiki/mw-config/index.php(50): WebInstaller->execute(Array)
#12 /var/www/www.mybrokensite.org/mediawiki/mw-config/index.php(18): wfInstallerMain()
#13 {main}

The wiki in question is 2006 vintage and has been upgraded through numerous mediawiki versions since but - not sure if it has ever actually ran on MySQL 4.

Reopen bug?

Reopening and marking for discussion tomorrow.

Good.  In the meantime I've found a time consuming manual method to convert the wiki to another charset but I've kept a snapshot of the VM for further testing, if needed.  Thanks!

We're going to provide a better fix for the string replace, document this better or replace or give an error message.  Taking off the blocker list.

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

I can reproduce the error in MySQL, but with r102488 followed up with r105010 (not that this should matter in this case, as it's not the ENGINE/TYPE at fault, it's the charset

I'm confused

It seems like a WFM, so I wonder what's different about yours

$cmd = $this->replaceVars( $cmd );

is doing what it is supposed to do

(In reply to comment #5)
> (In reply to comment #4)
> > Need clarification on workaround.  I am having this issue exists with an
> > upgrade from 1.17.0 to 1.18.0-beta1.
> 
> In your case, you can probably edit $wgDBTableOptions so that it doesn't
> include "charset=mysql4".

Hello sir, 
please can you tell us where we might find the $wgDBTableOptions ?
I would love to change this in mine as I have the same setup, sql 5 or something. 
but I don't think this $wgDBTableOptions is very straightforward to find since you didn't say where it is, if it is in an options screen, in a setting file or anything.

I have looked in every file that i can see referenced in that backtrace thing. can't find anything.

LocalSettings.php

(In reply to comment #14)
> LocalSettings.php

i'm not sure how that is possible, since the installer is the thing bringing up this issue and the installer is what creates the localsettings.php file. 
in other words, the file does not yet exist (i did actually look for it prior to my first post in this page). 
I also checked in LocalSettingsGenerator.php and could not find anything in there.

This bug was opened saying errors on upgrade, not errors on new install

On upgrade, your LocalSettings.php will be taken into account

(In reply to comment #16)
> This bug was opened saying errors on upgrade, not errors on new install
> 
> On upgrade, your LocalSettings.php will be taken into account

i see. well I am upgrading. but I changed my localsettings to localsettings.old.
is this wrong? 
it would not even run the upgrader with localsettings in place. it gave me a encoding error. the server hated it for some reason. 
anyway, it is upgrading the database tables, but the localsettings.php is not present, it is renamed to localsettings.old.php

has anyone found a fix for this yet? I need a work around or something. no one seems to know where to remove it from source code to hack it into working.

my thoughts are that the only way I could do this is to manually create the database tables. I checked my encoding and sql4 is not an option. neither is sql5 or any variation. just regular standard encoding like utf8 etc. 
I figured if i manually run the query, without the encoding option present, it might work. but then i need to make sure I get all the tables and don't miss any or i risk having a half baked database that won't run my current wikimedia version or the latest build.

Something had already gone wrong with the non-ASCII characters in my wiki.  Some of öäüßÖÄÜé were correct, other instances of the same characters were corrupted.  So when I ran into the upgrade issue, I decided to go for the sledgehammer solution.  With the mixed charsets already in the table I converted the table from the old charset which was latin1 to UTF-8 stored in binary format by executing something like

  ALTER TABLE cur CONVERT TO CHARACTER SET utf8;
  ALTER TABLE cur CONVERT TO CHARACTER SET binary;

on every table.  This may have done bad things to the non-latin1 (ISO-8859-1) characters in the table but that was hard to avoid at this stage.

The ALTER commands will fail on a bunch of tables that have indexes on columns that need to be converted between charsets.  I dealt with those cases by dropping and re-creating the index.  I'm sure the SQL wizard are screaming now and I'm interested in there prefered solution.

Anyway, after this procedure the Mediawiki upgradeskript ran successful; I then went through all pages manually fixing up the damaged non-ASCII characters.  Fortunately I had to do this part only for one of my 6 affected wikis and that was small wiki only.

after taking a few hours digging and searching, the easiest way to fix this problem and get the upgrade to finish was to replace /*$wgDBTableOptions*/ with ENGINE=InnoDB, DEFAULT CHARSET=binary in all files in /maintanence/archive

(In reply to comment #21)
> after taking a few hours digging and searching, the easiest way to fix this
> problem and get the upgrade to finish was to replace /*$wgDBTableOptions*/ with
> ENGINE=InnoDB, DEFAULT CHARSET=binary in all files in /maintanence/archive

The idea is that is substituted for the string at run time, by the value that should be in your local settings for it

http://svn.wikimedia.org/viewvc/mediawiki/trunk/phase3/includes/DefaultSettings.php?view=markup#l1196


In trunk, at least,

 /** MySQL table options to use during installation or update */1196 $wgDBTableOptions = 'ENGINE=InnoDB';


Is the only other option MediaWiki would set it to...

Ah, one more issue during upgrade.  Another upgrade issue is that the installer will only upgrade the interwiki table on initial installation but won't upgrade it later.  In the dark ages the iw_prefix column was generally in mixed uppper / lower case.  This worked because the column was something like a varchar but after the conversion to binary the db will no longer apply the concept of upper / lower cases during a lookup.

I opted to discard and re-create the interwiki table, then re-populate it with the same data that a fresh install would receive.  Then on top of that all my local changes and additions of the table.

This one is sort of related only so maybe it deserves its own bug?

Hello,
I want to upgrade from MW 1.16.4 with MySql 5 database and I have same bug with same message:
     Database returned error "1115: Unknown character set: 'mysql4' (localhost)"

I'm not an expert with php and database but I have looked inside code and I have found where the value 'mysql4' is determined in upgrade mode (I dont know if it's same in installation mode, I have not tested)
This is in file: ...\mediawiki-1.18.0\includes\installer\MysqlInstaller.php at line #160
	# Determine existing default character set
	if ( $conn->tableExists( "revision" ) ) {
		$revision = $conn->buildLike( $this->getVar( 'wgDBprefix' ) . 'revision' );
		$res = $conn->query( "SHOW TABLE STATUS $revision", __METHOD__ );
		$row = $conn->fetchObject( $res );
		if ( !$row ) {
			$this->parent->showMessage( 'config-show-table-status' );
			$existingSchema = false;
			$existingEngine = false;
		} else {
			if ( preg_match( '/^latin1/', $row->Collation ) ) {
				$existingSchema = 'mysql4';
			} elseif ( preg_match( '/^utf8/', $row->Collation ) ) {
				$existingSchema = 'utf8';
			} elseif ( preg_match( '/^binary/', $row->Collation ) ) {
				$existingSchema = 'binary';
			} else {
				$existingSchema = false;
				$this->parent->showMessage( 'config-unknown-collation' );
			}
			if ( isset( $row->Engine ) ) {
				$existingEngine = $row->Engine;
			} else {
				$existingEngine = $row->Type;
			}
		}
	} else {
		$existingSchema = false;
		$existingEngine = false;
	}

In my database I have different collation, som tables have utf8_general_ci, and others latin1_swedish_ci
Is this normal or not ?

For the table 'revision' collation is 'latin1_swedish_ci' and it' why the upgrade set the value to mysql4

What do you think about that, and have you an idea to correct

Thanks

(In reply to comment #25)
> Hello,

> For the table 'revision' collation is 'latin1_swedish_ci' and it' why the
> upgrade set the value to mysql4
> 
> What do you think about that, and have you an idea to correct
> 
> Thanks

Bruno, that is very interesting. many of my tables in my schema are in latin1_swedish. 
I do not know why this is either. it seems arbitrary that these tables would be using different charset. could it be that this is another name for iso8859?  

I might assume that we can change sql4 in that file to a charset that exists in our database, would it hurt to change that to utf8? or what about :-

if ( preg_match( '/^latin1/', $row->Collation ) ) {
                $existingSchema = 'latin1';

would this work perhaps? 
it seems a bit more logical, if the new tables are to match the old tables then why not?  it does seem like a bit of a messed up logic.

(In reply to comment #26)

>... could it be that this is another name for iso8859? 

I think yes, In MySQL 5.0 Manual chapter 9.1.13.2. West European Character Sets at this place dev.mysql.com/doc/refman/5.0/en/charset-we-sets.html there are this:
"...latin1 is the default character set. MySQL's latin1 is the same as the Windows cp1252 character set. This means it is the same as the official ISO 8859-1..."
 

> I might assume that we can change sql4 in that file to a charset that exists in
> our database, would it hurt to change that to utf8? or what about :-
> would this work perhaps? 
I think this is a possibility

But for now, I tested another solution:
The database of my wiki is in utf8, so I changed the charset of the table 'Revison' from latin1 to utf8, and then the upgrade completed successfully.
I do not know if this is correct, but for now I have not seen any malfunction.

Perhaps an old upgrade had functioned poorly, I think in the transition from MySQL 4 to 5, and conversion to utf8.
I have not tried a fresh installation, but it would know the charset assigned to each table, and then possibly to correct our bases, or to issue a patch integrated with upgrades.

(In reply to comment #7)
> Even though https://www.mediawiki.org/wiki/Special:Code/MediaWiki/102488 made
> it into the mediawiki 1.18.0 release I'm still hitting a very similar issue
> when upgrading from 1.17.1 to 1.18.0:
(In reply to comment #12)
> I can reproduce the error in MySQL, but with r102488 followed up with r105010

This is not surprising, because during the upgrade, the code corrected by r102488 is not used (I do not know when an installation))

Took me hours to figure this out, but I had to change the following in 
/includes/installer/MysqlInstaller.php

if ( preg_match( '/^latin1/', $row->Collation ) ) {
# $existingSchema = 'mysql4';
$existingSchema = 'binary';


And now the update worked - I still have to check if everything's correct.

Adding patch keyword per comment 29

(In reply to comment #26)
> or what about :-
> 
> if ( preg_match( '/^latin1/', $row->Collation ) ) {
>                 $existingSchema = 'latin1';
> 
> would this work perhaps? 
> it seems a bit more logical, if the new tables are to match the old tables then
> why not?  it does seem like a bit of a messed up logic.

Hi there,
I recently had the same problem, and made this change as you suggested and the installer worked.
Hope that helps answer a few questions. :)

(In reply to comment #31)
> (In reply to comment #26)
> > or what about :-
> > 
> > if ( preg_match( '/^latin1/', $row->Collation ) ) {
> >                 $existingSchema = 'latin1'; 
>
> I recently had the same problem, and made this change as you suggested and the
> installer worked.

I tested and confirmed this and applied the fix in r108044