Moodle 3. 3. database issues

Moodle 3. 3. database issues

by seçkin girgin -
Number of replies: 26

I have been receiving database errors on occassion when I do the following;

  • turn on editing in courses
  • edit settings
  • duplicate quizzes

I experience database errors in such cases in a random fashion. When I have error reading from database error I simply refresh the page and things work perfectly.

Apart from database error issues I can't import data from other courses and also I cannot delete my courses. The page goes unresponsive when I try to delete my courses. When I am trying to import data from another course then I have id missing or parameter missing error. 

I am not a developer and I can't risk playing with server settings or PHP versions etc. I search the internet several times trying to figure out why the problem might be occuring but so far no luck.

Anybody who has experienced similar problems?

Average of ratings: -
In reply to seçkin girgin

Re: Moodle 3. 3. database issues

by Conn Warwicker -
Picture of Core developers Picture of Plugin developers

Can you turn debugging on and post some of the error messages?

Average of ratings: -
In reply to Conn Warwicker

Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

I will do that soonish and let you know. Thanks a lot

Average of ratings: -
In reply to seçkin girgin

Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

This is what I get when trying to import data from another course. 


And the second image shows what  I get on occasion. When I refresh page it runs smoothly though.


When I try to delete a course it just gets stuck. The page goes unsresponsive. Please refer to the third image

Attachment Screen Shot 2018-10-11 at 12.44.24.png
Attachment Screen Shot 2018-10-11 at 12.46.13.png
Attachment Screen Shot 2018-10-11 at 12.50.22.png
Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Moodle 3. 3. database issues

by Colin Fraser -
Picture of Documentation writers Picture of Testers

Server/hardware... for sure. 2GB of RAM is just not enough, of course, I can't read Turkish but 2GB RAM suggests that is your problem. I would suggest for a 4core CPU, running Moodle, I would think at least 16GB of RAM.

Average of ratings: Useful (1)
In reply to Colin Fraser

Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

Thank you for your help. I am going to talk to my provider to see what ı can do about it. Hope this will work things out. But Colin do you think that is the reason why I cannot delete a course or I get database errors? I need to be 100 percent sure and you sound like it smile That'll cost me some money.

Average of ratings: -
In reply to seçkin girgin

Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

This is what I get when I tried to delete a course

Attachment Screen Shot 2018-10-11 at 16.26.23.png
Average of ratings: -
In reply to seçkin girgin

Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

This is my database error:


Debug info: MySQL server has gone away
SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM mdlxi_sessions WHERE sid = ?
[array (
0 => '20613525f4ad7d766f13e762e0738554',
)]
Error code: dmlreadexception
Stack trace:
  • line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1558 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
  • line 1530 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
  • line 1509 of /lib/dml/moodle_database.php: call to moodle_database->get_record_select()
  • line 286 of /lib/classes/session/manager.php: call to moodle_database->get_record()
  • line 82 of /lib/classes/session/manager.php: call to core\session\manager::initialise_user_session()
  • line 785 of /lib/setup.php: call to core\session\manager::start()
  • line 26 of /config.php: call to require_once()
  • line 30 of /course/format/grid/editimage.php: call to require_once()

Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
If you have the money, you can throw them at more resources and come back. I would think 2 GB are enough for Moodle to take off. (We are not talking about a loaded situation, you are the only user when this happens, I suppose.)

"MySQL server has gone away" means the database program (MySQL) is not responsive. If this is a VPS and otherwise not utilized, your provider has overbooked their server. i.e. other customers are consuming power. Yes, talk to the provider.

If it is a shared server, a possibility is that they have upper limits on database operations. Again, talk to the provider.
Average of ratings: Useful (1)
In reply to seçkin girgin

Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Ken Task -
Picture of Particularly helpful Moodlers

Think this is the issue ... "MySQL server has gone away" ... in the past that has meant/could mean that DB server settings for max_packet_allowed is too low and needs to be increased.

See section on https://docs.moodle.org/35/en/MySQL

Installing MySQL ... although in your case you are not installing, just tweaking the config of DB server.

If you cannot make that tweak yourself then talk to service provider.

'spirit of sharing', Ken


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

Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

Thanks a million Ken. However, I have just learnt that max_allowed_packet is 1gb which should be way too enough for Moodle I guess. 

Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Ken Task -
Picture of Particularly helpful Moodlers

