Moodle in English: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Wednesday, 28 September 2011, 3:34 AM

I need to upgrade a busy 1.9 site to 2.1. This site started with 1.6 and has been updated/upgraded through all major versions.

I created a test instance in the same server going through the following steps which I've put into a script:
a) got the new source, 2.1.1+, build 20110925, to http://my.site/21/
b) made a copy of $moodledata
c) dumped the database, searched an replaced http://my.site by http://my.site/21 and fed in to a new database
d) copied config.php of 1.9 into the 2.1 directory and modified $CFG->dbname, wwwroot and dataroot to match the new setup.

Now when I kick the new site it takes me to
- Upgrading Moodle database from version 1.9.12 (Build: 20110510) (2010061900.22) to 2.1.1+ (Build: 20110925) (2011070101.12)
- then to Server checks, all the status ok
- Plugins check has a whole bunch of "Missing from disk": cwhiteboard, dialogue, exercise, hotpot, ilpconcern, ilptarget, journal, lams, mindmap, admin, admin_tree ilp ilp_student_info, loancalc and presence. Those are for the time being ok, but I would like to know why mod/resource, mod/wiki and filter/multimovie are there.

Anyway, the main problem is the next step, which throws
Error writing to database
Debug info: Column 'itemid' cannot be null
UPDATE mdl_role_assignments
SET itemid = (SELECT MIN(mdl_enrol.id)
FROM mdl_enrol
JOIN mdl_context ON (mdl_context.contextlevel = 50 AND mdl_context.instanceid = mdl_enrol.courseid)
WHERE mdl_role_assignments.component = CONCAT('enrol_', mdl_enrol.enrol) AND mdl_context.id = mdl_role_assignments.contextid)
WHERE component <> 'enrol_manual' AND component LIKE 'enrol_%'
[array (
)]
Stack trace:
* line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 708 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 4434 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
* line 1423 of /lib/upgradelib.php: call to xmldb_main_upgrade()
* line 277 of /admin/index.php: call to upgrade_core()

This came up in Dec 2010 http://moodle.org/mod/forum/discuss.php?d=163300
and resolved 20. Dec 2010 http://tracker.moodle.org/browse/MDL-25681.

Why is this difference in the database now? Do I need to http://docs.moodle.org/en/Verify_Database_Schema and manually update/alter tables?

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Brian Lockwood - Wednesday, 28 September 2011, 5:01 PM

I came across similar problems doing an upgrade which is described here http://moodle.org/mod/forum/discuss.php?d=104406#p461763

Essentially, there are a nhandful of issues that occur in the trail 1.6 > 2.0+ which you will need to deal with or have dealt with. In the above link, I describe a solution realating to the mdl_gropus table. I think you are in a similar situation and you need a good scheme so that you do not store up problems for the future.

In the event that you need to fix thsi yourself because the database scheme fix dos not work for some reason then take a backup of your original database and put it somewhere safe. Then you can try solutions and if they do not work, revert.

As a first stab, it looks like item_id is not autoincrement when maybe it should be. I have a working mdl_2 and so if you want definitive table settings just ask.

Average of ratings: Useful (1)

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Wednesday, 28 September 2011, 5:19 PM

Hi Brian

So this sounds like "This happens because of small errors or oversights in the upgrade scripts. Most of these differences are not harmful, but some may cause strange or unexpected errors. For example, if a default value has been added to a field and this was not reflected in an upgrade script code that assumes the presence of the default may fail to work as expected." as mentioned in http://docs.moodle.org/en/Verify_Database_Schema.

Now I'm faced with the choice, either repair the anomalies I encounter manually or do a systematic clean up as described in the doc mentioned above. You say "you need a good scheme so that you do not store up problems for the future". Does is mean the latter choice?

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Wednesday, 28 September 2011, 11:13 PM

This is the deal sofar.

The 1.9 dump has the following table definition:

