Solved

Mysql Database crashes, need to know why

Posted on 2012-04-06
11
2,281 Views
Last Modified: 2012-06-12
I'm managing a Moodle installation that occasionally has a database become unresponsive. Restarting the service fixes the issue, but I've never been able to determine what's going on and why it crashes in the first place.

Well, when attempting an upgrade to the latest version of Moodle, I've been able to replicate the database crash. I'm not entirely sure that it's the same cause as the other crashes, but I could use some help in figuring out what happened to cause the mysql database to stop responding. In the middle of the upgrade I get this error:

========================================================
Error writing to database

Debug info: Lost connection to MySQL server during query
UPDATE mdl_context
 SET depth = 2,
 path = CONCAT('/1/', id)
 WHERE contextlevel=30
[array (
)]

Stack trace: •line 397 of /lib/dml/moodle_database.php: dml_write_exception thrown
•line 722 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
•line 5861 of /lib/accesslib.php: call to mysqli_native_moodle_database->execute()
•line 5342 of /lib/accesslib.php: call to context_user::build_paths()
•line 1425 of /lib/upgradelib.php: call to context_helper::build_all_paths()
•line 236 of /admin/index.php: call to upgrade_core()

Fatal error: Uncaught exception 'dml_write_exception' with message 'Error writing to database' in /var/www/html/moodle_22/moodle/lib/dml/moodle_database.php:397 Stack trace: #0 /var/www/html/moodle_22/moodle/lib/dml/mysqli_native_moodle_database.php(1096): moodle_database->query_end(false) #1 /var/www/html/moodle_22/moodle/lib/dml/moodle_database.php(1613): mysqli_native_moodle_database->delete_records_select('config', 'name = ?', Array) #2 /var/www/html/moodle_22/moodle/lib/moodlelib.php(1351): moodle_database->delete_records('config', Array) #3 /var/www/html/moodle_22/moodle/lib/upgradelib.php(1184): unset_config('upgraderunning') #4 /var/www/html/moodle_22/moodle/lib/upgradelib.php(1169): upgrade_finished() #5 [internal function]: upgrade_finished_handler() #6 {main} thrown in /var/www/html/moodle_22/moodle/lib/dml/moodle_database.php on line 397
============================================

The mysql server status is:
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.36, for pc-linux-gnu (i686) using readline 5.1

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.36-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 min 10 sec

Threads: 1  Questions: 56  Slow queries: 0  Opens: 28  Flush tables: 1  Open tables: 21  Queries per seco                               nd avg: 0.800
--------------


Since I'm not very familiar with managing mysql from a command line, I'm just looking at this point for some pointers of where to look to identify the issue. Is it too many connections (that's what I thought it was when it crashed previously, but I'm the only one on it now, so that's probably not it), or some other issue?

Any direction on where to look at logs would be helpful. I've got the error log, but It's not something I'm too adept at reading:
mysqlerror.log
0
Comment
Question by:musickmann
11 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37818308
From your error log:

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=2
max_threads=300
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 662893 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Open in new window


Does the host machine have enough memory to cover the 660M+?  If not you need to decrease some numbers for the variables mentioned in your my.cnf then restart the database.  Or, add more memory.
0
 
LVL 2

Author Comment

by:musickmann
ID: 37818320
The server has 4GB of RAM installed
free -m shows:
             total       used       free     shared    buffers     cached
Mem:          4052       1803       2249          0         99       1603
-/+ buffers/cache:         99       3952
Swap:         4094          0       4094

So I would think there's enough? The max connections was upped becuase the moodle installation handles 10k registered users and about 2000 concurrent at any given time.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 250 total points
ID: 37818457
Can you check your my.cnf or set variables if you have transaction isolation level set to read-committed" or you have "innodb_locks_unsafe_for_binlog" ?  You seem to be hitting this bug: http://bugs.mysql.com/bug.php?id=45357

It was fixed by release 5.1.37.  Workaround if you cannot upgrade yet is to not have those parameters set.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 2

Author Comment

by:musickmann
ID: 37818941
The my.cnf file consists of:

[mysqld]
general_log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
max_connections = 300

Would these variables be set some other place?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37818960
Please do the following within mysql:

show variables like 'innodb_locks_unsafe%';
show variables like '%isolation%';
0
 
LVL 2

Author Comment

by:musickmann
ID: 37818969
innodb liocks set to Off
tx_isolation set to Repeatable-read
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37818982
Okay sorry but I'm out of ideas.
0
 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 250 total points
ID: 37819119
Hi!

If it is performance issue that are causing this then I suggest you download the
mysqltuner script, install it and run and take the suggested actions.
https://github.com/rackerhacker/MySQLTuner-perl

Regards,
    Tomas Helgi
0
 
LVL 2

Author Comment

by:musickmann
ID: 37819266
I will try that. Having some issues getting it installed, but will work on that. Thanks for the tip
0
 
LVL 3

Expert Comment

by:pete-mcpetey
ID: 37819395
You are probably having deadlocks. use
SHOW FULL PROCESSLIST ;
to see what queries are running.
0
 
LVL 2

Author Comment

by:musickmann
ID: 37819445
Hmmm... interesting.
So I recreated the crash by attempting to upgrade moodle again, same error about not writing to database because connection lost.
Now, the original installation is still running
The Moodle 2 test bed we created is not running
And the Moodle 2.2 test bed we created is not running. I cloned the Moodle 2 installation for the upgrade, and all three instances are running with separate databases.
Moth of the Moodle 2 installations show
Database connection failed (a generic Moodle error message)
But the original installation is running fine.

The process list doesn't seem to show anything at all:

mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  4 | root | localhost | NULL | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Setting up Secure Ubuntu server on VMware 1.      Insert the Ubuntu Server distribution CD or attach the ISO of the CD which is in the “Datastore”. Note that it is important to install the x64 edition on servers, not the X86 editions. 2.      Power on th…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question