mysql autoincrement reset behaviour causes fids to be reused

[GoogleCodeExporter]

We recently had a power cut in one of our datacentres, which rebooted an entire 
cluster, including the mogilefs mysql server for that datacentre. We were 
somewhat surprised when the servers came back up that new files were being 
inserted with fids that apparently started again at 0.                          

Some poking around seems to show that mysql had lost track of the last          
used value for the fid column on the tempfile table. The reason seems to        
be that INNODB finds the next value to use in autoincrememnt by doing a         
MAX() on the auto increment column on startup. It would appear that at          
the moment we lost power the tempfile table was empty, so INNODB just           
set the auto incrementer back to 0.                                             

I think this might have the potential to cause some bad behaviour with          
new files clashing with old fids, but it's not clear to me if that is           
definitely the case.

Original issue reported on code.google.com by stu.teas...@gmail.com on 15 Aug 2011 at 5:55

[GoogleCodeExporter]

Adding my notes to this bug:

If mogilefs ends up getting a duplicate error while inserting into file, it'll
SELECT MAX(fid) FROM file and correct itself. If your lower fids have been
emptied out it could take quite a while to hit this.

Unfortunately those _server_startup_test rows are irrelevant.

We could probably have trackers insert a test row when they first start,
pre-fork, and if tempfile's last_insert_id comes back empty it'll do the
MAX() call against file and reseed it...

If that's not good enough, we'll have to come up with something else.

Original comment by dorma...@rydia.net on 16 Aug 2011 at 9:33

• Changed state: Accepted

[GoogleCodeExporter]

On startup, add a global $fid_minimum via SELECT MAX(fid) FROM file

If adding a tempfile comes up with a fid lower than that, drop into the 
MAX(fid) routine again and reset itself.

If you add a ton of new fids, then delete all of them, then end up in this 
routine, it's impossible to fix without us adding an external counter row into 
settings or something.

In the meantime, the above should work? I'll code it up.

Original comment by dorma...@rydia.net on 29 Oct 2011 at 2:19

[GoogleCodeExporter]

Fixed in master.

Will continue to improve with time I think, but for now this is probably a big 
improvement, especially in your case of a power outage.

Original comment by dorma...@rydia.net on 29 Oct 2011 at 10:21

• Changed state: Fixed