Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Gary Joyce -
Number of replies: 12

Hi there,

I would greatly appreciate any advice, as I've been trialling Moodle for a while and it looks ideal for our organisation. I've followed one of the guides on upgrading whereby the moodle code for 3.7 has been backed up, removed and replaced by the the new code for 3.8.1. The moodledata folder is kept separate as recommended.

Then I downloaded and copied in the latest versions of the handful of plugins that we've been using. This involved several rounds of "Upgrading to new version" where moodle was upgrading a few of these at a time, and the displays indicated that these were successful, and would then show whichever other components were for upgrading, and all of these seemed to upgrade OK. Then it got to a point where it showed that no plugins require attention. But the "admin" user cant get any further, so all I can do is click "Upgrade moodle database" and now I'm seeing errors such as the one below. I admit that I did not use "Maintenance mode" while doing the above, but this system is not in "production". It's been for demonstration with a view to eventually setting up for production.

My other users (Trainer / Students etc) are still able to logon and can do their stuff, but clearly administrative access is now impossible. Also it seems that the cron scheduled tasks cant run. When I run that manually, it states that it won't run as an upgrade is pending. So if something is broken, I cant be sure if this could have knock-on effects elsewhere.

I guess I'm wondering if this is fixable, or would I be better off starting off a new instance from scratch and trying to bring everything over.

Thanks in advance for any insight or comments.

-----

DDL sql execution error

Debug info: Table 'mdl_quiz_statistics' already exists

CREATE TABLE mdl_quiz_statistics (
id BIGINT(10) NOT NULL auto_increment,
hashcode VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
whichattempts SMALLINT(4) NOT NULL,
timemodified BIGINT(10) NOT NULL,
firstattemptscount BIGINT(10) NOT NULL,
highestattemptscount BIGINT(10) NOT NULL,
lastattemptscount BIGINT(10) NOT NULL,
allattemptscount BIGINT(10) NOT NULL,
firstattemptsavg NUMERIC(15,5),
highestattemptsavg NUMERIC(15,5),
lastattemptsavg NUMERIC(15,5),
allattemptsavg NUMERIC(15,5),
median NUMERIC(15,5),
standarddeviation NUMERIC(15,5),
skewness NUMERIC(15,10),
kurtosis NUMERIC(15,5),
cic NUMERIC(15,10),
errorratio NUMERIC(15,10),
standarderror NUMERIC(15,10),
CONSTRAINT PRIMARY KEY (id)
)
ENGINE = InnoDB
DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
COMMENT='table to cache results from analysis done in statistics repo'
Error code: ddlexecuteerror
Stack trace:
  • line 492 of \lib\dml\moodle_database.php: ddl_change_structure_exception thrown
  • line 1072 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 77 of \lib\ddl\database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
  • line 425 of \lib\ddl\database_manager.php: call to database_manager->execute_sql_arr()
  • line 370 of \lib\ddl\database_manager.php: call to database_manager->install_from_xmldb_structure()
  • line 614 of \lib\upgradelib.php: call to database_manager->install_from_xmldb_file()
  • line 1877 of \lib\upgradelib.php: call to upgrade_plugins()
  • line 694 of \admin\index.php: call to upgrade_noncore()
Our Setup is:  Windows Server 2016; MariaDB; PHP 7.3.8

Thanks
/Gary

Average of ratings: -
In reply to Gary Joyce

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

During an upgrade I'd expect plugins to be upgraded one after the other, not a few at a time.

Moodle's plugin architecture encompasses "core" plugins, i.e. plugins included in the main Moodle source code, and third party plugins, i.e. those that you download and install separately. But when Moodle performs an upgrade it doesn't distinguish between these, it just checks the plugin version in the database and the version in the plugin source code; if the database version is lower it performs the plugin's upgrade steps.

Did the steps to upgrade included taking a backup of the database and Moodledata beforehand (this would be an essential step for a production site)? If so you could revert to that save-point by replacing the existing Moodledata and database (back up the non-working database, drop it, create a new database and restore the pre-upgrade backup). Then try the upgrade again.

Otherwise you can start from scratch as you say, or you can try to troubleshoot this. It depends how much time you have – and how much you want to learn about Moodle's workings!

From the listed error, the Statistics (quiz_statistics) plugin is a core plugin but the stack trace shows that the version information isn't present in the database so it's trying to perform the installation of the plugin but the database table is already present resulting in the error. Obviously the database table will exist in an existing site, the question is how did the version information come to be missing? The following query lists the plugin versions for my test site (Moodle 3.7.2):

