Migration / Upgrade Problem

Migration / Upgrade Problem

by Aaron NA -
Number of replies: 4

Ok, I'm really banging my head into a wall here. Here's what we have...

Linux Slackware 10.2 running MySQL 4.1.16 Moodle Server 1.8.3 and believe it's all in UTF8 Database. This setup runs without any major issues, I have some administration problems which I think was due to a botched upgrade from 1.7.1 to 1.8.3 but we're able to do everything in it no problems.

I want to migrate/upgrade this setup to a Linux Redhat Enterprise Server running Enterprise v5 and MySQL 5.0.45.

If I do a direct install of Moodle v1.8.5, it creates the database and everything just fine, I can get into the Moodle server and do whatever I want, without any problems. I export our Moodle Database data from our live slackware box to a .SQL File and then import that file into the Redhat server. This causes the once perfectly running Moodle 1.8.5 to display

"Sorry, but your database is not already in Unicode, and this version of Moodle is not able to migrate your database to Unicode. Please upgrade to Moodle 1.7.x first and perform the Unicode migration from the Admin page. After that is done you should be able to migrate to Moodle 2007021550"

So I back peddel and drop all the tables, put up moodle 1.7.X, run the install, delete the moodle 1.7.x public_html files and put in 1.8.5 and perform the upgrade. Everything upgrades and all that just fine. So the servers running fine on 1.8.5. Then I re-import our live server data and get the same stupid error. I've made sure to export from our live server as UTF8, confirmed that the .SQL file contains the character set UTF8 commands, etc...

My MySQL System Variables all say everything is UTF8


 character_set_client utf8

character_set_connection utf8

character_set_database utf8

character_set_filesystem binary

character_set_results utf8

character_set_server utf8

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

collation_connection utf8_general_ci

collation_database utf8_unicode_ci

collation_server utf8_general_ci

WHAT THE HECK IS GOING ON? And yes I double checked the SHOW LOCAL VARIABLES and they confirm the same thing.

Now the only thing I have not done yet, is copy over the public_html and course files from our Live Moodle server to the new server after the import. I wouldn't think that the missing course files would cause it to say that theres a Unicode Database issue. Heck, maybe I'm wrong, been known to happen now and again.

Any help you guys and girls can provide, would be greatly appreciated!

Oh PS> We are running Webmin and our server is on a virtual server created by Virtualmin. Our live server is setup the same way. With the exception of the live server on the linux slackware machine is Webmin 1.360 and the new server (Redhat) is running Webmin 1.420.

Thanks in advance!

Average of ratings: -
In reply to Aaron NA

Re: Migration / Upgrade Problem

by Myles Carrick -
hi Aaron,
Are the actual tables in a unicode format? MySQL makes it possible for the database and its tables to have different collation types (stupid, i know).
PHPMyAdmin or similar should show you this when you browse, or at the MySQL command line you can type 
show table status;
to see the sort of info you want. That might shed some light on what is going on.
Myles C

Average of ratings: -
In reply to Myles Carrick

Re: Migration / Upgrade Problem

by Aaron NA -

Thanks, I didn't actually know about the "show table status;" command but that had some interesting information in it. All tables did report that it was in UTF8 unicode character set. What I believe ended up resolve all my issues was running the command

mysql> charset utf8
Charset Changed

The charset command issues a SET NAMES statement, and also changes the default character set that is used if mysql reconnects after the connection has dropped.

From my quick reading of the differences between MySQL 4.X and MySQL 5.X, MySQL 4.X doesn't require this independant setting.

My upgrade after performing this command went flawless afterwards!

Here's additional information incase you want to check it out...

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

Average of ratings: -
In reply to Aaron NA

Re: Migration / Upgrade Problem

by Richard Enison -
AN,
  1. I think MC is sort of on the right track. However, I have found that running SHOW TABLE STATUS; in a command prompt window using the mysql client program produces four or five lines of output for each table including collation, and with a typical Moodle database (db) this means about a thousand lines altogether, most of which scrolls off the screen so fast you can't read it. Maybe you can do better with phpMyAdmin, but it's beside the point anyway.
  2. I believe you spent an enormous amount of time and energy back pedaling, as you put it, unnecessarily.
  3. The problem, I believe, is that some installations are set up so that when you do a mysqldump, even of a UTF8 db, it produces a Latin1 sql file. If so, you need to create a new blank UTF8 db and import/restore to it from that sql file, selecting the input file character set as Latin1. See http://moodle.org/mod/forum/discuss.php?d=76882#p342091
RLE
Average of ratings: -
In reply to Richard Enison

Re: Migration / Upgrade Problem

by Aaron NA -

Well I thank you both for replying however I found the problem, or at least what I believe the problem was. I was browsing through some MySQL 5.X Documentation when I saw this one command.

mysql> charset utf8
Charset changed

So, being completely frustrated and confused. I ran this command and afterwards I had no problems with downgrading to Moodle 1.7.x public_html files, which then warned me about not being able to downgrade to a previous version, then I extracted Moodle 1.8.5 to Public_HTML, which upgraded just fine, then I upgraded again to Moodle 1.9.2 without any issues either. Now I'm by no means a MySQL Expert and don't really understand what this command changed to get everything to run right. But it certainly fixed my issue. Here's the link for the reference page I was browsing to resolve my issue. Maybe it'll help someone else out there banging their head over a MySQL 4.X migration to MySQL 5.X backend.

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

Average of ratings: -