2

Below is the extract of the most common lines of error being recorded in error logs on my 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)

How to fix these issues? I am using MySQL Community Server 5.5.14. It is installed on a PC with 3 GB RAM on NTFS partion. OS is Windowx XP SP2.

Below is the my.ini file:

# 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
RPK
  • 1,425
  • 6
  • 20
  • 39

1 Answers1

2

One of the silent killers of MySQL Connections is the MySQL Packet.

According to the MySQL Documentation

  • You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.

  • An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

You can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=128M
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536