[ https://forge.continuent.org/jira/browse/SEQUOIA-1127?page=comments#action_14861 ]
Emmanuel Cecchet commented on SEQUOIA-1127: ------------------------------------------- Hi Alex, It looks like your MySQL auto_increment_increment (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_auto-increment-increment) value are not synchronized between the 2 nodes. Could you check manually on each node what the value is and see if there is not a default value in your my.cnf that would be different on the node that uses odd values? Thanks Emmanuel > Different primary keys on two backends with autoincrement column > ---------------------------------------------------------------- > > Key: SEQUOIA-1127 > URL: https://forge.continuent.org/jira/browse/SEQUOIA-1127 > Project: Sequoia > Type: Bug > Versions: sequoia 2.10.10 > Environment: Controller: Linux SuSE 10.3 > Database: Debian Etch, Mysql 5.0.32 > Reporter: Alexander Sahler > Attachments: testdb_20081013_4 > > > Hi Folks. > Provided I have a single controller with two database nodes (node1, node2) > which have a database with a very simple structure: > CREATE TABLE `t_test` ( > `pk_testid` bigint(20) NOT NULL auto_increment, > `testvalue` varchar(100) default NULL, > PRIMARY KEY (`pk_testid`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > After importing and bringing them to status enabled the table status is like > this: > node1: > mysql> show table status; > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | t_test | MyISAM | 10 | Dynamic | 2 | 24 | > 48 | 281474976710655 | 2048 | 0 | 4 | 2008-10-13 > 13:00:12 | 2008-10-13 13:00:20 | NULL | latin1_swedish_ci | NULL | > | | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > node2: > show table status; > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | t_test | MyISAM | 10 | Dynamic | 2 | 24 | > 48 | 281474976710655 | 2048 | 0 | 4 | 2008-10-13 > 13:01:33 | 2008-10-13 13:01:33 | NULL | latin1_swedish_ci | NULL | > | | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > As we can see, both autoincrement values are '4'. > The table contents is: > node1: > select * from t_test; > +-----------+--------------+ > | pk_testid | testvalue | > +-----------+--------------+ > | 1 | 0 | > | 3 | zweite zeile | > +-----------+--------------+ > 2 rows in set (0.00 sec) > node2: > select * from t_test; > +-----------+--------------+ > | pk_testid | testvalue | > +-----------+--------------+ > | 1 | 0 | > | 3 | zweite zeile | > +-----------+--------------+ > 2 rows in set (0.00 sec) > Which are the same obviously. > Now I connect to the virtual database (using iSql) and submit an insert > command: > insert into t_test (testvalue) values ('dritte zeile'); > Let's have a look on the two backends now: > node1: > select * from t_test; > +-----------+--------------+ > | pk_testid | testvalue | > +-----------+--------------+ > | 1 | 0 | > | 3 | zweite zeile | > | 5 | dritte zeile | > +-----------+--------------+ > 3 rows in set (0.00 sec) > node2: > select * from t_test; > +-----------+--------------+ > | pk_testid | testvalue | > +-----------+--------------+ > | 1 | 0 | > | 3 | zweite zeile | > | 4 | dritte zeile | > +-----------+--------------+ > 3 rows in set (0.00 sec) > The two primary key values differ and the table status now gives: > node1: > mysql> show table status; > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | t_test | MyISAM | 10 | Dynamic | 3 | 25 | > 76 | 281474976710655 | 2048 | 0 | 6 | 2008-10-13 > 13:00:12 | 2008-10-13 13:03:38 | NULL | latin1_swedish_ci | NULL | > | | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > 1 row in set (0.00 sec) > node2: > mysql> show table status; > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > | t_test | MyISAM | 10 | Dynamic | 3 | 25 | > 76 | 281474976710655 | 2048 | 0 | 5 | 2008-10-13 > 13:01:33 | 2008-10-13 13:03:38 | NULL | latin1_swedish_ci | NULL | > | | > +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ > 1 row in set (0.01 sec) > The autoincrement values clearly are different. > The issue is reproducable. > If you need additional information, please come back to me. > Regards, > Alexander. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://forge.continuent.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
