Moodle in English: Moodle Update error: Error reading from database

Moodle Update error: Error reading from database

by Barry Schaudt - Monday, January 11, 2021, 9:50 PM

I need help. When updating moodle I always get "Error reading from database"

Some history and more information.

-- Red Hat Enterprise Linux Server release 7.8 (Maipo)

--PHP 7.4.7 (cli) (built: Jun 9 2020 10:57:17) ( NTS )

-- We were running moodle 3.8.6

-- We first updated mariadb from   mysqld 5.5.47-MariaDB to mysql Ver 15.1 Distrib 10.4.17-MariaDB, for Linux
(x86_64) using readline 5.1. All seemed fine.

-- I checked the requirements (Environment) and everything is ok for 3.10 Upwards (also for 3.9)

-- I then tried to install '3.10+ (Build: 20210108)'

-- I always get "Error reading from database" I also get this when I try to upgrade to 3.9. We are using the Adaptable theme. I also tried updating after I use the boost theme. Same error.

-- I used moodle's debug mode. Here is some information from it from 3.10. I can send the complete log

Error reading from database
Debug info: Unknown column 'sortorder' in 'order clause'
SELECT * FROM mdl_license ORDER BY sortorder ASC
[array (
)]
Error code: dmlreadexception
Stack trace:

    line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
    line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 1500 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
    line 232 of /lib/licenselib.php: call to moodle_database->get_records_select()
    line 362 of /lib/licenselib.php: call to license_manager::get_licenses()
    line 379 of /lib/licenselib.php: call to license_manager::get_active_licenses()
    line 33 of /admin/settings/license.php: call to license_manager::get_active_licenses_as_array()
    line 8762 of /lib/adminlib.php: call to require()
    line 19 of /admin/settings.php: call to admin_get_root()

Error reading from database
Debug info: Unknown column 'sortorder' in 'order clause'
SELECT * FROM mdl_license ORDER BY sortorder ASC
[array (
)]
Error code: dmlreadexception
Stack trace:

    line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
    line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 1500 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
    line 232 of /lib/licenselib.php: call to moodle_database->get_records_select()
    line 362 of /lib/licenselib.php: call to license_manager::get_licenses()
    line 379 of /lib/licenselib.php: call to license_manager::get_active_licenses()
    line 33 of /admin/settings/license.php: call to license_manager::get_active_licenses_as_array()
    line 8762 of /lib/adminlib.php: call to require()
    line 4351 of /lib/navigationlib.php: call to admin_get_root()
    line 4214 of /lib/navigationlib.php: call to settings_navigation->load_administration_settings()
    line 769 of /lib/pagelib.php: call to settings_navigation->initialise()
    line 830 of /lib/pagelib.php: call to moodle_page->magic_get_settingsnav()
    line 135 of /blocks/settings/block_settings.php: call to moodle_page->__get()
    line 341 of /blocks/moodleblock.class.php: call to block_settings->get_content()
    line 235 of /blocks/moodleblock.class.php: call to block_base->formatted_contents()
    line 1181 of /lib/blocklib.php: call to block_base->get_content_for_output()
    line 1239 of /lib/blocklib.php: call to block_manager->create_block_contents()
    line 642 of /lib/outputrenderers.php: call to block_manager->ensure_content_created()
    line 83 of /theme/adaptable/layout/includes/head.php: call to core_renderer->standard_head_html()
    line 179 of /theme/adaptable/layout/includes/header.php: call to require_once()
    line 30 of /theme/adaptable/layout/columns2.php: call to require_once()
    line 1374 of /lib/outputrenderers.php: call to include()
    line 1304 of /lib/outputrenderers.php: call to core_renderer->render_page_layout()
    line 2828 of /lib/outputrenderers.php: call to core_renderer->header()
    line 390 of /lib/setuplib.php: call to core_renderer->fatal_error()
    line ? of unknownfile: call to default_exception_handler()

-- Here are the plugins that are installed before I updated and I installed these after I updated but before I took moodle out of maintenence mode.

block_course_contents_moodle34_2018020100.zip block_massaction_moodle33_2018013000.zip
block_faces_moodle35_2018111200.zip block_poll_moodle35_2018052500.zip
block_gchat_moodle24_2012091300.zip

mod_bigbluebuttonbn_moodle310_2019101003.zip mod_jazzquiz_moodle37_2019073000.zip
mod_hvp_moodle39_2020080400.zip mod_zoom_moodle310_2020120800.zip
mod_icontent_moodle38_2020041200.zip

qtype_ddimageortext_moodle29_2015051000.zip

-- I also have the database query log.

Re: Moodle Update error: Error reading from database

by Ken Task - Monday, January 11, 2021, 11:50 PM

PHP version might be too high for 3.8.6 of moodle. What did Moodle Admin - Server - Environment Check say? Take PHP down a version:

http://www.syndrega.ch/blog/#php-and-dbms-compatibility-of-major-moodle-releases

