utf8 issues

[kirpi]

I got this error:

CREATE TABLE ft_account_settings ( account_id mediumint(8) unsigned NOT NULL, setting_name varchar(255) NOT NULL, setting_value mediumtext NOT NULL, PRIMARY KEY (account_id,setting_name) ) DEFAULT CHARSET=utf8 - 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=utf8' at line 6

and I'm stucked at step 3.
I'm running on a shared Linux hosting where mysql select version(); gives this result: 4.0.30-standard-log (which should be fine, I think).
[update: Form Tools check at step 2 gives this result instead: MySQL Version 5.1.56 PASS - I'm puzzled]

By searching for a solution here on the forum, I found that there is probably a way round [1] and Ben filed it as a bug [2].
Yet such a thread dates about 1.5 years ago.
Also, Ben confirmed on another thread [3] that all data is "stored as UTF-8 in the DB".

So, before trying to hack the code or doing something wrong, I wanted to please ask the community here about any possible updates on the matter, as well as the best way to go.

Thanks
kirpi

----
[1] http://forums.formtools.org/showthread.p...86#pid2686
[2] http://bugs.formtools.org/index.php?cmd=view&id=224
[3] http://forums.formtools.org/showthread.p...light=utf8

[Ben]

Hi Kirpi,

Thanks for the post! And sorry you're having trouble. Yes, I logged that as a bug a long while back, but it's such a fringe case I seem to have focused on other things.

To be perfectly honest, if I were to "fix" it, I'd just update the installation script to detect for this, then prevent the installation from continuing. Obviously that's not a fix, but at least it'll be clear what's going on and not throw that awful error you saw.

At this stage, other than updating your database the only fix *is* to hack the code, to remove all references. This is kind of crumby because you'd run into trouble whenever you upgrade, plus most modules may well need to be edited as well. And in the case of the premium modules, you may find you can't install them at all (because the installation code is obfuscated).

Any chance your hosting provider would be willing to add that charset? You could tell them it's *very* standard now.

Sorry I couldn't be more help.

- Ben

[kirpi]

> ...prevent the installation from continuing...
> Any chance your hosting provider would be willing
> to add that charset? You could tell them it's *very* standard now.

Hmmm...
So, you mean that there is no utf-8 charset available on that mysql install?
It sounds much weird because, as you say, utf-8 is so much used today...

I would be sorry to give up just because of this, you know.
Would there be any direct command I could try, please, in order to be sure of such a lack...?

Luigi

[Ben]

Hi Luigi,

So, you mean that there is no utf-8 charset available on that mysql install?

Yeah, that's what it sounds like. I don't see why else it would fail. To check out what character sets are available for your database, just select it, then run the following query:

Code:

SHOW CHARACTER SET

Look for one with charset "utf8". Let me know what you find! I'd be curious to know if that wasn't the cause.

- Ben

[EDIT: sorry! I meant SHOW CHARACTER SET (singular), not SHOW CHARACTER SETS. That would be too obvious... *thanks* MySQL....!]

[kirpi]

phpMyAdmin shows:

SHOW CHARACTER SET
Messaggio di MySQL
#1064 - 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 'CHARACTER SET' at line 1

I'm quite puzzled, Ben.
I'm on a cheap hosting, ok, BUT it works fine since years and hosts a *lot* of general applications (Wordpress,...) which adopt mysql and which, I am sure, make use of utf-8.
And why this last error? Not only that instal wouldn't use utf-8, but also would not know enough sql to understand and answer my question? Too strange.
Or, perhaps, I'm missing something.
As an example, look at my first post: I queried the system with
select version();
and I got: 4.0.30-standard-log
while your FormTools got instead: 5.1.56

Quite odd, on the whole.
I'm afraid *I* am doing something wrong...

Any more ideas to better test...?

Luigi
----
N.B. I use, for my websites, a flat-file wiki (pmwiki) since years, which works really well. For this reason I have no experience in databases.

[Ben]

Hi Luigi,

Huh, that IS strange... that query should work on MySQL 3, 4 and 5. See here for your version:
http://dev.mysql.com/doc/refman/4.1/en/s...r-set.html

I'd contact your hosting provider. Maybe they chose to disable that query for some reason (?!) but until we can confirm you have utf-8 on your system, we won't be able to proceed.

Btw, I'm going to change this to a configurable setting in 2.2.0. I looked through the code and it's not too much work to remove all the hardcoded references. The modules will take more time to roll out, because all of them that use a database will need to be updated one by one. However, time-wise I can't give you any estimates: we have a LOT going on right now. Sorry about that.

- Ben