Hello!

I'd like to raise an issue that caused some data-integrity problems for us. And to see if this is something reasonable to address within gh-ost itself.

Today we ran into an interesting case with gh-ost when a primary key in an old table is legitimately 0.

MySQL, by default, interprets 0 inserted into an auto-increment column to generate the next auto-increment and insert that id. See the docs on NO_AUTO_VALUE_ON_ZERO for more info.

So in our case, when gh-ost was copying this row, in the source table the id was 0, but in the target table, the id became the next auto-increment (1).

We realize this case is against a few best practices, like 1) storing 0 in that field, and 2) (apparently) switching away from the sql_mode that allowed us to insert that row that way in the first place.

However, this seems like an obvious case where gh-ost can choose to prefer the sql_mode that is more likely to result in a one-to-one copy. I don't know if there would be any drawbacks to this approach.

Test Case

1. Create a table with autoincrement

CREATE TABLE `jpt_test_zeros` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `other` text(20),
  PRIMARY KEY (`id`)
)

2. Insert a row with a 0 pk. This requires us to disable the default handling

-- Needed to insert a literal 0 primary key
SET SESSION SQL_MODE = CONCAT(@@SESSION.SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO');

INSERT INTO jpt_test_zeros (id, other) VALUES (0, "testing");

-- Verify it is recorded as 0
SELECT * FROM  jpt_test_zeros WHERE id = 0;

3. Run the gh-ost migration, (anything should reproduce, including ENGINE=innodb)

4. ⚠️ See the row inserted into the result table with id 1.

SELECT * FROM  jpt_test_zeros WHERE id = 0;
-- Nothing Returned
SELECT * FROM  jpt_test_zeros WHERE id = 1;
-- Our Testing row returned

Let me know if I can clear anything up with this request. I just want to start by asking if setting this sql_mode by default within gh-ost sounds reasonable to you. I'm not familiar enough with the code to make a PR for it yet, but I'm happy to help review and test things as well.

Thanks for making a very useful tool! <3