[ https://forge.continuent.org/jira/browse/SEQUOIA-1127?page=comments#action_14848 ]
Emmanuel Cecchet commented on SEQUOIA-1127: ------------------------------------------- Which version of the MySQL driver are you using? What happen if you insert another row? Does the second database continue in sequence or does another hop? Do you notice the same problem is you use InnoDB instead of MyIsam as the table type? Thanks for the feedback 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
