upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Carl Brewer -
Number of replies: 10

G'day,

I've got a vanilla moodle 3.8.x (just upgraded to 3.8.4, no problems with that upgrade via CLI) on a CentOS 8.2 with MariaDB 10.5.4 and all the up to date InnoDB settings.  PHP 7.4.3

It upgraded from 3.8.2 to 3.8.4 with no problems.

3.8.4 (and, 3.8.2, I tried that too) is failing at upgrade time :




This is the error

sudo -u apache /usr/local/apache/2.4/bin/php admin/cli/upgrade.php

.

.

.

CREATE TABLE mdl_h5pactivity_attempts_results (
    id BIGINT(10) NOT NULL auto_increment,
    attemptid BIGINT(10) NOT NULL,
    subcontent VARCHAR(128) COLLATE utf8mb4_unicode_ci,
    timecreated BIGINT(10) NOT NULL,
    interactiontype VARCHAR(128) COLLATE utf8mb4_unicode_ci,
    description LONGTEXT COLLATE utf8mb4_unicode_ci,
    correctpattern LONGTEXT COLLATE utf8mb4_unicode_ci,
    response LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
    additionals LONGTEXT COLLATE utf8mb4_unicode_ci,
    rawscore BIGINT(10) NOT NULL DEFAULT 0,
    maxscore BIGINT(10) NOT NULL DEFAULT 0,
    duration BIGINT(10) DEFAULT 0,
    completion TINYINT(1),
    success TINYINT(1),
CONSTRAINT  PRIMARY KEY (id)
, KEY mdl_h5paatteresu_atttim2_ix (attemptid, timecreated)
, KEY mdl_h5paatteresu_att2_ix (attemptid)
)
 ENGINE = InnoDB
 DEFAULT COLLATE = utf8mb4_unicode_ci ROW_FORMAT=Compressed
 COMMENT='H5Pactivities_attempts tracking info'
Error code: ddlexecuteerror !!
!! Stack trace: * line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
* line 1098 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 427 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
* line 372 of /lib/ddl/database_manager.php: call to database_manager->install_from_xmldb_structure()
* line 830 of /lib/upgradelib.php: call to database_manager->install_from_xmldb_file()
* line 565 of /lib/upgradelib.php: call to upgrade_plugins_modules()
* line 1917 of /lib/upgradelib.php: call to upgrade_plugins()
* line 193 of /admin/cli/upgrade.php: call to upgrade_noncore()
 !!


any suggestions?  The particular instance of Moodle has no extra plugins, and as above, it upgraded from 3.8.2 to 3.8.4 with no problems.  I've backed it back out to 3.8.4 for now.


Average of ratings: -
In reply to Carl Brewer

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Is that definitely all of the error? The actual thing it's complaining about is annoyingly absent. That seems to be a new table in 3.9 but I can't immediately see why the CREATE command would cause a problem.

It might be worth trying a clean 3.9 upgrade to make sure that completes.
Average of ratings: -
In reply to Carl Brewer

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Ken Task -
Picture of Particularly helpful Moodlers

"... has no extra plugins".  h5p became core in 3.9.  Prior to that version h5p was an addon plugin.

"!! Stack trace: * line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown"

Use scripts in admin/cli/ for mysql to see about mdl_h5pactivity_attempts_results table.

[root@server cli]# php mysql_compressed_rows.php -i
Database version:      5.7.31
Database name:         moodle38
Database engine:       InnoDB
innodb_file_per_table: ON
innodb_file_format:    Barracuda

[root@server cli]# php mysql_compressed_rows.php -l
mdl_badge                       Compressed
mdl_data                        Compressed
mdl_data_fields                 Compressed
mdl_enrol_lti_lti2_consumer     Compressed
mdl_enrol_paypal                Compressed
mdl_lti                         Compressed
mdl_oauth2_issuer               Compressed
mdl_user                        Compressed
mdl_user_info_field             Compressed

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
h5p became core in 3.8 (not 3.9) - https://docs.moodle.org/dev/Moodle_3.8_release_notes#H5P_integration
Average of ratings: -
In reply to Howard Miller

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Ken Task -
Picture of Particularly helpful Moodlers

