Hello,

I urgently need some assistance please, if possible. I have an old hosted Moodle site with hundreds of users/courses. In trying to migrate the site to a new host, we found it wouldn't load due to it currently running on PHP 5.4.45.

In PHP 7.2 it is strictly prohibited to declare classes that have reserved names. Moodle up until 3.4 does have instances of this.
To resolve this on our old CPanel I had to downgrade PHP to under 7.0, and then the site loaded.
However I can't upgrade it to Moodle 3.4 or higher (to resolve the newer PHP issue) without resolving the database collation problem. Under my Environment page it shows that all my data is not in Unicode format. No problem, I'll just change that...well, I go to run the mysql_collation.php and it says "This collation is not available on this server!" In fact, running mysql_collation.php -a shows 0 available collation options.

I tried changing collation manually in PHPMyAdmin but it throws an error trying to change it on all columns with "index column size is too large".

`1709 - Index column size too large. The maximum column size is 767 bytes`

It's also confusing because checking status with mysql_collation.php -l indicates I have a lot of sub items still as general instead of unicode, but in PHP My Admin those same tables show as Unicode...and the Moodle install says they are not all Unicode as well. (see attached 2 images here)

So now I'm stuck...I can't upgrade the installation without resolving the Unicode problem. I can't migrate this to a different host because anything newer than PHP 7 causes the site to not load at all with a fatal error. How should I proceed?

Thank you!

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月22日星期三 03:10

Moodle 2.9 didn't support PHP 7.0. See https://moodledev.io/general/releases/2.9 > Server requirements.

The march route towards Moodle 4 has to take a couple of hops. There are different routes, which you can find out consulting http://www.syndrega.ch/blog/#php-and-dbms-compatibility-of-major-moodle-releases. Notice the min. and max. versions of system software.

Once you have the route consult the upgrading documentation in the form https://docs.moodle.org/31/en/Upgrading (keep on replacing /31/ with the "hops" you plan to take. Read them carefully, there were special instructions related to the database conversions, uninstalling Moodle 2.2 Assignment activity and many more.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月22日星期三 03:14

Thank you for providing this info, I have reviewed it in depth. The major blocker to proceeding with even a marginal upgrade to 3.1 for example from 2.9, I can't do because of the unicode error on the environment page. It doesn't allow me to upgrade and even though I've tried multiple ways to adjust the collation, it doesn't seem to work.
The php collation script fails with "this collation is not available on this server". Doing it in PHPMyAdmin it throws the error about Index Column Size Too Large.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月22日星期三 03:20

You mean the environment page in the 2.9 itself has lines marked red? Could you post a screenshot? Then select Moodle 3.1 in the drop-down list at the top and post a screenshot of that page too?

I am assuming that the 2.9 version you are talking about functions flawlessly.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月22日星期三 04:01

Sure thing, here it is on environment 3.0 and also on the normal operating version of 2.9.

2.9 Does currently run without issue, but obviously it's sorely out of date and the client needs to have it migrated to a new host/provider anyway.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月22日星期三 04:11

2.9 and 3.1 (LTS), I said.

Anyway, you see that it is talking about UTF-8, which was implicitly utfmb3, if I remember right.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月23日星期四 01:40

3.1 has the exact same notice/error.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月25日星期六 21:12

Earlier you said, "even a marginal upgrade to 3.1 for example from 2.9, I can't do because of the unicode error on the environment page". Can you upgrade the 2.9.4 to 2.9.final (2.9.9)? This is not just test, generally it is better to start the march first upgrading to the latest/final point release of the same version.

Dumb question: Does the 2.9.4 run in its present environment?

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Rick Jerz - 2024年05月22日星期三 03:17

Others here on Moodle.org will probably provide more detailed instructions and help.

