Mailing List Archive

SQLException: innodb_log_file_size headache
FYI,
After performing a number of upgrades on my FreeBSD ports based install
on an older release of FreeBSD 9.1-STABLE, I started getting corrupt
messages. Since it was seemingly random on message with attachments, and
I had upgraded postfix, mailscanner, dbmail, and mysql all the the same
time, it took a while before I could capture a queued message.
I finally was able to determine that postfix and mailscanner were not to
blame, but rather somewhere in dbmail/mysql.

After taking a look in lmtp logs I found:
Mar 21 01:28:57 mail.consult-sc dbmail-lmtpd[74282]: [0x804c21810]
Error:[message] blob_insert(+203): SQLException: The size of BLOB/TEXT
data inserted in one transaction is greater than 10% of redo log size.
Increase the redo log size using innodb_log_file_size.

Here is a link to explain what changed in MySQL:
http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql

This message is to make sure that nobody else starts to run into this issue.

My change to my.cnf:
###innodb_log_file_size=5242880 # Too Small!!
innodb_log_file_size=1048576000 # Close to 100MB accepted now which is
large enough for most normal email setups

-Jon

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: SQLException: innodb_log_file_size headache [ In reply to ]
Am 21.03.2016 um 10:07 schrieb Jonathan Feally:
> After taking a look in lmtp logs I found:
> Mar 21 01:28:57 mail.consult-sc dbmail-lmtpd[74282]: [0x804c21810]
> Error:[message] blob_insert(+203): SQLException: The size of BLOB/TEXT
> data inserted in one transaction is greater than 10% of redo log size.
> Increase the redo log size using innodb_log_file_size

that's a change in MySQL 5.6 / MariaDB 10.0.x

remember you can't change "innodb_log_file_size" without shutdown the
server and delete the currect "ib_logfile0" and "ib_logfile1" because
otherwise mysqld won't start again

> Here is a link to explain what changed in MySQL:
> http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql
>
> This message is to make sure that nobody else starts to run into this
> issue.
>
> My change to my.cnf:
> ###innodb_log_file_size=5242880 # Too Small!!
> innodb_log_file_size=1048576000 # Close to 100MB accepted now which is
> large enough for most normal email setups

mysql:
innodb_log_file_size = 512M

postfix:
message_size_limit = 26214400

dbmail:
max_message_size = 26214400
Re: SQLException: innodb_log_file_size headache [ In reply to ]
I was able to change without extra work with mysql56-server-5.6.27, but
I agree that care should be taken depending on your version of MySQL.

See my startup log:

2016-03-21 01:38:14 69664 [Note] /usr/local/libexec/mysqld: Shutdown
complete

160321 01:38:14 mysqld_safe mysqld from pid file
/var/db/mysql/db.domain.com.pid ended
160321 01:38:17 mysqld_safe Starting mysqld daemon with databases from
/var/db/mysql
2016-03-21 01:38:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use --explicit_defaults_for_timestamp server option
(see documentation for more details).
2016-03-21 01:38:18 0 [Note] /usr/local/libexec/mysqld (mysqld
5.6.27-log) starting as process 75322 ...
2016-03-21 01:38:19 75322 [Note] Plugin 'FEDERATED' is disabled.
2016-03-21 01:38:19 802807400 InnoDB: Warning: Using
innodb_additional_mem_pool_size is DEPRECATED. This option may be
removed in future releases, together with the option
innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-03-21 01:38:19 75322 [Note] InnoDB: Using atomics to ref count
buffer pool pages
2016-03-21 01:38:19 75322 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-21 01:38:19 75322 [Note] InnoDB: Mutexes and rw_locks use GCC
atomic builtins
2016-03-21 01:38:19 75322 [Note] InnoDB: Memory barrier is not used
2016-03-21 01:38:19 75322 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-21 01:38:19 75322 [Note] InnoDB: Using CPU crc32 instructions
2016-03-21 01:38:19 75322 [Note] InnoDB: Initializing buffer pool, size
= 10.0G
2016-03-21 01:38:20 75322 [Note] InnoDB: Completed initialization of
buffer pool
2016-03-21 01:38:20 75322 [Note] InnoDB: Highest supported file format
is Barracuda.
*2016-03-21 01:38:21 75322 [Warning] InnoDB: Resizing redo log from
2*320 to 2*64000 pages, LSN=184525750777**
**2016-03-21 01:38:21 75322 [Warning] InnoDB: Starting to delete and
rewrite log files.**
**2016-03-21 01:38:21 75322 [Note] InnoDB: Setting log file
./ib_logfile101 size to 1000 MB**
**InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000**
**2016-03-21 01:38:34 75322 [Note] InnoDB: Setting log file
./ib_logfile1 size to 1000 MB**
**InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000**
**2016-03-21 01:38:45 75322 [Note] InnoDB: Renaming log file
./ib_logfile101 to ./ib_logfile0*
2016-03-21 01:38:45 75322 [Warning] InnoDB: New log files created,
LSN=184525750777
2016-03-21 01:38:45 75322 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-21 01:38:45 75322 [Note] InnoDB: Waiting for purge to start
2016-03-21 01:38:45 75322 [Note] InnoDB: 5.6.27 started; log sequence
number 184525750777
2016-03-21 01:38:46 75322 [Note] Server hostname (bind-address): '*';
port: 3306
2016-03-21 01:38:46 75322 [Note] IPv6 is available.
2016-03-21 01:38:46 75322 [Note] - '::' resolves to '::';
2016-03-21 01:38:46 75322 [Note] Server socket created on IP: '::'.
2016-03-21 01:38:50 75322 [Note] Event Scheduler: Loaded 0 events
2016-03-21 01:38:50 75322 [Note] /usr/local/libexec/mysqld: ready for
connections.


On 3/21/2016 3:39 AM, Reindl Harald wrote:
>
>
> Am 21.03.2016 um 10:07 schrieb Jonathan Feally:
>> After taking a look in lmtp logs I found:
>> Mar 21 01:28:57 mail.consult-sc dbmail-lmtpd[74282]: [0x804c21810]
>> Error:[message] blob_insert(+203): SQLException: The size of BLOB/TEXT
>> data inserted in one transaction is greater than 10% of redo log size.
>> Increase the redo log size using innodb_log_file_size
>
> that's a change in MySQL 5.6 / MariaDB 10.0.x
>
> remember you can't change "innodb_log_file_size" without shutdown the
> server and delete the currect "ib_logfile0" and "ib_logfile1" because
> otherwise mysqld won't start again
>
>> Here is a link to explain what changed in MySQL:
>> http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql
>>
>> This message is to make sure that nobody else starts to run into this
>> issue.
>>
>> My change to my.cnf:
>> ###innodb_log_file_size=5242880 # Too Small!!
>> innodb_log_file_size=1048576000 # Close to 100MB accepted now which is
>> large enough for most normal email setups
>
> mysql:
> innodb_log_file_size = 512M
>
> postfix:
> message_size_limit = 26214400
>
> dbmail:
> max_message_size = 26214400
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: SQLException: innodb_log_file_size headache [ In reply to ]
Jon,

Thank You, Thank You, Thank You!!!! for that investigation and analysis.
I have been experiencing intermittent message corruption to SOME
messages with attachments for a number of months. I foolishly and lazily
didn't investigate my logs and I just put it down to a dbmail error. I
had even started thinking of moving away from dbmail after using it for
over 10 years.

I made the change to the my.cnf file and restarted mysql and I could
receive a message with attachments that failed to be received properly
just a few days ago. I'm running the gentoo version of mysql v5.6.26
and I only had to change the my.cnf file and restart mysql (no need to
delete any log files).

Thanks,
Ken.


On 21/03/2016 7:07 PM, Jonathan Feally wrote:
> FYI,
> After performing a number of upgrades on my FreeBSD ports based
> install on an older release of FreeBSD 9.1-STABLE, I started getting
> corrupt messages. Since it was seemingly random on message with
> attachments, and I had upgraded postfix, mailscanner, dbmail, and
> mysql all the the same time, it took a while before I could capture a
> queued message.
> I finally was able to determine that postfix and mailscanner were not
> to blame, but rather somewhere in dbmail/mysql.
>
> After taking a look in lmtp logs I found:
> Mar 21 01:28:57 mail.consult-sc dbmail-lmtpd[74282]: [0x804c21810]
> Error:[message] blob_insert(+203): SQLException: The size of BLOB/TEXT
> data inserted in one transaction is greater than 10% of redo log size.
> Increase the redo log size using innodb_log_file_size.
>
> Here is a link to explain what changed in MySQL:
> http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql
>
> This message is to make sure that nobody else starts to run into this
> issue.
>
> My change to my.cnf:
> ###innodb_log_file_size=5242880 # Too Small!!
> innodb_log_file_size=1048576000 # Close to 100MB accepted now which is
> large enough for most normal email setups
>
> -Jon
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail