0

I am creating a Django app where the primary keys are AutoFields. i.e. I am not manually assigning any field as primary key in my models.

I need to use mySQL.

I will need to export all the data to excel or perhaps another django app from time to time. Therefore the primary keys must be unique to be able to identify new records or records to be deleted in excel/other app.

However, I have read that mySQL autoincrement counter resets to the max key when database restarts. This will result in reassignment of keys if the latest records were deleted.

I need to avoid this. No key should be reassigned.

How can this be done?

mad_accountant
  • 41
  • 7
  • *I have read that mySQL autoincrement counter resets to the max key when database restarts. This will result in reassignment of keys if the latest records were deleted.* Read one more time and check for conditons for such behavior... – Akina Mar 03 '21 at 19:11
  • From the other side - I don't understand your problem source... you tell about export only, and no words about rows deletion. – Akina Mar 03 '21 at 19:15
  • I am referring to this https://docs.djangoproject.com/en/3.1/ref/databases/#storage-engines – mad_accountant Mar 03 '21 at 19:15
  • Refer to [MySQL 8.0 Reference Manual / ... / AUTO_INCREMENT Handling in InnoDB](https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html), do not use re-tellings. – Akina Mar 03 '21 at 19:20

1 Answers1

1

MySQL 8.0 now keeps the last auto-increment per table persistently. So it remembers between restarts, and does not reset the auto-increment.

https://www.percona.com/blog/2018/10/08/persistence-of-autoinc-fixed-in-mysql-8-0/

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • *MySQL 8.0 now keeps the last auto-increment per table persistently.* Engine-dependent (at least). – Akina Mar 03 '21 at 19:13
  • So this text in Django documentation is outdated? https://docs.djangoproject.com/en/3.1/ref/databases/#storage-engines – mad_accountant Mar 03 '21 at 19:14
  • 1
    Unless the documentation is meant to support projects still using MySQL 5.x. Naturally, when a major new version of a product comes out, it will take several years for everyone to upgrade. – Bill Karwin Mar 03 '21 at 19:16
  • 1
    The link from mysql docs is: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization – Luuk Mar 03 '21 at 19:17
  • @Akina, yes, this is an enhancement to InnoDB, because InnoDB had the problem with auto-inc reset. This issue did not affect MyISAM in earlier versions of MySQL. If you're referring to third-party storage engines, they each have their own task to implement something similar. – Bill Karwin Mar 03 '21 at 19:18