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
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
