Hello,
My boss has asked me to finally upgrade their old Moodle site from 1.8 up to 2.09 so that they can upgrade their PHP and I can then follow the upgrades forward from there.
I have discovered that in order to avoid headaches later I should make sure the database schema is as it should be in 1.8 before beginning the upgrade process. I have created a clean version of the database and checked it against the version I am going to run the upgrade on and come up with the attached diff file. According to Moodle.org the simplest way to sync the two schema is to use MySQL Workbench.
I have this downloaded on my computer but I have run into a problem. I can't find any tutorials on how to use it to sync the two versions, and I have been working on it for hours. I have created ERR from existing databases, (one for each version) and with either model open I see under Database>Synchronize with any Source. It looks like I should be able to use that to generate an Alter file, or make the changes directly to the database, The first time I tried, I had the two versions backward (Thankfully I caught the error). Since no matter what I do it seems to be trying to sync against itself and tells me there are no changes.
Please help. I am running mysql version 5.0.4, and php version 5.2.17, I am currently at the most recent 1.8 and want to fix my schema before going through 1.9+ and 2.0.9.
You should not upgrade to Moodle 2.0.x. You should upgrade to 2.2.x, or ideally 2.3.x.
What you should do first is to upgrade to the latest 1.9.x release. The Moodle upgrade will update the database schema for you. See http://docs.moodle.org/19/en/Upgrading and http://docs.moodle.org/22/en/Upgrading.
Also, you are more likely to get help with upgrading in the Installation forum: https://moodle.org/mod/forum/view.php?id=28
Thank you, for your response. I know that I need to upgrade into 2.3 but I can't do that until we have our production Moodle in a version that uses the same PHP. I posted here because my question was about the Database Schema. The Upgrading forum recommends correcting bad schema before going through the upgrade process.
Re: Databases: Update/fix mysql schema before upgrade
Sanda, I am working with a customer helping migrate their content from 1.8 to 2.3 moodle instance. You should have a *backup of your site that you can test the upgrade on, in case you have some issue and need to start over. Aside from that, I would upgrade my 1.8 instance in place to the lastest 1.9.x version. This will update your DB for you. Then you could simply perform normal course backup from the 1.9.x instance and then restore to a new fresh empty 2.3 instance.
That is how I have handled this type of upgrade in the past and has worked pretty well.
Re: Update/fix mysql schema --Verify_Database_Schema
Hi James,
I tested the backup months ago and it seemed to work fine, and then when I started trying to do the upgrade for real (luckily on a second more current backup of our database) I had real problems. Even though after several efforts I got the upgrade completed I was receiving errors that related to foreign keys, indexes and other things not being correct.
I have been combing through the Moodle.org forums and documents for weeks to find the likely causes and it comes down to the version of Moodle I have on our production server with students actively working in it doesn't have the correct schema. So I found this Document: http://docs.moodle.org/23/en/Verify_Database_Schema
Apparently in old (1.7 and earlier) versions of Moodle, upgrades didn't always manage to change the database completely. The recommendation is that before going up to 2.0 I fix the schema. I know how to do this manually, and I have read the Moodle Documentation on how to do this using programs (which would seem more logical since we are talking about over a hundred tables needing changes), but I can't seem to wrap my head around the program recommended to make the change to my Database Schema. What I have discovered is that my Mac 10.6 doesn't have the ODBC driver files, and I haven't found where to get them on the internet. I tried installing Schema Sync (the other recommended program) and when it started running it took my CPU Load Average from .2 to 8.5 in les than a minute. Obviously I killed the process before it could crash my server.
Hi,
I had opened a thread some months ago about database schema, if it can help...
Modifiying schema is time consuming (but not sure it would have crashed your server), anf i think you should test the whole thing (modifying schema, then upgrading) when nearly nobody is connected, or on another server, on a copy of your site...
Séverin