Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mysql Database crashes, need to know why

Posted on 2012-04-06
11
Medium Priority
?
2,382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 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