Error reading database

Error reading database

by Anthony Green -
Number of replies: 25

Hi,

I have been asked by my organisation to update our version of moodle from 3.1.1 to 3.11, as part of this piece of work I decided to build a replica of our server so I can go through the process without downtime.

So these are the components I have used

  • Moodle 3.1.1
  • PHP 5.6
  • Apache2
  • Mysql 5.7

I have done the install ok however when I try and login or click on course links I am getting a database read error.

Can anyone perhaps point me in the right direction please? I have a linux background but not really done much with databases etc.

Thanks in Advance

Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
The system software look right. Pl. double check against https://docs.moodle.org/dev/Moodle_3.1_release_notes#Server_requirements.

To make a copy, you don't install Moodle anew, rather do a https://docs.moodle.org/31/en/Moodle_migration.

Then to march to to 3.11 plan your route from this chart: http://www.syndrega.ch/blog/#php-and-dbms-compatibility-of-major-moodle-releases. Notice that the system software requirements also have maximum versions.

Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Error reading database

by Anthony Green -
Hi,

Many thanks for your assistance, I have pretty much done all those steps in the Moodle Migration document. The only thing I missed was to change the url's in the database dump itself but I have now done that but still getting same issue unfortunately.

Kind regards
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Do you say, you did https://docs.moodle.org/31/en/Moodle_migration twice, once with https://docs.moodle.org/31/en/Moodle_migration#Restore_the_database_backup_to_the_new_server and once without. Both behave the same?

That is odd. Raise https://docs.moodle.org/31/en/Debugging to its maximum and get a full trace.

Are you sure that $CFG->dbname points to the database you are uploading the database dump?
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Well, did you copy over the moodle folder, the moodledata folder and the database?  Have you created the database user to match the same credentials as used on your initial site and have you checked that your config.php file is all pointing to the right places? 

Need a lot more detail please..

Average of ratings: -
In reply to Emma Richardson

Re: Error reading database

by Anthony Green -
Hi Emma,

Yes I did all of that. Everything is identical.

Kind regards
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

On your current production 3.1 server,

go to Server Admin->Environment - update the component.

Then set the drop down pick list for moodle versions ... each one above 3.1 to see what needs to be done at what stage of the march.

ssh into it and go to code/admin/cli/

There you will see some scripts related to DB ... run the scripts that begin with mysql_.

If those throw any errors or indicate DB for moodle isn't optimal for character set, collaton, Barracuda, compresed or whatever, those scripts will fix if the DB user on config.php file of the site has privs enough to do so.

Then to practice the 'march' ... and you will be doing a 'march' clone your moodle to another server that has same versions of php/mariadb, etc.   On that one you will have to run search and replace to get the change to the FQDN of the test server.

Once getting that up and running, it's a good time to get your 3.1 test server moodle code under git (side load).   Then update the 3.1 to the highest version available for 3.1.

https://docs.moodle.org/400/en/Git_for_Administrators

To me ... doing so will make the march of the moodle a lot easier and less prone to human error with all those 'moving parts' to upgrading the 'traditional' way.

Yes, you will have to at some stage before an upgrade to moodle code upgrade your PHP + extensions.

See Mr. V's charts.

