UTF Migration Script Stops

UTF Migration Script Stops

by Manish Verma -
Number of replies: 6
Hi,

I upgraded from 1.5.3+ to 1.7+ a few months ago. The upgrade was fine but the utf migration script always stopped when I ran it several times (I always waited each time and never killed it). I used to get strange symbols but a database dump then import again to mysql using bigdump script coupled with replace.php script helped a lot (bigdump script ran the whole night).

The situation is like this till today and I have minor issues, otherwise things work fine with the installation. Moodle installation shows that migration is not complete. I do not use any other language except English. Now, I am a bit inclined to upgrade to 1.9 and hence am back to the same issue of utf migration.

What can I do? Can I migrate the left out part (if any) from phpadmin? If yes, detailed instructions will help a lot as I do not use phpadmin often. Also, what about the additional data that has been added to the database since the past few months after upgrade? I have only run the migration script then and not recently.

Here is the environment report:

Name Information Report Status
unicode
is recommended to be installed/enabled Check
database mysql version 4.1.16 is required and you are running 4.1.22 OK
php
version 4.3.0 is required and you are running 5.2.2 OK
php_extension iconv is recommended to be installed/enabled OK
php_extension mbstring is recommended to be installed/enabled OK

Doing course by course back up and restoring in the new version is not possible as backup script stops being shared linux server.

The database size is 160 MB+.

Config.php shows that utf is not enabled.

Manish.
Average of ratings: -
In reply to Manish Verma

Re: UTF Migration Script Stops

by Sativa Huang -

It seems your database encoding is not in UTF-8, since the Migration Scripts could not perform well, you may try to convert it manually. What you have to do as follows:

Backup & UTF-8 Migrating:

  • Backup your courses.
  • Using phpMyAdmin or MySQL to Backup your database (eg. mdle.sql)
  • Utilising Notepad or Notepad2 to open mdle.sql, converting the current  charset, eg. latin1 to utf8 (Edit > Replace > Replace all)
  • Checking if there are any utf8_swedish_ci, utf8_unicode_ci or other  encodings, converting all of them to utf8_general_ci.
    Note: utf8_unicode is also applicable. Utf8_general works faster but less accurate than utf8_unicode_ci.
  • Saving mdle.sql in UTF-8 and Zipping it.

Moodle Installation & Database Restoring:

  • Using phpMyAdmin for creating a new empty database, eg. mdle, choosing utf8_general_ci for Collation.
  • Applying MySQL to add user to the new database, ie.mdle, set Privileges to ALL.
  • Installing Moodle.
  • Opening phpMyAdmin, choosing mdle, scrolling to the end of database, clicking Check All, then With Selected: Drop.
  • Importing your old but UTF-8 migrated database.
  • Restoring your courses.

It works for me, would you please check if it works for you as well?

Regards,

Average of ratings: -
In reply to Sativa Huang

Re: UTF Migration Script Stops

by Manish Verma -
Hi,

There are two issues at the moment in the above mentioned steps:
  1. Courses backup: I cannot take backup for some courses as these have very large number of quiz questions and the backup script stops most probably due to server restrictions beyond my control.
  2. Notepad/Wordpad are unable to open the .sql file due to its size.
Is it not possible to attain the objective via phpMyAdmin completely for the current installation. Once, the current installation (1.7) is confirmed to be migrated in utf, I can upgrade the whole installation to 1.9.

Manish.

PS: http://docs.moodle.org/en/Alternative_migration_process looks interesting but here I do not have have MySQL 5.0.x (I have 4.1.x). Moreover, the first bulleted point says, "Ensure your Moodle site use only one encoding (eg. all courses use a latin1/ISO-8859-1 language)". Since, migration script has been run I do not think that this criterion has been met.
Average of ratings: -
In reply to Manish Verma

Re: UTF Migration Script Stops

by Sativa Huang -