Just to clarify ... 'just learnt' means you saw the config file for MySQL and max_allowed_packets is set to 1G or did technical support for your hosting told you that.

Do you have ssh access to your server?   Can you use a mysql client on your server?   Might be able to do this with PHPMyAdmin if that's installed ...

Do this query:

mysql> show variables like 'max_allowed_packet';

If you are on a shared host, more than likely the database server is also shared.  So provider might have that variable set to 1G but that's to handle **all** customers using that DB server.

Your first posting said 'database errors on occassion' ... nothing consistent.  So if another customer on the same server is hosting a moodle or other app and doing something that involves 'packets' of data (big chunks of data) ... it's the total ... the max ... for **anyone** using that DB server.

Servers are simply under powered.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Colin Fraser -
Picture of Documentation writers Picture of Testers

@Ken, "Servers are simply underpowered."  The drawbacks of lower end virtualization I suspect, Ken. 

The issue is really the amount of RAM available, I suggest. If you have a 2GB RAM limit, and access to only one core of the CPU, then it's going to run slow, and under pressure, it will slow significantly. When I am running VMs I allocate 2 cores and a minimum of 4GB of RAM, performance is not that fast it couldn't be better, but it is not that slow that I can make a coffee between loading an app and sitting down to use it.  The i3 CPUs were all 4 core I believe, so you would likely be sharing the CPU with at least 2 other users and one core for the bare-metal hypervisor, or whatever system your provider uses. That's as far as I have pushed VMs, and there were significant performance issues, so I'm not willing to try any further. I had a Win10, a MacOS and a Ubuntu Linux running on a Win7 host, using VMWare Workstation Pro, an i3 CPU with 2GB RAM for each VM. The host machine has 16GB RAM for the host. The results were....mmmmm.... discouraging. Running a memory hog like PHP/MySQL is not likely to work well, I believe. 

As far as the errors are concerned, there may be some software issue, but I suspect that memory allocation is too slow and Moodle is outrunning it, or perhaps, allocation and releases are happening out of sequence, so errors are being thrown. But these are just guesses that may or may not be helpful at all. Ask your provider if you can upgrade your memory to 4GB for a month or so, see what happens.  

Average of ratings: Useful (2)
In reply to Colin Fraser

Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

The issue is it was running smooth even on a slower server. The problem arised some months ago. And I know the hosting company. I have seen the reports that I was nowhere near pushing the limits of my plan. Because I don't have many active users. I believe there should be something wrong with my script but I haven't been able to locate it. At some point the system gets stuck for most probably a small reason. Btw so far your suggestions have been so useful and at least I could communicate with my provider using suitable terminology. smile 

I guess there's no way we can detect and fix any script issues? Or do most people simply erase everything and have a fresh start. But even if I do that, I will have to use my old database and Mysql which means I won't get rid of what I have been experiencing. I feel like kinda desperate now. 

Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Ken Task -
Picture of Particularly helpful Moodlers

Is your plan one where you are the only customer on a server?  If not, you are sharing resources - which includes DB server.  

The error you've shared in here clearly says 'Server has gone away'.    If I experimented (and I have before) with a sandbox server and un-set the max_allowed_packet ... ie, put it back to defaults ... would get a similar error ... 'Server gone away ... when running a backup.

So let's do this ... since you are stuck in decision making now ... and this is a good idea anyway if your server is experiencing problems ... let's see if you can make full user backups of the courses you have.

*IF* they succeed, download the backups.

Everyone who has responded here in this thread has said the same thing ... 2Gig memory not enough.

No, folks don't resolve such issues by wiping out the site and installing fresh.  If that's what you choose to do, then make sure you have course backups.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

Ok white flag smile. I am going to upgrade my account temporarily and see if it the server capacity causing the issue. I will test it for a while. If not resolved then I will start from scratch.  Thanks everybody for your help and support.

Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Ken Task -
Picture of Particularly helpful Moodlers

Didn't mean to brow beat ya into submission! smile ... but ... you get those notifications from your Moodle, don't ya?   The ones that say a new version is available ... and 'strongly encouraged'.    Tech moves forward ... onwards/upwards ... eg. 'more' ... and because it does 'more' ... requires 'more'.  Go with the flow! smile