I stand corrected ... you are 100% correct, Howard.

Am gonna have to drop 'latin' ... did a find ./moodle38 -name hvp ... which showed nada! 

Dropping latin:

Kens-MBP-2:MDLGITTEST kentask$ find ./moodle38 -name h5p
./moodle38/lib/h5p
./moodle38/lib/editor/atto/plugins/h5p
./moodle38/h5p

Now back to server version to look at tables in a 3.8.x! :|

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Carl Brewer -
sudo -u moodle /usr/local/apache/2.4/bin/php /home/moodle/www/admin/cli/mysql_compressed_rows.php -i
Database version: 5.5.5-10.5.4-MariaDB
Database name: moodle
Database engine: InnoDB
innodb_file_per_table: ON
innodb_file_format: Barracuda


sudo -u moodle /usr/local/apache/2.4/bin/php /home/moodle/www/admin/cli/mysql_compressed_rows.php -l
mdl_badge Compressed
mdl_data Compressed
mdl_data_fields Compressed
mdl_enrol_lti_lti2_consumer Compressed
mdl_enrol_paypal Compressed
mdl_lti Compressed
mdl_oauth2_issuer Compressed
mdl_user Compressed
mdl_user_info_field Compressed
Average of ratings: -
In reply to Carl Brewer

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Ken Task -
Picture of Particularly helpful Moodlers

Hmmmm in a server based 3.8.highest DB that has h5p but no activities set up.

mysql> show tables like '%h5p%';
+--------------------------------+
| Tables_in_moodle38 (%h5p%)     |
+--------------------------------+
| mdl_h5p                        |
| mdl_h5p_contents_libraries     |
| mdl_h5p_libraries              |
| mdl_h5p_libraries_cachedassets |
| mdl_h5p_library_dependencies   |
+--------------------------------+
5 rows in set (0.00 sec)

OP's table is mdl_h5pactivity_attempts_results

OP's error is this one:

!! Stack trace: * line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown

change structure

so db is innodb, barracuda, with utf8mb4 character set and utf8mb4_unicode_di?

What's in config.php for collation?

The db user in config.php, does that user have privy to alter/change structure of a db?

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Carl Brewer -
MariaDB [moodle]> show tables like '%h5p%';
+----------------------------------+
| Tables_in_moodle (%h5p%) |
+----------------------------------+
| mdl_h5p |
| mdl_h5p_contents_libraries |
| mdl_h5p_libraries |
| mdl_h5p_libraries_cachedassets |
| mdl_h5p_library_dependencies |
| mdl_h5pactivity |
| mdl_h5pactivity_attempts |
| mdl_h5pactivity_attempts_results |
+----------------------------------+
8 rows in set (0.000 sec)

database created :
MariaDB [moodle]> CREATE DATABASE moodle DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

the moodle DB user's perms set by :
MariaDB [moodle]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO moodleuser@localhost;
Average of ratings: -
In reply to Carl Brewer

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Carl Brewer -
I just tried to bump this instance to 3.10, same problem. I'm at a loss. Any more suggestions?
Average of ratings: -
In reply to Carl Brewer

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

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

H5P functionality was added in 3.8 but the actual H5P activity wasn't added until 3.9 so the tables mdl_h5pactivity, mdl_h5pactivity_attempts and mdl_h5pactivity_attempts_results shouldn't be there.

This problem can occur when an upgrade attempt has been rolled back to the previous version by restoring the database and the restore didn't remove the new tables, which is the default behaviour with mysqldump/mysql < XXX.sql.

So hopefully the fix is to drop the above three tables and also mdl_quizaccess_seb_quizsettings and mdl_quizaccess_seb_template if present, then attempt the upgrade.


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

Re: upgrade from 3.8 to 3.9(.1) failing from CLI and TTW

by Carl Brewer -

Command line upgrade from 3.8.4+ (Build: 20200807) (2019111804.05) to 3.10+ (Build: 20201211) (2020110900.05) completed successfully.


Bingo!

Thank you!!


Carl


Average of ratings: -