Weird issue in mysql db server

cooljai · 10-14-2009, 12:14 PM

Hi,

I've two Mysql servers (both 5.0.45) on RHEL 5 boxes. I've setup Master to Master Replication and its running perfectly since few weeks. Both servers are accepting read/write with heavy usage.

few related settings (my.cnf):

Server 1:
---------

Code:

server-id=1
auto_increment_increment=2
auto_increment_offset=1

Server 2:
----------

Code:

server-id=2
auto_increment_increment=2
auto_increment_offset=2

So, both servers will insert records without conflicting on auto increment fields, one will generate even numbers and other will odds.

This is what I understood and happening but I just checked an incident where a table has weird values in autoincrement field. A script insert records in this table connecting Server 2 only. No other program/script accessing this table.

Record 1:

ID: 10011 Time: 2009-10-13 02:00:25

Record 2:

ID: 10014 Time: 2009-10-13 02:00:23

Hows this possible? record 1 has higher timestamp but smaller id value then record 2 and moreover isn't record 2 should have odd id value (ie 10013)

Any pointer will be helpful.

never say never · 10-14-2009, 04:12 PM

I am not a MySql guru by any means . . .

You don't say what type of table you are using but it looks like you may have been premature in your settings, since there were problems with them until 5.0.46, at least with NDB tables.

Quote:

#

auto_increment_increment is supported for use with NDB tables beginning with MySQL 5.0.46. Previously, setting it when using MySQL Cluster tables produced unpredictable results.
#

auto_increment_offset is supported for use with NDB tables beginning with MySQL 5.0.46. Previously, setting it when using MySQL Cluster tables produced unpredictable results.

This information can be found here:
http://dev.mysql.com/doc/refman/5.0/...crement_offset

Hope this is useful

cooljai · 10-14-2009, 10:51 PM

Hi, thanks for info but all tables in our databases are in MyISAM. I'm still clueless.

Smartpatrol · 10-14-2009, 11:41 PM

cooljai · 10-15-2009, 02:05 AM

Hello Smartpatrol, thanks for info. Just checked the time on both servers and found exactly same.

Any other thoughts?

Thanks,