You could upgrade PHP after the first hop 3.8.6 to 3.5.highest me thinks.

Before the upgrade, set default theme to boost via config.php.

$CFG->theme = 'boost';

line above before the "require_once" line in config.

Also, one cannot hyperjump from 3.8.6 to 3.9 or higher .... must make a stop at 3.5.highest first.

See:

https://docs.moodle.org/39/en/Upgrading

and the small paragraph under 'Notes:'

You do show a plugin that is very old ... check that it has a version for destination version or versions in steps. If it does not have updated versions, it might have to go. block_gchat_moodle24_2012091300.zip

Actually it would be a good idea for you to check all versions for all plugins to assure there is a compat version for destination version of core.

https://moodle.org/plugins/

Highly recommend taking a site backup prior to pulling trigger on upgrades. Minimally moodle code, DB dump, and first time moodledata/filedir/

After sucessfull upgrade to first hop, then another minimal site backup (code/DB dump) ... this so if the next hop in the upgrade path fails due to plugins. You haven't lost ground then.

'SoS', Ken

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Visvanath Ratnaweera - Tuesday, January 12, 2021, 12:30 AM

Ken

> PHP version might be too high for 3.8.6 of moodle.

Since release 3.8.3 is Moodle PHP 7.4 compatible. See the note [4] in the Syndrega table.

To the OP
> -- I then tried to install '3.10+ (Build: 20210108)'

I guess, you installed Moodle 3.10 first to test the server supports that release? The environment check in the running 3.8 should have been sufficient.

> -- I always get "Error reading from database"

Odd. If you couldn't install Moodle 3.10, where is the database it is trying to read from?

> I also get this when I try to upgrade to 3.9. We are using the Adaptable theme. I also tried updating after I use the boost theme. Same error.

According to my records (the Syndrega table) you don't need rel. 3.8 to jump to 3.9 or 3.10 - even rel. 3.5 is sufficient. So something messed up. Are you sure that both PHP CLI and CGI versions are identical? Or, the PHP database driver module is the right one for the database actually running? Any other confusions with the diverting MySQL and MariaDB?

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Ken Task - Tuesday, January 12, 2021, 2:36 AM

@Visvanath Ratnaweera - thanks for correction ... yep, didn't look at the footnotes.

However, think I'd still take PHP backwards a version ... reason ... plugins.

block_gchat_moodle24_2012091300.zip

and

mod_hvp_moodle39_2020080400.zip

The readme found in un-archived mod_hvp above says:

"Inside your `moodle/mod` folder you run the following command:
```
git clone -b master https://github.com/h5p/h5p-moodle-plugin.git hvp && cd hvp && git submodule update --init
```"

Don't think the GUI upgrade will do that.

Ooops ... read that wrong ... yes, one doesn't need 3.5!!!! that's backwards ... only excuse ... not enough coffee this AM! :|

+ Adaptable theme ... has had reported issues recently.

Am looking at stock 3.8 code acquired via git ... there is no /admin/settings/license.php

There should be in 3.9 and 3.10.

But OP's debug shows:

line 33 of /admin/settings/license.php: call to license_manager::get_active_licenses_as_array()

So ... mixed code? or code not removed? or ????

'SoS', Ken

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Barry Schaudt - Tuesday, January 12, 2021, 2:45 AM

No mixed code. I ran: mv moodle moodle.backup.`date +%F` before I ran unzip moodle-latest-310.zip

I will look at plugins. Note: everything was running fine with moodle.3.8.6. Upgrading to 3.9 or to 3.10 produces: Error reading from database

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Visvanath Ratnaweera - Tuesday, January 12, 2021, 5:12 AM

Did it go exactly as in the documentation https://docs.moodle.org/39/en/Upgrading or https://docs.moodle.org/310/en/Upgrading ? From what others have found out, the missing column in mdl_license, it looks like the upgrade.php script was not triggered for some reason. The idea is that the database knows what Moodle version it was, and if the code is newer than that, the upgrade.php will be triggered. You know it because it asks your permission on screen full of text and only a Continue button.

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Barry Schaudt - Tuesday, January 12, 2021, 6:09 AM

I followed all the instructions. Tomorrow morning, I will upgrade again (I save everything so I can restore) I will run

php admin/cli/upgrade.php --non-interactive

as mentioned by Brett Miller.

If that fails, I will see what plugins I can delete, and then see if I can get versions of the plugins for 3.10 (and if that fails 3.9).
I already tried switching to the boost theme before upgrading (I used $CFG->theme = 'boost'; prior to upgrading), that failed with the same database error.

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Ken Task - Tuesday, January 12, 2021, 3:05 AM

SELECT * FROM mdl_license ORDER BY sortorder ASC

in a 3.8 mdl_license table has no column named 'sortorder'.

Since MariaDB is supposed to be a 'dropin' replacement for MySQL, don't think there would be any difference in syntax of query of mdl_license table.

