Hi, we have the case where the IDs of the user table reaches its limit. The field has the type int(11).
Does there exists a tool to compact the IDs.
We have only 300.000 users. But somehow are users registering and then deleting themselves and their data again. I guess bots or hackers. The result is big gaps of 100.000 or more not used IDs between long time use users.
We know how to do this manually, but maybe there already is a tool for fixing the situation.
Thanks
@Andrew_es Are you sure that there is not some other reason for this? A subscriber is not able to delete themselves, so if you have had lots of fake subscriptions they would still be in the database. There must be manual actions by an admin to delete subscribers.
I think that an int is a signed 32 bit field so hitting the maximum value would take one subscription each second for 68 years.
@duncanc - Thanks for the info. Yes, there could be other reasons this could happen. I don’t know what. I just guessed.
I just have the fact that the ID numbers aren’t normal, and AUTOINCREMENT has reached the limit of int(11).
Here the last 50 IDs used:
2147483647
2072223457
2058109200
2038662592
1985703076
1967670497
1909933185
1876955979
1826198615
1793856332
1779044967
1764612018
1764052406
1742007182
1718206218
1628943170
1625782742
1405034839
1056762917
844618294
643257142
619505072
394724569
264906804
222478112
75637602
75636875
75636097
75635230
75634291
75633384
75632074
75631459
75630671
75629966
75629173
75628318
75627411
75626586
75625864
75625203
75624520
75623859
75623114
75622327
75621513
75620804
75620071
75619435
75618786
This is the table phplist_user_user.
Could this be when they move the system and reimport the DB ?.. I don’t expect it to be at updates of phpList. But there could be other reasons the admins move the system and reimport the DB.
Thanks for your informative comment, duncan.
@Andrew_es I guess that there is a process importing subscribers that is causing the auto increment field to increase. There are some mysql statements that will increment the field even though a subscriber was not successfully added such as “replace into” and “insert … on duplicate key update”.
I suggest raising this with your system administrator/database administrator.
@duncanc - Thanks for your suggestions.
So a sys admin / db admin is expected to have a tool to fix such a thing in PhpList ?..
I personally never have met such a tool. But maybe it exists for PhpList. - That was one of my first questions.
I am left with the understanding PhpList doesn’t have a tool for such case. So one of my first questions has been answered.
I will leave the topic public, if somebody else one day has the question / challenge. He can then read such does not exist.
Thanks in any way for your efforts, Duncanc.
No, I meant that they might be able to explain what is causing the ID column to keep changing. Possibly a backup/restore that is incorrect or importing new subscribers that also is not working as intended.
I concur with Duncan. I have millions of subscribers in phpList, and and never run out of id’s.
There are those tools in the web interface (check db structure, re-index database). That has helped me occasionally, it’s just something to do if you haven’t done it already.
It might take some ‘fancy footwork’ to correct… you would need to keep all the id values in every table where it’s used sync’ed… at which time you could reset the userids to be contiguous, and set the autoincrement value in the user_user table to the proper value (highest autoincrement value +1).
Do your experimenting on a backup copy of the database (not the live copy).
Thanks for your participation, Dan.
There is (to me) an external department running server and doing admin stuff. Backups are nothing I can influence.
The case is left for now with converting int(11) to bigint.
