MySQL charset error?

CB

I'm trying to install media wiki on my server, and I get this message during install:

MySQL 4.1/5.0 charset mode enabled, but older version detected; will likely fail.

then, two steps later, it fails out of the installation with this message:

CREATE TABLE `user` ( user_id int 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, user_newpassword tinyblob NOT NULL, user_newpass_time binary(14), user_email tinytext NOT NULL, user_options blob NOT NULL, user_touched binary(14) NOT NULL default '', user_token binary(32) NOT NULL default '', user_email_authenticated binary(14), user_email_token binary(32), user_email_token_expires binary(14), user_registration binary(14), user_editcount int, PRIMARY KEY user_id (user_id), UNIQUE INDEX user_name (user_name), INDEX (user_email_token) ) ENGINE=InnoDB, DEFAULT CHARSET=binary " failed with error code "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' DEFAULT CHARSET=binary' at line 20 (db1851.perfora.net)".

Is this error caused by the charset mode? Anyone know what I can do about it.

Here's the entire dialog if it helps:

[LIST]
[*]PHP 5.2.8 installed
[*]Found database drivers for:  MySQL  SQLite
[*]PHP server API is cgi; using ugly URLs (<tt>index.php5?title=Page_Title</tt>)
[*]Have XML / Latin1-UTF-8 conversion support.
[*]Session save path (<tt>/tmp</tt>) appears to be valid.
[*]PHP's <tt>memory_limit</tt> is 40M.
[*]Couldn't find [URL="http://turck-mmcache.sourceforge.net/"]Turck MMCache[/URL],         [URL="http://eaccelerator.sourceforge.net/"]eAccelerator[/URL],         [URL="http://www.php.net/apc"]APC[/URL] or [URL="http://trac.lighttpd.net/xcache/"]XCache[/URL];         cannot use these for object caching.
[*]Found GNU diff3: <tt>/usr/bin/diff3</tt>.
[*]Found ImageMagick: <tt>/usr/bin/convert</tt>; image thumbnailing will be enabled if you enable uploads.
[*]Found GD graphics library built-in.
[*]Installation directory: <tt>/homepages/12/d208215605/htdocs/isles/wiki</tt>
[*]Script URI path: <tt>/wiki</tt>
[*]Installing MediaWiki with <tt>php5</tt> file extensions
[*]Environment checked. You can install MediaWiki.
[B]Notice[/B]:  Undefined variable: wikiLang in [B]/homepages/12/d208215605/htdocs/isles/wiki/config/index.php[/B] on line [B]1999[/B]
[*] [B]Generating configuration file...[/B]
[*]Database type: MySQL
[*]Loading class: DatabaseMysql
[*]Attempting to connect to database server as dbo274114825...success.
[*]Connected to 4.0.27-max-log; [B]MySQL 4.1/5.0 charset mode enabled, but older version detected; will likely fail.[/B]
[*]Database <tt>db274114825</tt> exists
[*]Creating tables...Query "CREATE TABLE `user` ( user_id int 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, user_newpassword tinyblob NOT NULL, user_newpass_time binary(14), user_email tinytext NOT NULL, user_options blob NOT NULL, user_touched binary(14) NOT NULL default '', user_token binary(32) NOT NULL default '', user_email_authenticated binary(14), user_email_token binary(32), user_email_token_expires binary(14), user_registration binary(14), user_editcount int, PRIMARY KEY user_id (user_id), UNIQUE INDEX user_name (user_name), INDEX (user_email_token) ) ENGINE=InnoDB, DEFAULT CHARSET=binary " failed with error code "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' DEFAULT CHARSET=binary' at line 20 (db1851.perfora.net)".
[/LIST]

kryyst

Can you give us a print out of the global variables you are using for MySql. Based on that line it looks like things are set to binary, which is well, odd.

CB

The install program recommended binary. Should I change it to UTF-8?

edit: tried UTF-8, and I get the same error.

These are the only MySQL specific options the installer gives me. Does that help?

kryyst

Hmmm googling's not finding much for that error only thing a few things are hinting is that your machine isn't processing php correctly and that the php engine isn't connecting to the mysql engine correctly.

Khaos

Like all things MySQL, the documentation is pretty dense. Have you read this article in the MySQL 5.0 Reference Manual: 9.6. Problems with Character Sets?

It seems that your character sets may not have been compiled with the MySQL binaries or the character set definitions / character array definitions are invalid. There is a whole host of messy documentation on the subject that I found in the ref. manual. I have not read any of it myself aside from skimming the linked article.

Are you installing a compiled release that you downloaded, or did you compile it yourself on your server?

CB

I downloaded an archive file containing the latest version of MediaWiki from their website. The instructions I found led me to extract the files into a directory on my server (hosted by 1and1), and run the configuration utility from my web browser.

Was there something else I should have done first?

BTW: I don't know much about MySQL (if you didn't notice yet). I went into this thinking it would be just like installing a program on my PC.

Khaos

Eep.. Ok, I'm not sure either then. I did install MySQL once on Apache, but that was 7 years ago and I didn't run into any annoying snags like you have here. If I am home tonight I will dig into it with you if you don't have it sorted out already.

CB

Could it make a difference that my serveris on MySQL 4.0?

kryyst

Yes that makes a big difference if you are trying to use a package designed for mysql 4.1/5. There is a rather large compatibility gap between mysql 4 and 4.1 and then again in 5.

CB

Ah... When I see a .1, I assume non-critical changes...

The installer specifically recommends against the 4.0 charset, but I guess I'll try the install with that option.

edit: That seems to have done it. Thanks guys.

kryyst

Normally you'd be right but in this case the 4.0 to 4.1 was a rather big version change.

But glad that fixed it for you.

toddthuma

CB wrote:

Ah... When I see a .1, I assume non-critical changes...

The installer specifically recommends against the 4.0 charset, but I guess I'll try the install with that option.

edit: That seems to have done it. Thanks guys.

Hey, I have found the same issue you found. So how do you install with the 4.0 charset option?

Thanks in advance!

Todd

CB

Hi Todd. Welcome to Icrontic.

All I did was check the box for 4.0 (the one it recommends against clicking), and then it installed just fine.

toddthuma

CB wrote:

Hi Todd. Welcome to Icrontic.

All I did was check the box for 4.0 (the one it recommends against clicking), and then it installed just fine.

Hey, I think you may have been referring to the SQLite option versus the MySQL option. When I left the MySQL option I kept getting the error. When I finally selected the SQLite option everything installed.

I too am on 1and1.com and enjoy their service though I do not do much with it. Hopefully, that will change as I intend to roll out a lexicon of LEGO Bricks and elements at BrickLexicon.com. Check it out, in a few weeks.

Todd