Solved

Mysql Database crashes, need to know why

Posted on 2012-04-06
11
2,254 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:musickmann
Comment Utility
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
Comment Utility
Please do the following within mysql:

show variables like 'innodb_locks_unsafe%';
show variables like '%isolation%';
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:musickmann
Comment Utility
innodb liocks set to Off
tx_isolation set to Repeatable-read
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Okay sorry but I'm out of ideas.
0
 
LVL 24

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
You are probably having deadlocks. use
SHOW FULL PROCESSLIST ;
to see what queries are running.
0
 
LVL 2

Author Comment

by:musickmann
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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 …
SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now