Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

by Sandra King -
Number of replies: 5

Hello,

I have been unable to upgrade Moodle from where I was when the sysadmins migrated from MySQL to MariaDB.  I have been occupied with other projects but now over a year later am coming back to try to solve the problem.  I am posting here so that hopefully someone who knows more than me can help me with troubleshooting ideas, and if I find a solution for myself I will post it here to hopefully help someone else.

Here is what I have now.

Moodle 3.7.3
MariaDB 10.3.34
PHP 7.3.33

The only check on upgrading to 3.11 that shows up is sodium should be installed.

Background: After my first attempt at upgrading failed...

Based on some forum posts I ran the check to make sure that file structure is Baracuda and cli/mysql_compressed_rows.php showed no errors - I fixed the few errors I had found and re-exported the DB re-imported it as a new DB  (I am running my upgrades in version-specific folders all on the same server )

A weird thing that I noticed is that it seems the new DB is significantly larger than the old DB even before I try the upgrade. 

I got the error upgrading within 3.7 - but after I get the error if I try to continue the upgrade seems to complete, and in 3.7.9 it seems everything works ok. 
Then when I upgrade to 3.9 I again get the same error and this time I get a lot of invalid permissions notifications every time I move between pages, which is what happens when I go straight from 3.7 to 3.11 too.

Here is the error:

Error writing to database

More information about this error
Debug info: Data too long for column 'capabilities' at row 1
UPDATE mdl_external_functions SET name = ?,classname = ?,methodname = ?,classpath = ?,component = ?,capabilities = ?,services = ? WHERE id=?
[array (
0 => 'core_course_update_courses',
1 => 'core_course_external',
2 => 'update_courses',
3 => 'course/externallib.php',
4 => 'moodle',
5 => 'moodle/course:update, moodle/course:changecategory, moodle/course:changefullname, moodle/course:changeshortname, moodle/course:changeidnumber, moodle/course:changesummary, moodle/course:visibility',
6 => NULL,
7 => '73',
)]
Error code: dmlwriteexception
Stack trace:

    line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
    line 1587 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 1619 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->update_record_raw()
    line 1251 of /lib/upgradelib.php: call to mysqli_native_moodle_database->update_record()
    line 1870 of /lib/upgradelib.php: call to external_update_descriptions()
    line 519 of /admin/index.php: call to upgrade_core()

Appreciate any ideas -

Average of ratings: -
In reply to Sandra King

Ri: Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

by Sergio Rabellino -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Did you ran the script admin/cli/mysql_compressed_rows.php to check if your db tables uses compressed rows (and your db is configured correctly)?

cfr. https://docs.moodle.org/311/en/Administration_via_command_line#Converting_InnoDB_tables_to_Barracuda
Average of ratings: -
In reply to Sandra King

Re: Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

by Ken Task -
Picture of Particularly helpful Moodlers

Data too long for column 'capabilities' at row 1

Venturing 2 guesses ... which might get you resolved or close to resolution ...

Guess 1

From mysql client and using the DB for moodle:
explain mdl_external_functions;

You should see something like (don't have a 3.7 any more and what am sharing below
is from a 3.8)

mysql> explain mdl_external_functions;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| id           | bigint(10)    | NO   | PRI | NULL    | auto_increment |
| name         | varchar(200)  | NO   | UNI |         |                |
| classname    | varchar(100)  | NO   |     |         |                |
| methodname   | varchar(100)  | NO   |     |         |                |
| classpath    | varchar(255)  | YES  |     | NULL    |                |
| component    | varchar(100)  | NO   |     |         |                |
| capabilities | varchar(255)  | YES  |     | NULL    |                |
| services     | varchar(1333) | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

| capabilities | varchar(255)

What does yours show?

Guess 2: character set and collation of 3.x should now be

utf8mb4 and utf8mb4_unicode_ci

From command line, cd /path/to/moodlecode/admin/cli/

Then run:

php mysql_collation.php -l

That list all tables in the DB and provides a summary at the end.

You should see:

Table collations summary for https://yoursite:
utf8mb4_unicode_ci: 1544

IF you see other collations, one can fix:

php mysql_collation.php -f

Ok, done guessing! smile

'SoS', Ken

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

Re: Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

by Sandra King -
Most of the columns that I expected to be varchar(255) are now varchar(191) across all tables.
When I run explain I get
    Field Type Null Key Default Extra 
    id bigint(10) NO PRI NULL auto_increment
    name varchar(191) NO UNI 
    classname varchar(100) NO 
    methodname varchar(100) NO 
    classpath varchar(191) YES NULL 
    component varchar(100) NO 
    capabilities varchar(191) YES NULL 
    services varchar(191) YES NULL
This is the first time I have seen instructions for finding the collation of all tables through the command line - Thank you
I see:
utf8mb4_unicode_ci: 1171
utf8_general_ci: 317
utf8mb4_bin: 66

I followed directions and typed 

$ php mysql_collation.php -f
    Unrecognised options:
    -f
    Please use --help option.

So I re-sent with the help command: 

$ php mysql_collation.php --help
    MySQL collation conversions script.
        It is strongly recommended to stop the web serverbefore the conversion.
        
        This script may be executed before the main upgrade - 1.9.x data for example.

        Options:
        --collation=COLLATION Convert MySQL tables to different collation
        -l, --list Show table and column information
        -a, --available Show list of available collations
        -h, --help Print out this help 
        Example: $ sudo -u www-data /usr/bin/php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

I am going to have to wait until Friday night to put moodle in maintenance mode and run the script. 

Thank you so much for your help and I will post more when I have more information...

Average of ratings: -
In reply to Sandra King

Re: Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

by Ken Task -
Picture of Particularly helpful Moodlers

The environment check does not go deep into DB other than version.

Ooops!  My Bad!  Sorry 'bout that.  Was attempting to explain switches from memory and got it wrong for collation script - no fix option. sad  There is a -f (fix option) for mysql_compressed_rows.php.

Be sure to check your config.php file, DB array section, for a line for DB collation as it should match all the table collations - utf8mb4_unicode_ci.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Upgrade from 3.7 to 3.11 failed - retried upgrade 3.7 to 3.79 and then to 3.9 same error possibly caused by change from MySQL to MariaDb

by Sandra King -
Gentlemen, thank you for your help. following your instructions, I was able to update the database collation and fix the varchar sizes on the problematic table. After that I upgraded to 3.79 and then to 3.9 and then to 3.11.

I took a backup at the end of each step, but all told the site was in maintenance mode for only 4 hours.
I will post if any of the students using the site report problems but otherwise I consider this a wonderful success.

Sandra.
Average of ratings: Useful (1)