Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Perry Way -
Number of replies: 8

Hello,

I am upgrading a backup of our site as an exercise before attempting the real thing with our live system. I have jumped through all the various hoops along the way, upgrading the database, Barracuda, dealing with all the various issues and then this happens:

While running the upgrade process using the CLI interface, I got an error alerting me that mdl_tool_cohortrules doesn't exist.

I thought initially I did a poor job backing up and restoring the database to a new database, maybe I forgot a table, but no, the database I am upgrading from the original one does not have this table.

If the table did not exist before then why is it being referenced in an upgrade installation? Sounds to me like this is the cart before the horse.

Anyone with some helpful information, please chime in! It goes without saying that my site is now unable to be updated, and fairly certain I can't roll it back since I've done so much to get to this point. How do I progress from this?

I've included the dialogue that existed in the CLI interface

C:\Websites\moodle.3.8>php c:/websites/moodle.3.8/website/admin/cli/maintenance.php --disable
== Maintenance mode (https://<redacted>) ==
Maintenance mode has been disabled and the site is running normally again

C:\Websites\moodle.3.8>php c:/websites/moodle.3.8/website/admin/cli/upgrade.php
== Upgrading Moodle database from version 3.6.1+ (Build: 20181220) (2019042300.01) to 3.8.1+ (Build: 20200124) (2019111801.03) ==

Your Moodle files have been changed, and you are about to automatically
upgrade your server to this version:

3.8.1+ (BUILD: 20200124) (2019111801.03)

Once you do this you can not go back again. Please note that this process
can take a long time.

Are you sure you want to upgrade this server to this version?

type y (means yes) or n (means no)
: y
-->Language import utility: en_us
++ Language pack 'en_us' is up-to-date ++
-->System
!!! Error writing to database !!!
!! Table 'moodle.new.mdl_tool_cohortroles' doesn't exist
DELETE FROM mdl_tool_cohortroles WHERE roleid NOT IN (SELECT id FROM mdl_role)
[array (
)]
Error code: dmlwriteexception !!
!! Stack trace: * line 489 of \lib\dml\moodle_database.php: dml_write_exception thrown
* line 1660 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database-&gt;query_end()
* line 3792 of \lib\db\upgrade.php: call to mysqli_native_moodle_database-&gt;delete_records_select()
* line 1817 of \lib\upgradelib.php: call to xmldb_main_upgrade()
* line 187 of \admin\cli\upgrade.php: call to upgrade_core()
 !!
Average of ratings: -
In reply to Perry Way

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Ken Task -
Picture of Particularly helpful Moodlers

Non-Windows person responding here ... sooo ...

I take it in the 3.6.x you ran environment check and the only thing found was fixing DB?  Your production is still non-modified code and DB ... run environment check there and let's see what it says.

When you created the cloned DB to work with, you did a mysqldump, right?
If so then the SQL file created and then imported into new DB would have same tables ... BTW, a 3.6.highest and a 3.8.highest DB's DO have the same table ... ie, does exist.

So problem might be with config.php and DB matching.
In config.php what is the $CFG->dbtype?  Match your DB server?

Check config.php for DB variable: 'dbcollation'

What is seen there must match DB in MySQL.

In moodlecode/admin/cli/ run

php mysql_compressed_rows.php -i
will show if DB engine is InnoDB and InnoDB file format is Baracuda or not.

Also run:
php mysql_collation.php -l

That will list all tables in the DB and show the collation of not only the tables, but the columns in the tables.

Summary at end of script run above should show:

Table collations summary youriite:
valuefromDBcollation: then a number ... which should be all tables in the DB.

IF, for example, in config.php one has
  'dbcollation' => 'utf8mb4_general_ci',

Then the script run should show
utf8mb4_general_ci: then a number

Same script can 'fix' all collations ... all tables and all columns in tables using the --collation= option but the DB user/password used in config.php must be superuser.

'SoS', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Perry Way -
The table exists in earlier versions of Moodle. Does not exist in Moodle 3.8.1+.

I took care of all barracuda conversions. That was a breeze. I thought I was clear I jumped through all those hurdles and my issue was strictly related to the installer after all those checks.

It appears that there is a bug in the 3.8.1+ /lib/db/upgrade.php file, line 3790 references a version before 2019111800.04, and 3.6.1 we are running presently is before that date and that table does not exist in that database.

So I fixed the problem for us by commenting out line 3792 since that table does not exist. Once I did that an re-ran the installer, it worked like a charm. I've included a screenshot so anyone else can benefit from this considering upgrading from 3.6.1+. You will need to comment this line out, otherwise it will fail!

code view
Average of ratings: Useful (1)
In reply to Perry Way

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Ken Task -
Picture of Particularly helpful Moodlers

Interesting ..

"The table exists in earlier versions of Moodle. Does not exist in Moodle 3.8.1+."

In  git acquired code:

$release  = '3.8.1+ (Build: 20200130)

mysql> use moodle38;

mysql> show tables like '%cohort%';
+-------------------------------+
| Tables_in_moodle38 (%cohort%) |
+-------------------------------+
| mdl_cohort                    |
| mdl_cohort_members            |
| mdl_competency_templatecohort |
| mdl_tool_cohortroles          |
+-------------------------------+
4 rows in set (0.00 sec)

Table exist!!!!

php mysql_collation.php -l |grep cohort

mdl_cohort                              utf8mb4_unicode_ci
mdl_cohort_members                      utf8mb4_unicode_ci
mdl_competency_templatecohort           utf8mb4_unicode_ci
mdl_tool_cohortroles                    utf8mb4_unicode_ci

But ... whatever works, I guess!

'SoS', Ken



Average of ratings: -
In reply to Ken Task

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Perry Way -
That's odd. Why do I not have the table then? Could a previous version of Moodle installer not create a necessary table? I find that hard to believe, but I haven't dropped any tables.

I searched the XMLDB system and found the table defined there and so I got the SQL and executed it and now I have that table. But this is quite odd that XMLDB system did not prompt to create the table. How many other tables are missing?

Questions likely without answers. Have a nice day.
Average of ratings: -
In reply to Perry Way

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Ken Task -
Picture of Particularly helpful Moodlers

Been one other user in past months that had something like that happen to them.   Used the XMLDB system to rebuild the tables they had missing.

Other tables missing?  Dunno!

In a pretty much stock 3.8.highest just updated via git today:

mysql> show tables;

shows 421 rows in set (0.01 sec)

'SoS', Ken

Average of ratings: -
In reply to Perry Way

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Paul Holden -
Picture of Core developers Picture of Moodle HQ Picture of Moodle Workplace team Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Perry,

For reference, the table certainly exists in a stock 3.8 install: https://github.com/moodle/moodle/blob/MOODLE_38_STABLE/admin/tool/cohortroles/db/install.xml

Why do I not have the table then?

At a guess, it's possible the tool_cohortrole plugin was uninstalled at some point in the past. I was able to re-create the same issue when I uninstalled it and attempted to upgrade - see MDL-67891

Average of ratings: Useful (3)
In reply to Paul Holden

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Perry Way -
How interesting, Thanks for the feedback Paul.

I do not recall ever having uninstalled that plugin, to be honest. If it was deleted it was deleted a long time ago like 4+ years ago. I have a history of databases used in the past and I went through and did not find that table in any of the databases we have ever used. So if it was installed at one time it would be 4+ years ago.

It would seem to me if someone could remove the plugin then the plugin must be optional.

So of course I'm wondering why is the table referenced in an upgrade script?

Thanks for your feedback, this does shed some light on the issue.
Average of ratings: -
In reply to Perry Way

Re: Error upgrading from Moodle 3.6.1 to 3.8.1 - mdl_tool_cohortroles does not exist

by Paul Holden -
Picture of Core developers Picture of Moodle HQ Picture of Moodle Workplace team Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

The plugin is indeed optional, so it's not unreasonable for someone to uninstall it.

Regarding the upgrade script - that's what MDL-67891 should hopefully address 👍

Average of ratings: -