Link to home
Start Free TrialLog in
Avatar of musickmann
musickmann

asked on

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
Avatar of johanntagle
johanntagle
Flag of Philippines image

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.
Avatar of musickmann
musickmann

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Please do the following within mysql:

show variables like 'innodb_locks_unsafe%';
show variables like '%isolation%';
innodb liocks set to Off
tx_isolation set to Repeatable-read
Okay sorry but I'm out of ideas.
SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I will try that. Having some issues getting it installed, but will work on that. Thanks for the tip
You are probably having deadlocks. use
SHOW FULL PROCESSLIST ;
to see what queries are running.
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)