IDGEN Duplicate Error

Madimant

Morning

Can somebody please explain to me how the IDGEN works in version 8.3 on MySQL?
I have seen some discussions on the IDGEN table but still not making sense to me.
I thought every set of tables would have their own IDGEN Table?

I had to recreate my server files last week and reinstalled 8.3 on the server.
Since then I get Primary key duplicate errors.

I have two Business Spaces with Databases each with their own Databases Tables outside of BASDB.
The only IDGEN I can find is in the BASDB and BASDBTEST databases and none in my 2 BS Tables?

I installed AwareIM 8.3 on the server, created my two BS in the Configurator, imported the latest versions, published and then restored my data tables with MySQL Workbench. Now I randomly get the 'Primary key Duplicate' error.

I can see that all the databases are using the BAS_IDGEN from the BASDB as when I get an error, the same number is in the BAS_IDGEN table? and this is a low number ie 2748 vs some my BV table ID which is already in the 167 000?

How does the BAS_IDGEN know what number to use after a restore?
I upped the MAX_ID number to 20 000 too overcome the errors but not feeling very confident in doing restores in the future without understanding implications.

ddumas

No sure if this helps, but in AwareIM SQL Server, I am seeing a database sequence BAS_IDGEN_SEQ. I am wondering that maybe ALL ID columns use this "generic" sequence, and if so, that a database call to get the next value of the sequence is called just before a new record is written to the database.

In SQL Server, I always use IDENTITY columns (auto-increment) for this purpose. Maybe some databases do not have that feature.

Dave

ACDC

How does the BAS_IDGEN know what number to use after a restore?

After restoring your databases, manually edit the IDGEN table to start allocating new ID numbers from a number that does not exist. It seems IDGEN is trying to create an ID that already exists. The IDGEN number should be the next highest number. I think you have done that by bumping it up to 20 000 so it should be fine

When running separate DB's outside BASDB, IDGEN in BASDB manages the creation of all new Objects.ID in whatever DB. Doing a brand new install will start BASDB at 1. So if you restored your data into BASDB it would have been fine because IDGEN would have been restored as well. But separate DB method does not have its own IDGEN

Part of your restore process should have included BASDB, the IDGEN would have been restored and started at the correct number

If you are using separate DB's you should always consider BASDB as part of your database backup and restore procedure

Madimant

Thanks for the replies.
Dave, I think the BAS_IDGEN_SEQ only works with SQL and not MYSQL... couldn't find any with that name.

Thanks for the explanation ACDC, that's what a did in the end (manually upped the number) but I stupidly assumed that every set of tables would have their own IDGEN.

Sorted now but scary at the time as I thought all my data was corrupted.

customaware

Only in MSSQL and MariaDB. Not MySQL.