Mysql Database crashes, need to know why

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
LVL 2
musickmannData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
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
musickmannData AnalystAuthor Commented:
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
johanntagleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

musickmannData AnalystAuthor Commented:
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
johanntagleCommented:
Please do the following within mysql:

show variables like 'innodb_locks_unsafe%';
show variables like '%isolation%';
0
musickmannData AnalystAuthor Commented:
innodb liocks set to Off
tx_isolation set to Repeatable-read
0
johanntagleCommented:
Okay sorry but I'm out of ideas.
0
Tomas Helgi JohannssonCommented:
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
musickmannData AnalystAuthor Commented:
I will try that. Having some issues getting it installed, but will work on that. Thanks for the tip
0
pete-mcpeteyCommented:
You are probably having deadlocks. use
SHOW FULL PROCESSLIST ;
to see what queries are running.
0
musickmannData AnalystAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.