CREATE TABLE `mdl_role_assignments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `roleid` int(10) unsigned NOT NULL DEFAULT '0',
  `contextid` int(10) unsigned NOT NULL DEFAULT '0',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `hidden` int(1) unsigned NOT NULL DEFAULT '0',
  `timestart` int(10) unsigned NOT NULL DEFAULT '0',
  `timeend` int(10) unsigned NOT NULL DEFAULT '0',
  `timemodified` int(10) unsigned NOT NULL DEFAULT '0',
  `modifierid` int(10) unsigned NOT NULL DEFAULT '0',
  `enrol` varchar(20) NOT NULL DEFAULT '',
  `sortorder` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `contextid-roleid-userid` (`contextid`,`roleid`,`userid`),
  KEY `roleid` (`roleid`),
  KEY `contextid` (`contextid`),
  KEY `userid` (`userid`),
  KEY `sortorder` (`sortorder`)
) ENGINE=MyISAM AUTO_INCREMENT=17100 DEFAULT CHARSET=utf8;

whereas a dump from a fresh 2.1 has this:

CREATE TABLE `mdl_role_assignments` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `roleid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `contextid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `userid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  `modifierid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `component` varchar(100) NOT NULL DEFAULT '',
  `itemid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `sortorder` bigint(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mdl_roleassi_sor_ix` (`sortorder`),
  KEY `mdl_roleassi_rol_ix` (`roleid`),
  KEY `mdl_roleassi_con_ix` (`contextid`),
  KEY `mdl_roleassi_use_ix` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='assigning roles in different context';

And this is the same table after the upgrade script crashed:

CREATE TABLE `mdl_role_assignments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `roleid` int(10) unsigned NOT NULL DEFAULT '0',
  `contextid` int(10) unsigned NOT NULL DEFAULT '0',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `timemodified` int(10) unsigned NOT NULL DEFAULT '0',
  `modifierid` int(10) unsigned NOT NULL DEFAULT '0',
  `component` varchar(100) NOT NULL DEFAULT '',
  `itemid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `sortorder` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `roleid` (`roleid`),
  KEY `contextid` (`contextid`),
  KEY `userid` (`userid`),
  KEY `sortorder` (`sortorder`)
) ENGINE=MyISAM AUTO_INCREMENT=17100 DEFAULT CHARSET=utf8;

Which is much closer to the freshly created table. Yet it is not clear to me which modification I should do this table definition and exactly when.

BTW, the original mdl_role_assignments has only a single (monster) record, with all the LDAP-enrol data.

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Brian Lockwood - Thursday, 29 September 2011, 2:46 AM

"Which is much closer to the freshly created table."

I am not sure what you mean by this.
Are you showing a dump before and after the upgrade attempt because what we see here is that no upgrade to this table has taken place. At least to the structure. Hopefully, all your data is safe.

How many courses have you in this Moodle?

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Thursday, 29 September 2011, 3:45 AM

> "Which is much closer to the freshly created table."
> I am not sure what you mean by this.

The structure of the original table in 1.9 was changed by the upgrade script. The result is closer to the structure of the same table in the fresh 2.1.

> Are you showing a dump before and after the upgrade attempt because what we see here is that no upgrade to this table has taken place. At least to the structure.

>No they are different. If I call the original structure 19old.sql and the structure before the crash 19new.sql, a diff shows:

$ diff 19old.sql 19new.sql 
6,8d5
<   `hidden` int(1) unsigned NOT NULL DEFAULT '0',
<   `timestart` int(10) unsigned NOT NULL DEFAULT '0',
<   `timeend` int(10) unsigned NOT NULL DEFAULT '0',
11c8,9
<   `enrol` varchar(20) NOT NULL DEFAULT '',
---
>   `component` varchar(100) NOT NULL DEFAULT '',
>   `itemid` bigint(10) unsigned NOT NULL DEFAULT '0',
14d11
<   UNIQUE KEY `contextid-roleid-userid` (`contextid`,`roleid`,`userid`),

Four fields have vanished, two new added, one is our `itemid`.

> Hopefully, all your data is safe.

Sure, all this happen in a copy!

I'm still looking for the correct modification though.

> How many courses have you in this Moodle?

Some 285. Batch import is out of the question, most of these courses are auto-generated from an external Open-LDAP database, and should remain that way.

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Friday, 21 October 2011, 6:41 PM

Couple of things have changed in the mean time, but the original problem still remains.

- Upgraded the original 1.9.12 (Build: 20110510) site to 1.9.14 (Build: 20111010) which went without a hitch.

- Now I'm doing a test migration to a slightly different URL, to http://my.original.site/21migrated following the same steps described in my OP 27. Sept. The new version is 2.1.2+ (Build: 20111012).

After the courses have been converted, the db reorganization sends
"Error writing to database
More information about this error
Debug info: Column 'itemid' cannot be null
UPDATE mdl_role_assignments
SET itemid = (SELECT MIN(mdl_enrol.id)
FROM mdl_enrol
JOIN mdl_context ON (mdl_context.contextlevel = 50 AND mdl_context.instanceid = mdl_enrol.courseid)
WHERE mdl_role_assignments.component = CONCAT('enrol_', mdl_enrol.enrol) AND mdl_context.id = mdl_role_assignments.contextid)
WHERE component <> 'enrol_manual' AND component LIKE 'enrol_%'
[array (
)]
Stack trace:

* line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 722 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 4434 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->execute()
* line 1423 of /lib/upgradelib.php: call to xmldb_main_upgrade()
* line 277 of /admin/index.php: call to upgrade_core()"

This is the original (1.9.14) structure:
CREATE TABLE `mdl_role_assignments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`roleid` int(10) unsigned NOT NULL DEFAULT '0',
`contextid` int(10) unsigned NOT NULL DEFAULT '0',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`hidden` int(1) unsigned NOT NULL DEFAULT '0',
`timestart` int(10) unsigned NOT NULL DEFAULT '0',
`timeend` int(10) unsigned NOT NULL DEFAULT '0',
`timemodified` int(10) unsigned NOT NULL DEFAULT '0',
`modifierid` int(10) unsigned NOT NULL DEFAULT '0',
`enrol` varchar(20) NOT NULL DEFAULT '',
`sortorder` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `contextid-roleid-userid` (`contextid`,`roleid`,`userid`),
KEY `roleid` (`roleid`),
KEY `contextid` (`contextid`),
KEY `userid` (`userid`),
KEY `sortorder` (`sortorder`)
) ENGINE=MyISAM AUTO_INCREMENT=17342 DEFAULT CHARSET=utf8;

