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.p hp: dml_write_exception thrown
•line 722 of /lib/dml/mysqli_native_moo dle_databa se.php: call to moodle_database->query_end ()
•line 5861 of /lib/accesslib.php: call to mysqli_native_moodle_datab ase->execu te()
•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/mo odle/lib/d ml/moodle_ database.p hp:397 Stack trace: #0 /var/www/html/moodle_22/mo odle/lib/d ml/mysqli_ native_moo dle_databa se.php(109 6): moodle_database->query_end (false) #1 /var/www/html/moodle_22/mo odle/lib/d ml/moodle_ database.p hp(1613): mysqli_native_moodle_datab ase->delet e_records_ select('co nfig', 'name = ?', Array) #2 /var/www/html/moodle_22/mo odle/lib/m oodlelib.p hp(1351): moodle_database->delete_re cords('con fig', Array) #3 /var/www/html/moodle_22/mo odle/lib/u pgradelib. php(1184): unset_config('upgraderunni ng') #4 /var/www/html/moodle_22/mo odle/lib/u pgradelib. php(1169): upgrade_finished() #5 [internal function]: upgrade_finished_handler() #6 {main} thrown in /var/www/html/moodle_22/mo odle/lib/d ml/moodle_ database.p hp 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
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.p
•line 722 of /lib/dml/mysqli_native_moo
•line 5861 of /lib/accesslib.php: call to mysqli_native_moodle_datab
•line 5342 of /lib/accesslib.php: call to context_user::build_paths(
•line 1425 of /lib/upgradelib.php: call to context_helper::build_all_
•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/mo
==========================
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
[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%';
show variables like 'innodb_locks_unsafe%';
show variables like '%isolation%';
ASKER
innodb liocks set to Off
tx_isolation set to Repeatable-read
tx_isolation set to Repeatable-read
Okay sorry but I'm out of ideas.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
SHOW FULL PROCESSLIST ;
to see what queries are running.
ASKER
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)
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)
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.