The huge database & quiz questions are your main issue. Since it is impossible for notepad to handle it, you may try to use Excel, Word etc. You can choose which format to be used while exporting database by applying phpMyAdmin. In fact, your_database.sql is also editable by Word, then save it as your_databse.sql, UTF-8 encoding.

Since the bottleneck is the huge database, the best choice is to break it down into10, 20, 30…parts. By doing so, it will make it editable by notepad or other editors. Regarding the very large number of quiz questions, is it possible to break it down too? It will be better if you could obtain a copy of your database directly, e.g. from your hosting provider.

Regards

Attachment phpMyAdmin.jpg
Average of ratings: -
In reply to Sativa Huang

Re: UTF Migration Script Stops

by Manish Verma -
Hi,

Let me thank you Sativa for providing the step by step points and by explaining in nice way including screenshot. The step by step points were useful in thought process and I used WordPad to edit the .sql file thanks to your suggestion.

Some points however, I have done differently and I am putting them here in question/answer format hoping they may be useful to others.
Q: What should be done for backup before I migrate database to utf8?
A: Just do a database export from phpMyAdmin. If you are on shared hosting then there may be time-out limit for the export process. After you have the database file say in .gz format, get the .sql file by extracting .gz file using 7 zip program (say) and see its size. Compare its size with the size mentioned in phpMyAdmin. You probably need not worry about moodledata/uploaddata folder where your other files are kept as you are not disturbing them by database utf conversion. If your database is too big and export is not working properly then you may contact your host. In moodle forums some ways are mentioned to do it using some scripts as well.
Q: What to do when moodle migration script stops on its own without being killed.

A:

Option (1)
See if you can trim database. I could do it by cleaning the tables of log and cache text. After database trim, I could resume migration script and it completed without any problem.

Option (2)
  • Export your database from phpMyAdmin in .gz format etc. Extract it using a program like 7 zip and get the .sql file. Use Notepad or Wordpad to open it. If it is very large, then these programs may not be able to open it. In this case LTFViewr should work. Convert the current charset, eg. latin1 to utf8 (Edit > Replace > Replace all).
  • Check if there are any utf8_swedish_ci, utf8_unicode_ci or other encodings, convert all of them to utf8_general_ci (if applicable). Note: utf8_unicode is also fine. Utf8_general works faster but is less accurate than utf8_unicode_ci.
  • Save this modified database and convert it to .gz format (7 zip program can be used).
Q: After trimming the database, I could run migration script but I see some strange characters occasionally. How do I remove them.
A: replace.php script may be used. It is there at ..../admin/replace.php .
Q: I used WordPad to modify the .sql file manually and saved it as .gz file. Now what do I do?
A:
  • Create a new database from MySQL management. Give all rights to a user setting privileges to all. Use phpMyAdmin to convert its collation to utf8_general_ci.
  • Import the modified database from phpMyAdmin. If your database is large and you are unable to import it using phpMyAdmin, then you can use bigdump.php script (just google for it).
  • Edit config.php file and change the database setting there. Also add the line $CFG->unicodedb = true; in the same file.
  • Log on to your moodle install. Check notifications and you should find no notification to migrate your database. Check you whole installation randomly.
Q: After editing .sql file manually using WordPad and doing all the steps mentioned finally I see some strange characters occasionally. How do I remove them.
A: replace.php script may be used. It is there at ..../admin/replace.php .
Average of ratings: -
In reply to Manish Verma

Re: UTF Migration Script Stops

by Sativa Huang -
You are welcome Manish!
Thanks for your Q&A. Perhaps you would like to add the summary of these posts into Moodle documentation.

Regards,
Average of ratings: -
In reply to Sativa Huang

Another Alternative Migration Process

by Manish Verma -
Hi,

I wanted to make some changes in my previous post but could not do so due to 30 minutes restriction but have introduced them at http://docs.moodle.org/en/Another_alternative_migration_process . Just see if you would like to edit/add something there and if possible can someone help in fixing the wiki formatting of the concerned page.

Manish.
Average of ratings: -