主页 / dba / 问题 / 6134
Accepted
RPK
Asked: 2011-09-26 03:42:53 +0800 CST

修复错误日志中记录的 MySQL 错误

  • 772

以下是我 PC 上错误日志中记录的最常见错误行的摘录：

110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled.
110905 16:16:54 InnoDB: The InnoDB memory heap is disabled
110905 16:16:54 InnoDB: Mutexes and rw_locks use Windows interlocked functions
110905 16:16:54 InnoDB: Compressed tables use zlib 1.2.3
110905 16:16:55 InnoDB: Initializing buffer pool, size = 500.0M
110905 16:16:55 InnoDB: Completed initialization of buffer pool
110905 16:16:55 InnoDB: highest supported file format is Barracuda.
110905 16:16:57  InnoDB: Waiting for the background threads to start
110905 16:16:58 InnoDB: 1.1.8 started; log sequence number 14539513
110905 16:16:59 [Note] Event Scheduler: Loaded 0 events
110905 16:16:59 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.15-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
110905 16:46:29 [Warning] Aborted connection 1 to db: 'stockist' user: 'root' host: 'localhost' (Got an error reading communication packets)
110905 16:50:42 [Warning] Aborted connection 2 to db: 'dummy_db' user: 'root' host: 'localhost' (Got an error reading communication packets)

如何解决这些问题？我正在使用 MySQL 社区服务器 5.5.14。它安装在 NTFS 分区上具有 3 GB RAM 的 PC 上。操作系统是 Windowx XP SP2。

下面是my.ini文件：

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=latin1

# SERVER SECTION
[mysqld]
log-bin=E:/CRITICAL_MYSQL_LOGGING/AUTOMATED_BINARY_LOGS/MySqlBinLog

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow. 
max_connections=02

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. 
query_cache_size=15M

# The number of open tables for all threads. 
table_cache=256

# Maximum size for internal (in-memory) temporary tables.
tmp_table_size=9M

# How many threads we should keep in a cache for reuse. When a client
# disconnects
thread_cache_size=8

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=500M

innodb_log_file_size=10M

innodb_thread_concurrency=8

log-queries-not-using-indexes

log-warnings

log=E:/CRITICAL_MYSQL_LOGGING/QUERY_LOGS/QueryLog

log-error=E:/CRITICAL_MYSQL_LOGGING/ERROR_LOGS/ErrorLog

log-slow-queries=E:/CRITICAL_MYSQL_LOGGING/SLOW_QUERY_LOGS/SlowQueryLog

log-bin-index=E:/CRITICAL_MYSQL_LOGGING/BinLogIndexFile

long_query_time=2
mysql mysql-5.5

1 个回答

  1. Best Answer

    MySQL 连接的沉默杀手之一是 MySQL 数据包。

    根据MySQL 文档

    • 如果您向服务器发送不正确或太大的查询，您也可能会收到这些错误。如果 mysqld 收到一个太大或乱序的数据包，它会假定客户端出现问题并关闭连接。如果您需要大查询（例如，如果您使用大 BLOB 列），您可以通过设置服务器的 max_allowed_pa​​cket 变量来增加查询限制，该变量的默认值为 1MB。您可能还需要增加客户端的最大数据包大小。有关设置数据包大小的更多信息，请参见第 C.5.2.10 节，“数据包太大”。

    • 插入大量行的 INSERT 或 REPLACE 语句也可能导致这类错误。无论要插入的行数如何，这些语句中的任何一个都向服务器发送单个请求；因此，您通常可以通过减少每次 INSERT 或 REPLACE 发送的行数来避免错误。

    推荐

    尝试将max_allowed_pa​​cket提高到更大的数字，因为默认值为 1M。我建议您在当前数据集中拥有的最大 TEXT 或 BLOB 字段大约是 10 倍。

    您可以将其添加到 /etc/my.cnf 或 my.ini

    [mysqld]
max_allowed_packet=128M
    • 2

相关问题