{"action":"created","issue":{"url":"https://api.github.com/repos/facebook/mysql-5.6/issues/263","repository_url":"https://api.github.com/repos/facebook/mysql-5.6","labels_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/263/labels{/name}","comments_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/263/comments","events_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/263/events","html_url":"https://github.com/facebook/mysql-5.6/issues/263","id":159622625,"number":263,"title":"MyRocks auto_increment skips values if you insert a negative value","user":{"login":"tplavcic","id":4625195,"avatar_url":"https://avatars.githubusercontent.com/u/4625195?v=3","gravatar_id":"","url":"https://api.github.com/users/tplavcic","html_url":"https://github.com/tplavcic","followers_url":"https://api.github.com/users/tplavcic/followers","following_url":"https://api.github.com/users/tplavcic/following{/other_user}","gists_url":"https://api.github.com/users/tplavcic/gists{/gist_id}","starred_url":"https://api.github.com/users/tplavcic/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tplavcic/subscriptions","organizations_url":"https://api.github.com/users/tplavcic/orgs","repos_url":"https://api.github.com/users/tplavcic/repos","events_url":"https://api.github.com/users/tplavcic/events{/privacy}","received_events_url":"https://api.github.com/users/tplavcic/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"jkedgar","id":8951526,"avatar_url":"https://avatars.githubusercontent.com/u/8951526?v=3","gravatar_id":"","url":"https://api.github.com/users/jkedgar","html_url":"https://github.com/jkedgar","followers_url":"https://api.github.com/users/jkedgar/followers","following_url":"https://api.github.com/users/jkedgar/following{/other_user}","gists_url":"https://api.github.com/users/jkedgar/gists{/gist_id}","starred_url":"https://api.github.com/users/jkedgar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jkedgar/subscriptions","organizations_url":"https://api.github.com/users/jkedgar/orgs","repos_url":"https://api.github.com/users/jkedgar/repos","events_url":"https://api.github.com/users/jkedgar/events{/privacy}","received_events_url":"https://api.github.com/users/jkedgar/received_events","type":"User","site_admin":false},"assignees":[{"login":"jkedgar","id":8951526,"avatar_url":"https://avatars.githubusercontent.com/u/8951526?v=3","gravatar_id":"","url":"https://api.github.com/users/jkedgar","html_url":"https://github.com/jkedgar","followers_url":"https://api.github.com/users/jkedgar/followers","following_url":"https://api.github.com/users/jkedgar/following{/other_user}","gists_url":"https://api.github.com/users/jkedgar/gists{/gist_id}","starred_url":"https://api.github.com/users/jkedgar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jkedgar/subscriptions","organizations_url":"https://api.github.com/users/jkedgar/orgs","repos_url":"https://api.github.com/users/jkedgar/repos","events_url":"https://api.github.com/users/jkedgar/events{/privacy}","received_events_url":"https://api.github.com/users/jkedgar/received_events","type":"User","site_admin":false}],"milestone":null,"comments":2,"created_at":"2016-06-10T12:17:16Z","updated_at":"2016-09-19T21:46:09Z","closed_at":null,"body":"After inserting (0),(-1),(0) to a table with int auto_increment the MyRocks auto_increment counter is at 6 while InnoDB is at 4 and there's difference in inserted values.\r\nNotice I cannot reproduce this if I split this one insert into 3 separate inserts, also it works for any negative value.\r\n\r\nInnoDB:\r\n```\r\nmysql> DROP DATABASE test;\r\nQuery OK, 1 row affected (0.20 sec)\r\n\r\nmysql> CREATE DATABASE test DEFAULT CHARACTER SET=\"Binary\" DEFAULT COLLATE=\"Binary\";\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> USE test;\r\nDatabase changed\r\nmysql> create table t1(a int auto_increment key);\r\nQuery OK, 0 rows affected (0.28 sec)\r\n\r\nmysql> INSERT INTO t1 VALUES(0),(-1),(0);\r\nQuery OK, 3 rows affected (0.08 sec)\r\nRecords: 3  Duplicates: 0  Warnings: 0\r\n\r\nmysql> show table status like 't1';\r\n+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+\r\n| 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 |\r\n+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+\r\n| t1   | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2016-06-10 12:05:28 | NULL        | NULL       | binary    |     NULL |                |         |\r\n+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from t1;\r\n+----+\r\n| a  |\r\n+----+\r\n| -1 |\r\n|  1 |\r\n|  2 |\r\n+----+\r\n3 rows in set (0.00 sec)\r\n```\r\n\r\nMyRocks:\r\n```\r\nmysql> DROP DATABASE test;\r\nQuery OK, 1 row affected (0.04 sec)\r\n\r\nmysql> CREATE DATABASE test DEFAULT CHARACTER SET=\"Binary\" DEFAULT COLLATE=\"Binary\";\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> USE test;\r\nDatabase changed\r\nmysql> create table t1(a int auto_increment key);\r\nQuery OK, 0 rows affected (0.18 sec)\r\n\r\nmysql> INSERT INTO t1 VALUES(0),(-1),(0);\r\nQuery OK, 3 rows affected (0.02 sec)\r\nRecords: 3  Duplicates: 0  Warnings: 0\r\n\r\nmysql> show table status like 't1';\r\n+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+\r\n| 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 |\r\n+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+\r\n| t1   | ROCKSDB |      10 | Fixed      |    0 |              0 |          54 |               0 |            0 |         0 |              6 | NULL        | NULL        | NULL       | binary    |     NULL |                |         |\r\n+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> select * from t1;\r\n+----+\r\n| a  |\r\n+----+\r\n| -1 |\r\n|  1 |\r\n|  4 |\r\n+----+\r\n3 rows in set (0.00 sec)\r\n```"},"comment":{"url":"https://api.github.com/repos/facebook/mysql-5.6/issues/comments/248137180","html_url":"https://github.com/facebook/mysql-5.6/issues/263#issuecomment-248137180","issue_url":"https://api.github.com/repos/facebook/mysql-5.6/issues/263","id":248137180,"user":{"login":"jkedgar","id":8951526,"avatar_url":"https://avatars.githubusercontent.com/u/8951526?v=3","gravatar_id":"","url":"https://api.github.com/users/jkedgar","html_url":"https://github.com/jkedgar","followers_url":"https://api.github.com/users/jkedgar/followers","following_url":"https://api.github.com/users/jkedgar/following{/other_user}","gists_url":"https://api.github.com/users/jkedgar/gists{/gist_id}","starred_url":"https://api.github.com/users/jkedgar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jkedgar/subscriptions","organizations_url":"https://api.github.com/users/jkedgar/orgs","repos_url":"https://api.github.com/users/jkedgar/repos","events_url":"https://api.github.com/users/jkedgar/events{/privacy}","received_events_url":"https://api.github.com/users/jkedgar/received_events","type":"User","site_admin":false},"created_at":"2016-09-19T21:46:09Z","updated_at":"2016-09-19T21:46:09Z","body":"MySQL allows the storage engine to reserve a number of values for auto-incremented columns when inserting multiple rows in the same statement, but it gets complicated if the user then specifies a value for the auto-incremented column in the middle of the rows.  Our handling of this was naive which resulted in the loss of certain values.\r\n\r\nI've changed the code to simply always reserve one row at a time for the time being and we can redo this sometime in the future if performance is impacted.\r\n\r\nNote that this will not yield exactly the same behavior as InnoDB.  In the test above InnoDB reserves 3 values originally but only 2 are used (because the user specified -1 as the value for one row).  The third reserved value is never used and SHOW TABLE STATUS shows that the next auto-increment value is 4.  MyRocks will now only reserve 1 value at a time, so only two new values get used and SHOW TABLE STATUS shows that the next auto-increment value is 3.\r\n\r\nhttps://reviews.facebook.net/D64131"}}
{"actor":{"display_login":"jkedgar"}}