and this is what a fresh 2.1 installation has:
CREATE TABLE `mdl_role_assignments` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`roleid` bigint(10) unsigned NOT NULL DEFAULT '0',
`contextid` bigint(10) unsigned NOT NULL DEFAULT '0',
`userid` bigint(10) unsigned NOT NULL DEFAULT '0',
`timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
`modifierid` bigint(10) unsigned NOT NULL DEFAULT '0',
`component` varchar(100) NOT NULL DEFAULT '',
`itemid` bigint(10) unsigned NOT NULL DEFAULT '0',
`sortorder` bigint(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mdl_roleassi_sor_ix` (`sortorder`),
KEY `mdl_roleassi_rol_ix` (`roleid`),
KEY `mdl_roleassi_con_ix` (`contextid`),
KEY `mdl_roleassi_use_ix` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='assigning roles in different context';

Can somebody see where the collision is?

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Stuart Morrison - Friday, 21 October 2011, 8:55 PM

^Hi

^{I had to do something similar and found the thirty day trial from SQLyog http://www.webyog.com/ very helpful (compare schemas and produce an alter script very easily). I hope to be very shortly buying a full licence.}

^{I also went to 2.0.4+ before 2.1.1}

^{I did have a glitch once upgraded to 2.0.4+ although have managed to workaround (not pretty and not recommended so not posted!) and am now (mainly) running on 2.1.1+}

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Saturday, 22 October 2011, 5:57 AM

This is the offending query:

UPDATE mdl_role_assignments
SET itemid = (SELECT MIN(mdl_enrol.id)
FROM mdl_enrol
JOIN mdl_context ON (mdl_context.contextlevel = 50 AND mdl_context.instanceid = mdl_enrol.courseid)
WHERE mdl_role_assignments.component = CONCAT('enrol_', mdl_enrol.enrol) AND mdl_context.id = mdl_role_assignments.contextid)
WHERE component <> 'enrol_manual' AND component LIKE 'enrol_%'

Well, just before the breakdown it says, "This upgrade introduces two new navigation blocks that will replace these blocks: Administration, Courses, Activities and Participants. If you had set any special permissions on those blocks you should check to make sure everything is behaving as you want it." The question is, what are "special permissions"?

Average of ratings: -

Re: Upgrading 1.9 to 2.1 (now 2.0.6): mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Friday, 30 December 2011, 5:55 AM

I've tried the same procedure, this time for 2.0.6. Same error.

This is what happening. The original (1.9.14) table mdl_role_assignments has the following structure:

mysql> describe mdl_role_assignments;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| roleid       | int(10) unsigned | NO   | MUL | 0       |                |
| contextid    | int(10) unsigned | NO   | MUL | 0       |                |
| userid       | int(10) unsigned | NO   | MUL | 0       |                |
| hidden       | int(1) unsigned  | NO   |     | 0       |                |
| timestart    | int(10) unsigned | NO   |     | 0       |                |
| timeend      | int(10) unsigned | NO   |     | 0       |                |
| timemodified | int(10) unsigned | NO   |     | 0       |                |
| modifierid   | int(10) unsigned | NO   |     | 0       |                |
| enrol        | varchar(20)      | NO   |     |         |                |
| sortorder    | int(10) unsigned | NO   | MUL | 0       |                |
+--------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

The upgrade script in 2.0 and 2.1 changes it to

mysql> describe mdl_role_assignments;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| roleid       | int(10) unsigned    | NO   | MUL | 0       |                |
| contextid    | int(10) unsigned    | NO   | MUL | 0       |                |
| userid       | int(10) unsigned    | NO   | MUL | 0       |                |
| timemodified | int(10) unsigned    | NO   |     | 0       |                |
| modifierid   | int(10) unsigned    | NO   |     | 0       |                |
| component    | varchar(100)        | NO   |     |         |                |
| itemid       | bigint(10) unsigned | NO   |     | 0       |                |
| sortorder    | int(10) unsigned    | NO   | MUL | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+

The new column 'component' has data values like enrol_metacourse (~3900) or enrol_ldap (~880). But also there are ~1300 records where 'component' is empty. These records cause the first test in the clause

WHERE mdl_role_assignments.component = CONCAT('enrol_', mdl_enrol.enrol) AND \
mdl_context.id = mdl_role_assignments.contextid)

to throw a syntax error.

So the problem narrows down to the upgrade script, i.e. why it created records where 'component' is empty?

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Iroshini Ratnapala - Wednesday, 18 June 2014, 8:10 PM

Dear Mr. Vishvanath

I also got the same problem when trying to update from 1.9.19 to 2.2.11. Couldn't you find a solution to fix this error?

Regards

Iroshini

Average of ratings: -

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

by Visvanath Ratnaweera - Wednesday, 18 June 2014, 8:49 PM

Hallo Iroshini

I am not convinced that you have the same problem, because I am talking of a class of problems. They creep in during many generations of upgrades through variing upgrade paths. The are either small differences in database schemata or old data which break new constraints.

I don't think I have solved this particular problem. The general procedure for such cases is called database schema verification. See http://docs.moodle.org/en/Verify_Database_Schema.

Average of ratings: -

Installing and upgrading help

Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1 (now 2.0.6): mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Re: Upgrading 1.9 to 2.1: mdl_role_assignments, column 'itemid' cannot be null

Empowering educators to improve our world

Moodle

Support

Get Involved

Contributions

Downloads

Tracker

Development

Empowering educators to improve our world