SELECT plugin, name, value FROM mdl_config_plugins WHERE name = 'version' AND plugin LIKE 'quiz\_%';
+-----------------+---------+------------+
| plugin          | name    | value      |
+-----------------+---------+------------+
| quiz_grading    | version | 2019052000 |
| quiz_overview   | version | 2019052000 |
| quiz_responses  | version | 2019052000 |
| quiz_statistics | version | 2019052000 |
+-----------------+---------+------------+

Presumably the "quiz_statistics" row is missing in your database but are the other rows present?

As you say, Maintenance Mode is less important for a demonstration site, even on a live site I wouldn't expect the issues you've encountered to arise as a result of not using this.


Average of ratings: Useful (2)
In reply to Leon Stringer

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Gary Joyce -
Thank you Leon for coming back on this.
First, I admit and regret that this has not been set up with backups at every stage, as I would do in a real deployment. It is sitting on a VM and has been created as a demo and also has actually worked very well for our Interns to give them an idea of what a LMS can do for trainers/students.

I have basically bypassed this issue rather than solving it, by removing what I thought were the problematic plugins (the quiz related ones), so they are no longer in the DB, and the LMS subsequently allowed me to logon as Admin and go ahead to the main interface. It all works as before if a little slower.
However, I now notice that the cron job seems to be broken. This had been set up as a scheduled task every 5 mins and before this "upgrade", it took a few seconds and went through all the various steps just fine. Now it runs and seems to repeat the same step below over and over, and can be up to and above 50,000 or 100,000 times. The Task log shows this line but repeated that number of times. It also seems to create the same number of folders in the localcache folder. So its gone from a couple of seconds to over half an hour.
This line is repeated "many" times ~ 50,000 or more.

"3" "0" "moodle" "core_files\task\conversion_cleanup_task" "0" "1581597244.4254000000" "1581597244.9216000000" "1" "1" "0" "Execute scheduled task: Cleanup of temporary records for file conversions. (core_files\task\conversion_cleanup_task)
... started 12:34:04. Current memory use 3.8MB.
... used 2 dbqueries
... used 0.20989322662354 seconds
Scheduled task complete: Cleanup of temporary records for file conversions. (core_files\task\conversion_cleanup_task)"

If I run it manually in the browser (/moodle/admin/cron.php?password=xxx), it also repeats that same clean-up task but nothing else and runs for about 2 mins.

When I saw file conversion, I thought this may be related to the change from UTF8 to UTF8mb4 which I was advised to do as part of this upgrade. I had used the scripts kindly provided elsewhere on here to check and set the correlations on the relevant tables, in config.php and in the DB itself. Those seemed to work without error.
I realise these may be unrelated issues, and I will probably have to rebuild everything from scratch. But it is really helpful to know what could be happening so that I can do the right things next time.
Thanks again.
Average of ratings: Useful (1)
In reply to Gary Joyce

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Daniel Thies -
Picture of Core developers Picture of Plugin developers Picture of Testers
It may be that this is significant that the tables here are quiz report tables. These are technically not independent plugins, but are subplugins defined in the quiz module rather than by independent version files. The method by which subplugins are defined was modified from Moodle 3.7 and 3.8. In 3.7 a new file mod/quiz/subplugin.json was added. This may be why it does not have the correct version.

I am sure that there were tests of the upgrade process, but they may not have tried all releases of 3.7. What was the exact 3.7 release version that you upgraded from?
Average of ratings: Useful (2)
In reply to Daniel Thies

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Gary Joyce -
Thanks Daniel. I was impressed at how easy it was for Moodle to recognise these plugins by simply adding them to the \mod folder and then the upgrade would kick in. It actually made me feel like a kid in a candy store when I saw all the amazing plugins that were available.
I also saw how the version must match the moodle version. I was pretty sure that I was using the right versions of everything so I will go back and see if I can re-install only the ones that I need, and maybe remove the other ones that are "nice to have".
The previous version from the old version file:

$version  = 2019052001.06;              // 20190520      = branching date YYYYMMDD - do not modify!
                                        //         RR    = release increments - 00 in DEV branches.
                                        //           .XX = incremental changes.

$release  = '3.7.1+ (Build: 20190809)'; // Human-friendly version name

$branch   = '37';                       // This version's branch.
$maturity = MATURITY_STABLE;             // This version's maturity level.

