Mysql Auto increment Indexing Issue

[bhargav1991]

I want to cleanup the database and start a fresh.

I truncated all the tables Accordingly.

Suppose i want to cleanup all customer data 

i truncated all the tables which were referring id_customer field which is a primary key in customer table.

But when i add a customer the indexing of id_customer starts randomly .

Sometimes it starts with 6 sometimes 1 .

And in between it skips the id , checkout the image for better understanding

 

 

[Nishith Nesdiya]

Hi...

please use the "prestashop cleaner module" truncated  prestashop data

Thanks

[Scully]

Run this command against your database:

ALTER TABLE ps_customer AUTO_INCREMENT = 1;

And you have to be sure that you have truncated all tables referencing to id_customer.

[bhargav1991]

Run this command against your database:

ALTER TABLE ps_customer AUTO_INCREMENT = 1;

And you have to be sure that you have truncated all tables referencing to id_customer.

Already tried that, the issue seems to be clustered environment.

I have 3 mysql servers clustered.

Surprisingly when i change the engine from innodb to myisam the indexing seems to be working properly

[Scully]

The missing information in the first part was that you use a MySQL cluster. Auto increment does not work the same way with clustered database compared to a single instance setup. In clustered environment, each cluster prefetches some auto increment values specifically and only used for this cluster.

 

For example:

Cluster 1 gets auto increment 1-5

Cluster 2 gets auto increment 5-10

Cluster 3 gets auto increment 11-15

 

Depending on which cluster is processing the request, the auto incr may be different.

This is intended by design and not a bug. It helps to avoid inconsistencies with auto increment values when insert commands are processes in parallel on the same tables.

Edited July 9, 2017 by Scully (see edit history)