Now upgrading your account ... with your provider ... does that mean the DB server will be on your machine where you can admin it ... ie, set variables (there are multiple) for the DB ... not only max_allowed_packets but other to be in compliance with 3.3.x and ready for upgrades to 3.4 and 3.5 ... right?

So after they upgrade your account, first thing, try what you were doing to see if the error appears again.

If it does, fix it.  That error ... 'Server gone away ...' is fixable.

Yes, one pays more ... but hey ... at least the software is free!!!!  If you are willing to role up your sleeves a little and learn how to make those tweaks it's only your time.   How much do you pay yourself an hour?  If your courses are commercial, students will be happier ... best advertising in the world is a 'satisfied customer'.

Anhooo ,,, hang in there ... things could be worse! smile

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

So here's the thing. A developer will look into the issue in detail and I hope he will come up with solutions. I will share the results, findings and the solutions of course. I am grateful to all who have helped me sort things out. Thanks a lot.

Average of ratings: -
In reply to Ken Task

Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Ken

You said:
> Everyone who has responded here in this thread has said the same thing ... 2Gig memory not enough.

Then you haven't read my post above, where i wrote:
>>> I would think 2 GB are enough for Moodle to take off...
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Ynt: Re: Ynt: Re: Ynt: Re: Ynt: Re: Moodle 3. 3. database issues

by Ken Task -
Picture of Particularly helpful Moodlers

Conflict?

"Obviously your server can not handle Moodle, a) either because it is too weak b) or it is misconfigured."

OP said on certain processes/usage server would 'mis-behave' ... even listed which/what.  So installation is semi working.   In the past, had investigated something similar and found that in some .php files Moodle was attempting to get 128M more memory before executing ... that was programmed into the file by a variable.  Strange things happen when max memory is reached or exeeded or SWAP space is used.

Anyhoo, OP has a 'developer' looking into it ... let's hope 'developer' can 'weave some magic' for  the OP. smile

'spirit of sharing', Ken


Average of ratings: -
In reply to seçkin girgin

Re: Moodle 3. 3. database issues

by Jerry Lau -

I get the similar weird situations as well.. the application is so slow when users do quizzes at the same time (say 30 of them) that it almost makes moodle useless.

the hardware is pretty good with 32 GB ram for app and db server (MySQl 5.7.21 - community edition though) with 64 GB of rams... tweaked the .cfn database server settings and does not seem to make a difference.


Average of ratings: -
In reply to seçkin girgin

Re: Moodle 3. 3. database issues

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

Obviously your server can not handle Moodle, a) either because it is too weak b) or it is misconfigured. If it is a) you can not do anything other than ordering more resources from the provider. But you should first know whether it is b). Otherwise you order those resources for nothing!

To investigate it, the minimum you could provide is the details of your hosting package. The kind of details needed are explained here: https://moodle.org/mod/forum/post.php?forum=94.
Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

I think my server can handle Moodle. However its configuration might be the case. I should focus on how to configure it and that's beyond my expertise sad

Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Moodle 3. 3. database issues

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> I think my server can handle Moodle.

You _think_? Have you looked at the specs?

> However its configuration might be the case.

_might_. You can be more certain if you follow the link I gave earlier and compare the advice give with your settings.

> I should focus on how to configure it and that's beyond my expertise

What is the question?
Average of ratings: -
In reply to Visvanath Ratnaweera

Ynt: Re: Moodle 3. 3. database issues

by seçkin girgin -

the link you provided me didn't seem to work or maybe I did it wrong. I am sharing the specs of the server. It should be more than enough. Besides Moodle was working fine for like a year before these issues showed up.

Attachment Screen Shot 2018-10-11 at 12.37.37.png
Average of ratings: -
In reply to seçkin girgin

Re: Ynt: Re: Moodle 3. 3. database issues

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

3 vCPUs and 2 GB RAM should be enough to get a Moodle server going on processing side. How about the disk storage? Often the (virtualized) file system is the bottle neck of VPSes.

> the link you provided me didn't seem to work or maybe I did it wrong.

So you haven't taken the trouble to read the performance docs. Try again: https://moodle.org/mod/forum/post.php?forum=94.

> Besides Moodle was working fine for like a year before these issues showed up.

What has happened in between? Are you 100% sure that the provider hasn't done anything. Not even a system upgrade? What have you changed in Moodle as the 'admin'? Switched on the Theme Designer Mode?
sad

Average of ratings: -