4

I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB connections and libwebsockets for both HTTP and websockets interaction. The previous version was written in C, no locks happened. The chat server has a websocket regime and the refresh regime. It also uses the database for user authentication and loading/saving of settings.

Now when I run this server, after a certain period, the database gets flooded with connections with different queries, all with the status "Waiting for table metadata lock". When I kill the chat, the queries start being processed.

I am 100% sure, there are no ALTER TABLE, CREATE TABLE or similar queries being executed at any time, so they are not the reason.

So I don't understand what in this situation may be the reason for database to hang, and how can I trace and solve it. I would appreciate any help or ideas! Thanks ahead!

  • 1
    Do you ever run TRUNCATE TABLE during this period ? – RolandoMySQLDBA Jul 25 '14 at 2:29
  • Nope, this query is also not used... – user14679 Jul 25 '14 at 6:23
  • 1
    Can you use the general log/slow log with long-query-time=0 to trace every single query sent to the server? Maybe you are sending anything like an implicit lock tables (for example, it happens when you set autocommit=0). – jynus Jul 25 '14 at 8:09
  • autocommit is indeed 1, I will try to make a run with logging, though restarting the chat everytime and hanging the system is not good in my case, that's why I am asking for help, maybe someone has the ideas or had a similar situation... – user14679 Jul 28 '14 at 8:21
1

Thanks to jynus for advising on working with general log, the problem has been solved!

As in most of the cases, the error was quite simple, here is the description:

On certain occasions, a function was invoked, that performed the following:

  1. Lock several tables in the DB

  2. Do its queries

  3. Send a message to chat with the results

  4. Unlock tables

The message to the chat was sent via a socket with connection timeout of 10 seconds. Sometimes, after step 1, at step 3, the chat was busy logging on the new user, and was trying to access the tables, that are locked. So the loop was closed - the chat was waiting for the unlock and the function was waiting for processing its connection request.

10 seconds was enough for the other requests to reach the max_connections limit and lock the database.

The only thing that puzzles is me, is why the state of all queries was "Waiting for table metadata lock".

After switching the places of steps 3 and 4, the problem was no more.

Thanks for help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.