Thanks again.
Average of ratings: -
In reply to Gary Joyce

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Daniel Thies -
Picture of Core developers Picture of Plugin developers Picture of Testers
I just did a trial upgrade for this version to 3.8 with just core to test whether there was an issue with the upgrade path, but it went fine. From you comment above it looks like the problem is with one of the additional plugins you mentioned you removed. You can add them back one and upgrade one at a time if you want to find the one with a problem. It may be that a third party plugin is trying to utilize the core quiz in a way it should not.
Average of ratings: Useful (1)
In reply to Daniel Thies

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Gary Joyce -
Thanks for checking this. I just have two "additional" plugins, that is ones with the word Additional above them.
Plugins requiring attention : 0 All plugins : 406 ... and all state "Standard / Installed"
Yet when I click "Upgrade Moodle database now", there are a couple of minutes waiting and then it seems there is a timeout which hasn't happened before.

HTTP Error 500.0 - Internal Server Error
C:\php\php-cgi.exe - The FastCGI process exceeded configured activity timeout
Detailed Error Information:
Module FastCgiModule
Notification ExecuteRequestHandler
Handler php7
Error Code 0x80070102
Requested URL http://..../moodle/admin/index.php?cache=0&confirmplugincheck=1
Physical Path C:\inetpub\wwwroot\moodle\admin\index.php
Logon Method Anonymous
Logon User Anonymous

I will hopefully be able to continue with my non-Admin users but will certainly plan to start afresh next week.
Many thanks for your advice.
Average of ratings: -
In reply to Gary Joyce

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Aristotelis Gorgias -
I have encountered this problem many times during upgrading to newer versions and what I always did and what always worked for me is to simply delete one by one the tables that were causing the problem (I always checked if the tables contained any data and hopefully they never did. Just empty tables causing the problem. If the tables had data, I would export them as SQL inserts and then delete the table, so I can re-import those data afterwards) so after deleting the tables I was the re-running the upgrade and it was continuing to upgrade normally and bypass the step that caused the error previously. This could happen more than once in some upgrades and I had to re-run the upgrade multiple times cause each time there were new tables causing the issue and so I had to delete -> re-run -> new tables causing error -> delete -> re-run and so on until it completed successfully.
I hope this will help a few people.
Average of ratings: Useful (1)
In reply to Aristotelis Gorgias

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
This should *never* happen during an upgrade. I appreciate that this might have got you out of trouble but it's a symptom of some underlying problem that I would *really* want to get to the root cause of.
Average of ratings: Useful (3)
In reply to Aristotelis Gorgias

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Gary Joyce -
Thanks for this. I did something along those lines last week, which did get me over the issue and allowed the Admin user to get into the system at least. However, as said by others, it doesn't solve the issue whatever it might have been and there's the risk that the upgrade did not complete fully. Also, it seems to knocks the cron out of whack. So I'm in the middle of putting up a fresh new instance (code, DB, data) and I'm going to very carefully select which plugins I need... and make backups along the way.
I'm sure you're right that it is related to the tables. I just want to get in with the Admin user so I can at least export everything useful out, ready for the clean install.
Thanks again.
Average of ratings: -
In reply to Gary Joyce

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Daniel Thies -
Picture of Core developers Picture of Plugin developers Picture of Testers
Hi Gary,

It would be helpful to the community to know which plugins you tried that may be causing problems. Howard is right. This should never occur. If it seems in your case to be caused by a third party plugin that is coded incorrectly, then it would be best if the author of that plugin were able to be notified that there is a bug to fix.
Average of ratings: Useful (2)
In reply to Daniel Thies

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Gary Joyce -
Hi Daniel

I've rebuilt from scratch and exported everything over so all is fine now, with cron working nicely both on schedule and on demand etc.
I re-installed the two plugins that I had tried out before and both have installed just fine on the new instance, no problem. So I would not say they were involved.
My guess is that at some point pre-upgrade, I was running scripts to update the collation to utf8mb4 (probably without really needing to but I always try to go with the advised option). Then I started to have the trouble with the "quiz" and "statistics" components during the upgrade. That led me down the slippery slope of working with the DB tables to see what was wrong and what was breaking the upgrade cycle. This probably introduced other issues such as the massive build up of cache temporary files and a 6Gb mdl_tasks table in the DB. I saw the hole getting deeper so I decided to pull the plug and "stop digging" ..
So the fault is likely mine, but it has allowed me to learn how to migrate from instance to instance, how/when to upgrade, and how great the support is from the community.
Thanks again.
Average of ratings: -
In reply to Gary Joyce

Re: Issue after Upgrading from 3.7 to 3.8.1 - Tables already exist

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
The most likely reason for this sort of thing is a failed upgrade. You may not even have noticed. The upgrade gets part way through, you get a fatal error and you start again. The upgrade - not surprisingly - fails.

I would...

- restore a working site from the backup I hope you took
- enable Debugging in config.php
- try again

Watch very closely for errors and notices.
Average of ratings: Useful (1)