远程MySQL服务器频繁启停致Django聊天应用异常求助

开发了一款基于Django的聊天应用，使用WebSocket（Daphne）实现实时聊天功能，MySQL作为后端数据库。近期遇到以下问题：

• MySQL服务器频繁自动启停，导致应用在保存用户发送的消息、加载聊天记录时出现django.db.utils.OperationalError: (2013, 'Lost connection to MySQL server during query')错误
• 更新用户资料、存储账户信息等其他数据库操作功能正常
• 执行sudo shutdown -r now重启服务器后问题暂时解决，但不久后会复发

以下是相关日志及代码信息，请求协助排查解决：

相关日志 #

MySQL服务日志（执行sudo journalctl -u mysql输出） #

gamedeveloper@animechatapp:~$ sudo journalctl -u mysql
Sep 20 13:18:17 animechatapp systemd[1]: Starting mysql.service - MySQL Community Server...
Sep 20 13:18:18 animechatapp systemd[1]: Started mysql.service - MySQL Community Server.
Oct 13 08:28:45 animechatapp systemd[1]: mysql.service: Deactivated successfully.
Oct 13 08:28:45 animechatapp systemd[1]: Stopped mysql.service - MySQL Community Server.
Oct 13 08:28:45 animechatapp systemd[1]: mysql.service: Consumed 1h 32min 45.508s CPU time.
Oct 13 08:28:45 animechatapp systemd[1]: Starting mysql.service - MySQL Community Server...
Oct 13 08:28:46 animechatapp systemd[1]: Started mysql.service - MySQL Community Server.
Oct 13 08:47:00 animechatapp systemd[1]: Stopping mysql.service - MySQL Community Server...
Oct 13 08:47:02 animechatapp systemd[1]: mysql.service: Deactivated successfully.
Oct 13 08:47:02 animechatapp systemd[1]: Stopped mysql.service - MySQL Community Server.
Oct 13 08:47:02 animechatapp systemd[1]: mysql.service: Consumed 4.195s CPU time.
-- Boot 4b053cef278948d2b1c15ebc9add886d --
Oct 13 08:48:23 animechatapp systemd[1]: Starting mysql.service - MySQL Community Server...
Oct 13 08:48:27 animechatapp systemd[1]: Started mysql.service - MySQL Community Server.
Oct 17 06:50:09 animechatapp systemd[1]: Stopping mysql.service - MySQL Community Server...
Oct 17 06:50:11 animechatapp systemd[1]: mysql.service: Deactivated successfully.
Oct 17 06:50:11 animechatapp systemd[1]: Stopped mysql.service - MySQL Community Server.
Oct 17 06:50:11 animechatapp systemd[1]: mysql.service: Consumed 17min 43.573s CPU time.
-- Boot 663ec237c1424bd295c0df89dcc4f29b --

Daphne服务状态日志（执行sudo systemctl status daphne.service输出） #

gamedeveloper@animechatapp:~$ sudo systemctl status daphne.service
● daphne.service - Daphne ASGI Server for Your Django Project
     Loaded: loaded (/etc/systemd/system/daphne.service; enabled; preset: enabled)
     Active: active (running) since Tue 2023-10-31 08:46:08 UTC; 1 day 21h ago
   Main PID: 520 (daphne)
      Tasks: 4 (limit: 994)
     Memory: 37.8M
        CPU: 33.250s
     CGroup: /system.slice/daphne.service
             └─520 /home/gamedeveloper/venv/bin/python /home/gamedeveloper/venv/bin/daphne -b 0.0.0.0 -p 8001 anime_chat_app.asgi:application

Nov 02 06:13:06 animechatapp daphne[520]:     return self.cursor.execute(query, args)
Nov 02 06:13:06 animechatapp daphne[520]:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Nov 02 06:13:06 animechatapp daphne[520]:   File "/home/gamedeveloper/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 179, in execute
Nov 02 06:13:06 animechatapp daphne[520]:     res = self._query(mogrified_query)
Nov 02 06:13:06 animechatapp daphne[520]:           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Nov 02 06:13:06 animechatapp daphne[520]:   File "/home/gamedeveloper/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 330, in _query
Nov 02 06:13:06 animechatapp daphne[520]:     db.query(q)
Nov 02 06:13:06 animechatapp daphne[520]:   File "/home/gamedeveloper/venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 255, in query
Nov 02 06:13:06 animechatapp daphne[520]:     _mysql.connection.query(self, query)
Nov 02 06:13:06 animechatapp daphne[520]: django.db.utils.OperationalError: (2013, 'Lost connection to MySQL server during query')

应用配置与代码 #

Django数据库配置（settings.py） #

DATABASES = {
        'default':{
                'ENGINE':'django.db.backends.mysql',
                'NAME':'anime_chat_app',
                'USER':'myuser',
                'PASSWORD':'my_password',
                'HOST':'the_host',
                'PORT':'3306',
                }
}

前端WebSocket代码（chatroom.html） #

