Error upgrading to 2.0 sites with 1.9, originally installed as 1.6

Error upgrading to 2.0 sites with 1.9, originally installed as 1.6

by Lorenzo Nicora -
Number of replies: 4

Hi all

I got errors upgrading to 2.0.1 some 1.9.10 sites.
These sites have been originally installed as 1.6, years ago, and later upgraded to 1.8 (I skipped 1.7) and then to 1.9

I see that mdl_user table has many NULLable columns without any DEFAULT (city, institution and so on...)
This cause a SQL error when the upgrade process try to set them to NOT NULL without setting any DEFAULT.

I experienced it on PostgreSQL. I suppose it is the same with MySQL, but I have no way to test it.

I suppose it is due to XMLDB introduced in 1.7, as other sites installed as 1.8 do not have this problem.
Probably the upgrade from 1.6 to XMLDB was faulty and subsequent upgrades did not fix it.

I already entered an issue in Tracker
http://tracker.moodle.org/browse/MDL-25948

Attached to the Issue there is a sql script to fix the NOT NULLs and DEFAULTs and bypass the upgrading errors
Actually, I was not able to  test the upgrade to the end, as I had other problems after this; but this is another story...

Lorenzo

Average of ratings: -
In reply to Lorenzo Nicora

Re: Error upgrading to 2.0 sites with 1.9, originally installed as 1.6

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
In the XMLDB editor (in the site admin > Miscellaneous section), there are some checks for database integrity (indexes and bigints). I suggested ages ago that a check is also required for defaults but it was not implemented (I have seen problems).

If you are really paranoid you can dump the schema of your site and compare against the schema from a clean install of Moodle made with exactly the same code base.

I've certainly seen lots of creeping problems in the schema on Moodle sites that have been around for a while.
Average of ratings: -
In reply to Howard Miller

Re: Error upgrading to 2.0 sites with 1.9, originally installed as 1.6

by Lorenzo Nicora -

As you said, those checks look for int field types and indexes, not for defaults and nulls.
I ran those checks and fixing scripts before trying to upgrade (...they tried to convert a couple of timestamps to bigint, failing in PostgreSQL, but never mind...)

It is not a matter of being paranoids. If you have to upgrade many sistes, with different "birth" versions, you cannot spend one day per site, comparing every column with a clear installation (that is how I found the problem)...

IMHO  this missing check is a big issue for upgrading "old" sites.

There are also a lot of users, managing small sites, that are not able to understant SQL diffs.

Lorenzo

Average of ratings: -
In reply to Howard Miller

Re: Error upgrading to 2.0 sites with 1.9, originally installed as 1.6

by Lorenzo Nicora -

Wait... Is there something I'm missing?

I'm reading through the XMLDB's Docs in these days.
I just read this:

http://docs.moodle.org/en/XMLDB_Problems#NOT_NULL_fields_using_a_DEFAULT_.27.27_clause

So, NOT NULL columns with empty string DEFAULT are deprecated since 1.7
(actually the Doc is a bit confusing, mixing TEXT and VARCHAR columns problems...)

Now, upgrading to 2.0 fails for NULL columns without DEFAULT.
Isn't it exactly what the Docs suggests to developers?

mixed

Average of ratings: -
In reply to Howard Miller

Re: Error upgrading to 2.0 sites with 1.9, originally installed as 1.6

by Lorenzo Nicora -

Hi Howard,

I decided to spend one day to implement the NULL and DEFAULT check by myself (for 1.9.x), as I badly need it before upgrading.

I posted in Databases forum:
http://moodle.org/mod/forum/discuss.php?d=166289

I voluntarily limited the fixed cases to avoid problems.
I'm testing it on MySQL and PostgreSQL and it seems to be working.

Do you think it could be added to the official distribution?

Lorenzo

Average of ratings: -