DB dml_read_exception Issue after Moodle upgrade

DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
Number of replies: 15
I recently upgraded my Moodle app from v4.0.5 to v4.2. Which required an upgrade from mysql5.7 to mysql8.0.
I have very little db exerience so basically I'm attacking most errors I find with Stackoverflow and google.

During the upgrade I ran into db issues(below) where mysql failed to start, these were fixed with workarounds I found online.

[ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.

Fix:

comment out from /etc/mysql/my.cnf

#query_cache_limit      = 1M

#query_cache_size        = 16M


[ERROR] [MY-000067] [Server] unknown variable 'innodb_file_format=Barracuda'.

Fix:

Comment out from /etc/mysql/my.cnf

#innodb_file_format = Barracuda

#innodb_file_per_table = 1

#innodb_large_prefix



Mysql started, app upgraded fine, however I keep getting errors reading from the DB around the same general error.


mysqldump: Got error: 1709: Index column size too large. The maximum column size is 767 bytes. when using LOCK TABLES

mysqldump: Couldn't execute 'show create table `mdl_comments`': Index column size too large. The maximum column size is 767 bytes. (1709)




I pulled a debug of the last one:

Debug info: Index column size too large. The maximum column size is 767 bytes.

SELECT COUNT('x') FROM mdl_comments WHERE component = ? AND commentarea = ? AND itemid = ? AND contextid = ?

-- line 1679 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()

-- line 220 of /question/classes/local/bank/column_base.php: call to qbank_comment\comment_count_column->display_content()

-- line 1280 of /question/classes/local/bank/view.php: call to core_question\local\bank\column_base->display()

-- line 1200 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->print_table_row()

-- line 1176 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->print_table()

-- line 1013 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->display_questions()

-- line 777 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->display_question_list()

-- line 60 of /question/edit.php: call to core_question\local\bank\view->display()

[array (

0 => 'qbank_comment',

1 => 'question',

2 => '254076',

3 => 1,

)]

Error code: dmlreadexception


Stack trace:

line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown

line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()

line 1371 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()

line 1679 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()

line 1752 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()

line 1963 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()

line 1946 of /lib/dml/moodle_database.php: call to moodle_database->count_records_sql()

line 1929 of /lib/dml/moodle_database.php: call to moodle_database->count_records_select()

line 65 of /question/bank/comment/classes/comment_count_column.php: call to moodle_database->count_records()

line 220 of /question/classes/local/bank/column_base.php: call to qbank_comment\comment_count_column->display_content()

line 1280 of /question/classes/local/bank/view.php: call to core_question\local\bank\column_base->display()

line 1200 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->print_table_row()

line 1176 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->print_table()

line 1013 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->display_questions()

line 777 of /question/classes/local/bank/view.php: call to core_question\local\bank\view->display_question_list()

line 60 of /question/edit.php: call to core_question\local\bank\view->display()


There is a lot of conflicting information on Stack Overflow and my usual suspects, So I thought maybe someone here could point me in the correct direction without my having to break 1000 things in figuring this out??



Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

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

Index column size too large is because large database indexes aren't enabled. This used to be controlled by the innodb_large_prefix setting. However this shouldn't be needed for MySQL 8.0, this option was removed as large indexes are always used from this version onwards. Are you certain about the MySQL version? Can you get to Moodle's environment page (admin/environment.php)? What does it say under database (normally the third row).

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

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
Yeah its requires mysql 8.0, excerpt for Environment page below;
database mysql (8.0.33-0ubuntu0.20.04.4) version 8.0 is required and you are running 8.0.33 OK

My troubleshooting identified that the problematic table was mdl_comments in the moodle db.
I tried to DROP the table and rebuild it from mysqldump backups, but even dropping the table fails with the same error.

mysql> DROP TABLE mdl_comments;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Really really frustrating...
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

by Ken Task -
Picture of Particularly helpful Moodlers
Please see:

and

if you have shell access to your server, look in code/admin/cli/
There are 2 mysql_blah.php scripts that will help with DB.

Also suggest installing MySQLTuner - a perl script ... will help tune DB.   Run tuner with super user creds.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
Thanks Ken,

Went through this, https://www.techtutsonline.com/error-1709-index-column-size-too-large/
You will not believe how many of these I have tried since yesterday if I told you.

I did modify the default row format as seen below and restarted, with no luck.
root@myhost:~# mysqld --verbose --help |grep ^innodb-default-row-format
innodb-default-row-format dynamic

Also ran the mysql_blah, with no luck as well.
root@moodlehost:~# /usr/bin/php /var/www/html/moodle/admin/cli/mysql_compressed_rows.php --fix
No changes necessary
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
The issue is the ROW_Format for that table, I found a mysql bug report that says its generally bad news for me.
https://bugs.mysql.com/bug.php?id=99791

My approach now is to rebuild the table from a dated backup and take my chances, but the fact that I cannot drop it is a headache.
Bcos the restore will try to drop the table to restore it.

mysql> check table mdl_comments;
+---------------------+-------+----------+--------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+-------+----------+--------------------------------------------------------------------+
| moodle.mdl_comments | check | Error | Index column size too large. The maximum column size is 767 bytes. |
| moodle.mdl_comments | check | Error | Table 'moodle.mdl_comments' doesn't exist |
| moodle.mdl_comments | check | error | Corrupt |
+---------------------+-------+----------+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> repair table mdl_comments;
+---------------------+--------+----------+--------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+--------+----------+--------------------------------------------------------------------+
| moodle.mdl_comments | repair | Error | Index column size too large. The maximum column size is 767 bytes. |
| moodle.mdl_comments | repair | Error | Table 'moodle.mdl_comments' doesn't exist |
| moodle.mdl_comments | repair | error | Corrupt |
+---------------------+--------+----------+--------------------------------------------------------------------+
3 rows in set (0.01 sec)

Another drastic approach to build a new db, recreate the table with the correct ROW_FORMAT and repopulate the data, now where I need help is,
If I go this route how would I tell Moodle to look to the new DB, without any application impact?
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

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

Have you tried running ALTER TABLE mdl_comments ROW_FORMAT=Compressed? I think mysql_compressed_rows.php --fix may be incorrectly deciding no changes are necessary (MDL-59486).

Average of ratings: -
In reply to Leon Stringer

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
Yes, I have, every SQL command thrown at this thing results in the same error.
mysql> ALTER TABLE moodle.mdl_comments ROW_FORMAT=Compressed;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Even tried to drop the indexes.
show indexes from moodle.mdl_comments;
mysql> alter table moodle.mdl_comments drop index mdl_comm_concomite_ix,algorithm=inplace;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> DROP TABLE moodle.mdl_comments;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
I have been able to find a fix/workaround, with some lost data.

Took a mysqldump of the existing db, ignoring the mdl_comments table,
Created a new db, restoring the mysqldump to it,
then created the mdl_comments table on this new db, using sql CREATE and INSERT statements in an even older mysqldump backup from before my upgrade,
replcated moodle db user grants to the new db,
Put Moodle in maintenance,
edit the config.php file with the new db creds, restart the web service
Pop out of maintenance, and looks like the issue is fixed.

The mysql bug results in no way to recover the table but from a backup restore.
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
Thanks Ken and Leon for the suggestions.
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

by Ken Task -
Picture of Particularly helpful Moodlers
Congrats!  Heck of a deal! sad
As far as data loss ... wondered how significant the mdl_comments table is/was?
So checked into a K12 site tables and see that mdl_comments is just that ... comments - which, to me, not that signifcant!

See attached image clip.

'SoS', Ken

Attachment Screenshot 2023-07-27 at 12.34.21 PM.png
Average of ratings: -
In reply to Ken Task

Re: DB dml_read_exception Issue after Moodle upgrade

by Obi Okonkwo -
Yeah, I guess I was lucky it wasnt a more critical table that went poof.

As I see it, this bug should affect tables whose ROW_FORMAT was Compact before the upgrade from 5.7 to 8.0.
And there were a bunch of them in the old db.

mysql> select COUNT(ROW_FORMAT) from information_schema.tables where TABLE_SCHEMA='moodle' AND ROW_FORMAT = 'Compact';
+-------------------+
| COUNT(ROW_FORMAT) |
+-------------------+
| 269 |
+-------------------+

Lucky ! There are no Compact config tables in the new db.
Average of ratings: -
In reply to Obi Okonkwo

Re: DB dml_read_exception Issue after Moodle upgrade

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

I think Ken's correct, specifically it's mysql_compressed_rows.php that you should run.

The steps would be:

1. Take a backup of your Moodle database.

2. At the command line change to your Moodle source code folder with cd, then run the above script. For example, if your Moodle source code is in public_html run:

$ cd public_html
$ php admin/cli/mysql_compressed_rows.php --fix

If you don't have command line access you should be able to make these changes using phpMyAdmin by running the following statement on the SQL tab:

ALTER TABLE mdl_comments ROW_FORMAT=Compressed

You would need to repeat this statement for every table you see this error for.

Average of ratings: -
In reply to Leon Stringer

Re: DB dml_read_exception Issue after Moodle upgrade

by Ken Task -
Picture of Particularly helpful Moodlers
@Leon ... do you happen to know if upgrading the DB server/client to 8.0 or 8.1 automatically goes into existing databases and changes engine?

IMHO, moodle code should have kept the admin/cli/mysql_engine.php script if the above doesn't happen.  Those mysql_blah.php scripts loop through all tables and columns in the DB for moodle.   Much easier than having to issue an alter query in PHPMyAdmin for all effected tables. smile

Wonder if one could acquire mysql_engine.php from a 3.9.highest download of code and copy that mysql_engine.php script to a moodle 4.x and it would still work?

Anyhoo ... as usual, my 2 cents!
'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: DB dml_read_exception Issue after Moodle upgrade

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

@Ken: admin/cli/mysql_engine.php migrated tables' engine from MyISAM to InnoDB. Support for MyISAM was dropped in Moodle 2.9 (MDL-46064), from which point Moodle versions would refuse to upgrade if you still had this. So this script was last needed if you had Moodle 2.8 with MyISAM tables and wanted to upgrade Moodle. It had been redundant since then and finally removed in Moodle 4.0. (For some reason Moodle had two ways to do this conversion, if you go to admin/tool/innodb in your browser you'll find this functionality is still there – albeit also completely redundant because one can't possibly have MyISAM tables).

I think we both suspect OP's issue to be caused by the row format which is a different thing, the script for that – admin/cli/mysql_compressed_rows.php – is still present.

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

Re: DB dml_read_exception Issue after Moodle upgrade

by Ken Task -
Picture of Particularly helpful Moodlers
Thanks, Leon.   That admin/tool snuck in on me!  And I see by it's comments says it's for mysql only - which happens to be what I see the most in assistsing others.  Don't recall now if I had ever run the old script on mariaDB tables or not.

'SoS', Ken

Average of ratings: -