phpMyAdmin crashing the MySQL host server

Question

I have encountered this problem a couple of times, in the last few days. So, it happens occasionally. I have setup mysql on a remote machine, and there is a java program on another machine querying the database to read and write records every few seconds.

I am using phpMyAdmin to administer my database. And, at times, after running some SQL query, the mysql server stops responding. Even the pinging the host machine doesn't succeed. And, I have to ask someone with physical access to the machine to boot it up again.

I checked for log files but couldn't find them in the mysql directory. Is logging disabled by default? What is missing here? And, how can I go about troubleshooting this?

EDIT: I was able to ping the server after some while. So, the server must have been temporarily busy. It's not a specific query but things like re-ordering the data of a table serially under the browse tab.

Answer 1

I'd highly recommend using MySQL's own GUI tools for database management, for a vriety of reasons:

• They have full support for InnoDB tables, including Foreign Key management
• You can use database-level security to make sure only you get into your data (unlike phpMyAdmin, which at best can only be root access installed behind a .htaccess password)
• It is official and supported. No extra binaries run on the server, so you run no risk of it crashing and taking the server down with it (unless your query itself is locking it...)

Answer 2

Use a mysqlclient to make a connection and keep it open. I personally use the mysql from the commandline.

If the server becomes unresponsive execute

SHOW PROCESSLIST;

It will list all mysql processes and will show how long queries are waiting/executing.

Optionally use the KILL statement to terminate the query that locking the tables.

KILL $pid