Using Fantastico! script in cPanel (popular Web hosting control panel) is a quick and easy way to install and setup Moodle, but the DB tables are set in latin charset in stead of UTF-8 charset. That is why input data (characters in a charset other than latin) are collapsed (like ????).
This can be fixed by set all the tables and the fields in the Moodle DB to UTF-8 charset. The attached PHP script is for doing that.
usage: just upload the PHP script to your Moodle server (installed by Fantastico) and run it by accessing it with a Web browser. Fill the DB information of your Fantastico-installed Moodle in the form and click OK.
I hope you find the script useful.
# Be sure to backup your DB if you have some existing content in it.
fixing Fantastico! installation problem of DB charset not UTF-8
by Toshihiro KITA -
Number of replies: 22
In reply to Toshihiro KITA
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Clinton Randolph -
I used fantastico in Siteground to upgrade my Moodle installation on my hosts site without any problems so far. I have an issue with installation of the certificate module. I installed it according to the MoodleDocs instrictions, but the final step 3 I could not figure out what I was supposed to do.
Clint R
In reply to Clinton Randolph
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Toshihiro KITA -
I have nerver used Certificate module, but I guess the step you mean is:
----------------------------------------
Go to your Moodle, and click on the Notifications link in the administation block. Moodle knows something has been added and it will proceed to install the certificate module.
----------------------------------------
If so, login as admin, at the top page (Moodle 1.8 or later)
you possibly find 'Notifications' in Site Administration block.
If you click it, the installation process of the module will automatically start.
And in
Site Administration -> Modules -> Activities
you find the all the modules installed in your Moodle.
(BTW It sometimes takes some minutes or hours for the newly installed module to be recognized in Moodle.)
and I guess you maybe need some PHP module or library for Certificate module to run.
----------------------------------------
Go to your Moodle, and click on the Notifications link in the administation block. Moodle knows something has been added and it will proceed to install the certificate module.
----------------------------------------
If so, login as admin, at the top page (Moodle 1.8 or later)
you possibly find 'Notifications' in Site Administration block.
If you click it, the installation process of the module will automatically start.
And in
Site Administration -> Modules -> Activities
you find the all the modules installed in your Moodle.
(BTW It sometimes takes some minutes or hours for the newly installed module to be recognized in Moodle.)
and I guess you maybe need some PHP module or library for Certificate module to run.
In reply to Toshihiro KITA
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Clinton Randolph -
I clicked on notifications and nothing seemed to happen. Today, I clicked on it again and it asked me to run a script which I did, but afterward I saw no changes in the modules. When I click on modules>activities>manage activities, the module is shown but still don't see anyway to run it.
Clint R
In reply to Toshihiro KITA
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Diana Brewster -
Is this procedure and script OK to use for Fantastico updates to Moodle? I've had good experience with Fantastico scripts so far on Lunarpages, my hosting company.
When I upgraded from 1.8 to 1.9, I converted all my tables to UTF-8 (manually, doing search-and-replace on the SQL file, then restoring that database.)
What will happen to Fantastico updates of an existing UTF-8 Moodle installation? Will databases be converted back to latin-1? Will there be a conflict?
Thanks in advance, if you know!
---Diana
When I upgraded from 1.8 to 1.9, I converted all my tables to UTF-8 (manually, doing search-and-replace on the SQL file, then restoring that database.)
What will happen to Fantastico updates of an existing UTF-8 Moodle installation? Will databases be converted back to latin-1? Will there be a conflict?
Thanks in advance, if you know!
---Diana
In reply to Diana Brewster
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by E. L. Cooper -
I am neither a fan nor a foe of fantastico but fantastico will not upon upgrade convert your data base from unicode back to latin1
In reply to Diana Brewster
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Toshihiro KITA -
I belive it is OK.
The PHP script I posted will not convert the contents of the table.
I guess newly created tables (if any) created after the Fantastico updates are in latin-1, so you need converting the table property and each field property in it to UTF-8 (e.g. you can run the PHP script above for it) .
The PHP script I posted will not convert the contents of the table.
I guess newly created tables (if any) created after the Fantastico updates are in latin-1, so you need converting the table property and each field property in it to UTF-8 (e.g. you can run the PHP script above for it) .
In reply to Toshihiro KITA
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Diana Brewster -
thanks very much for the reply and guidance re unicode tables and Fantastico updates!
---Diana
In reply to Diana Brewster
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Diana Brewster -
... and the update went very well!
In reply to Diana Brewster
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Toshihiro KITA -
very glad to hear that. 
In reply to Toshihiro KITA
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Brad Iverson -
I installed Moodle v1.9.2 via Fantastico, uploaded the PHP script to /public_html, ran it via my browser, and got the following error message:
The requested URL /tablelist.php was not found on this server.
Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
In reply to Brad Iverson
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Toshihiro KITA -
Oh I am sorry! The script name was wrong.
Please rename my script as 'tablelist.php' before uploading it.
Or use the corrected script attached to this post.
Please rename my script as 'tablelist.php' before uploading it.
Or use the corrected script attached to this post.
In reply to Toshihiro KITA
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Brad Iverson -
Eeek! Now I'm getting more errors using that corrected script (path and user name changed):
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'user'@'localhost' (using password: YES) in /path/to/public_html/alltabletoutf8.php on line 32
Warning: mysql_list_tables(): supplied argument is not a valid MySQL-Link resource in /path/to/public_html/alltabletoutf8.php on line 33
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /path/to/public_html/alltabletoutf8.php on line 37
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'user'@'localhost' (using password: YES) in /path/to/public_html/alltabletoutf8.php on line 32
Warning: mysql_list_tables(): supplied argument is not a valid MySQL-Link resource in /path/to/public_html/alltabletoutf8.php on line 33
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /path/to/public_html/alltabletoutf8.php on line 37
In reply to Brad Iverson
Re: fixing Fantastico! installation problem of DB charset not UTF-8
by Toshihiro KITA -
That is typically the errors you get when the username or the password or the database name is wrong. You can see the right values of these in config.php in your Moodle directory.
Your are correct. I guess I missed filling in the password field when I used Fantastico to reinstall Moodle, so it generated one for me. I still got errors when I ran the PHP script, though. I'd attach the ouput text to this, but it is too large (511 KB). Here's an example of one of the many errors:
ALTER TABLE `adodb_logsql` CHANGE `id` `id` bigint(10) unsigned CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
FALSE
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 utf8 COLLATE utf8_unicode_ci NOT NULL' at line 1
That second part, "You have an error..." is repeated for all errors. The bottom of the output reads:
Finished! execution: 1992 error : 1291
ALTER TABLE `adodb_logsql` CHANGE `id` `id` bigint(10) unsigned CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
FALSE
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 utf8 COLLATE utf8_unicode_ci NOT NULL' at line 1
That second part, "You have an error..." is repeated for all errors. The bottom of the output reads:
Finished! execution: 1992 error : 1291
In reply to Brad Iverson
Re: PW wrong, but errors still on alltabletoutf8 output
by Toshihiro KITA -
Yes, Those are errors but not serious.
The script tries to modify charset of ALL the fields even if the modification is not necessary (and not possible), for example a field storing integer numbers like bigint(10).
I believe you can now input unicode chararcters in your Moodle pages. Please try.
The script tries to modify charset of ALL the fields even if the modification is not necessary (and not possible), for example a field storing integer numbers like bigint(10).
I believe you can now input unicode chararcters in your Moodle pages. Please try.
In reply to Toshihiro KITA
Re: PW wrong, but errors still on alltabletoutf8 output
by Brad Iverson -
Yes, using phpMyAdmin, I see that utf8_unicode_ci is in the collation field for several of the tables, the ones that have something in the collation field. However, when I select the database and go to operations, latin1_general_ci is in the collation field. Is that how it should be?
In reply to Brad Iverson
Re: PW wrong, but errors still on alltabletoutf8 output
by Toshihiro KITA -
Which field in which table is in still latin1_general_ci? Would you give me several examples so that I can compare with my Fanstastico-installed Moodle.
And are unicode characters (Chinese?) still not usable in your Moodle?
And are unicode characters (Chinese?) still not usable in your Moodle?
In reply to Toshihiro KITA
Re: PW wrong, but errors still on alltabletoutf8 output
by Brad Iverson -
I haven't tried any Chinese characters yet. I just installed Moodle and don't have any content (i.e., I'm trying to avoid a problem in the future). After running your script, there is no latin1_general_ci in the collation field of any tables. All I see in the tables is utf8_unicode_ci in the collation fields.
The latin1_general_ci I was referring to is in the collation field of the database itself. I see this by selecting the database and then Operations in phpMyAdmin. It's the default character set. Is that the way it should be?
The latin1_general_ci I was referring to is in the collation field of the database itself. I see this by selecting the database and then Operations in phpMyAdmin. It's the default character set. Is that the way it should be?
In reply to Brad Iverson
Re: PW wrong, but errors still on alltabletoutf8 output
by Toshihiro KITA -
Now I see what you mean. Thank you.
In the setting of my Moodle site, the collation of database itself is latin1_swedish_ci but it works well for any Japanese input so far. So probably you do not have to care about it and maybe it is better to be set to utf8_unicode_ci.
In the setting of my Moodle site, the collation of database itself is latin1_swedish_ci but it works well for any Japanese input so far. So probably you do not have to care about it and maybe it is better to be set to utf8_unicode_ci.
It seems that after i fixed the chinese language issue, I can't change or input my grades. I notice my installation of alltabletoutf8.php has got some errors, only 129X out of 18xx. Could this be the cause? Anyone can shed some light?
It appears that this script may remove the default value for some fields in database, so commands that are not null fail.
The problem with this script is it sets EVERY field to NOT NULL as well as losing any default value set. This prevents Gradebook and a few other things from working properly.
I had to completely recreate my Moodle database table structures then restore in my Moodle data to fix the site after this. (Although my site is now UTF8.)
If anyone else is experiencing this I've written up what I did to fix it, but it is pretty rough so make sure you understand what you are doing.
http://jrudd.org/wordpress/2009/06/18/moodle-structure-problems/
I had to completely recreate my Moodle database table structures then restore in my Moodle data to fix the site after this. (Although my site is now UTF8.)
If anyone else is experiencing this I've written up what I did to fix it, but it is pretty rough so make sure you understand what you are doing.
http://jrudd.org/wordpress/2009/06/18/moodle-structure-problems/