Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
There is no such query that INSERTS or UPDATES more than 12 rows. –  RPK Sep 26 '11 at 3:16
    
Where to declare this max_allowed_packet property? In my.ini file? –  RPK Sep 26 '11 at 15:06
    
@RPK : Yes, please add it to my.ini and restart the mysql service. –  RolandoMySQLDBA Sep 26 '11 at 15:23
    
Shall I reduce the inno_db_buffer_pool_size which is at present set to 500 M? –  RPK Sep 26 '11 at 17:53
2  
Actually you can raise the InnoDB Buffer Pool to 750M. You should also resize ib_logfile0 and ib_logilfe1 to 183M. To resize you must 1) shutdown mysql, 2) delete ib_logfile*, 3) add innodb_log_file_size=183M to my.ini, and 3) restart mysql (a rebuild of the ib_logilfes will happen) –  RolandoMySQLDBA Sep 26 '11 at 18:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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