Moodle
  1. Moodle
  2. MDL-11743

install.php does not recognize UTF8 unicode setup and stops under DB Checker

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.2, 1.8.3
    • Fix Version/s: 1.6.6, 1.7.4, 1.8.4, 1.9, 2.0
    • Component/s: Installation
    • Labels:
      None
    • Environment:
      MySQL server 5.0.37
      Client 4.1.21
      PHP 5.1.16
      Linux Kernel version 2.6.22-9_1.BHsmp
    • Database:
      MySQL
    • URL:
      moodle-cvs.acpgomes.net
    • Affected Branches:
      MOODLE_18_STABLE
    • Fixed Branches:
      MOODLE_16_STABLE, MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE
    • Rank:
      30077

      Description

      install.php does not recognize UTF8 unicode setup and stops under DB Checker

      1. php.ini
        39 kB
        Antonio Carlos Pereira Gomes
      2. setuplib.php.diff
        0.7 kB
        Anthony Borrow
      3. setuplib.php.diff
        2 kB
        Anthony Borrow
      1. moodle183-geo.acpgomes.net.png
        59 kB
      2. MySqlSettings.jpg
        10 kB

        Issue Links

          Activity

          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Attached there are a few screenshots.

          Show
          Antonio Carlos Pereira Gomes added a comment - Attached there are a few screenshots.
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          I can help setting up a subdomain, a database an a FTP account under my environment to help tests. Just let me know. Personal contact at acpgomes@gmail.com.
          regards.
          Antonio Carlos.

          Show
          Antonio Carlos Pereira Gomes added a comment - I can help setting up a subdomain, a database an a FTP account under my environment to help tests. Just let me know. Personal contact at acpgomes@gmail.com. regards. Antonio Carlos.
          Show
          Antonio Carlos Pereira Gomes added a comment - See the posts: http://moodle.org/mod/forum/discuss.php?d=82258 http://moodle.org/mod/forum/user.php?id=257094&course=5
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          My php.ini file.

          Show
          Antonio Carlos Pereira Gomes added a comment - My php.ini file.
          Hide
          Antonio Carlos Pereira Gomes added a comment - - edited

          Settings to
          http://moodle-cvs.acpgomes.net/

          FTP Account:
          Server: ftp.acpgomes.net
          Username: martin@acpgomes.net
          Password: martin

          WebAddress
          http://moodle-cvs.acpgomes.net
          DataDirectory
          /home/acpgomes/public_html/moodle-cvsdata

          Database settings:

          Show
          Antonio Carlos Pereira Gomes added a comment - - edited Settings to http://moodle-cvs.acpgomes.net/ FTP Account: Server: ftp.acpgomes.net Username: martin@acpgomes.net Password: martin WebAddress http://moodle-cvs.acpgomes.net DataDirectory /home/acpgomes/public_html/moodle-cvsdata Database settings:
          Hide
          Birdie Newborn added a comment -

          I'm getting a similar result – the installation stalls at the requirement for UTF8 – it says "checking"" but it checked overnight and didn't resolve. I chose the only unicode I could find for my databases: MySQL charset: UTF-8 Unicode (utf8) and MySQL connection collation: utf8_unicode_ci.

          How do I get past this?

          This is Moodle 1.8.3+, no upgrade, fresh start.

          Show
          Birdie Newborn added a comment - I'm getting a similar result – the installation stalls at the requirement for UTF8 – it says "checking"" but it checked overnight and didn't resolve. I chose the only unicode I could find for my databases: MySQL charset: UTF-8 Unicode (utf8) and MySQL connection collation: utf8_unicode_ci. How do I get past this? This is Moodle 1.8.3+, no upgrade, fresh start.
          Hide
          Richard L. Enison added a comment -

          ACPG,

          From some of your screenshots offhand it looks like your database's character set is latin1, not utf8; and collation latini1_swedish_ci. Only the MySQL defaults are Unicode.

          RLE

          Show
          Richard L. Enison added a comment - ACPG, From some of your screenshots offhand it looks like your database's character set is latin1, not utf8; and collation latini1_swedish_ci. Only the MySQL defaults are Unicode. RLE
          Hide
          Birdie Newborn added a comment - - edited

          Richard –

          Those were old screenshots. I had latin1_swedish and switched it to utf8 unicode in all but one old database, which I left as it was. I could just dump it – would that make a difference?

          Show
          Birdie Newborn added a comment - - edited Richard – Those were old screenshots. I had latin1_swedish and switched it to utf8 unicode in all but one old database, which I left as it was. I could just dump it – would that make a difference?
          Hide
          Lynn Mole added a comment -

          I am also having the same issue with a new install of 1.8.2 or 1.9. I have confirmed that my new, empty database is definitely UTF8 collation and character set.
          See http://moodle.org/mod/forum/discuss.php?d=81369.
          I was able to install a slightly older version of 1.9beta a few weeks ago, which makes me think that it is a bug in the installer rather than a problem with my system.

          Show
          Lynn Mole added a comment - I am also having the same issue with a new install of 1.8.2 or 1.9. I have confirmed that my new, empty database is definitely UTF8 collation and character set. See http://moodle.org/mod/forum/discuss.php?d=81369 . I was able to install a slightly older version of 1.9beta a few weeks ago, which makes me think that it is a bug in the installer rather than a problem with my system.
          Hide
          Richard L. Enison added a comment -

          BN,

          I was referring to ACPG's screenshots.

          As for your databases, Moodle only uses one database. Did you mean all but one table? If so, that is probably your problem. Moodle 1.8 on requires the database to be 100% pure Unicode. Which means:

          1. the default character set and collation of the database must be UTF8;
          2. the default character set and collation of every table must be UTF8;
          3. the character set of every column in every table must be UTF8.

          RLE

          Show
          Richard L. Enison added a comment - BN, I was referring to ACPG's screenshots. As for your databases, Moodle only uses one database. Did you mean all but one table? If so, that is probably your problem. Moodle 1.8 on requires the database to be 100% pure Unicode. Which means: 1. the default character set and collation of the database must be UTF8; 2. the default character set and collation of every table must be UTF8; 3. the character set of every column in every table must be UTF8. RLE
          Hide
          Syndi Poston added a comment -

          I have had the same problem with the "unicode" error. I get to the PHP configuration screen and the database checker, and it says that unicode is not installed. However, bluehost.com says that it is installed on their web server, and I have verified that the database I created is set to utf8. The installer will not let me go beyond the error screen - it's stuck. What can I do? I am using v. 1.83

          Show
          Syndi Poston added a comment - I have had the same problem with the "unicode" error. I get to the PHP configuration screen and the database checker, and it says that unicode is not installed. However, bluehost.com says that it is installed on their web server, and I have verified that the database I created is set to utf8. The installer will not let me go beyond the error screen - it's stuck. What can I do? I am using v. 1.83
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          People who are experiencing this issue SHOULD VOTE in it. So, login at Moodle tracker and select the option VOTE on the left menu. It's important to vote, otherwise it may be taken as not relevant, and won't be fixed.
          Regards.
          Antonio Carlos.

          Show
          Antonio Carlos Pereira Gomes added a comment - People who are experiencing this issue SHOULD VOTE in it. So, login at Moodle tracker and select the option VOTE on the left menu. It's important to vote, otherwise it may be taken as not relevant, and won't be fixed. Regards. Antonio Carlos.
          Hide
          Birdie Newborn added a comment -

          I'm getting hung up even before tables are created in the database, which I have specified utf8 collation and characterset. I'd like to get started, It says it's checking, but hours later, no movement.

          Show
          Birdie Newborn added a comment - I'm getting hung up even before tables are created in the database, which I have specified utf8 collation and characterset. I'd like to get started, It says it's checking, but hours later, no movement.
          Hide
          Leah Davies added a comment -

          I'm experiencing the same issue with a fresh install of 1.8.3, database charset is definitely utf8 unicode and connection collation is utf8_unicode_ci.

          Show
          Leah Davies added a comment - I'm experiencing the same issue with a fresh install of 1.8.3, database charset is definitely utf8 unicode and connection collation is utf8_unicode_ci.
          Hide
          Richard L. Enison added a comment - - edited

          To everyone experiencing this issue, if you are using MySQL, please see my post http://moodle.org/mod/forum/discuss.php?d=82414#p365142. Feel free to try the same query and post the result here, on the forum, and/or send me a Moodle message.

          If you are using a different database type, a different query applies to you. Let us know.

          RLE

          EDIT: PS While waiting for a response from someone to my offer, I took another look at the script. Here are the queries to run for the other database types, in case anyone having this problem is using them:

          Postgres: SHOW server_encoding;

          Oracle: SELECT parameter, value FROM nls_database_parameters where parameter = 'NLS_CHARACTERSET';

          MS SQL Server is not tested; it is assumed to always use Unicode.

          Show
          Richard L. Enison added a comment - - edited To everyone experiencing this issue, if you are using MySQL, please see my post http://moodle.org/mod/forum/discuss.php?d=82414#p365142 . Feel free to try the same query and post the result here, on the forum, and/or send me a Moodle message. If you are using a different database type, a different query applies to you. Let us know. RLE EDIT: PS While waiting for a response from someone to my offer, I took another look at the script. Here are the queries to run for the other database types, in case anyone having this problem is using them: Postgres: SHOW server_encoding; Oracle: SELECT parameter, value FROM nls_database_parameters where parameter = 'NLS_CHARACTERSET'; MS SQL Server is not tested; it is assumed to always use Unicode.
          Hide
          Lynn Mole added a comment -

          Richard, I've created a new database but my cPanel only allows that through a MySQL plugin, which is creating a latin1 database by default with no option to create anything else. ALTER database in phpMyAdmin obviously isn't changing it properly, although it seems to be. For some reason phpMyAdmin's ability to create a MySQL database is not allowed with my web host, so what to do now.
          Can I create something locally and upload it and if so how?

          Show
          Lynn Mole added a comment - Richard, I've created a new database but my cPanel only allows that through a MySQL plugin, which is creating a latin1 database by default with no option to create anything else. ALTER database in phpMyAdmin obviously isn't changing it properly, although it seems to be. For some reason phpMyAdmin's ability to create a MySQL database is not allowed with my web host, so what to do now. Can I create something locally and upload it and if so how?
          Hide
          Syndi Poston added a comment -

          I gave up on installing v 1.8 and uploaded to install v 1.9. However, I am getting the same unicode error at the "database checker" screen, and it will not advance beyond that screen. The databases are set to UTF-8 in the MySQL panel. I've attached a screen shot of those settings.

          Any help would be greatly appreciated.

          Show
          Syndi Poston added a comment - I gave up on installing v 1.8 and uploaded to install v 1.9. However, I am getting the same unicode error at the "database checker" screen, and it will not advance beyond that screen. The databases are set to UTF-8 in the MySQL panel. I've attached a screen shot of those settings. Any help would be greatly appreciated.
          Hide
          Richard L. Enison added a comment - - edited

          The bottom line is, with only one Moodler so far running the query I suggested, namely

          SHOW VARIABLES LIKE 'character_set_database';

          and posting the results (LATIN1), it is looking like the problem is in MySQL or phpMyAdmin. If more Moodlers would do this, posting the version of MySQL (and phpMyAdmin if they are using it) along with the results, we might be able to pin this down. Because that is the query Moodle uses to test MySQL databases for Unicode compliance.

          By the way, yes, I have run the query myself, but (a) I am only running a stand-alone XAMPPlite version of Moodle 1.8.2 on my home PC and (b) I am not having this problem. My result? UTF8. EDIT: Oh yeah, and (c) I am not using phpMyAdmin.

          Show
          Richard L. Enison added a comment - - edited The bottom line is, with only one Moodler so far running the query I suggested, namely SHOW VARIABLES LIKE 'character_set_database'; and posting the results (LATIN1), it is looking like the problem is in MySQL or phpMyAdmin. If more Moodlers would do this, posting the version of MySQL (and phpMyAdmin if they are using it) along with the results, we might be able to pin this down. Because that is the query Moodle uses to test MySQL databases for Unicode compliance. By the way, yes, I have run the query myself, but (a) I am only running a stand-alone XAMPPlite version of Moodle 1.8.2 on my home PC and (b) I am not having this problem. My result? UTF8. EDIT: Oh yeah, and (c) I am not using phpMyAdmin.
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Hi people,
          I've noticed that many of us have had the same problem, but unfortunally we have only 4 votes to the tracker. If we don't raise up the voting, it won't be fixed. So, vote at all!
          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Hi people, I've noticed that many of us have had the same problem, but unfortunally we have only 4 votes to the tracker. If we don't raise up the voting, it won't be fixed. So, vote at all! Regards. Antonio.
          Hide
          Richard L. Enison added a comment - - edited

          OK, MySQL fans. Here's the score. There are four (4) votes for a fix in Moodle for this MySQL/phpMyAdmin problem. Only two (2) so far have run my suggested query (see my previous comment) and both got the same result: LATIN1. Anyone want to make it three?

          ACPG,

          I might vote for this issue if you did two things:

          1. Tell me what kind of a fix do you want? A workaround for people who apparently are using a web hosting service that doesn't allow them to have UTF8 databases (see Lynn Mole's last comment above)?

          2. Run the query! (EDIT: I am even more convinced in your case that you will get LATIN1 as the result than I was for the others, because of what I said in my first comment above.)

          RLE

          Show
          Richard L. Enison added a comment - - edited OK, MySQL fans. Here's the score. There are four (4) votes for a fix in Moodle for this MySQL/phpMyAdmin problem. Only two (2) so far have run my suggested query (see my previous comment) and both got the same result: LATIN1. Anyone want to make it three? ACPG, I might vote for this issue if you did two things: 1. Tell me what kind of a fix do you want? A workaround for people who apparently are using a web hosting service that doesn't allow them to have UTF8 databases (see Lynn Mole's last comment above)? 2. Run the query! (EDIT: I am even more convinced in your case that you will get LATIN1 as the result than I was for the others, because of what I said in my first comment above.) RLE
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Hi Richard,
          see the results for your query below:
          at Hostmonster.com, where I can't run the installation script:

          Variable_name Value
          character_set_database latin1

          And this after I've setup database collation under the Operation Tab under phpMyAdmin.

          at MidPhase.com, where I CAN run the installation script smoothly:

          Variable_name Value
          character_set_database utf8

          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Hi Richard, see the results for your query below: at Hostmonster.com, where I can't run the installation script: Variable_name Value character_set_database latin1 And this after I've setup database collation under the Operation Tab under phpMyAdmin. at MidPhase.com, where I CAN run the installation script smoothly: Variable_name Value character_set_database utf8 Regards. Antonio.
          Hide
          Richard L. Enison added a comment -

          ACPG,

          Thank you for adding your query results. Science marches on.

          I think now you know why you can run the installation script at one host and not the other.

          RLE

          Show
          Richard L. Enison added a comment - ACPG, Thank you for adding your query results. Science marches on. I think now you know why you can run the installation script at one host and not the other. RLE
          Hide
          Lynn Mole added a comment -

          Hi Richard, am beginning to lose track of where we are a bit, so I'm just going to post on this problem here from now on, if that's okay?

          I have just added the following comment to http://moodle.org/mod/forum/discuss.php?d=81369

          "sorry I wasn't very clear. I was thinking more about creating a database in MAMP which I know is unicode, exporting it as an SQL file and then importing it into an empty database on the server. I don't really know enough about it to know whether that would work. Alternatively, could I export the newly created moodle database from MAMP and upload that and then copy my config.php file.

          I asked hostmonster about creating a unicode database with MySQL but got fobbed off with 'we do not support manual installation of moodle, use fantastico', so you could be right about moving, but I'd rather not go through the hassle if I can work round it.

          I might just do some experiments!"

          Anyway I've done some experiments. I created a empty moodle database (UTF8) in MAMP and exported structure only as an SQL file and then imported it into a new database in webserver mysql. Everything was fine except that it was still reporting latin1 when I queried SHOW VARIABLES LIKE 'character_set_database';

          So a workaround which allowed install with a useless web host would be great for me, because I only want to use English anyway.

          My only other hope is that I noticed my cPanel supports PostreSQL dabases as well, which I think moodle supports. I have created a new database but running SHOW server_encoding; gives me SQL_ASCII, so do you by any chance know the query I need to change this to UTF8?

          Show
          Lynn Mole added a comment - Hi Richard, am beginning to lose track of where we are a bit, so I'm just going to post on this problem here from now on, if that's okay? I have just added the following comment to http://moodle.org/mod/forum/discuss.php?d=81369 "sorry I wasn't very clear. I was thinking more about creating a database in MAMP which I know is unicode, exporting it as an SQL file and then importing it into an empty database on the server. I don't really know enough about it to know whether that would work. Alternatively, could I export the newly created moodle database from MAMP and upload that and then copy my config.php file. I asked hostmonster about creating a unicode database with MySQL but got fobbed off with 'we do not support manual installation of moodle, use fantastico', so you could be right about moving, but I'd rather not go through the hassle if I can work round it. I might just do some experiments!" Anyway I've done some experiments. I created a empty moodle database (UTF8) in MAMP and exported structure only as an SQL file and then imported it into a new database in webserver mysql. Everything was fine except that it was still reporting latin1 when I queried SHOW VARIABLES LIKE 'character_set_database'; So a workaround which allowed install with a useless web host would be great for me, because I only want to use English anyway. My only other hope is that I noticed my cPanel supports PostreSQL dabases as well, which I think moodle supports. I have created a new database but running SHOW server_encoding; gives me SQL_ASCII, so do you by any chance know the query I need to change this to UTF8?
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Hi Richard,
          read below:

          ___________________________________________

          Hi Craig,
          thanks!
          But even if I update all tables to UNICODE, as you suggested, I still got the error message when moodle evaluate the system environment as below:
          unicode

          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).
          Selecionar

          and inside this database ( acpgomes_mdle1) I still get the message:

          Variable_name Value
          character_set_database latin1

          when I run the query:

          SHOW VARIABLES LIKE 'character_set_database';

          and the Operation Tab reviews to me that it is set up to UTF8_unicode_ci.
          While I don't get this solved, I can't upgrade my moodle installation, and even get a fresh install. I know that the Fantastico makes a fresh install, but it's installation is under the risk to get errors in the future because it install as latin1, and it does not enable upgrading, in other words, it's freezed.
          Below a few screenshots.
          Regards.
          Antonio.

          ______________________________________

          The answer:

          "Dear Customer,
          Then you will have to contact Moodle for assistance. We have provided all of the assistance we can."
          In this way, I'll insist as a moodle error, because it does not offer a way to evaluate the database itself

          ________________________________________

          In this way Richard, I will insist as a moodle error/limitation in the installation script, where it does not allow a consistent evaluation of the environment with the query
          SHOW VARIABLES LIKE 'character_set_database';
          and this should be a query like
          ALTER DATABASE `moodle_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci to setup the environment prior to run the sql script that build the tables. And the sql script should build tables utf8_unicode_ci.
          And the System environment, inside moodle's interface, where you can see the upgradability of your installation, should also evaluate the installation considering tables collation.
          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Hi Richard, read below: ___________________________________________ Hi Craig, thanks! But even if I update all tables to UNICODE, as you suggested, I still got the error message when moodle evaluate the system environment as below: unicode 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). Selecionar and inside this database ( acpgomes_mdle1) I still get the message: Variable_name Value character_set_database latin1 when I run the query: SHOW VARIABLES LIKE 'character_set_database'; and the Operation Tab reviews to me that it is set up to UTF8_unicode_ci. While I don't get this solved, I can't upgrade my moodle installation, and even get a fresh install. I know that the Fantastico makes a fresh install, but it's installation is under the risk to get errors in the future because it install as latin1, and it does not enable upgrading, in other words, it's freezed. Below a few screenshots. Regards. Antonio. ______________________________________ The answer: "Dear Customer, Then you will have to contact Moodle for assistance. We have provided all of the assistance we can." In this way, I'll insist as a moodle error, because it does not offer a way to evaluate the database itself ________________________________________ In this way Richard, I will insist as a moodle error/limitation in the installation script, where it does not allow a consistent evaluation of the environment with the query SHOW VARIABLES LIKE 'character_set_database'; and this should be a query like ALTER DATABASE `moodle_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci to setup the environment prior to run the sql script that build the tables. And the sql script should build tables utf8_unicode_ci. And the System environment, inside moodle's interface, where you can see the upgradability of your installation, should also evaluate the installation considering tables collation. Regards. Antonio.
          Hide
          Richard L. Enison added a comment -

          Lynn M,

          1. And I replied to that forum post.

          2. Yes, Moodle supports PostgreSQL, as well as the other two database types I mentioned in my comment of Oct. 17.

          3. According to http://docs.moodle.org/en/Installing_Moodle#Using_the_command_line, the query to create a Unicode database with Postgres is
          create database moodle with encoding 'unicode';
          Presumably changing "create" to "alter" should do it.

          4. Seems like hostmonster doesn't want their clients creating Unicode databases of any type!

          RLE

          Show
          Richard L. Enison added a comment - Lynn M, 1. And I replied to that forum post. 2. Yes, Moodle supports PostgreSQL, as well as the other two database types I mentioned in my comment of Oct. 17. 3. According to http://docs.moodle.org/en/Installing_Moodle#Using_the_command_line , the query to create a Unicode database with Postgres is create database moodle with encoding 'unicode'; Presumably changing "create" to "alter" should do it. 4. Seems like hostmonster doesn't want their clients creating Unicode databases of any type! RLE
          Hide
          Richard L. Enison added a comment -

          ACPG,

          I'm sorry, but I disagree. I have searched the MySQL manual and SHOW VARIABLES is the only query it has for determining the character set of a database. I don't see how running that query completely outside of Moodle and finding out that your database is really LATIN1 is a Moodle error. And I don't see how Moodle can determine the correct character set of a MySQL database any other way. I think the problem is that hostmonster is playing games with its customers' databases, and MidPhase isn't. Lynn Mole's experience (see her comments) with hostmonster shows that they won't let you create a Unicode MySQL or Postgres database whether you use phpMyAdmin's GUI, an ALTER DATABASE query, or import a SQL file. They sabotage you however you try.

          RLE

          Show
          Richard L. Enison added a comment - ACPG, I'm sorry, but I disagree. I have searched the MySQL manual and SHOW VARIABLES is the only query it has for determining the character set of a database. I don't see how running that query completely outside of Moodle and finding out that your database is really LATIN1 is a Moodle error. And I don't see how Moodle can determine the correct character set of a MySQL database any other way. I think the problem is that hostmonster is playing games with its customers' databases, and MidPhase isn't. Lynn Mole's experience (see her comments) with hostmonster shows that they won't let you create a Unicode MySQL or Postgres database whether you use phpMyAdmin's GUI, an ALTER DATABASE query, or import a SQL file. They sabotage you however you try. RLE
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Richard,
          I'll justifie my insistance.
          I've setup a installation on MidPhase.com with UTF8. With phpMyAdmin I exported all the content in a SQL script. Then, I did a manual (CVS) installation on HostMonster.com, restoring the midphase.com's SQL script (with properly find and replace details), I copied the config.php to hostmonster installation with proper setups, and my installation is running well at hostmonster.com (you my try yourself on tema.odontoblasto.org and geo.acpgomes.net). I think that the only problem that remain is the unupgradability, besides I've not tried yet, and this because moodle's script, once the database is properly setup. And yes, the collation under your query is still latin1.
          I understand your considerations on the limitations of the MySQL about the SHOW VARIABLES, but wouldn't be enough to moodle to set the collation with ALTER DATABASE `moodle_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci and at an upgrade check the tables?
          could you comment this?
          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Richard, I'll justifie my insistance. I've setup a installation on MidPhase.com with UTF8. With phpMyAdmin I exported all the content in a SQL script. Then, I did a manual (CVS) installation on HostMonster.com, restoring the midphase.com's SQL script (with properly find and replace details), I copied the config.php to hostmonster installation with proper setups, and my installation is running well at hostmonster.com (you my try yourself on tema.odontoblasto.org and geo.acpgomes.net). I think that the only problem that remain is the unupgradability, besides I've not tried yet, and this because moodle's script, once the database is properly setup. And yes, the collation under your query is still latin1. I understand your considerations on the limitations of the MySQL about the SHOW VARIABLES, but wouldn't be enough to moodle to set the collation with ALTER DATABASE `moodle_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci and at an upgrade check the tables? could you comment this? Regards. Antonio.
          Hide
          Richard L. Enison added a comment -

          ACPG,

          OK. I gather from your last comment that the Moodle installation you have copied to hostmonster from the other host is a version lower than 1.8. That explains why it is running at all, and why you said it is not upgradable.

          Have you tried upgrading it? Moodle has an upgrade script for migrating to 1.8, and it includes a script that automatically migrates the database to Unicode. What happens at hostmonster when you run that script? That will tell us whether your idea of having Moodle run the ALTER DATABASE query will work, because that is undoubtedly what the database migration script does (I haven't looked at the source code of it yet, but it just about has to be what it does).

          RLE

          Show
          Richard L. Enison added a comment - ACPG, OK. I gather from your last comment that the Moodle installation you have copied to hostmonster from the other host is a version lower than 1.8. That explains why it is running at all, and why you said it is not upgradable. Have you tried upgrading it? Moodle has an upgrade script for migrating to 1.8, and it includes a script that automatically migrates the database to Unicode. What happens at hostmonster when you run that script? That will tell us whether your idea of having Moodle run the ALTER DATABASE query will work, because that is undoubtedly what the database migration script does (I haven't looked at the source code of it yet, but it just about has to be what it does). RLE
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Richard,
          I didn't copy the moodle files from my previous host (midphase.com), I only got the db, from a fresh CVS installation at MidPhase.com (where the script run ok!). Then I performed a CVS installation on Hostmonster.com too, and restored the db (with proper edition of details like url, etc..), and the version running at hostmonster with my manual CVS installation is 1.8.3+. I'll send a screencapture to the tracker and you can see.
          Regards.
          Antonio Carlos.

          Show
          Antonio Carlos Pereira Gomes added a comment - Richard, I didn't copy the moodle files from my previous host (midphase.com), I only got the db, from a fresh CVS installation at MidPhase.com (where the script run ok!). Then I performed a CVS installation on Hostmonster.com too, and restored the db (with proper edition of details like url, etc..), and the version running at hostmonster with my manual CVS installation is 1.8.3+. I'll send a screencapture to the tracker and you can see. Regards. Antonio Carlos.
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Richard,
          I didn't copied the moodle files from my previous host (midphase.com), I only got the db, from a fresh CVS installation at MidPhase.com (where the script run ok!). Then I performed a CVS installation on Hostmonster.com too, and restored the db (with proper edition of details like url, etc..), and the version running at hostmonster with my manual CVS installation is 1.8.3+. See the screencapture.
          Regards.
          Antonio Carlos.

          Show
          Antonio Carlos Pereira Gomes added a comment - Richard, I didn't copied the moodle files from my previous host (midphase.com), I only got the db, from a fresh CVS installation at MidPhase.com (where the script run ok!). Then I performed a CVS installation on Hostmonster.com too, and restored the db (with proper edition of details like url, etc..), and the version running at hostmonster with my manual CVS installation is 1.8.3+. See the screencapture. Regards. Antonio Carlos.
          Hide
          Richard L. Enison added a comment - - edited

          ACPG,

          I think I see how you managed to avoid the problem. You manually set up config.php so you never had to run install.php at all. So it never did the Unicode test (or any other test). OK. Perhaps you would like the installation script to simply bypass the Unicode test altogether!

          Anyway, I'd still like to see what happens if you run the database migration script. If necessary, you could install Moodle 1.7 or something.

          RLE

          Show
          Richard L. Enison added a comment - - edited ACPG, I think I see how you managed to avoid the problem. You manually set up config.php so you never had to run install.php at all. So it never did the Unicode test (or any other test). OK. Perhaps you would like the installation script to simply bypass the Unicode test altogether! Anyway, I'd still like to see what happens if you run the database migration script. If necessary, you could install Moodle 1.7 or something. RLE
          Hide
          Lynn Mole added a comment -

          Hi Richard,

          having read your comments in the forum, I realised that I had actually dumped the contents of the database and not the database itself, so I tried dumping the whole database. I then checked every line to ensure that it did not contain any latin1 encoding, but to no avail: I could not upload it because phpmyadmin gave me an error to the effect that I did not have necessary permissions.

          Host monster are giving me the run-around, so my only option is to try postreSQL. Is there any downside to this versus MySQL that you know, or anything I should be aware of?

          That's assuming it works of course, which seems like a slim hope at the moment

          Show
          Lynn Mole added a comment - Hi Richard, having read your comments in the forum, I realised that I had actually dumped the contents of the database and not the database itself, so I tried dumping the whole database. I then checked every line to ensure that it did not contain any latin1 encoding, but to no avail: I could not upload it because phpmyadmin gave me an error to the effect that I did not have necessary permissions. Host monster are giving me the run-around, so my only option is to try postreSQL. Is there any downside to this versus MySQL that you know, or anything I should be aware of? That's assuming it works of course, which seems like a slim hope at the moment
          Hide
          Richard L. Enison added a comment - - edited

          Lynn M,

          Actually, from what I have heard Postgres is superior to MySQL. I suspect MySQL is more popular because it is simpler and easier to use. So it's the old trade-off between versatility and simplicity. The more capabilities you give a program, the more capabilities its users need to learn how to use.

          But in this case I don't think that should be a big problem, because Moodle takes care of the interfacing with the database. The only thing you need to do with it is create it and back it up, and the docs tell you how to do that. You've already had a taste of it, when you created a Postgres database, tried to alter it to Unicode and ran the query that proved that it wasn't. Now that wasn't so hard, was it?

          One thing puzzles me, though. You and ACPG both use hostmonster, but he was able to import a database (it's not Unicode, but other than that) without running into permission problems as you did. Perhaps he can help you with that. Then you could bypass the installation script as he did by copying and modifying config.php from your Mac.

          Or you could patch install.php to bypass the Unicode test. I could help you with that, if you'd like.

          RLE

          Show
          Richard L. Enison added a comment - - edited Lynn M, Actually, from what I have heard Postgres is superior to MySQL. I suspect MySQL is more popular because it is simpler and easier to use. So it's the old trade-off between versatility and simplicity. The more capabilities you give a program, the more capabilities its users need to learn how to use. But in this case I don't think that should be a big problem, because Moodle takes care of the interfacing with the database. The only thing you need to do with it is create it and back it up, and the docs tell you how to do that. You've already had a taste of it, when you created a Postgres database, tried to alter it to Unicode and ran the query that proved that it wasn't. Now that wasn't so hard, was it? One thing puzzles me, though. You and ACPG both use hostmonster, but he was able to import a database (it's not Unicode, but other than that) without running into permission problems as you did. Perhaps he can help you with that. Then you could bypass the installation script as he did by copying and modifying config.php from your Mac. Or you could patch install.php to bypass the Unicode test. I could help you with that, if you'd like. RLE
          Hide
          Richard L. Enison added a comment - - edited

          ACPG (& whoever else is interested),

          I just noticed something as I was looking at install.php on my computer that I had seen before but forgotten about. If you don't create a database before running it, guess what? It tries to create one for you! So that's another option to try. I looked at the cross-reference listing at moodle.org to make sure that code is there, because my copy of Moodle is the XAMPPlite version, which might be different. The database it tries to create is a MySQL database, using the database host, username, password, and name you give it. Unfortunately, the comments say that code doesn't seem to work. (That comment isn't in my version)

          But then the next thing it does, whether it creates a database or one already exists (because you had created it first), is, if the database is MySQL, it checks its character set (you know how!) and if it is not UTF8, it runs an ALTER DATABASE query to change it to UTF8, collating sequence UTF8_UNICODE_CI. That code isn't in my version either. I wonder if they added it because of this tracker issue!

          If that code isn't in your install.php, hopefully you will be able to find it at moodle.org or CVS.

          RLE

          EDIT: PS If you can't get this updated install.php because it's too new, here is where it is:
          http://xref.moodle.org/install.php.source.txt
          Shhh don't tell anybody!

          It still says it is version 1.8.1. Go figure.

          Show
          Richard L. Enison added a comment - - edited ACPG (& whoever else is interested), I just noticed something as I was looking at install.php on my computer that I had seen before but forgotten about. If you don't create a database before running it, guess what? It tries to create one for you! So that's another option to try. I looked at the cross-reference listing at moodle.org to make sure that code is there, because my copy of Moodle is the XAMPPlite version, which might be different. The database it tries to create is a MySQL database, using the database host, username, password, and name you give it. Unfortunately, the comments say that code doesn't seem to work. (That comment isn't in my version) But then the next thing it does, whether it creates a database or one already exists (because you had created it first), is, if the database is MySQL, it checks its character set (you know how!) and if it is not UTF8, it runs an ALTER DATABASE query to change it to UTF8, collating sequence UTF8_UNICODE_CI. That code isn't in my version either. I wonder if they added it because of this tracker issue! If that code isn't in your install.php, hopefully you will be able to find it at moodle.org or CVS. RLE EDIT: PS If you can't get this updated install.php because it's too new, here is where it is: http://xref.moodle.org/install.php.source.txt Shhh don't tell anybody! It still says it is version 1.8.1. Go figure.
          Hide
          Lynn Mole added a comment -

          Hi Richard, another day dawns with no success. PostreSQL has ground to a halt as
          alter database moodle with encoding 'unicode'; doesn't work and I tried numerous combinations after searching the docs but couldn't hit on the right one. I'm not a programmer so it's all greek to me!

          I've had many fruitless missives with host monster, the latest being:

          It is not a bug in MySQL or PhpMyAdmin but is the default encoding set for databases on our system. The best way to change the encoding would be to export the database then when using PhpMyAdmin to import you will get the option to set the encoding or charset. This is the one way that you can set this on the server to use another encoding.

          I have uploaded an SQl file and they are going to try it and let me know how they managed it. Clearly they have more confidence in their own abilities than I do at this moment, but we'll see.

          Had a look at the install file you recommended and the one I was using had the same code. I deleted the following code and ran it again:

          // Check if we can navigate from the environemt page (because it's ok)

          if ($INSTALL['stage'] == ENVIRONMENT) {
          error_reporting(0); // Hide errors
          $dbconnected = $db->Connect($INSTALL['dbhost'],$INSTALL['dbuser'],$INSTALL['dbpass'],$INSTALL['dbname']);
          error_reporting(7); // Show errors
          if ($dbconnected) {
          /// Execute environment check, printing results
          if (!check_moodle_environment($INSTALL['release'], $environment_results, false))

          { $nextstage = ENVIRONMENT; }

          } else

          { /// We never should reach this because DB has been tested before arriving here $errormsg = get_string('dbconnectionerror', 'install'); $nextstage = DATABASE; }

          }

          which did get me past the environment checking page but then it stalled on setting up the databse and I got the following:
          (mysql): SHOW TABLES
          (mysql): SHOW VARIABLES LIKE 'character_set_database'
          (mysql): SHOW VARIABLES LIKE 'character_set_database'

          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).

          More information about this error

          (mysql): SELECT * FROM mdl_course WHERE category = '0' LIMIT 1
          1146: Table 'marlinpr_vle2.mdl_course' doesn't exist
          ADOConnection._Execute(SELECT * FROM mdl_course WHERE category = '0' LIMIT 1, false) % line 891, file: adodb.inc.php
          ADOConnection.Execute(SELECT * FROM mdl_course WHERE category = '0' LIMIT 1, false) % line 496, file: adodb-mysql.inc.php
          ADODB_mysql.SelectLimit(SELECT * FROM mdl_course WHERE category = '0', 1, -1) % line 674, file: dmllib.php
          get_recordset_sql(SELECT * FROM mdl_course WHERE category = '0', 0, 1) % line 476, file: dmllib.php
          get_record_sql(SELECT * FROM mdl_course WHERE category = '0') % line 416, file: dmllib.php
          Home
          (mysql): SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192786160
          1146: Table 'marlinpr_vle2.mdl_cache_flags' doesn't exist
          ADOConnection._Execute(SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192..., false) % line 891, file: adodb.inc.php
          ADOConnection.Execute(SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192...) % line 676, file: dmllib.php
          get_recordset_sql(SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192..., , ) % line 603, file: dmllib.php
          get_recordset_select(cache_flags, flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192786160, , name,value, , ) % line 884, file: dmllib.php
          get_records_select(cache_flags, flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192786160, , name,value) % line 741, file: moodlelib.php

          So I'm still stuck.

          I think it now requires a trip to the forums to find the most suitable one to ask a question about good, supportive web hosts for moodle. I suspect I'll need a dedicated server if my site takes off as I think it will but I really know very little about apache, mySQL etc so I was hoping to trial it on a web host to give me time to sort out the back end stuff

          Show
          Lynn Mole added a comment - Hi Richard, another day dawns with no success. PostreSQL has ground to a halt as alter database moodle with encoding 'unicode'; doesn't work and I tried numerous combinations after searching the docs but couldn't hit on the right one. I'm not a programmer so it's all greek to me! I've had many fruitless missives with host monster, the latest being: It is not a bug in MySQL or PhpMyAdmin but is the default encoding set for databases on our system. The best way to change the encoding would be to export the database then when using PhpMyAdmin to import you will get the option to set the encoding or charset. This is the one way that you can set this on the server to use another encoding. I have uploaded an SQl file and they are going to try it and let me know how they managed it. Clearly they have more confidence in their own abilities than I do at this moment, but we'll see. Had a look at the install file you recommended and the one I was using had the same code. I deleted the following code and ran it again: // Check if we can navigate from the environemt page (because it's ok) if ($INSTALL ['stage'] == ENVIRONMENT) { error_reporting(0); // Hide errors $dbconnected = $db->Connect($INSTALL ['dbhost'] ,$INSTALL ['dbuser'] ,$INSTALL ['dbpass'] ,$INSTALL ['dbname'] ); error_reporting(7); // Show errors if ($dbconnected) { /// Execute environment check, printing results if (!check_moodle_environment($INSTALL ['release'] , $environment_results, false)) { $nextstage = ENVIRONMENT; } } else { /// We never should reach this because DB has been tested before arriving here $errormsg = get_string('dbconnectionerror', 'install'); $nextstage = DATABASE; } } which did get me past the environment checking page but then it stalled on setting up the databse and I got the following: (mysql): SHOW TABLES (mysql): SHOW VARIABLES LIKE 'character_set_database' (mysql): SHOW VARIABLES LIKE 'character_set_database' 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). More information about this error (mysql): SELECT * FROM mdl_course WHERE category = '0' LIMIT 1 1146: Table 'marlinpr_vle2.mdl_course' doesn't exist ADOConnection._Execute(SELECT * FROM mdl_course WHERE category = '0' LIMIT 1, false) % line 891, file: adodb.inc.php ADOConnection.Execute(SELECT * FROM mdl_course WHERE category = '0' LIMIT 1, false) % line 496, file: adodb-mysql.inc.php ADODB_mysql.SelectLimit(SELECT * FROM mdl_course WHERE category = '0', 1, -1) % line 674, file: dmllib.php get_recordset_sql(SELECT * FROM mdl_course WHERE category = '0', 0, 1) % line 476, file: dmllib.php get_record_sql(SELECT * FROM mdl_course WHERE category = '0') % line 416, file: dmllib.php Home (mysql): SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192786160 1146: Table 'marlinpr_vle2.mdl_cache_flags' doesn't exist ADOConnection._Execute(SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192..., false) % line 891, file: adodb.inc.php ADOConnection.Execute(SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192...) % line 676, file: dmllib.php get_recordset_sql(SELECT name,value FROM mdl_cache_flags WHERE flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192..., , ) % line 603, file: dmllib.php get_recordset_select(cache_flags, flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192786160, , name,value, , ) % line 884, file: dmllib.php get_records_select(cache_flags, flagtype='accesslib/dirtycontexts' AND expiry >= 1192786221 AND timemodified > 1192786160, , name,value) % line 741, file: moodlelib.php So I'm still stuck. I think it now requires a trip to the forums to find the most suitable one to ask a question about good, supportive web hosts for moodle. I suspect I'll need a dedicated server if my site takes off as I think it will but I really know very little about apache, mySQL etc so I was hoping to trial it on a web host to give me time to sort out the back end stuff
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Lynn Mole,
          if you'd like to move from hostmonster.com, I'd suggest to go to anhosting.com or midphase.com. In fact they are the same, one at 6.95 (anhosting.com) and the other 7.95. Midphase owns anhosting, and the support is from midphase team. I've experienced hostmonster.com like you, and get the same problem with moodle installation, and I think anhosting/midphase has a unbeatable support, and I had no problems with UTF8. The performance at all is a little smaller than hostmonster.
          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Lynn Mole, if you'd like to move from hostmonster.com, I'd suggest to go to anhosting.com or midphase.com. In fact they are the same, one at 6.95 (anhosting.com) and the other 7.95. Midphase owns anhosting, and the support is from midphase team. I've experienced hostmonster.com like you, and get the same problem with moodle installation, and I think anhosting/midphase has a unbeatable support, and I had no problems with UTF8. The performance at all is a little smaller than hostmonster. Regards. Antonio.
          Hide
          Richard L. Enison added a comment -

          Lynn M (& everyone else having this problem),

          1. I agree with you about hostmonster being alergic to Unicode. I came to the same conclusion in my comment, addressed to ACPG, of Oct. 17 at 8:27 PM (I believe that is West Australia Time).

          2. You may not be a programmer, but don't worry, I am. And I said in my last comment addressed to you that I could help you bypass the Unicode test. And besides, many non-programmers, with professional help, have been known to lead happy and even productive lives!

          3. However, what you have discovered is why the environment check for Unicode is there. It really needs the database to be Unicode, and bypassing the check doesn't change that. So yes, if tech support is not able to help you create a Unicode database, or do it for you, you need to change hosts.

          4. You might find this forum discussion useful in selecting a host: http://moodle.org/mod/forum/discuss.php?d=42688

          RLE

          Show
          Richard L. Enison added a comment - Lynn M (& everyone else having this problem), 1. I agree with you about hostmonster being alergic to Unicode. I came to the same conclusion in my comment, addressed to ACPG, of Oct. 17 at 8:27 PM (I believe that is West Australia Time). 2. You may not be a programmer, but don't worry, I am. And I said in my last comment addressed to you that I could help you bypass the Unicode test. And besides, many non-programmers, with professional help, have been known to lead happy and even productive lives! 3. However, what you have discovered is why the environment check for Unicode is there. It really needs the database to be Unicode, and bypassing the check doesn't change that. So yes, if tech support is not able to help you create a Unicode database, or do it for you, you need to change hosts. 4. You might find this forum discussion useful in selecting a host: http://moodle.org/mod/forum/discuss.php?d=42688 RLE
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Richard,
          I've noticed that a previous version of moodle 1.8.2+ I've installed had 191 tables, and after a cvs update (1.8.3+) presented 193 tables, and I'm having a issue regarding glossary autolinking: a course that I backed up from the previous installation (1.8.3+ at midphase), does not show autolinking when restored to a installation (1.8.3+) at hostmonster. Any guess about what happend? Did I miss any table? Autolinking is a php function?
          Any guess would help.
          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Richard, I've noticed that a previous version of moodle 1.8.2+ I've installed had 191 tables, and after a cvs update (1.8.3+) presented 193 tables, and I'm having a issue regarding glossary autolinking: a course that I backed up from the previous installation (1.8.3+ at midphase), does not show autolinking when restored to a installation (1.8.3+) at hostmonster. Any guess about what happend? Did I miss any table? Autolinking is a php function? Any guess would help. Regards. Antonio.
          Hide
          Richard L. Enison added a comment -

          ACPG,

          The only one of your questions I can answer is: there is no php function called autolinking, or anything close. Other than that, I don't know much about auto-linking except that I just did a search on Moodle's home page and it is described here: http://moodle.org/mod/resource/view.php?id=2303. Also, a problem similar to yours is discussed in http://moodle.org/mod/forum/discuss.php?d=61164 and http://moodle.org/mod/forum/discuss.php?d=75450. A possible solution is at http://moodle.org/mod/forum/discuss.php?d=72962#p326067.

          RLE

          Show
          Richard L. Enison added a comment - ACPG, The only one of your questions I can answer is: there is no php function called autolinking, or anything close. Other than that, I don't know much about auto-linking except that I just did a search on Moodle's home page and it is described here: http://moodle.org/mod/resource/view.php?id=2303 . Also, a problem similar to yours is discussed in http://moodle.org/mod/forum/discuss.php?d=61164 and http://moodle.org/mod/forum/discuss.php?d=75450 . A possible solution is at http://moodle.org/mod/forum/discuss.php?d=72962#p326067 . RLE
          Hide
          Birdie Newborn added a comment -

          Bluehost has 'fessed up. Here is their response about utf8:

          "The global default for the char sets on the database are set to latin1 on our server. This is a default that will cover more languages and fits the needs of our server and keeps the size down on the databases stored on the server. The option to change the character sets is there in phpmyadmin. You can change this but the default on the server will remain constant as the default. If you want to the character set you can export the entire database and on the import set the character set when you perform the import. I cannot change this setting on our server."

          Very disappointing, after I paid for a year in advance... So, do I have to abandon Bluehost entirely, or is there something to be done?

          Show
          Birdie Newborn added a comment - Bluehost has 'fessed up. Here is their response about utf8: "The global default for the char sets on the database are set to latin1 on our server. This is a default that will cover more languages and fits the needs of our server and keeps the size down on the databases stored on the server. The option to change the character sets is there in phpmyadmin. You can change this but the default on the server will remain constant as the default. If you want to the character set you can export the entire database and on the import set the character set when you perform the import. I cannot change this setting on our server." Very disappointing, after I paid for a year in advance... So, do I have to abandon Bluehost entirely, or is there something to be done?
          Hide
          Richard L. Enison added a comment - - edited

          BN,

          Bluehost's response is a little ambiguous, but offhand it looks like they are saying that it is only their server defaults that are locked in as latin1 and latin1_swedish_ci (those are MySQL's defaults also, by the way), not your database's defaults, which you are free to change (unlike hostmonster, which seems to sabotage its clients every chance it gets). That's good news, because it is the database default variable (CHARACTER_SET_DATABASE), not the server default (CHARACTER_SET_SERVER), that the Moodle installation script looks at. What is ambiguous is whether they are saying you can change your database's default character set and collation with phpmyadmin directly, or whether you can only change them by exporting and importing. You might want to ask them to clarify this.

          RLE

          EDIT: PS If it would take too long to wait for a response from them again, why not try it one way (ALTER DATABASE or whatever other method phpmyadmin provides) and if it doesn't work, try it the other way?

          Show
          Richard L. Enison added a comment - - edited BN, Bluehost's response is a little ambiguous, but offhand it looks like they are saying that it is only their server defaults that are locked in as latin1 and latin1_swedish_ci (those are MySQL's defaults also, by the way), not your database's defaults, which you are free to change (unlike hostmonster, which seems to sabotage its clients every chance it gets). That's good news, because it is the database default variable (CHARACTER_SET_DATABASE), not the server default (CHARACTER_SET_SERVER), that the Moodle installation script looks at. What is ambiguous is whether they are saying you can change your database's default character set and collation with phpmyadmin directly, or whether you can only change them by exporting and importing. You might want to ask them to clarify this. RLE EDIT: PS If it would take too long to wait for a response from them again, why not try it one way (ALTER DATABASE or whatever other method phpmyadmin provides) and if it doesn't work, try it the other way?
          Hide
          Richard L. Enison added a comment - - edited

          ACPG,

          By the way, if you want to know what the two new tables are in Moodle 1.8.3+ that weren't in 1.8.2+, hopefully you have a backup of your database from 1.8.2+. Restore it somewhere (not on top of your current database!) and do a SHOW TABLES query, then get the output of that query into a text file or some place where you can copy it to your operating system's clipboard (not the server's) so you can paste it somewhere else. How you do that depends on what op. sys. and other s/w you have.

          Now paste the list into column A of a spreadsheet (I am most familiar with the one from the enemy, MS Excel) so that each table name is in a separate cell. Do the same for your current Moodle database and past the list into column C of the spreadsheet, allowing a blank column B for readability. You might want to widen columns A and C, and perhaps make column B a little narrower, but it is not necessary, just cosmetic. I'm assuming both lists are in alphabetical order. If not, do a sort on column A and again on column C (don't sort them together).

          Finally, enter a formula into cell E1 to compare A1 and C1. For example, in Excel I would type
          =(C1=A1)
          and copy that formula down the column to E193. The formula should change automatically in each cell so that it compares the cells in the same row; for example, in E2 it should say
          =(C2=A2)
          and so on. Scan down column E with your eyes to see where the cell values change from TRUE to FALSE. The first row where you see FALSE in column E is where you have the alphabetically first new table name in column C.

          Now insert a cell in column A in that row, so that all the cells below it in column A are pushed down (don't insert a whole row). You should now see cells below that row in column E saying TRUE again, unless the very next table name in column C is the other new table. Scan down column E again, starting at the row below the insertion in column A, until it starts saying FALSE. That's the row where the other new table name is in column C.

          RLE

          Show
          Richard L. Enison added a comment - - edited ACPG, By the way, if you want to know what the two new tables are in Moodle 1.8.3+ that weren't in 1.8.2+, hopefully you have a backup of your database from 1.8.2+. Restore it somewhere (not on top of your current database!) and do a SHOW TABLES query, then get the output of that query into a text file or some place where you can copy it to your operating system's clipboard (not the server's) so you can paste it somewhere else. How you do that depends on what op. sys. and other s/w you have. Now paste the list into column A of a spreadsheet (I am most familiar with the one from the enemy, MS Excel) so that each table name is in a separate cell. Do the same for your current Moodle database and past the list into column C of the spreadsheet, allowing a blank column B for readability. You might want to widen columns A and C, and perhaps make column B a little narrower, but it is not necessary, just cosmetic. I'm assuming both lists are in alphabetical order. If not, do a sort on column A and again on column C (don't sort them together). Finally, enter a formula into cell E1 to compare A1 and C1. For example, in Excel I would type =(C1=A1) and copy that formula down the column to E193. The formula should change automatically in each cell so that it compares the cells in the same row; for example, in E2 it should say =(C2=A2) and so on. Scan down column E with your eyes to see where the cell values change from TRUE to FALSE. The first row where you see FALSE in column E is where you have the alphabetically first new table name in column C. Now insert a cell in column A in that row, so that all the cells below it in column A are pushed down (don't insert a whole row). You should now see cells below that row in column E saying TRUE again, unless the very next table name in column C is the other new table. Scan down column E again, starting at the row below the insertion in column A, until it starts saying FALSE. That's the row where the other new table name is in column C. RLE
          Hide
          Lynn Mole added a comment -

          Birdie, bad news I'm afraid. Bluehost actually own host monster so I imagine that like them you can't change the default. I've had the same run around from host monster and got nowhere with them. They also told me to import the database, which I did. They even did it for me but when you run SHOW VARIABLES LIKE 'character_set_database' you still get latin1 as the character set. I'm beginning to wonder how I actually managed the first install.

          By the way, thanks for the tip Antonio, I'll certainly check them anhosting or midphase.

          In addition, thanks for all your help Richard.

          Show
          Lynn Mole added a comment - Birdie, bad news I'm afraid. Bluehost actually own host monster so I imagine that like them you can't change the default. I've had the same run around from host monster and got nowhere with them. They also told me to import the database, which I did. They even did it for me but when you run SHOW VARIABLES LIKE 'character_set_database' you still get latin1 as the character set. I'm beginning to wonder how I actually managed the first install. By the way, thanks for the tip Antonio, I'll certainly check them anhosting or midphase. In addition, thanks for all your help Richard.
          Hide
          Romeu Klug added a comment - - edited

          My webhosting is bluehost and i have de same problem.

          Show
          Romeu Klug added a comment - - edited My webhosting is bluehost and i have de same problem.
          Hide
          Richard L. Enison added a comment -

          See http://moodle.org/mod/forum/discuss.php?d=82414#p367202 for a summary of what is going on at these renegade hosts.

          Show
          Richard L. Enison added a comment - See http://moodle.org/mod/forum/discuss.php?d=82414#p367202 for a summary of what is going on at these renegade hosts.
          Hide
          Romeu Klug added a comment -

          Hi, any fixe about this problem?

          Rkjunior

          Show
          Romeu Klug added a comment - Hi, any fixe about this problem? Rkjunior
          Hide
          Richard L. Enison added a comment - - edited

          RKjr.,

          For now, the fix is to find a web host that supports Unicode databases.

          RLE

          Show
          Richard L. Enison added a comment - - edited RKjr., For now, the fix is to find a web host that supports Unicode databases. RLE
          Hide
          Jorge Custódio added a comment -

          Hi !

          I have tried to question host monster via chat and email and I got this answer :

          "We definitely support unicode...both in our databases and in our pages. So I would try to recreate the Database shell...make sure it is UTF 8, and then do the import of the raw sql. Try that. Thanks!"

          Mike Dickson
          Support Level 1
          HostMonster.com
          ...

          I have tried to install Moodle using Fantastico and manually without any luck !
          But I will continue to battle for UTF8 support !

          Anyone got it working?

          Regards,
          Jorge

          Show
          Jorge Custódio added a comment - Hi ! I have tried to question host monster via chat and email and I got this answer : "We definitely support unicode...both in our databases and in our pages. So I would try to recreate the Database shell...make sure it is UTF 8, and then do the import of the raw sql. Try that. Thanks!" Mike Dickson Support Level 1 HostMonster.com ... I have tried to install Moodle using Fantastico and manually without any luck ! But I will continue to battle for UTF8 support ! Anyone got it working? Regards, Jorge
          Hide
          onitzuka added a comment - - edited

          On my host (bluehost.com) we already changed the database to utf8/utf8_unicode_ci, and that did not help.

          I then tried a manual installtion of 1.8.3 to a database that I created as utf8/utf8_unicode_ci. The installer insists that the database is not utf8 and refuses to continue the installation.

          Based on my experience, even having the database utf8 made no difference. It is just refusing to install outright.

          What exactly is Moodle doing to determine the default encoding of the database? Is that function/method returning wrong data? Maybe it is returning a blank string instead of utf8 or something?

          Show
          onitzuka added a comment - - edited On my host (bluehost.com) we already changed the database to utf8/utf8_unicode_ci, and that did not help. I then tried a manual installtion of 1.8.3 to a database that I created as utf8/utf8_unicode_ci. The installer insists that the database is not utf8 and refuses to continue the installation. Based on my experience, even having the database utf8 made no difference. It is just refusing to install outright. What exactly is Moodle doing to determine the default encoding of the database? Is that function/method returning wrong data? Maybe it is returning a blank string instead of utf8 or something?
          Hide
          Jorge Custódio added a comment -

          As far I have debugged the install.php does this check:

          $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'");

          which in the case of the host monster host is set to latin1 !
          So the install.php doesn't check the collation or fields char set. So I don't know
          if this is a bug of moodle or the variable has to be really setup for utf8 in order
          to obtain a full utf8 database support in mysql.

          Jorge

          Show
          Jorge Custódio added a comment - As far I have debugged the install.php does this check: $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); which in the case of the host monster host is set to latin1 ! So the install.php doesn't check the collation or fields char set. So I don't know if this is a bug of moodle or the variable has to be really setup for utf8 in order to obtain a full utf8 database support in mysql. Jorge
          Hide
          Richard L. Enison added a comment -

          O & JC,

          You both could have saved yourself time and effort if you had read the earlier comments. But thanks, JC, for confirming the SQL query that Moodle uses to determine database character set, as I mentioned in my comment of Oct. 18 as well as my forum post of Oct. 16 as referenced in my comment of Oct. 17. Several of the other commenters, using BlueHost or HostMonster, have run that query outside of Moodle and found that no matter what they do to their databases, and whether they use MySQL or PostgreSQL (a different query is used for PostgreSQL), the result is Latin1. So it is not a Moodle bug. And if you had read LM's comment of Oct. 19 you would know that bypassing the code that checks for Unicode in the Moodle install script doesn't help; it will cause problems later on.

          RLE

          Show
          Richard L. Enison added a comment - O & JC, You both could have saved yourself time and effort if you had read the earlier comments. But thanks, JC, for confirming the SQL query that Moodle uses to determine database character set, as I mentioned in my comment of Oct. 18 as well as my forum post of Oct. 16 as referenced in my comment of Oct. 17. Several of the other commenters, using BlueHost or HostMonster, have run that query outside of Moodle and found that no matter what they do to their databases, and whether they use MySQL or PostgreSQL (a different query is used for PostgreSQL), the result is Latin1. So it is not a Moodle bug. And if you had read LM's comment of Oct. 19 you would know that bypassing the code that checks for Unicode in the Moodle install script doesn't help; it will cause problems later on. RLE
          Hide
          onitzuka added a comment -

          I just tried the statement Jorge Custódio found MySQL on my host (Bluehost) and the result returned was the following:

          mysql> SHOW VARIABLES LIKE 'character_set_database';
          ------------------------------+

          Variable_name Value

          ------------------------------+

          character_set_database latin1

          ------------------------------+
          1 row in set (0.00 sec)

          Yes.... it shows latin1. Yet I most certainly did alter the database to use utf8/utf8_unicode_ci. And phpMyAdmin is using some statement to correctly see that the database itself is set to utf8/utf8_unicode_ci.

          Does anybody know what statement phpMyAdmin is using?

          Or is it the case that Moodle's installer is seeing things correctly, and going past this only results in problems?

          Any ideas?

          Show
          onitzuka added a comment - I just tried the statement Jorge Custódio found MySQL on my host (Bluehost) and the result returned was the following: mysql> SHOW VARIABLES LIKE 'character_set_database'; ----------------------- -------+ Variable_name Value ----------------------- -------+ character_set_database latin1 ----------------------- -------+ 1 row in set (0.00 sec) Yes.... it shows latin1. Yet I most certainly did alter the database to use utf8/utf8_unicode_ci. And phpMyAdmin is using some statement to correctly see that the database itself is set to utf8/utf8_unicode_ci. Does anybody know what statement phpMyAdmin is using? Or is it the case that Moodle's installer is seeing things correctly, and going past this only results in problems? Any ideas?
          Hide
          Jorge Custódio added a comment -

          Want I meant about this being a bug is this: What will happen if you install Moodle via Fantastico and then update the collation and fields char set to utf8 ? But maybe this is a MySQL question, what happens if you have that variable set to latin1 and the collation and fields to utf8, will MySQL store the data in utf8 or latin1?

          Regards,
          Jorge

          Show
          Jorge Custódio added a comment - Want I meant about this being a bug is this: What will happen if you install Moodle via Fantastico and then update the collation and fields char set to utf8 ? But maybe this is a MySQL question, what happens if you have that variable set to latin1 and the collation and fields to utf8, will MySQL store the data in utf8 or latin1? Regards, Jorge
          Hide
          onitzuka added a comment -

          The sad news is that the foreign text gets destroyed. I tested the Chat and Wiki and found that foreign (in this case Japanese) gets returned as ??? in the Chat and gets completely deleted in the Wiki.

          Show
          onitzuka added a comment - The sad news is that the foreign text gets destroyed. I tested the Chat and Wiki and found that foreign (in this case Japanese) gets returned as ??? in the Chat and gets completely deleted in the Wiki.
          Hide
          onitzuka added a comment -

          I have seen a stream of messages in the forums with people unable to install Moodle because the database check is blocking. I tried the install on bluehost as well as my own machine (I run a 100% Japanese UTF8 installation) and it blocked on the database in both cases.

          Show
          onitzuka added a comment - I have seen a stream of messages in the forums with people unable to install Moodle because the database check is blocking. I tried the install on bluehost as well as my own machine (I run a 100% Japanese UTF8 installation) and it blocked on the database in both cases.
          Hide
          onitzuka added a comment -

          I believe that I found the source of the installation problems. Lines 359-377 of install.php.
          The short answer is that the statement "SHOW VARIABLES LIKE 'character_set_database'"?DOES NOT report the character set or collation for any one database. Notice how the ALTER DATABASE statement refers to a specific database? The SHOW VARIABLES statement is referring to a goal server setting. The statement that should be used in place of the SHOW VARIABLES statement should be the one that matches the ALTER DATABASE statement.

          Are there any folks here that are really sharp with MySQL/SQL who could tell us what that statement would be?

          switch ($INSTALL['dbtype']) {
          case 'mysql':
          /// Get MySQL character_set_database value
          $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'");
          if ($rs && $rs->RecordCount() > 0) {
          $records = $rs->GetAssoc(true);
          $encoding = $records['character_set_database']['Value'];
          if (strtoupper($encoding) != 'UTF8') {
          /// Try to set the encoding now!
          if (! $db->Metatables())

          { // We have no tables so go ahead $db->Execute("ALTER DATABASE `".$INSTALL['dbname']."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"); $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); // this works }

          }
          /// If conversion fails, skip, let environment testing do the job
          }
          break;

          Show
          onitzuka added a comment - I believe that I found the source of the installation problems. Lines 359-377 of install.php. The short answer is that the statement "SHOW VARIABLES LIKE 'character_set_database'"?DOES NOT report the character set or collation for any one database. Notice how the ALTER DATABASE statement refers to a specific database? The SHOW VARIABLES statement is referring to a goal server setting. The statement that should be used in place of the SHOW VARIABLES statement should be the one that matches the ALTER DATABASE statement. Are there any folks here that are really sharp with MySQL/SQL who could tell us what that statement would be? switch ($INSTALL ['dbtype'] ) { case 'mysql': /// Get MySQL character_set_database value $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); if ($rs && $rs->RecordCount() > 0) { $records = $rs->GetAssoc(true); $encoding = $records ['character_set_database'] ['Value'] ; if (strtoupper($encoding) != 'UTF8') { /// Try to set the encoding now! if (! $db->Metatables()) { // We have no tables so go ahead $db->Execute("ALTER DATABASE `".$INSTALL['dbname']."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"); $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); // this works } } /// If conversion fails, skip, let environment testing do the job } break;
          Hide
          onitzuka added a comment -

          An interesting note about CHARACTER SETS from the MySQL docs:

          http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

          " The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. The database character set also is used by LOAD DATA INFILE. The character set and collation have no other purposes.

          The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server. "

          Show
          onitzuka added a comment - An interesting note about CHARACTER SETS from the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/charset-database.html " The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. The database character set also is used by LOAD DATA INFILE. The character set and collation have no other purposes. The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server. "
          Hide
          onitzuka added a comment -

          FOUND IT!!! It was in a comment at the bottom of the same page!!!! The correct statement for checking the CHARACTER SET for COLLATION for a database is:

          SHOW CREATE DATABASE `DBNAME`;

          It was at the bottom of the page: http://dev.mysql.com/doc/refman/5.0/en/charset-database.html of the MySQL docs!!!

          This is probably the statement that needs to be used instead of the SHOW VARIABLES statement.

          Show
          onitzuka added a comment - FOUND IT!!! It was in a comment at the bottom of the same page!!!! The correct statement for checking the CHARACTER SET for COLLATION for a database is: SHOW CREATE DATABASE `DBNAME`; It was at the bottom of the page: http://dev.mysql.com/doc/refman/5.0/en/charset-database.html of the MySQL docs!!! This is probably the statement that needs to be used instead of the SHOW VARIABLES statement.
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Hi people,
          I've noticed that many of us have had the same problem, but unfortunally we have only 8 votes to the tracker. If we don't raise up the voting, it won't be fixed. So, vote at all! Voting is available in the left colum after login.
          Regards.
          Antonio.

          Show
          Antonio Carlos Pereira Gomes added a comment - Hi people, I've noticed that many of us have had the same problem, but unfortunally we have only 8 votes to the tracker. If we don't raise up the voting, it won't be fixed. So, vote at all! Voting is available in the left colum after login. Regards. Antonio.
          Hide
          onitzuka added a comment -

          Here is my first pass at a fix to install.php. It is currently not working in my test, but I think it is the right way to go. I commented out the lines that I felt need to be replaced. Again, it doesnt work, but I believe that it addresses the offending checks in the install.php. It might not, tho, since it did not change the outcome. What do you think?

          switch ($INSTALL['dbtype']) {
          case 'mysql':
          /// Get MySQL character_set_database value
          // $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'");
          $rs = $db->Execute("SHOW CREATE DATABASE `".$INSTALL['dbname']."`");

          if ($rs && $rs->RecordCount() > 0) {
          $records = $rs->GetAssoc(true);
          // $encoding = $records['character_set_database']['Value'];
          $encoding = $records[$INSTALL['dbname']]['Create Database'];
          //if (strtoupper($encoding) != 'UTF8') {
          if (eregi('utf8', $encoding)) {
          /// Try to set the encoding now!
          if (! $db->Metatables())

          { // We have no tables so go ahead $db->Execute("ALTER DATABASE `".$INSTALL['dbname']."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"); // $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); // this works $rs = $db->Execute("SHOW CREATE DATABASE `".$INSTALL['dbname']."`"); }

          }
          /// If conversion fails, skip, let environment testing do the job
          }
          break;

          Show
          onitzuka added a comment - Here is my first pass at a fix to install.php. It is currently not working in my test, but I think it is the right way to go. I commented out the lines that I felt need to be replaced. Again, it doesnt work, but I believe that it addresses the offending checks in the install.php. It might not, tho, since it did not change the outcome. What do you think? switch ($INSTALL ['dbtype'] ) { case 'mysql': /// Get MySQL character_set_database value // $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); $rs = $db->Execute("SHOW CREATE DATABASE `".$INSTALL ['dbname'] ."`"); if ($rs && $rs->RecordCount() > 0) { $records = $rs->GetAssoc(true); // $encoding = $records ['character_set_database'] ['Value'] ; $encoding = $records[$INSTALL ['dbname'] ] ['Create Database'] ; //if (strtoupper($encoding) != 'UTF8') { if (eregi('utf8', $encoding)) { /// Try to set the encoding now! if (! $db->Metatables()) { // We have no tables so go ahead $db->Execute("ALTER DATABASE `".$INSTALL['dbname']."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"); // $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); // this works $rs = $db->Execute("SHOW CREATE DATABASE `".$INSTALL['dbname']."`"); } } /// If conversion fails, skip, let environment testing do the job } break;
          Hide
          onitzuka added a comment -

          Hey folks, I just found what seems to be a related bug!!! And it posts a solution that seems to get the job done in a single clear query for MySQL 5.x.

          Please check out: http://tracker.moodle.org/browse/MDL-12062

          Show
          onitzuka added a comment - Hey folks, I just found what seems to be a related bug!!! And it posts a solution that seems to get the job done in a single clear query for MySQL 5.x. Please check out: http://tracker.moodle.org/browse/MDL-12062
          Hide
          Anthony Borrow added a comment -

          I don't consider MDL-12062 to be a single query solution for MySQL; however, if we can change the way that Moodle checks for the unicode and make it look at the database rather than the MySQL server default character set then it should work for MySQL 5.0.6 and beyond. Prior to MySQL 5.0.6 it does not look like it was able to distinguish the database collation. My solution which I consider cheating is to manually copy over a zip file of the Moodle code and extract it. I also install that version of Moodle on my local machine (under localhost) and let it go through the installation. Then I use phpMyAdmin to backup the local Moodle database and create a zipped sql dump of the database. On the host, I create a database and database user (obviously making sure that the database created uses a utf8 default character set), and import (restore) the data. Then I edit config.php and get it setup. Essentially this by passes installing it on the host machine and getting to the part during the install that prevents it from moving forward. The other option would be simply to ignore the check by modifying the code which may be the much simpler solution for folks (at least temporarily). If I get a chance, I will try to do that later today. Peace - Anthony

          Show
          Anthony Borrow added a comment - I don't consider MDL-12062 to be a single query solution for MySQL; however, if we can change the way that Moodle checks for the unicode and make it look at the database rather than the MySQL server default character set then it should work for MySQL 5.0.6 and beyond. Prior to MySQL 5.0.6 it does not look like it was able to distinguish the database collation. My solution which I consider cheating is to manually copy over a zip file of the Moodle code and extract it. I also install that version of Moodle on my local machine (under localhost) and let it go through the installation. Then I use phpMyAdmin to backup the local Moodle database and create a zipped sql dump of the database. On the host, I create a database and database user (obviously making sure that the database created uses a utf8 default character set), and import (restore) the data. Then I edit config.php and get it setup. Essentially this by passes installing it on the host machine and getting to the part during the install that prevents it from moving forward. The other option would be simply to ignore the check by modifying the code which may be the much simpler solution for folks (at least temporarily). If I get a chance, I will try to do that later today. Peace - Anthony
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Anthony,
          don't forget to manually find and replace a few data in your sql backup, prior to restoring it on server, reflecting remote server instead of you localhost setup.
          I tried to change the install script, but unfortunally I'm not expert in php, so I could reach a solution.
          If you can, please let me know how to make the script skip the UTF 8 check.
          Regards.
          Antonio Carlos Pereira Gomes

          Show
          Antonio Carlos Pereira Gomes added a comment - Anthony, don't forget to manually find and replace a few data in your sql backup, prior to restoring it on server, reflecting remote server instead of you localhost setup. I tried to change the install script, but unfortunally I'm not expert in php, so I could reach a solution. If you can, please let me know how to make the script skip the UTF 8 check. Regards. Antonio Carlos Pereira Gomes
          Hide
          Richard L. Enison added a comment -

          O, AB, & ACPG,

          Congratulations for rediscovering what I found after hours of studying the MySQL manual and reported here almost a month ago (partially); and for misinterpreting what it says. Now here's the full story:

          1. The character_set_database variable IS the character set for the current database, NOT the server default. If you want the server default, no problem, look at the variable character_set_server.
          2. Just to make sure that it is the manual, and not HostMonster, that is telling the truth, I tried it on my home PC, which has a (so far!) operational Moodle 1.8.2 installed. See for yourself:

          mysql> show variables like 'character%';
          -------------------------------------------------------------------------------------------+

          Variable_name Value

          -------------------------------------------------------------------------------------------+

          character_set_client latin1
          character_set_connection latin1
          character_set_database utf8
          character_set_filesystem binary
          character_set_results latin1
          character_set_server latin1

          3. I didn't suggest using "show create database" before because the manual was unclear about whether it shows the command that was actually used to create the database (before any "alter database" queries were run), or whether it reports the current state of the database. So I tried it also on my PC and, yes, it does show the current character set, but not the collation.

          mysql> create database `xyz`;
          Query OK, 1 row affected (0.19 sec)

          mysql> use 'xyz';
          Database changed
          mysql> show 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

          mysql> show variables like 'collat%';
          ---------------------------------------+

          Variable_name Value

          ---------------------------------------+

          collation_connection latin1_swedish_ci
          collation_database latin1_swedish_ci
          collation_server latin1_swedish_ci

          ---------------------------------------+
          3 rows in set (0.00 sec)

          mysql> alter database `xyz` character set 'utf8' collate 'utf8_general_ci';
          Query OK, 1 row affected (0.11 sec)

          mysql> show create database xyz;
          ----------------------------------------------------------------------+

          Database Create Database

          ----------------------------------------------------------------------+

          xyz CREATE DATABASE `xyz` /*!40100 DEFAULT CHARACTER SET utf8 */

          ----------------------------------------------------------------------+
          1 row in set (0.00 sec)

          4. If you really want to see the collation instead of the character set, no problem. Look at the collation_database variable.

          mysql> show variables like 'collat%';
          ---------------------------------------+

          Variable_name Value

          ---------------------------------------+

          collation_connection latin1_swedish_ci
          collation_database utf8_general_ci
          collation_server latin1_swedish_ci

          ---------------------------------------+
          3 rows in set (0.00 sec)

          mysql> show create database moodle;
          -------------------------------------------------------------------------+

          Database Create Database

          -------------------------------------------------------------------------+

          moodle CREATE DATABASE `moodle` /*!40100 DEFAULT CHARACTER SET utf8 */

          -------------------------------------------------------------------------+
          1 row in set (0.11 sec)

          5. ACPG, I'm afraid you have ignored Lynn M's experience with skipping the UTF8 check. See her comment of October 19, which I mentioned in my comment of yesterday.

          RLE

          Show
          Richard L. Enison added a comment - O, AB, & ACPG, Congratulations for rediscovering what I found after hours of studying the MySQL manual and reported here almost a month ago (partially); and for misinterpreting what it says. Now here's the full story: 1. The character_set_database variable IS the character set for the current database, NOT the server default. If you want the server default, no problem, look at the variable character_set_server. 2. Just to make sure that it is the manual, and not HostMonster, that is telling the truth, I tried it on my home PC, which has a (so far!) operational Moodle 1.8.2 installed. See for yourself: mysql> show variables like 'character%'; ------------------------- ------------------------------------------------------------------+ Variable_name Value ------------------------- ------------------------------------------------------------------+ character_set_client latin1 character_set_connection latin1 character_set_database utf8 character_set_filesystem binary character_set_results latin1 character_set_server latin1 3. I didn't suggest using "show create database" before because the manual was unclear about whether it shows the command that was actually used to create the database (before any "alter database" queries were run), or whether it reports the current state of the database. So I tried it also on my PC and, yes, it does show the current character set, but not the collation. mysql> create database `xyz`; Query OK, 1 row affected (0.19 sec) mysql> use 'xyz'; Database changed mysql> show 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 mysql> show variables like 'collat%'; --------------------- ------------------+ Variable_name Value --------------------- ------------------+ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci --------------------- ------------------+ 3 rows in set (0.00 sec) mysql> alter database `xyz` character set 'utf8' collate 'utf8_general_ci'; Query OK, 1 row affected (0.11 sec) mysql> show create database xyz; --------- -------------------------------------------------------------+ Database Create Database --------- -------------------------------------------------------------+ xyz CREATE DATABASE `xyz` /*!40100 DEFAULT CHARACTER SET utf8 */ --------- -------------------------------------------------------------+ 1 row in set (0.00 sec) 4. If you really want to see the collation instead of the character set, no problem. Look at the collation_database variable. mysql> show variables like 'collat%'; --------------------- ------------------+ Variable_name Value --------------------- ------------------+ collation_connection latin1_swedish_ci collation_database utf8_general_ci collation_server latin1_swedish_ci --------------------- ------------------+ 3 rows in set (0.00 sec) mysql> show create database moodle; --------- ----------------------------------------------------------------+ Database Create Database --------- ----------------------------------------------------------------+ moodle CREATE DATABASE `moodle` /*!40100 DEFAULT CHARACTER SET utf8 */ --------- ----------------------------------------------------------------+ 1 row in set (0.11 sec) 5. ACPG, I'm afraid you have ignored Lynn M's experience with skipping the UTF8 check. See her comment of October 19, which I mentioned in my comment of yesterday. RLE
          Hide
          onitzuka added a comment -

          Hey, I just tried this and posted this on the other bug thread (http://tracker.moodle.org/browse/MDL-12062). I am adding it here since it would be useful for this discussion:

          Wow... I tried the query doing with a wildcard for the fields like this:

          SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'database_name';

          and it shows CHARACTER SET and COLLATION along with the name of the table! This is very cool!!

          Show
          onitzuka added a comment - Hey, I just tried this and posted this on the other bug thread ( http://tracker.moodle.org/browse/MDL-12062 ). I am adding it here since it would be useful for this discussion: Wow... I tried the query doing with a wildcard for the fields like this: SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'database_name'; and it shows CHARACTER SET and COLLATION along with the name of the table! This is very cool!!
          Hide
          Anthony Borrow added a comment -

          Richard - This is interesting. On hostmonster.com when I do show variables like 'character%'; I get:

          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 /usr/share/mysql/charsets/

          ;however, when I do SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'jesuits1_moodle';

          CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
          NULL jesuits1_moodle utf8 utf8_general_ci NULL

          Is the default_character_set_name for the database not the same as the character_set_database? I would have expected them to be the same.

          Show
          Anthony Borrow added a comment - Richard - This is interesting. On hostmonster.com when I do show variables like 'character%'; I get: 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 /usr/share/mysql/charsets/ ;however, when I do SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'jesuits1_moodle'; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH NULL jesuits1_moodle utf8 utf8_general_ci NULL Is the default_character_set_name for the database not the same as the character_set_database? I would have expected them to be the same.
          Hide
          onitzuka added a comment -

          I think I understand what you mean. The catch seems to be in a line of the the MySQL docs (http://dev.mysql.com/doc/refman/5.0/en/charset-database.html) that seems to suggest that it depends on what the default database is. So if one has not set the default database to be, let's say, the moodle database, then it would return whatever the system default is.

          I think that it is that bit of ambiguity might be the catch. The session's default database may or may not be the moodle database at a particular time, so a more explicity context-free query might be better (eg. SELECT blah-dee-blah).

          Hmmm.....

          Show
          onitzuka added a comment - I think I understand what you mean. The catch seems to be in a line of the the MySQL docs ( http://dev.mysql.com/doc/refman/5.0/en/charset-database.html ) that seems to suggest that it depends on what the default database is. So if one has not set the default database to be, let's say, the moodle database, then it would return whatever the system default is. I think that it is that bit of ambiguity might be the catch. The session's default database may or may not be the moodle database at a particular time, so a more explicity context-free query might be better (eg. SELECT blah-dee-blah). Hmmm.....
          Hide
          Anthony Borrow added a comment -

          Yes, that documentation makes it clearer for me:

          The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.

          I think the best way to ensure that the character set being returned by the database that Moodle is attempting to install to would then be:

          SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$dbname' LIMIT 1;

          I'll see if I can play with this some this evening.

          Peace - Anthony

          Show
          Anthony Borrow added a comment - Yes, that documentation makes it clearer for me: The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server. I think the best way to ensure that the character set being returned by the database that Moodle is attempting to install to would then be: SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$dbname' LIMIT 1; I'll see if I can play with this some this evening. Peace - Anthony
          Hide
          Richard L. Enison added a comment -

          O,

          I believe the default database is whatever database you are currently using. So while Moodle is running, the Moodle database is the default database.

          AB,

          Interesting. I too would have thought they would be the same. But maybe they are supposed to be the same. It wouldn't be the first inconsistency they have created. According to the MySQL manual, every collation can be used only with one character set, namely, the one whose name the collation name begins with. Which means that if the database collation is utf8_general_ci, the database character set must be utf8. But with HostMonster and BlueHost, you get utf8_general_ci with latin1.

          And by the way, that reminds me of one other point I meant to mention in my last comment. This problem with these renegade hosts is not restricted to MySQL. Lynn M. reported she had the exact same problem with PostgreSQL, in her comment of October 19.

          RLE

          Show
          Richard L. Enison added a comment - O, I believe the default database is whatever database you are currently using. So while Moodle is running, the Moodle database is the default database. AB, Interesting. I too would have thought they would be the same. But maybe they are supposed to be the same. It wouldn't be the first inconsistency they have created. According to the MySQL manual, every collation can be used only with one character set, namely, the one whose name the collation name begins with. Which means that if the database collation is utf8_general_ci, the database character set must be utf8. But with HostMonster and BlueHost, you get utf8_general_ci with latin1. And by the way, that reminds me of one other point I meant to mention in my last comment. This problem with these renegade hosts is not restricted to MySQL. Lynn M. reported she had the exact same problem with PostgreSQL, in her comment of October 19. RLE
          Hide
          Martin Dougiamas added a comment -

          Great to see all this work here ... I'll be very glad to see a definitive solution emerge from this.

          Just a data point: I think the check was done the way it is done now because it had to work on MySQL 4.1.16 and later ... so just be aware of MySQL versions ...

          Show
          Martin Dougiamas added a comment - Great to see all this work here ... I'll be very glad to see a definitive solution emerge from this. Just a data point: I think the check was done the way it is done now because it had to work on MySQL 4.1.16 and later ... so just be aware of MySQL versions ...
          Hide
          Anthony Borrow added a comment -

          I think the solution will look something like a modification to nuance what is happening with in /lib/setuplib.php with the setup_is_unicodedb function.

          Currently we have:
          case 'mysql':
          $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'");
          if ($rs && $rs->RecordCount() > 0) {
          $records = $rs->GetAssoc(true);
          $encoding = $records['character_set_database']['Value'];
          if (strtoupper($encoding) == 'UTF8')

          { $unicodedb = true; }

          }

          I would say that we should change it to:

          case 'mysql':

          // get the version of the mysql server, then do an if else statement such that if the database is less than 5.0.6 then use the current method; however, if it is greater than 5.0.6 then the execute statement should be - SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$CFG->dbname' LIMIT 1;

          Does that seem like a reasonable approach? If so I will investigate the best way to get the current database version number. Peace - Anthony

          Show
          Anthony Borrow added a comment - I think the solution will look something like a modification to nuance what is happening with in /lib/setuplib.php with the setup_is_unicodedb function. Currently we have: case 'mysql': $rs = $db->Execute("SHOW VARIABLES LIKE 'character_set_database'"); if ($rs && $rs->RecordCount() > 0) { $records = $rs->GetAssoc(true); $encoding = $records ['character_set_database'] ['Value'] ; if (strtoupper($encoding) == 'UTF8') { $unicodedb = true; } } I would say that we should change it to: case 'mysql': // get the version of the mysql server, then do an if else statement such that if the database is less than 5.0.6 then use the current method; however, if it is greater than 5.0.6 then the execute statement should be - SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$CFG->dbname' LIMIT 1; Does that seem like a reasonable approach? If so I will investigate the best way to get the current database version number. Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          Martin - Here is a diff file of a patch that I am experimenting with. Please note that I have NOT actually gotten it to work. This is not a working patch but intended only to show the logic behind what I am thinking of for a patch. I'm going to bed but hopefully this will give you (or someone else) a starting place to work on this issue. Peace - Anthony

          Show
          Anthony Borrow added a comment - Martin - Here is a diff file of a patch that I am experimenting with. Please note that I have NOT actually gotten it to work. This is not a working patch but intended only to show the logic behind what I am thinking of for a patch. I'm going to bed but hopefully this will give you (or someone else) a starting place to work on this issue. Peace - Anthony
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Hi Anthony,
          thank you for your efforts to have this issue solved.
          I say that because besides the hostmonster policy about MySQL, it would be really great to have moodle running under it smoothly. Their php policy are better than midphase or anhosting, and give us more autonomy on server use like: they do not limit the amount of email per hour (300 at anhosting), they do not limit upload file size (8mb at anhosting), the performance is greater than others (I have my plan under a 8 core server) and the cost/bennefit is the best I've experienced, and the phpMyAdmin access is really fast. I say that because is painfull to access my phpMyAdmin under anhosting. So, it sounds like a dream to have moodle running just fine at hostmonster. In fact I have a instance installed running at tema.odontoblasto.org, it's a testing installation, but I'm affraid of any upgrade need because the UTF-8 issue. Feel free to taste it.
          Regards.
          Antonio Carlos Pereira Gomes.

          Show
          Antonio Carlos Pereira Gomes added a comment - Hi Anthony, thank you for your efforts to have this issue solved. I say that because besides the hostmonster policy about MySQL, it would be really great to have moodle running under it smoothly. Their php policy are better than midphase or anhosting, and give us more autonomy on server use like: they do not limit the amount of email per hour (300 at anhosting), they do not limit upload file size (8mb at anhosting), the performance is greater than others (I have my plan under a 8 core server) and the cost/bennefit is the best I've experienced, and the phpMyAdmin access is really fast. I say that because is painfull to access my phpMyAdmin under anhosting. So, it sounds like a dream to have moodle running just fine at hostmonster. In fact I have a instance installed running at tema.odontoblasto.org, it's a testing installation, but I'm affraid of any upgrade need because the UTF-8 issue. Feel free to taste it. Regards. Antonio Carlos Pereira Gomes.
          Hide
          Richard L. Enison added a comment -

          AB and whoever else is interested,

          There are two issues that seem to be avoided so far in regards to a solution,

          1. Before we can create or evaluate a proposed solution, we need to know what the problem is. Does anyone know exactly what HostMonster and BlueHost are doing to violate the rules of MySQL so that you get a character_set_database of latin1, collation_database of utf8_general_ci, and DEFAULT_CHARACTER_SET_NAME of utf8. If we adopt your change, what is to stop those hosts from changing the rules again to thwart our efforts to get Moodle installed? What is their motivaiton?
          2. Let's not forgot the problem exists on those hosts for PostgreSQL as well. The solution needs to address that case also.

          RLE

          Show
          Richard L. Enison added a comment - AB and whoever else is interested, There are two issues that seem to be avoided so far in regards to a solution, 1. Before we can create or evaluate a proposed solution, we need to know what the problem is. Does anyone know exactly what HostMonster and BlueHost are doing to violate the rules of MySQL so that you get a character_set_database of latin1, collation_database of utf8_general_ci, and DEFAULT_CHARACTER_SET_NAME of utf8. If we adopt your change, what is to stop those hosts from changing the rules again to thwart our efforts to get Moodle installed? What is their motivaiton? 2. Let's not forgot the problem exists on those hosts for PostgreSQL as well. The solution needs to address that case also. RLE
          Hide
          onitzuka added a comment -

          Hi! I'd like to throw some support behind testing helping with code testing. I am using bluehost for my hosting and have both MySQL and PostgeSQL databases as part of my package. I'd be happy to help in the testing as well.

          Show
          onitzuka added a comment - Hi! I'd like to throw some support behind testing helping with code testing. I am using bluehost for my hosting and have both MySQL and PostgeSQL databases as part of my package. I'd be happy to help in the testing as well.
          Hide
          Anthony Borrow added a comment -

          I'm trying to replicate the environment of hostmonster.com so from phpMyAdmin I saw the following variable were set:

          character set client utf8
          (Global value) latin1
          character set connection utf8
          (Global value) latin1
          character set database latin1
          character set filesystem binary
          character set results utf8
          (Global value) latin1
          character set server latin1
          character set system utf8
          character sets dir /usr/share/mysql/charsets/
          collation connection utf8_unicode_ci
          (Global value) latin1_swedish_ci
          collation database latin1_swedish_ci
          collation server latin1_swedish_ci

          I checked those same variables and compared it to my localhost installation and to my surprise they were the same. Yet I cannot reproduce the hostmonster.com behavior on my localhost. Does anyone have any ideas? I'd rather be able to troubleshoot this on my localhost then trying to upload and do it on hostmonster.com. Thanks for any insights or suggestions of further places I might look to get an idea of what is really going on. Peace - Anthony

          Show
          Anthony Borrow added a comment - I'm trying to replicate the environment of hostmonster.com so from phpMyAdmin I saw the following variable were set: character set client utf8 (Global value) latin1 character set connection utf8 (Global value) latin1 character set database latin1 character set filesystem binary character set results utf8 (Global value) latin1 character set server latin1 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_unicode_ci (Global value) latin1_swedish_ci collation database latin1_swedish_ci collation server latin1_swedish_ci I checked those same variables and compared it to my localhost installation and to my surprise they were the same. Yet I cannot reproduce the hostmonster.com behavior on my localhost. Does anyone have any ideas? I'd rather be able to troubleshoot this on my localhost then trying to upload and do it on hostmonster.com. Thanks for any insights or suggestions of further places I might look to get an idea of what is really going on. Peace - Anthony
          Hide
          Richard L. Enison added a comment - - edited

          AB,

          Your last post made me realize that I was mistaken about hostmonster and bluehost violating the rules of MySQL by having a database with character set latin1 and collation utf8_unicode_ci. As I look back at the screenshots I see that it was the connection collation shown as utf8_unicode_ci. It also said SQL character set was utf8, which is kind of ambiguous; perhaps that was a reference to the connection as well. I stand corrected.

          RLE

          EDIT: PS As a result, here is my resolution of this issue. Lynn M's comment of Oct. 19 shows that bypassing the Unicode test in the installation scripts doesn't work. Needless to say, trying to cover the problem up by changing the test to look at connection character set or collation, etc., would have the same effect. The same comment shows that the tech support staff either lied or were mistaken when they said that you could solve the problem by exporting and importing a Unicode database. So as long as Moodle requires a Unicode database, hosts that prevent their clients from creating or importing one cannot be used to host Moodle. Either get the host to change their policy (and at least stop lying about it), or switch hosts. That is my resolution.

          Show
          Richard L. Enison added a comment - - edited AB, Your last post made me realize that I was mistaken about hostmonster and bluehost violating the rules of MySQL by having a database with character set latin1 and collation utf8_unicode_ci. As I look back at the screenshots I see that it was the connection collation shown as utf8_unicode_ci. It also said SQL character set was utf8, which is kind of ambiguous; perhaps that was a reference to the connection as well. I stand corrected. RLE EDIT: PS As a result, here is my resolution of this issue. Lynn M's comment of Oct. 19 shows that bypassing the Unicode test in the installation scripts doesn't work. Needless to say, trying to cover the problem up by changing the test to look at connection character set or collation, etc., would have the same effect. The same comment shows that the tech support staff either lied or were mistaken when they said that you could solve the problem by exporting and importing a Unicode database. So as long as Moodle requires a Unicode database, hosts that prevent their clients from creating or importing one cannot be used to host Moodle. Either get the host to change their policy (and at least stop lying about it), or switch hosts. That is my resolution.
          Hide
          Anthony Borrow added a comment -

          This patch file fixed the problem on hostmonster.com. It is a simple one word patch; however, I do not understand why it works I just know that it does. I'm not sure how changing it in core might impact other installations but if someone else has a better understanding and feels comfortable checking it in then perhaps it will resolve this problem. It is simply a matter of changing the SQL from:

          SHOW VARIABLES LIKE 'character_set_database'

          to:

          SHOW LOCAL VARIABLES LIKE 'character_set_database'

          It has something to do with sessions and in a host environment it gives the correct result. Peace - Anthony

          p.s. - Thanks to Jason Schmidt for reporting MDL-11671 for the proposed fix on this.

          Show
          Anthony Borrow added a comment - This patch file fixed the problem on hostmonster.com. It is a simple one word patch; however, I do not understand why it works I just know that it does. I'm not sure how changing it in core might impact other installations but if someone else has a better understanding and feels comfortable checking it in then perhaps it will resolve this problem. It is simply a matter of changing the SQL from: SHOW VARIABLES LIKE 'character_set_database' to: SHOW LOCAL VARIABLES LIKE 'character_set_database' It has something to do with sessions and in a host environment it gives the correct result. Peace - Anthony p.s. - Thanks to Jason Schmidt for reporting MDL-11671 for the proposed fix on this.
          Hide
          Anthony Borrow added a comment -

          I'm not sure of the exact nature of the relationship between these two. In some ways it could be a duplicate or one helping the resolve the other. In any case, there is a relationship as the proposed solution MDL-11671 seems to resolve MDL-11743. Peace - Anthony

          Show
          Anthony Borrow added a comment - I'm not sure of the exact nature of the relationship between these two. In some ways it could be a duplicate or one helping the resolve the other. In any case, there is a relationship as the proposed solution MDL-11671 seems to resolve MDL-11743 . Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          Just to confirm that with the SHOW LOCAL VARIABLES LIKE 'character_set_database' in /lib/setuplib.php I was able to run through and do an install on hostmonster. com without issue. Hopefully this will provide an easy fix for those dealing with this issue while the solution is evaluated for inclusion into Moodle core. I don't think it will break anything but hopefully it will fix something. But it is probably best that we check with a DB expert. Also, I'm not sure how this may or may not help out for PostgreSQL. Peace - Anthony

          Show
          Anthony Borrow added a comment - Just to confirm that with the SHOW LOCAL VARIABLES LIKE 'character_set_database' in /lib/setuplib.php I was able to run through and do an install on hostmonster. com without issue. Hopefully this will provide an easy fix for those dealing with this issue while the solution is evaluated for inclusion into Moodle core. I don't think it will break anything but hopefully it will fix something. But it is probably best that we check with a DB expert. Also, I'm not sure how this may or may not help out for PostgreSQL. Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          http://dev.mysql.com/doc/refman/5.0/en/show-variables.html states:

          With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

          It would seem to me that since SESSION is the default and LOCAL is a synonym for SESSION then normally the default would make SHOW VARIABLES equivalent to SHOW LOCAL VARIABLES. There must be something in the way the these hosts are setting up mysql such that SHOW VARIABLES is returning the GLOBAL rather than the SESSION or LOCAL result.

          I think by making it explicitly ask for SESSION or LOCAL that we should be in good shape. I see no reason not to commit this but will defer to the experts on this one. Peace - Anthony

          Show
          Anthony Borrow added a comment - http://dev.mysql.com/doc/refman/5.0/en/show-variables.html states: With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION. It would seem to me that since SESSION is the default and LOCAL is a synonym for SESSION then normally the default would make SHOW VARIABLES equivalent to SHOW LOCAL VARIABLES. There must be something in the way the these hosts are setting up mysql such that SHOW VARIABLES is returning the GLOBAL rather than the SESSION or LOCAL result. I think by making it explicitly ask for SESSION or LOCAL that we should be in good shape. I see no reason not to commit this but will defer to the experts on this one. Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          Richard - I don't think that hostmonster is lying. They do allow for a UTF8 database. I think there was just some confusion about why the result being returned was indicating that the UTF8 database was latin1. By explicitly telling mysql to show us the LOCAL or SESSION variable (rather than the global) I think we are safe. Why the default for showing the variable was returning the global (latin1) is a mystery to me. I'd be interested to see their mysql config file. Peace - Anthony

          Show
          Anthony Borrow added a comment - Richard - I don't think that hostmonster is lying. They do allow for a UTF8 database. I think there was just some confusion about why the result being returned was indicating that the UTF8 database was latin1. By explicitly telling mysql to show us the LOCAL or SESSION variable (rather than the global) I think we are safe. Why the default for showing the variable was returning the global (latin1) is a mystery to me. I'd be interested to see their mysql config file. Peace - Anthony
          Hide
          Richard L. Enison added a comment - - edited

          AB,

          Point of order, Mr. Chairman. I didn't say they were lying. I said they were either lying or unaware they were being untruthful!

          Seriously, now that I have taken the time to look at this tracker issue again, and read the latest comments, I see that you had already found the MySQL manual page I wrote you about in my reply to your Installation Problems Forum post, three hours before I did. I was going by what was in your post and I hadn't seen your latest comments here. At least we came to the same conclusion about what the manual means. As they say, great minds think alike!

          As for whether changing SHOW VARIABLES to SHOW LOCAL VARIABLES in the query Moodle uses to test for Unicode will do any harm to "normal" servers, I think not. Since, according to the manual, the two forms of the query are equivalent, changing from one to the other should have absolutely no effect in a "normal" installation.

          But, as you can see from the Moodle code, as well as my comment here of Oct. 17, Moodle uses a different query to test PostgreSQL databases for Unicode compliance, and Lynn M reported the same problem for them at hostmonster/bluehost around the same time. So your mission, should you decide to accept it, is to fix the Moodle installation script for them as well.

          RLE

          Show
          Richard L. Enison added a comment - - edited AB, Point of order, Mr. Chairman. I didn't say they were lying. I said they were either lying or unaware they were being untruthful! Seriously, now that I have taken the time to look at this tracker issue again, and read the latest comments, I see that you had already found the MySQL manual page I wrote you about in my reply to your Installation Problems Forum post, three hours before I did. I was going by what was in your post and I hadn't seen your latest comments here. At least we came to the same conclusion about what the manual means. As they say, great minds think alike! As for whether changing SHOW VARIABLES to SHOW LOCAL VARIABLES in the query Moodle uses to test for Unicode will do any harm to "normal" servers, I think not. Since, according to the manual, the two forms of the query are equivalent, changing from one to the other should have absolutely no effect in a "normal" installation. But, as you can see from the Moodle code, as well as my comment here of Oct. 17, Moodle uses a different query to test PostgreSQL databases for Unicode compliance, and Lynn M reported the same problem for them at hostmonster/bluehost around the same time. So your mission, should you decide to accept it, is to fix the Moodle installation script for them as well. RLE
          Hide
          Richard L. Enison added a comment -

          AB,

          As a test of my point about "normal" sites, here is what happened in my XAMPPlite Moodle on my PC:

          mysql> show variables like 'character_set%';
          -----------------------------------------------------------------------------------+

          Variable_name Value

          -----------------------------------------------------------------------------------+

          character_set_client latin1
          character_set_connection latin1
          character_set_database utf8
          character_set_filesystem binary
          character_set_results latin1
          character_set_server latin1
          character_set_system utf8
          character_sets_dir C:\down\SourceView\Moodle\Moodle183\mysql\share\charsets\

          -----------------------------------------------------------------------------------+
          8 rows in set (0.92 sec)

          mysql> show local variables like 'character_set%';
          -----------------------------------------------------------------------------------+

          Variable_name Value

          -----------------------------------------------------------------------------------+

          character_set_client latin1
          character_set_connection latin1
          character_set_database utf8
          character_set_filesystem binary
          character_set_results latin1
          character_set_server latin1
          character_set_system utf8
          character_sets_dir C:\down\SourceView\Moodle\Moodle183\mysql\share\charsets\

          -----------------------------------------------------------------------------------+
          8 rows in set (0.00 sec)

          mysql> show global variables like 'character_set%';
          -----------------------------------------------------------------------------------+

          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 C:\down\SourceView\Moodle\Moodle183\mysql\share\charsets\

          -----------------------------------------------------------------------------------+
          8 rows in set (0.00 sec)

          RLE

          Show
          Richard L. Enison added a comment - AB, As a test of my point about "normal" sites, here is what happened in my XAMPPlite Moodle on my PC: mysql> show variables like 'character_set%'; ------------------------- ----------------------------------------------------------+ Variable_name Value ------------------------- ----------------------------------------------------------+ character_set_client latin1 character_set_connection latin1 character_set_database utf8 character_set_filesystem binary character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir C:\down\SourceView\Moodle\Moodle183\mysql\share\charsets\ ------------------------- ----------------------------------------------------------+ 8 rows in set (0.92 sec) mysql> show local variables like 'character_set%'; ------------------------- ----------------------------------------------------------+ Variable_name Value ------------------------- ----------------------------------------------------------+ character_set_client latin1 character_set_connection latin1 character_set_database utf8 character_set_filesystem binary character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir C:\down\SourceView\Moodle\Moodle183\mysql\share\charsets\ ------------------------- ----------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> show global variables like 'character_set%'; ------------------------- ----------------------------------------------------------+ 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 C:\down\SourceView\Moodle\Moodle183\mysql\share\charsets\ ------------------------- ----------------------------------------------------------+ 8 rows in set (0.00 sec) RLE
          Hide
          onitzuka added a comment -

          I tried the patch on bluehost. I downloaded 1.8.3+, patched it with the MySQL fix shown above, and reran the install and it worked perfectly this time.

          Using it after the installation, here are the results for Japanese text in Moodle (should apply to all UTF8 folks):

          • Chat: Japanese and English OK
          • Forums: Japanese and English OK
          • Wiki: Japanese and English OK

          So far, I am feeling really positive about this fix. I'm giving a preliminary thumbs up.... I will keep testing to see how it feels.

          Anybody else given this patch a whirl?

          Show
          onitzuka added a comment - I tried the patch on bluehost. I downloaded 1.8.3+, patched it with the MySQL fix shown above, and reran the install and it worked perfectly this time. Using it after the installation, here are the results for Japanese text in Moodle (should apply to all UTF8 folks): Chat: Japanese and English OK Forums: Japanese and English OK Wiki: Japanese and English OK So far, I am feeling really positive about this fix. I'm giving a preliminary thumbs up.... I will keep testing to see how it feels. Anybody else given this patch a whirl?
          Hide
          Anthony Borrow added a comment -

          OK - I have received feedback from a few folks indicating that this takes care of the MySQL end of things. I have not attempted to dive into the Postgres side yet. I hope that it is as simple. I am going to go ahead and commit the patch since it only makes explicit what is normally default behavior and seems to return the more accurate result about the database's actual character set. Peace - Anthony

          Show
          Anthony Borrow added a comment - OK - I have received feedback from a few folks indicating that this takes care of the MySQL end of things. I have not attempted to dive into the Postgres side yet. I hope that it is as simple. I am going to go ahead and commit the patch since it only makes explicit what is normally default behavior and seems to return the more accurate result about the database's actual character set. Peace - Anthony
          Hide
          Anthony Borrow added a comment -

          I have committed to 1.8 and 1.9 stable branches; however, I have to run to class and will apply the change to 1.6 and 1.7 upon return. Peace - Anthony

          Show
          Anthony Borrow added a comment - I have committed to 1.8 and 1.9 stable branches; however, I have to run to class and will apply the change to 1.6 and 1.7 upon return. Peace - Anthony
          Hide
          Antonio Carlos Pereira Gomes added a comment -

          Hi Anthony,
          God bless you!! You really gave us peace!
          I run two CVS at hostmonster: one as a fresh install, and it run just fine; and the other in a production environment (where I've setup moodle manually to override the UTF-8 problem) and I got an exercise tables upgrade and everything is just fine too. It was 191 tables and with the upgrade has 192, just like the fresh install.
          Best regards from Brazil.
          Antonio Carlos Pereira Gomes.
          acpgomes@gmail.com.

          Show
          Antonio Carlos Pereira Gomes added a comment - Hi Anthony, God bless you!! You really gave us peace! I run two CVS at hostmonster: one as a fresh install, and it run just fine; and the other in a production environment (where I've setup moodle manually to override the UTF-8 problem) and I got an exercise tables upgrade and everything is just fine too. It was 191 tables and with the upgrade has 192, just like the fresh install. Best regards from Brazil. Antonio Carlos Pereira Gomes. acpgomes@gmail.com.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi,

          I've applied the patch against HEAD too (was missing) and flagged the MERGED tags properly. I don't understand why the LOCAL keyword provides any advantage (on fact, reading the docs it's the default, if no specified).

          But if it's working for more people and doesn't cause headaches to the rest, it's ok!

          Thanks, Anthony! B-) (and the rest of the collaborators with this issue).

          Closing now...

          Show
          Eloy Lafuente (stronk7) added a comment - Hi, I've applied the patch against HEAD too (was missing) and flagged the MERGED tags properly. I don't understand why the LOCAL keyword provides any advantage (on fact, reading the docs it's the default, if no specified). But if it's working for more people and doesn't cause headaches to the rest, it's ok! Thanks, Anthony! B-) (and the rest of the collaborators with this issue). Closing now...
          Hide
          Anthony Borrow added a comment -

          Just to follow up on the Postgres side of things. I was able to install Moodle without incident on hostmonster.com; however, I had to call and request that they change the encoding of the database to unicode but I did not run into any problems thereafter. Peace - Anthony

          Show
          Anthony Borrow added a comment - Just to follow up on the Postgres side of things. I was able to install Moodle without incident on hostmonster.com; however, I had to call and request that they change the encoding of the database to unicode but I did not run into any problems thereafter. Peace - Anthony
          Hide
          Robert Allerstorfer added a comment -

          The problem is still there if you go to Administration ? Server ? Environment in Moodle 1.8.3+.The status of the "unicode" check is still "Check" instead of "OK". This was the same in the 1.7 Moodle I have updated to 1.8. I ignored the reported error and could update without any problems.

          However, before updating 1.8 to 1.9 I would like to have this cosmetic thing fixed. is there a fix already? Thanks.

          Show
          Robert Allerstorfer added a comment - The problem is still there if you go to Administration ? Server ? Environment in Moodle 1.8.3+.The status of the "unicode" check is still "Check" instead of "OK". This was the same in the 1.7 Moodle I have updated to 1.8. I ignored the reported error and could update without any problems. However, before updating 1.8 to 1.9 I would like to have this cosmetic thing fixed. is there a fix already? Thanks.
          Hide
          Robert Allerstorfer added a comment -

          please ignore my last post. It seems that I have in fact a problem with the UTF-8 settings on my MySQL server.

          Show
          Robert Allerstorfer added a comment - please ignore my last post. It seems that I have in fact a problem with the UTF-8 settings on my MySQL server.
          Hide
          Kent Bergstrom added a comment -

          I've read through all of this information to the best of my ability and I still don't understand what the "fix" or resolution to this issue is.
          I have two brand new installs that are doing this same thing of not being able to get past the "check utf-8" point on install and the
          server is utf-8 by default.

          Show
          Kent Bergstrom added a comment - I've read through all of this information to the best of my ability and I still don't understand what the "fix" or resolution to this issue is. I have two brand new installs that are doing this same thing of not being able to get past the "check utf-8" point on install and the server is utf-8 by default.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi Kent,

          not only the server has to be utf-8 but the database itself must be created with the 'UTF-8' encoding. I would suggest you to see the documentation about how create dbs in your DB flavour (Mysql, PostgreSQL..). It uses to be one "CREATE DATABASE ... " statement and also uses to include some way to define the encoding of the DB being created.

          I think that's the best fix - to create the database manually specifying the desired (UTF-8) encoding. Hope this helps.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Hi Kent, not only the server has to be utf-8 but the database itself must be created with the 'UTF-8' encoding. I would suggest you to see the documentation about how create dbs in your DB flavour (Mysql, PostgreSQL..). It uses to be one "CREATE DATABASE ... " statement and also uses to include some way to define the encoding of the DB being created. I think that's the best fix - to create the database manually specifying the desired (UTF-8) encoding. Hope this helps. Ciao
          Hide
          Anthony Borrow added a comment -

          Kent - I would add to Eloy by saying if you are using MySQL you no longer have to create the database and the Moodle install script should now take care of creating a UTF-8 encoded database for you. When in doubt, it can help to do it manually as you will know that it is UTF-8 without any doubt. But essentially the change that was done was can be seen at: http://cvs.moodle.org/moodle/lib/setuplib.php?r1=1.19.2.4&r2=1.19.2.5. Let us know if you have any questions. Peace - Anthony

          Show
          Anthony Borrow added a comment - Kent - I would add to Eloy by saying if you are using MySQL you no longer have to create the database and the Moodle install script should now take care of creating a UTF-8 encoded database for you. When in doubt, it can help to do it manually as you will know that it is UTF-8 without any doubt. But essentially the change that was done was can be seen at: http://cvs.moodle.org/moodle/lib/setuplib.php?r1=1.19.2.4&r2=1.19.2.5 . Let us know if you have any questions. Peace - Anthony
          Hide
          Jonathan Konrad added a comment -

          I'm a bit vague on the solution too. I have a production moodle server. It's running 1.8. I did the converstion to utf8 on my databases when I moved from 1.6 last year.

          Now I wanted to update to the latest 1.8.4 or even 1.9. The update fails on the db check. It reports I do not have a utf8 database. From everything I can check I do. The restuls from SHOW VARIABLES LIKE 'character_set_database'; is utf8.

          I'm stuck. I'm using MySQL 5.0.22, Apache Webserver 2.23, php 5.1.6, Moodle 1.8, on RHEL 5.

          I have screen shots, but I wasn't sure if I should post here. Thanks.

          Show
          Jonathan Konrad added a comment - I'm a bit vague on the solution too. I have a production moodle server. It's running 1.8. I did the converstion to utf8 on my databases when I moved from 1.6 last year. Now I wanted to update to the latest 1.8.4 or even 1.9. The update fails on the db check. It reports I do not have a utf8 database. From everything I can check I do. The restuls from SHOW VARIABLES LIKE 'character_set_database'; is utf8. I'm stuck. I'm using MySQL 5.0.22, Apache Webserver 2.23, php 5.1.6, Moodle 1.8, on RHEL 5. I have screen shots, but I wasn't sure if I should post here. Thanks.
          Hide
          Richard L. Enison added a comment -

          JK,

          Try

          SHOW LOCAL VARIABLES LIKE 'character_set_database'

          and see if you get a different result.

          RLE

          Show
          Richard L. Enison added a comment - JK, Try SHOW LOCAL VARIABLES LIKE 'character_set_database' and see if you get a different result. RLE

            People

            • Votes:
              14 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: