Binary logging problems

[James Lawton <ja...@tofinosoftware.com>]

Hi, 

Ofbiz is running fine on my machine with MySQL and no replication.

It used to run fine on our soon to be production machine with no
replication.

Now that replication is turned on for all databases on the machine - other
databases besides Ofbiz as well - get a ton of errors when starting Ofbiz to
the effect that binary logging is not being handled, CPU maxes out at 100%
and grinds the server.

tons of messages like this:
(Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in
InnoDB is not safe for binlog mode 'STATEMENT')). Rolling back transaction.


We really need replication for our DR and I am hoping that there is a nifty
configuration fix for this.

Has anyone run into this issue?

Thanks
James

Re: Binary logging problems

[Paul Foxworthy <pa...@cohsoft.com.au>]

Hi James,

I haven't had to do it myself so can't comment much.

But have you looked at the discussion at 
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-issues.html
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-issues.html 
?

Cheers

Paul Foxworthy
-- 
View this message in context: http://ofbiz.135035.n4.nabble.com/Binary-logging-problems-tp3300517p3302320.html
Sent from the OFBiz - User mailing list archive at Nabble.com.

Re: Binary logging problems

[Adrian Crum <ad...@sandglass-software.com>]

By the way, the MySql timestamp issue has a workaround in OFBiz. If you 
change the timestamp field's sql-type to CHAR(30), the entity engine 
will convert timestamps from/to CHAR field types.

-Adrian

On 2/12/2011 7:43 AM, Mike wrote:
> Maybe this?
>
> http://www.ispirer.com/wiki/sqlways/troubleshooting-guide/mysql/import/binlog_mode_statement
>
> Also, make sure you tune mysql for the huge number of tables in Ofbiz,
> or you will be chewing up diskIO like crazy.  All the tables need to
> be resident in RAM.
>
> table_cache            = 1400
> table_definition_cache  = 1400
>
> Run tuning-primer.sh on your mysql database to get other recommended
> tuning recommendations:
>
> https://launchpad.net/mysql-tuning-primer/+download
>
> I dumped mysql when I learned of the timestamp issue problem.
> Basically, it doesn't allow timestamps to the millisecond level (like
> postgres, and others, like Sybase and Oracle), only to the second.
> This is a huge issue IMHO.  This can cause backend timing problems if
> you are running a lot of transactions.
>
> Too bad, I really liked mysql.  I switched to postgres.
>
> On Thu, Feb 10, 2011 at 3:54 PM, James Lawton<ja...@tofinosoftware.com>  wrote:
>> Hi,
>>
>> Ofbiz is running fine on my machine with MySQL and no replication.
>>
>> It used to run fine on our soon to be production machine with no
>> replication.
>>
>> Now that replication is turned on for all databases on the machine - other
>> databases besides Ofbiz as well - get a ton of errors when starting Ofbiz to
>> the effect that binary logging is not being handled, CPU maxes out at 100%
>> and grinds the server.
>>
>> tons of messages like this:
>> (Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in
>> InnoDB is not safe for binlog mode 'STATEMENT')). Rolling back transaction.
>>
>>
>> We really need replication for our DR and I am hoping that there is a nifty
>> configuration fix for this.
>>
>> Has anyone run into this issue?
>>
>> Thanks
>> James
>>
>>
>>

Re: Binary logging problems

[Mike <mz...@gmail.com>]

Maybe this?

http://www.ispirer.com/wiki/sqlways/troubleshooting-guide/mysql/import/binlog_mode_statement

Also, make sure you tune mysql for the huge number of tables in Ofbiz,
or you will be chewing up diskIO like crazy.  All the tables need to
be resident in RAM.

table_cache            = 1400
table_definition_cache  = 1400

Run tuning-primer.sh on your mysql database to get other recommended
tuning recommendations:

https://launchpad.net/mysql-tuning-primer/+download

I dumped mysql when I learned of the timestamp issue problem.
Basically, it doesn't allow timestamps to the millisecond level (like
postgres, and others, like Sybase and Oracle), only to the second.
This is a huge issue IMHO.  This can cause backend timing problems if
you are running a lot of transactions.

Too bad, I really liked mysql.  I switched to postgres.

On Thu, Feb 10, 2011 at 3:54 PM, James Lawton <ja...@tofinosoftware.com> wrote:
> Hi,
>
> Ofbiz is running fine on my machine with MySQL and no replication.
>
> It used to run fine on our soon to be production machine with no
> replication.
>
> Now that replication is turned on for all databases on the machine - other
> databases besides Ofbiz as well - get a ton of errors when starting Ofbiz to
> the effect that binary logging is not being handled, CPU maxes out at 100%
> and grinds the server.
>
> tons of messages like this:
> (Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in
> InnoDB is not safe for binlog mode 'STATEMENT')). Rolling back transaction.
>
>
> We really need replication for our DR and I am hoping that there is a nifty
> configuration fix for this.
>
> Has anyone run into this issue?
>
> Thanks
> James
>
>
>