1) Make sure you have a complete backup of your Moodle before any upgrades.
2) 2.9 to 4.4 is a very long jump, and much has changed. You will need to do this in steps (I can't remember the exact steps, sorry.) I have never done this but would not look forward to it. (I see some steps: 2.7.20 -> 3.0.10 -> 3.6. But there are a few more from 3.6 to 4.4.)
3) Yes, as you upgrade, you will make sure to honor the PHP and database minimum and maximum versions. Visvanath has this nice web page as a help.

4) I would begin by installing your Moodle 2.9 on the new host to verify that it can run. However, yep, you do have that PHP version issue.

5) Your "collation" issue needs to be resolved. I am not sure when this happened. The recommended collation is "utf8mb4_unicode_ci". I think that the sooner you resolve this, the better. But I don't know if utf8mb4_unicode_ci will give Moodle 2.9 any problems.

6) You might also need to worry about any extra plugins or themes your M2.9 has.

Again, others here on Moodle.org should see your question and provide some better help.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月22日星期三 03:22

Thanks Rick. Yes, the collation issue seems to be the biggest roadblock at this time. I don't mind doing baby step incremental upgrades from 2.9 to 3.0 for example but the Moodle install won't allow it. I did make sure my CFG file has 'dbcollation' => 'utf8mb4_unicode_ci', in it as well.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月22日星期三 04:07

utfmb4_unicode_ci came recently. It was utf_unicode_ci or something similar, which was the default utfmb3. Read the Upgrading document for each hop carefully.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Rick Jerz - 2024年05月22日星期三 04:41

Is your Moodle 2.9 working?
Do you have a backup of everything (database, moodledata, and moodle)?

You might try installing a duplicate of your current Moodle on your current server, then begin migrating it. Of course, this depends on PHP and database versions available.

Depending on your Moodle's size, you might want to install it on your computer using one of the "amp" installers. I use MAMP on my Mac, and can still run my Moodle(s) 1.9, 2.9, 3.11, and 4.4 in this environment. My largest Moodle is 3.11, about 14GB.

平均分：Useful (1)

Re: Trying To Migrate Old 2.9.4 Install

Leon Stringer - 2024年05月22日星期三 04:50

Moodle upgrades: As others have said, to upgrade you'll need to work through the largest version steps you can (from Moodle 2.9 to 3.3, etc.). But you will need to perform these steps with the prerequisite versions of PHP. Note: you can normally get away with the latest MySQL/MariaDB version. The way I do this is by setting up VMs with the required software versions and perform the interim upgrade steps on those before migrating the data to the target server.

Database collation: There are two problems here, firstly there was a backward-incompatible change in MySQL 8.0.28 where character set utf8 is now reported as utf8mb3. There are details in MDL-74616 but in short the solution is to migrate to character set utf8mb4 and collation utf8mb4_unicode_ci. Error 1709 - Index column size too large. The maximum column size is 767 bytes suggests either your MySQL/MariaDB version isn't configured to use the large index size which is unlikely if it's a version with utf8mb3. So maybe you need to run php admin/cli/mysql_compressed_rows.php --fix before running php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci. Make sure you have a database backup before running these scripts.

平均分：Useful (2)

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月23日星期四 01:39

This helps a lot understanding the issue...I think the problem is since this is running on a shared host with a CPanel, I don't have super privileges on the database and can't perform these operations, at least via SQL statements. Thank you very much.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Rick Jerz - 2024年05月23日星期四 05:34

Sometimes phpMyAdmin will let you do this, and I see you have phpMyAdmin.

Pick the database, then "Operations." I believe you might see the ability to change the collation. However, your hosted server company might have removed the Operations and SQL tabs.

In this video, I show how I change both the collation and character set using phpMyAdmin. (Yes, Ken, I improved this video.) Use the "chapters" to go to "change collation.

Installing Moodle on a VPS using Only cPanel

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月23日星期四 06:41

Chapters ... nice ... since some hosting plans with cPanel do offer a 'Terminal' icon, might consider adding a chapter on Terminal and a command line way of acquiring code and then running the installation script in code/admin/cli/. BTW, one will still have to use some cPanel tools - DB name - DB user.

And, many hosting providers are highly recommending or just setting it up for customers the use of CloudFlare (CF) ... which is good, cept sometimes that messes up updates and upgrades. To avoid that ... cli update and upgrade is the way to go.

Still - good addition to video!

'Spirit of Sharing', Ken

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月23日星期四 22:24

This is a great resource. However, I get the "Index column size too large" trying to change collation on all tables and columns.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月23日星期四 23:34

According to:

https://bugs.mysql.com/bug.php?id=102597

There is a fix ... however, I see in above page this comment:

Upgrading to any 8.0.x version will make this table in accessible

but you can't do that and use the SAME DB for the version of Moodle you have.

It's looking like your best shot at this might be to make course backups and attempt to restore them to a fresh install of a Moodle 4.x

'SoS', Ken

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月25日星期六 00:45

What if one drops the offending index?

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Leon Stringer - 2024年05月23日星期四 23:49

In phpMyAdmin what do you get for SHOW GLOBAL VARIABLES WHERE variable_name IN ('innodb_file_format', 'innodb_large_prefix', 'innodb_file_per_table')? To run this paste it into in SQL tab in phpMyAdmin and click Go.

If you run php admin/cli/mysql_compressed_rows.php --showsql what do you get?

If the SHOW GLOBAL VARIABLES ... shows Baracuda or ON or is blank for every row, try running just the ALTER TABLE statements output by php admin/cli/mysql_compressed_rows.php --showsql in the SQL tab of phpMyAdmin (but not the SET ... statements).

If that works try php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci again.

平均分：Useful (1)

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月22日星期三 05:12

Think the key to this is in what Leon said ..

"The way I do this is by setting up VMs with the required software versions"

If attempting to do this on your own server ... it appears to be difficult for you to choose older versions of anything. You didn't mention where and how hosted as well as Operating System (relates to package manager you have available if older versions of PHP/MySQL are still available via package manager).

'SoS', Ken

平均分：Useful (1)

Re: Trying To Migrate Old 2.9.4 Install

Visvanath Ratnaweera - 2024年05月22日星期三 06:08

Alternatively, start with a Linux distribution of those days, say Debian 8 (2015-2020), and march Moodle forward and dist-upgrade Debian as necessary. Debian 11 will bring you to Moodle 4.1 LTS, which gets security updates till December 2025.

Oh yes, as somebody as pointed has out, you might have to give up some of the additional plug-ins, if there are any.

Either way, not something you can do in a hosted server. Depending on the size of the Moodle, a VM in your local computer could be sufficient.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月22日星期三 08:47

+5 to Mr. V's Debian suggestion ... and dist-upgrades.

One or more catch 22's in doing that on local lan.

Don't re-call when moodle allowed relative addressing but the earlier versions had moodle restricted to the URL in config - FQDN. If you used IP or another FQDN had to do search and replace on DB. You don't need to change that line. Instead use the /etc/host file trick and add a mapping private IP to FQDN shortname on local lan.

You then can use another workstation (linux or mac) to access the server being migrated by FQDN that's in config.php .... mac host file trick same thing - PC's can do that to but not as easily.

Also certs https ... ok to use self-signed certs inside a private lan.

And, if you need a panel of sorts ... can't use cPanel - no freebie there ... but there is Webmin - perl based web server so even if your apache is puttzed you can acces Webmin's web on port 10000 default. Has tools for package manager, tweaking PHP variables, and a gui tool for DB server and DB's - so don't have to do 100% command line - even though best to use the code/admin/cli/ php scripts where you can.

Know this is a lot but .... 2 more things ... if at all possible use git versioning for Moodle code ... that will make marching a lot easier - code stays in place ... no moving parts. And install of moosh - command line utility knife for Moodle. You'd have to start moosh using lower version for compat and upgrade it along the march.

https://moodle.org/plugins/pluginversions.php?id=522