In a 3.9 and a 3.10 there is a column called 'sortorder'.

So how about your DB's mdl_license table?

Does the DB user in config.php of site have privs enough to add a column to a table?

'SoS', Ken

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Barry Schaudt - Tuesday, January 12, 2021, 6:19 AM

I granted all on the database for the moodle user. This hasn't been a problem in the past. I update often, every time there is a security flaw fixed, and often more. The database commands have always run correctly. I held off going to 3.9 or 3.10 because were were not at mariadb 10. We cloned the system, upgraded the cloned system to mariadb 10, tested. All was well. We then upgraded mariadb on the actual site, tested moodle 3.6.8 -- all was well. The update from 3.8.6 to 3.10 and from 3.8.6 to 3.9 both failed. This is the first moodle upgrade with mariadb 10. I don't know if that matters. Other systems (non-moodle) that I manage haven't had problems after upgrading to mariadb 10

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Ken Task - Tuesday, January 12, 2021, 7:16 AM

Am confused ... "... tested moodle 3.6.8 - all was well."
How did you site get to 3.8.6 from 3.6.8 unless that's a typo?

http://www.syndrega.ch/blog/#php-and-dbms-compatibility-of-major-moodle-releases

DB version ...

MariaDB 10. isn't 10.2.29 or 10.5

Version confusion reigns considering MariaDB used MySQL version numbers in the past.
https://mariadb.com/kb/en/mariadb-server/

+20 to running cli upgrade, but suggest a couple of things just prior to pulling trigger on that script - run cli cron.php (can't run cron in maintenance mode) in same dir the purge caches. Then run the upgrade script.

Will say this ... never ... never ... compare moodle to other non-moodle apps. Moodle has always been a beast to itself when it comes to PHP versions and extensions as well as versions of MySQL/MariaDB.

'SoS', Ken

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Barry Schaudt - Tuesday, January 12, 2021, 8:15 AM

Typo, we are at moodle 3.8.6

mariadb: 10.4.17-MariaDB

I removed unused plugins and themes. Now only :
$ php uninstall_plugins.php --show-contrib
mod_bigbluebuttonbn BigBlueButtonBN
mod_jazzquiz JazzQuiz
block_course_contents Course contents
theme_adaptable Adaptable

Details:
block_course_contents_moodle34_2018020100.zip
mod_bigbluebuttonbn_moodle310_2019101003.zip
mod_jazzquiz_moodle37_2019073000.zip
theme_adaptable_moodle38_2020070100.zip

Tomorrow morning, I will set the theme to block, run cron.php, install moodle 3.10, install the latest versions of the above plugins, and see what happens. Of course, everything will be backed up. So, I can restore and try some things

Thank you everyone for the assistance so far. I will update everyone tomorrow morning after the upgrade attempt

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Bret Miller - Tuesday, January 12, 2021, 3:15 AM

The "sortorder" field looks like it was added in the 3.9.x upgrade which I did last month. It's not present on the previous 3.8.latest version of the mdl_license table. This would indicate a problem in the upgrade procedure where something didn't complete properly. If you are not using the command line to upgrade, I would strongly recommend that you do. To fix this you will need to:
1. clear out the existing database (so that extraneous tables are removed)
2. restore your backup of the 3.8.6 version you are upgrading
3. put your site in maintenance mode: php admin/cli/maintenance.php --enable
4. install the upgraded files as per the upgrade instructions
5. run the upgrade: php admin/cli/upgrade.php --non-interactive
6. take it out of maintenance mode: php admin/cli/maintenance.php --disable

Average of ratings: -

Ang: Moodle Update error: Error reading from database

by Preben Pedersen - Tuesday, January 12, 2021, 2:52 PM

Hello Barry

In my upgrades of Moodle environments to 3.9, i have had to upgrade mysql and mariaDB aswell on several servers.

I've encountered two different problems that gave me 'Error reading from database' message.

1. discrepancy between columns actual columns in the database compared to expected columns.
- This i sorted by running mysql_upgrade in terminal.

2. Unable to reach the database server (we keep front end and database at seperate servers).
- The upgrade changed the mysql config to only allow connections from localhost. Disabled that option in my.cnf

Average of ratings: -

Re: Moodle Update error: Error reading from database

by Barry Schaudt - Tuesday, January 12, 2021, 9:35 PM

The upgrade went through. I did the following:
-- deleted unused plugins and themes
-- upgraded from moodle 3.8.6 to 3.10
-- upgraded plugins to be sure they were compatible with at least 3.9
-- ran the command line upgrade (php upgrade.php --non-interactive)
Command line upgrade from 3.8.6 (Build: 20201109) (2019111806) to 3.10+ (Build: 20210108) (2020110900.08) completed successfully.
My guess is that it was a plugin that caused the problem, and not the upgrade that happens when I take moodle out of maintenance mode.
Thanks to everyone for the help.

Average of ratings:Useful (1)

Installing and upgrading help

Moodle Update error: Error reading from database