I'm trying to install the latest public beta of Moodle on my server. It is a shared UNIX server, my host is hostmonster.com. I have followed the installation process to the letter but continue to get hung up when creating the config.php file. I keep getting the following error:
It is required that you store all your data in Unicode format (UTF-8). New installations must be performed into databases that have their default character set as Unicode. If you are upgrading, you should perform the UTF-8 migration process (see the Admin page).
I have checked and double checked the database and I AM using Unicode, and all of my other databases are working fine. I don't know a lot about PHP coding but I am willing to give it a try. Does anyone have a solution for this?
CR,
The solution, I believe, is to find a host that is not allergic to Unicode, or convince them to let you have a genuine UTF-8 database. See Moodle Tracker issue http://tracker.moodle.org/browse/MDL-11743. Be sure to read the comments before concluding that your database is really UTF-8. There you will find the SQL query that you need to run to confirm this, one way or the other.
RLE
Thanks for the swift response. As you suggested I ran the query and came up with a surprising result that others have gotten=latin1(see screen shot). FYI my host is HostMonster and I see that others here are having similar problems. So now that I know that MySQL and Hostmonster are lying to me about Unicode what can I do to fix it?
Hmm I'm just curious that for a hosting company what kind of concern they would have to convert a single database to UTF-8? I thought that it just require their DBA or system admin to run this one line on mysql?
ALTER DATABASE moodle DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; Is there any special security concern or anything that would concern a typical hosting company to run database in UTF-8? Just curious..
WHC,
You have just proved that you have not read the comments in the Tracker issue I referenced in my earlier post. If you had, you would have found that the SQL query you suggest is exactly what the victims of these so-called hosts used, but somehow the host system either didn't allow it to work properly, or undid its effect.
As to your "Why?" question, you'll have to ask BlueHost.com and HostMonster.com. All their victims await their response.
CR,
As I said before, the only possible solutions are
- get your web host to change their policy (unlikely), or
- switch hosts!
RLE
Wen - Hostmonster allows for the database to be set to utf8; however, we are checking the default for the database server rather than the provided database. I'm looking into the proper SQL statement to get the data that I really think we should be checking for which is something along the lines of SHOW COLLATION for the specific DATABASE moodle rather than the default for creating a new database.Peace - Anthony
OK - I've discovered why things are done the way they are. It is a MySQL thing. Prior to 5.0.6 there was no easy way to determine the collation of the database; however, now there is. I am attaching the code from phpMyAdmin that shows how it is handled. I would advocate that the code be changed in Moodle to check for the database collation rather than the server's default. This will help avoid problems on hosts. Essentially the new SQL statement is:
SELECT DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';
I have created MDL-12062 for those who may wish to vote/watch it.
Peace - Anthony
SELECT DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';
I have created MDL-12062 for those who may wish to vote/watch it.
Peace - Anthony
Christopher - I have a little experience with HostMonster and I am going to try to work with them to fix up how they handle Moodle. I'm currently in conversation with them about enabling the xmlrpc extension in PHP to allow for Moodle Networking. Currently, I think the best way although I have not tried it yet is to not use the Fantastico automatic install of Moodle that they provide as it creates the tables in a non-unicode character set. I would upload the Moodle zip file and extract it, manually create the database, change the encoding to utf8. Now here is where the fun begins. As I understand things from a quick glance at it, Moodle is actually checking to see if the database default encoding is UTF8 rather than the chosen database. I don't see why Moodle should care what the Database server default character set is provided the database selected is UTF8. I could be wrong so I am going to try to create a database, export it and upload it to see if it works. If I find a way of making things play nicely, then I will let you know. Peace - Anthony
Christopher - Just a follow up on hostmonster.com and the xmlrpc extension issue in php. It seems that hostmonster.com have upgraded their servers so that most of the boxes now have the php xmlrpc extension installed. I have not tried actually using the Moodle Networking features via hostmonster.com so I cannot verify that it works but it does seems like the foundation is there to support Moodle Networking. This was not previously the case. Peace - Anthony
So the bottom line is that I was able to get Moodle working on a HostMonster.com site but I had to cheat. I installed Moodle 1.9beta2 by copying the zip file to the site and extracting it. Here is how I cheated. I installed Moodle 1.9beta2 on my local machine and created a fresh database. I then used phpMyAdmin to export the freshly created database to a sql file. Then I created the database and database user on the hostmonster.com site and imported. I then modified the config.php and uploaded it. The site works fine; however, if you go to the environment page you get the warning about unicode. I also created a work ticket to encourage hostmonster.com to change their fantastico installation so that it actually creates the database with the utf8_general_ci collation rather than the latin1_swedish. I hope that this helps. Let me know if you have any questions. Peace - Anthony
This bug has been reported and needs your vote so it gets fixed. Please go to http://tracker.moodle.org/browse/MDL-11743 and vote to get this fixed!!!
David - Thanks for pointing out that this was a duplicate. I have marked MDL-12062 as a duplicate of MDL-11743 and voted for it. It may be best if we can simply have a patch to the install files that ignores the check for users that know that everything is correct; however, I would like to work toward a more accurate long term solution by having Moodle check for the character set of the database rather than the default character set of the MySQL server. Peace - Anthony
I'm sorry - I have to revert my post. In fact, my Moodle 1.8.3 database has something like a mixture of utf8 and latin-1 set:
mysql> SHOW LOCAL VARIABLES LIKE 'character%';
+--------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/openpkg/share/mysql/charsets/ |
+--------------------------+------------------------------------+
How can I fix that?
Thanks!
mysql> SHOW LOCAL VARIABLES LIKE 'character%';
+--------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/openpkg/share/mysql/charsets/ |
+--------------------------+------------------------------------+
How can I fix that?
Thanks!
Rob - The way that I used was always somewhat of a cheat because I knew that I did not have much multilingual material (if any) that would be effected by it. I simply used MySQLAdmin, select the database, go to Operations, and change the collation to UTF8 (usually utf8_general_ci). I hope that helps. Peace - Anthony
Thanks a lot, Anthony, that was really easy. For now on, I will catch an eye on all CHARACTER SET and COLLATE settings in my MySQL databases, tables and rows. The default CHARACTER SET and COLLATE for the *database* is now set properly, however there are some tables who still does not have these set properly.
I can list all these tables with
SHOW TABLE STATUS WHERE Collation != 'utf8_general_ci';
and correct each single table with
ALTER TABLE <table_name> CHARACTER SET utf8 COLLATE utf8_general_ci;
but I'm currently not aware of a way to alter them all at once. Did you also handle this?
Best,
rob.
I can list all these tables with
SHOW TABLE STATUS WHERE Collation != 'utf8_general_ci';
and correct each single table with
ALTER TABLE <table_name> CHARACTER SET utf8 COLLATE utf8_general_ci;
but I'm currently not aware of a way to alter them all at once. Did you also handle this?
Best,
rob.
Robert - Yes... sort of, I have handled doing more than one at a time but it was pretty ugly. Essentially I had to do all of the tables. So I simply had the list from phpmyadmin, copied and pasted that list into a text editor and added the ALTER TABLE stuff around it. Then once I had all the queries, I copied them into the SQL window in PHPMyAdmin and let them all run at once. It saves some typing. Does that make sense? Peace - Anthony
In the meantime, I have recognized that the ALTER TABLE Syntax of MySQL only supports altering one table at once. Your workaround is a good time-saver! But converting each column of a table using something like
ALTER TABLE <table_name> CHANGE <column_name> <column_name> <type> CHARACTER SET utf8 COLLATE utf8_general_ci <null_or_not>
does not seem to make sense. This would be a pain. Did you care about these? I think I will let them as they are and just update my 1.8 Moodle to 1.9.
best,
rob.
ALTER TABLE <table_name> CHANGE <column_name> <column_name> <type> CHARACTER SET utf8 COLLATE utf8_general_ci <null_or_not>
does not seem to make sense. This would be a pain. Did you care about these? I think I will let them as they are and just update my 1.8 Moodle to 1.9.
best,
rob.
Anthony
Thanks for all of your work on this issue. I'm a high school teacher who can tinker a little with code but I am certainly no programmer. I decided to go with Hostmonster because they advertise Moodle support right on their website. I really want to start using Moodle with my classes so I hope this issue can be resolved soon. Thanks again.
Chris
Thanks for all of your work on this issue. I'm a high school teacher who can tinker a little with code but I am certainly no programmer. I decided to go with Hostmonster because they advertise Moodle support right on their website. I really want to start using Moodle with my classes so I hope this issue can be resolved soon. Thanks again.
Chris
I installed through Fantastico, found the error in environment and requested the BlueHost techs change the database to utf-8. They did so after requesting my password (now changed) and the error in Server -> Environments went away. It would seem that BlueHost has taken care of this, at least they know how to resolve it anyway.