<script>

    document.querySelector('#submit').onclick = function (e) {
        const messageInputDOM = document.querySelector('#input');
        const message = messageInputDOM.value;
        chatSocket.send(JSON.stringify({
            'message': message,
            'username': userUsername,
            'user_pfp':userPfp,
            'room_name':roomName
        }));
        messageInputDOM.value = '';
    };

    const chatMessagesDOM = document.querySelector('#chat-messages');
    const userUsername = JSON.parse(document.getElementById('user_username').textContent);
    const pathArray = window.location.pathname.split('/');
    const roomName = pathArray[pathArray.length - 2];
   const chatSocket = new WebSocket(
    (window.location.protocol === 'https:' ? 'wss://' : 'ws://') +
    'aniconnect.org'+ 
    '/ws/chat/' +
    roomName +
    '/'
);

chatSocket.onclose = function (event) {
    console.log('WebSocket connection closed. Code:', event.code, 'Reason:', event.reason);
};
    chatSocket.onopen = function (event) {
    console.log('WebSocket connection opened.');
};


chatSocket.onerror = function (error) {
    console.log('WebSocket error:', error);
};
    chatSocket.onmessage = function (e) {
    const data = JSON.parse(e.data);

    const messageContainer = document.createElement('div');
    messageContainer.classList.add('chat-message');

    let profilePicture = document.createElement('img');
    profilePicture.src = data.user_pfp;
    profilePicture.classList.add('profile-picture');
    messageContainer.appendChild(profilePicture);

    const messageContent = document.createElement('span');

    const usernameSpan = document.createElement('span');
    usernameSpan.textContent = data.username + ': ';
    messageContent.appendChild(usernameSpan);

    const messageSpan = document.createElement('span');
    messageSpan.textContent = data.message;
    messageContent.appendChild(messageSpan);

    messageContainer.appendChild(messageContent);

    chatMessagesDOM.appendChild(messageContainer);
};
</script>

WebSocket消费者代码（consumers.py） #

from channels.generic.websocket import AsyncWebsocketConsumer
import json
from django.contrib.auth.models import User
# from channels.db import database_sync_to_async
from asgiref.sync import sync_to_async
from .models import ChatRoom, Message, Profile
from asyncer import asyncify

@sync_to_async
def save_message_to_database(chatroom_name, username, message):
    try:
        chatroom = ChatRoom.objects.get(name=chatroom_name)
        user = User.objects.get(username=username)
        user_profile = Profile.objects.get(user=user)

        new_message = Message.objects.create(chat_room=chatroom, sender=user_profile, content=message)
        print("Message saved to DB:", new_message)

    except ChatRoom.DoesNotExist:
        print(f"Chatroom with name '{chatroom_name}' does not exist.")

    except Profile.DoesNotExist:
        print(f"User profile with username '{username}' does not exist.")

    except Exception as e:
        print(f"Error occurred while saving the message: {e}")



@sync_to_async
def get_chatroom_by_name(name):
    try:
        return ChatRoom.objects.get(name=name)
    except ChatRoom.DoesNotExist:
        return None
@sync_to_async
def get_messages_for_chatroom(chatroom):
    return list(Message.objects.filter(chat_room=chatroom).order_by('timestamp'))

class ChatRoomConsumer(AsyncWebsocketConsumer):
    async def connect(self):
        self.room_name = self.scope['url_route']['kwargs']['room_name']
        self.room_group_name = 'chat_%s' % self.room_name
        await self.channel_layer.group_add(
            self.room_group_name,
            self.channel_name
        )
        await self.accept()
        print("Before getting chatroom")
        chatroom = await get_chatroom_by_name(self.room_name)

        print("chatroom:",chatroom)

        print("Chatroom:",chatroom)
        messages = await get_messages_for_chatroom(chatroom.id)
        for message in messages:
            print("SENDING MESSAGE TO FUNCTION")
            await self.send_message_to_client(message)



    async def send_message_to_client(self, message):
        user_id = await sync_to_async(lambda: message.sender.user_id)()
        user = await sync_to_async(lambda: User.objects.get(id=user_id))()
        print("SENDING MESSAGES TO THE FRONTEND")
        await self.send(text_data=json.dumps({
            'message': message.content,
            'username': user.username,
            'user_pfp': message.sender.profile_picture.url,
            'room_name': self.room_name,
        }))

    async def disconnect(self, close_code):
        await self.channel_layer.group_discard(
            self.room_group_name,
            self.channel_name
        )

    async def receive(self, text_data):
        text_data_json = json.loads(text_data)
        message = text_data_json['message']
        username = text_data_json['username']
        user_pfp = text_data_json['user_pfp']
        room_name = text_data_json['room_name']

        await save_message_to_database(chatroom_name=room_name, username=username, message=message)

        await self.channel_layer.group_send(
            self.room_group_name,
            {
                'type': 'chatroom_message',
                'message': message,
                'username': username,
                'user_pfp': user_pfp,
                'tester_message': 'tester message in receive function'
            }
        )
    async def chatroom_message(self, event):
        message = event['message']
        username = event['username']
        user_pfp = event['user_pfp']

        await self.send(text_data=json.dumps({
            'message': message,
            'username': username,
            'user_pfp': user_pfp,
            'tester_message': 'tester message in chatroom message'
        }))

内容的提问来源于stack exchange，提问作者Fuzz Ashes