Take your time on the test server and 'git' (yes, that's a pun) it right then duping what you did on test server on production will go fairly smoothly.

Oh yeah ... themes ... and 3rd party addons to your moodle - check all those for compat versions for destination version 3.11.highest.  If you have commercial theme or other commercial plugins, research availability of those for your destination version.

'SoS', Ken

 


Average of ratings: -
In reply to Ken Task

Re: Error reading database

by Anthony Green -
Hi Ken,

Thanks very much for the advice, I have attempted to run the scripts with mysql at the start and they all run ok. I am wondering whether the actual backup of the "live" database has actually worked properly.


Kind regards

Anthony
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

How did you perform the SQL dump?

mysqldump?

simplest:

mysqldump -u dbuser -p'dbpass' dbname > /path/to/save/moodle-db-$(date +%Y%m%d%-H%M%S).sql

Sent you a PM ... please respond there!

'SoS', Ken

Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Just how old is this moodle site?   Was it ever a version 2 in it's history?

Is there a line in config.php for password salting?

https://docs.moodle.org/23/en/Password_salting

Sent you a PM ... please respond there!

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Error reading database

by Anthony Green -
Hi Ken,

No password salting entry, no this is a fresh 3.1.1 install. It is a bitnami image though which runs in Azure.

Tony
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Ahhhh ... a bit more info than shared before.   Factor?

Azure is your virtualization server which can and does host guest operating systems ... like Ubuntu.  Bitnami is an AMP stack not native to Ubuntu - which means Apache/MySQL (or Mariadb) and the PHP updates, etc. are really tied to Bitnami releases.   And, from what I re-call, one can't simply upgrade the moodle code (due to the way Bitnami is installed on a Ubuntu OS - one has to upgrade the entire Bitnami stack (pain in the arse as you need to make a moodle site backup and archive that prior to upgrading Bitnami.

Now the info you've been sharing with me via PM has nothing in it that couldn't be posted here in forums ... the more 'eyes' on a problem, the better the chance someone will jump in with suggestions ... BTW, my 'vision' isn't 20/20 anymore! sad

Suggest posting here in these forums ALL of the PM's you sent to me! smile

'SoS', Ken


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

Re: Error reading database

by Anthony Green -
Hi Ken,

Yeah bitnami is a pain, not sure why they went this route but hey ho. I am really loathed to do anything with our prod server until I have a plan which works hence the setup of this server.

So just for other peoples benefit it seems to be that I am not getting a full backup of the prod database to restore on to the new server.

These are the steps

1, Backup prod DB
mysqldump -u myusername -p'mypassword' -C -Q -e --create-options mydatabasename > mybackup
2, Change urls in mybackup with sed
3, Create DB on test server with identical name and character set
4, Restore DB
5, Start apache2 - this is where the fun starts everything starts up however I am getting read error on database
6, I run mysql scripts which live in code/admin/cli and all are ok however I am seeing only about 334 tables on the test server yet there is 388 on the prod server
7, Took a comparison of file sizes of both DB's
-rw-r--r-- 1 root root 117469176 Apr 29 13:46 newdump TEST DB
-rw-r--r-- 1 root root 117469854 Apr 29 15:18 testdump PROD DB
8, On running a diff between the 2 files I can see tables are missing which is confirmed when I run the check_database_schema.php
-------------------------------------------------------------------------------
upgrade_log
* table is missing
-------------------------------------------------------------------------------
user_enrolments
* table is missing
-------------------------------------------------------------------------------
user
* table is missing
-------------------------------------------------------------------------------
user_preferences
* table is missing
-------------------------------------------------------------------------------
user_lastaccess
* table is missing
-------------------------------------------------------------------------------
user_password_history
* table is missing
-------------------------------------------------------------------------------
user_info_field
* table is missing
-------------------------------------------------------------------------------
user_info_category
* table is missing
-------------------------------------------------------------------------------
user_info_data
* table is missing
-------------------------------------------------------------------------------
tag_correlation
* table is missing
-------------------------------------------------------------------------------
tag_instance
* table is missing
-------------------------------------------------------------------------------
user_private_key
* table is missing
-------------------------------------------------------------------------------
user_devices
* table is missing
-------------------------------------------------------------------------------
user_password_resets
* table is missing
-------------------------------------------------------------------------------
task_scheduled
* table is missing
-------------------------------------------------------------------------------
task_adhoc
* table is missing
-------------------------------------------------------------------------------
url
* table is missing
-------------------------------------------------------------------------------
wiki
* table is missing
-------------------------------------------------------------------------------
wiki_subwikis
* table is missing
-------------------------------------------------------------------------------
wiki_pages
* table is missing
-------------------------------------------------------------------------------
wiki_versions
* table is missing
-------------------------------------------------------------------------------
wiki_synonyms
* table is missing
-------------------------------------------------------------------------------
wiki_links
* table is missing
-------------------------------------------------------------------------------
wiki_locks
* table is missing
-------------------------------------------------------------------------------
workshop
* table is missing
-------------------------------------------------------------------------------
workshop_submissions
* table is missing
-------------------------------------------------------------------------------
workshop_assessments
* table is missing
-------------------------------------------------------------------------------
workshop_grades
* table is missing
-------------------------------------------------------------------------------
workshop_aggregations
* table is missing
-------------------------------------------------------------------------------
workshop_old
* table is missing
-------------------------------------------------------------------------------
workshop_elements_old
* table is missing
-------------------------------------------------------------------------------
workshop_rubrics_old
* table is missing
-------------------------------------------------------------------------------
workshop_submissions_old
* table is missing
-------------------------------------------------------------------------------
workshop_assessments_old
* table is missing

Any help would really be appreciated, not sure what I am doing wrong.
Average of ratings: Useful (1)
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

While not the recommended way of backing up a database, in this case one may not have a choice ... sad

With Bitnami stopped on production server ... that means apache and mysqld are stopped.   Find the directory in bitnami's collection of files/folder that houses the DB.  On a 'normal' linux system, that would be /var/lib/mysql/ don't know about bitnami.

Make a tar ball of the db directory and save outside of that area to /root or some place where you could save it and access it with other xfer tools ... assuming the DB name is 'moodle' you would see a 'moodle' directory.   Contained in there are the actual files of all the tables - each table having a .frm file and a .ibd file.  Might check to see if those files actually exist for the tables reported missing in sql dump first.

Then use that tar ball to place the DB on your test system.

You might have to reset ownerships/permissions on xfered/utarred folder files on test system prior to launching mysqld on test system.

-rw-r-----. 1 mysql mysql     9306 Jun 22  2020 mdl_workshop_submissions.frm
-rw-r-----. 1 mysql mysql    73728 Jun 22  2020 mdl_workshop_submissions.ibd

You are getting to the point of desparation now! :|

And a thought .. seems to me I re-call one could not upgrade the app inside a bitnami by native means ... like using git on moodle code directory.   If one wanted to upgrade the app (in this case moodle), bitnami recommended the entire stack be upgraded.

Unless you plan is to get customer OFF of bitnami.

'SoS', Ken


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

Re: Error reading database

by Anthony Green -
Hi Ken,

We are planning to "standardise" the moodle stack and get away from the horrible bitnami version of the installation to make life easier going forward. I will take a look at doing the tarball backup as you mentioned,

It really shouldn't be this difficult should it?

Tony
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Anthony Green -
This is interesting

Prod server
du -sh bitnami_moodle/
602M bitnami_moodle/

Test server
du -sh bitnami_moodle/
431M bitnami_moodle/

So the backup obviously isn't working correctly.......
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Do you have a check_database_schema.php script in the bitnami code?

What is contained in 'bitnami_moodle/'?  Is that the DB directory?

Minimally, all one needs is moodledata/filedir and a DB dump if you know the version of the moodle.   On test server, acquire the code via git (highest of that version), manually install all the plugins that exist on bitnami box ahead of cranking it up.  Manually create the moodledata directory and untar the filedir in there.

Run command line only upgrade.php script since you know code is new.  That should result in a update of that version to highest in that series with new caches.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Error reading database

by Anthony Green -
Hi Ken,

Yes I have a check_database_schema.php that runs perfectly on the bitnami prod server.

Yes bitnami_moodle is the DB directory you can see there is a whole chunk of data which is missing. I did try and just restore the database from the tarball and within mysql I could see the new database however moodle wasn't happy and I got the error below

Config table does not contain version, can not continue, sorry.


error/debug/missingconfigversion
Jump to:navigation, search

The most likely cause of this error is that the 'version' key is missing from your configuration dictionary.

Not easy is it?

Regards
Tony
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Ok, you've 'been ask to' suggest there is/was another person who admin'd the moodle and or server before you.   That person(s) still around?

It is/was possible that person(s) actually deleted core modules in Moodle rather than just hiding them. sad

check: /admin/plugins.php

as well as additional plugins: /admin/plugins.php?updatesonly=0&contribonly=1

and

Manage Activities admin/modules.php

The number you see in chart for above link is the number of modules used in any course and clicking number performs a search of courses and should show a listing of those courses.

Googling for "Config table does not contain version moodle" hits a bunch ... here's one hit:

https://moodle.org/mod/forum/discuss.php?d=199501

which is now locked and am not sure what's there applies!

There is an admin tool ... admin/tool/xmldb/

Home -> Site administration -> Development -> XMLDB editor

In the tabbed links in above tool, there is a link to 'Check Indexes".

For every table listed there are also various links:

[Create][Load][Edit][Save][Doc][XML][Revert][Unload][Delete]

It's been years, but do re-call using this tool to rebuild a missing table and thus it's index once - got table back with no data.  Have no idea what it will do in your case!

One messed up server ... they usually don't get in this condition cept in case where there was really 0 admin/maintenance, etc.  Just installing Bitnami is kinda an indication of that admin person's desire to do none of that! (wishful thinking).

Can you backup courses from command line and save those to a directory outside of moodledata/filedir/

Can/could you install a CLI only tool for Moodle called moosh?

You might be looking at rebuilding via course restores to a fresh moodle.

'SoS', Ken


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

Re: Error reading database

by Anthony Green -
Hi Ken,

So I have now got some progress. Noticed when I took a mysqldump with this command there was an error generated

mysqldump --single-transaction -u moodle_user -p -C -Q -e --create-options bitnami_moodle > /tmp/moodle-database.sql

moodle Error: Couldn't read status information for table mdl_tag_correlation ()

So run again like this

mysqldump --single-transaction --ignore-table=bitnami_moodle.mdl_tag_correlation -u moodle_user -p -C -Q -e --create-options bitnami_moodle > /tmp/moodle-database.sql

This worked with no errors.

Restored on to my test server and I can successfully log into moodle.

Time for a rest before I figure out how I am going to upgrade it now smile
Average of ratings: Useful (2)
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Congrats!   Yeah, attention to detail is required! smile

Sounds like DB and perhaps more than one table is/was in need of repair before dumping.

If you could install MySQLTuner (a perl script - and outside of Moodle) on the bitnami box then run with superuser creds, it has a notice about tables that are in need of optimizing.   Would imagine that site hasn't had any care/maintenance in a very long time and there could be multiple tables needing such repair.

tuner clip with no issues:

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 3.5G (Tables: 901)
[OK] Total fragmented tables: 0

If there were fragmented tables, at the bottom of the realtime report would be a recommendation to optimize those tables found fragmented ... even shows the command to use.

Have a K12 site that does a lot of duplicating quizzes (making copies) and removing resources of courses.   Plus I have set the retention of data in the 'who done it' log to 'trim' which means rows are removed.   That table used to get very, very large!   About once every 2 weeks, a tuner run will show some tables that need optimizing.  My version of MySQL really can't do 'optimize' so it backs up then restores those tables thus optimizing them.

Keeps the DB 'clean and mean' ... thus site faster!

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Error reading database

by Anthony Green -
Thanks for all your help Ken, I have managed to go from moodle ver 3.1.1 to 3.6.10 and now on to 3.11.6+ and have updated the plugins.

Next job will be getting those tables sorted but I now have a process in place which works.

Thanks all for your help.
Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Welcome!  And congrats! 

Again ... encourage the install of MySQL Tuner.   It will come in handy in future ... keeping the DB 'clean and lean' = 'mean' which is = better performance. smile

'SoS', Ken

Average of ratings: -
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

Since Bitnami is a 'one off stack', decided to see what Bitnami forums/docs said ...

Here's what I found ...

https://docs.bitnami.com/aws/apps/moodle/administration/backup-restore/
NOTE: If you want to create only a database backup, refer to these instructions for MySQL/MariaDB.

https://docs.bitnami.com/aws/apps/moodle/administration/backup-restore-mysql-mariadb/

The mysqldump commands shown aren't the typical commands ... why am I not surprised! :|

'SoS', Ken


Average of ratings: Useful (1)
In reply to Anthony Green

Re: Error reading database

by Ken Task -
Picture of Particularly helpful Moodlers

What, specifically, does this 'database read error' say?

Turn on debugging all the way up to developer for full and techie info.

Without some very specific and technical info, it's kinda like saying the 'thingy is broke!' and then asking  'Help me fix the thingy!' smile

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Error reading database

by Anthony Green -
Hi Ken,

I have sent you some info in the chat, many thanks for the assist.

Tony
Average of ratings: -