At first glance, I would say that your innodb_log_file_size is way too small. It should be bigger to do two things:
- Accommodate any big BLOB or TEXT fields
- Holding bigger transactions
Here is what you should do for now to see if it helps:
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 3G
innodb_log_file_size = 768M
STEP 02) service mysql stop
STEP 03) rm -f /var/lib/mysql/ib_logfile*
STEP 04) service mysql start
This will rebuild the following files
- /var/lib/mysql/ib_logfile0
- /var/lib/mysql/ib_logfile1
Give it a Try !!!
UPDATE 2013-07-03 12:37 EDT
I have updated my other posts on this and missed this one
ButtleButkus just commented at 2013-07-03 07:18:56 EDT
Wouldn't it be advisable to copy the ib_logfile* to another location for backup before deleting them?
Since there could be unfinished transactional data inside, here is what should be done
STEP 01) Change the following in /etc/my.cnf
[mysqld]
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 3G
innodb_log_file_size = 768M
STEP 02) mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"
STEP 03) service mysql stop
STEP 04) rm -f /var/lib/mysql/ib_logfile*
STEP 05) service mysql start
I added SET GLOBAL innodb_fast_shutdown = 0;
. What does that do? It forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0, ib_logfile1. If deleting them makes you nervous, then make Step 04
mv /var/lib/mysql/ib_logfile* ..
to put the old logs in /var/lib
. If the recreation of the logs is successful and mysqld starts up, then you can delete the old logs.
I have been using this feature for a year now. I have updated my other posts to reflect this...
If there are other older posts of mine where I do not mention innodb_fast_shutdown, let me know so I can update it. Thanks again, ButtleButkus.
This seems to be an elusive bug nobody wants to fix
According to http://bugs.mysql.com/bug.php?id=24761, the aborted connect is recorded in the general log
[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
/*
Log the command before authentication checks, so that the user can
check the log for the tried login tried and also to detect
break-in attempts.
*/
general_log_print(thd, command,
(thd->main_security_ctx.priv_user ==
thd->main_security_ctx.user ?
(char*) "%s@%s on %s" :
(char*) "%s@%s as anonymous on %s"),
thd->main_security_ctx.user,
thd->main_security_ctx.host_or_ip,
db ? db : (char*) "");
So, this is logged in the general log at least.
I was reviewing a patch that added more logging,
but I can't remember the worklog task number.
We just have to wait on MySQL (I mean Oracle) to fix it. (That was sarcasm)
Best Answer
You are probably trying to import very large rows. The 5 MB default log file size (10MB in total for the log) may be too small in many cases. The default for 5.6 is 50MB (100Mb in total), which is probably a more sane default in most cases.
If you do not have much load, the only problem of augmenting the log is having to handle a larger set of files, nothing else should change (this is not true in heavy-write environments). it will also make the load process much faster, assuming you have enough memory size for the buffer pool. You may want to change the innodb_log_buffer_size if you are loading lots of large rows.
Check a tutorial on how to change the transaction log size in MySQL 5.1.