One of moosh commands is to check plugin versions/availability from moodle.org plugins.

https://moosh-online.com/commands/

Themes are plugins ... I'd advise setting the theme forced to 'boost' via config.php file for the duration of the march ... worry about what it looks like LAST! Function FIRST!

Have done something similar only it was a Ubuntu box and the goal was a version not too far from what it was already running to get full user course backups off of it - only 15 courses.

And that's something else to consider ... once you get old box up, would full user backups of courses work on a newly installed external/public internet host?

One could use moosh in looping bash shell scripts to restore certain .mbz backups to whatever category in the new moodle.

So another 10 cents worth!

'SoS', Ken

平均分：Useful (1)

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月23日星期四 01:40

Shared host with a CPanel, on InMotion.

Apache Version 2.4.59
MySQL Version 10.6.18-MariaDB-log
Architecture x86_64
Operating System linux

Sorry, I should have posted this on my original post.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月22日星期三 05:57

2 'sense' more ... I wouldn't put this project on a public facing server - internet. Within less than one minute these days public facing servers have their first poke and probe. Best to do the VM's on a private network - no outside in allowed, but you do have the ability to reach package managers for software.

Rick's suggestion - something like it - might be the safest/best way.

'SoS', Ken

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ian Stuart - 2024年05月23日星期四 01:43

Thanks everyone for the input. At this time I may just cut my losses, and try to export all courses/users and redo it from scratch.

平均分：Useful (1)

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月23日星期四 03:09

If you are going that route, check the additional plugins in the 2.9 site and use moodle.org's Plugin site to see if those plugins are still supported.

If plugin is not supported, 2 options ... deleted the plugin at system level - that should remove those plugins at the course level. OR, step through a course backup deselecting those resources in the course - that backup does include users.

Download all backups.

Restore backups to new server.

Fingers crossed!

'SoS', Ken

平均分：Useful (1)

Re: Trying To Migrate Old 2.9.4 Install

Rick Jerz - 2024年05月24日星期五 00:41

I agree with your idea (since I do this myself).

You might want to get a current version of Moodle running while keeping your 2.9 running. Then using side-by-side browser windows, try recreating the course in your newest Moodle using 2.9 as a guide. Enough has changed in Moodle 4.4 to make recreation a worthwhile activity. The ability to duplicate topics, resources, and activities, along with the SharingCart plugin, might make this process go fast.

I agree with Ken to make backups. I have my old Moodle 2.9 running in MAMP, for reference.

平均分： -

Re: Trying To Migrate Old 2.9.4 Install

Ken Task - 2024年05月25日星期六 06:11

You've got command line access to the server with 2.9.x ... that's a plus.

How much free space do you have on that server?

In code/admin/cli/ there is a backup.php script which will backup courses using the the backup defaults set in GUI admin. Am almost willing to bet those were left to include users and all their work in a course.

I mention above because backup files don't have anything related to DB character set/collations - they are xml files that point to related directories contained in the backup .mbz when uncompressed.

If you have enough space, one could run a bash shell looping script that loops through course ID numbers - execluding course ID 1 because that is the front page and one cannot restore that.

And have those course backups go to a designated directory outside of moodledata/filedir/ - by a humanly recognizable name as well.

If you can run that, then one could xfer all those backups to a file system repo setup on a new 4.4 instance. Better yet, one could use a mount point on the 2.9.x server that is really the file system repo setup on the 4.4 server.

Yes, might be 1000's of courses, but ... on the 4.4 server one can use moosh to restore backups to a category. One could use looping bash shell scripts to do that as well.

Once you get the kinks worked out on the scripts by using just one course, you could then do multiple/looping.

Catch 22 - the addon-plugins used in the 2.9 that are used in those course backups. If you could get a list of plugins on the 2.9 and could install those on the 4.4 before restoring those course backups.

This proposal (I think) might be your 'path of least resistance' ... even if it will take a full day to complete (maybe more than a day - depending).

'SoS', Ken

平均分： -