SQLB-2399: Problems after updating to TD 6.3 with SQLBase

[Welskop]

Hello,

I have a Problem with my application after upgrading to TD 6.3 and I am not sure if this is the correct Forum to post my Problem. So I will try to explain the Problem I have.

My application is using the SQLBase database that is part of TD 6.3. I configured the database to run as a Service on on a Computer running Windows 8.1 (64 bit).

In my database that is being used by the application there are several database tables defined each of them having a Auto_increment field as a unique identifier. the application is working fine but sometimes I get the follwoing error Message:

"00805 ROW NUD: Insert/update of unique constrained columns with duplicate data"

This happened now several times and with different tables and I have no idea why! I am able to get this error fixed within the database but I am wondering if this is a bug or why it happens.

With using the older TD versions ( TD 6.1 and TD 6.1) I never had this problem before! So I am thinking of installing SQLBase 11.6 from TD 6.2 together with TD 6.3 instead of SQLBase 11.7 from TD 6.3. to see if the problem comes from SQLBase or from TD.

As this problem is not reproducable I am not able to create a test file for you!

So if anybody has an idea how to get rid of this Problem please let me know!

Regards,
Frank

[Welskop]

Still having this Problem

Any helpful is appreciated.

Regards,
Frank Welskop

[H3PO4]

Hi Frank,

you say, that you are able to fix this error within the database - what in detail are you doing to fix it?

Regards
Henning

[Welskop]

I have a look in the table to find out what the last ID in the Auto_increment field is! Then I use the command

ALTER TABLE <tablename>
AUTO_INCREMENT <next idnumber>;

to fix the Problem! Sometimes I have to do it in several tables at the same time!

On Windows 7 I never had this Problem! It is only appearing on the Windows 8.1 machine!

In the Moment I have no idea why this happens!

Regards,
Frank

[Mike Vandine]

Hi Frank,

Is this by chance happening on a database that has been restored and had .log files rolled forward? We have just had a report of the auto_increment columns not being updated when the logs are rolled through.

Best regards,

[Welskop]

Hi Mike,

Yes this could be the case. I usw the "BACKUP SNAPSHOT...." command to Backup and restore my databases and tat man tat Theresa are ".log" files. But the problem only appears on our Windows 8.1 machine. On Windows 7 I never had this problem. Bit the Windows 8.1 machine has now been replaced by a Windows 10 machine. I will observe If the problem also appears on the new computer.

Regards,
Frank

[Welskop]

Hello again,

On Monday I found the time to install the new machine with Windows 10 Home (64 bit). And yesterday I had the same problem again, that I had on the Windows 8.1 machine! I get the error message "Insert/update of unique constrained columns with duplicate data" (see screenshot).

In this case the column "lfdnr_artikel" is the only field in the table with a unique index and it is defined as an "Auto_increment" field.

The last two months we used the application on my "old" Windows 7 machine and this error did not appear once. So in my opinion it must have something to do with Windows 8.1 and Windows 10.

I use Team Developer 6.3 SP1.

Please help! I have no idea how to avoid this error.

Frank

[Mike Vandine]

How did you get the data to the new machine? Was it an unload/load or a restore of a snapshot?

[Welskop]

Hello Mike,

as I mentioned before I used "BACKUP SNAPSHOT..." and "RESTORE SNAPSHOT..." to backup and restore the database. On Windows 7 it has never been a problem, only on Windows 8.1 and Windows 10!

Do you have an idea why??

Frank

[Mike Vandine]

Hi Frank,

Yes, I *do* know why. It's a bug! Namely SQLB-2399.

Fixed with the SB12.0 SP1.

BTW, SB11.7 is not certified for Windows 10. Neither is TD6.3.

Best regards,

[Welskop]

Hello Mike,

thank you for the info! I am glad to hear that the problem is known but I am not using SQLBase 11.7. So the fix is nothing that helps me in the moment! But as you mentioned before, it has something to do with "BACKUP SNAPSHOT...." and "RESTORE SNAPSHOT....".

So if I switch to another backup/restore method I could avoid this error? Is that correct?

Frank

[Mike Vandine]

Hi Frank,

Hmmm. Sorry, I thought that early in the thread you said you were using TD6.3 and 11.7.

What is the SB version that you are using?

I think the problem comes from rolling the logs forward. I think this is what screws up the auto_increment 'next' number. The data actually restores correctly with the proper numbers that they should be, but the next number isn't updated properly. Then when you do the insert, it uses the bad number, which already exists.

The only way that I can see around this is to do what you previously said you were doing:

ALTER TABLE <tablename>
AUTO_INCREMENT <next idnumber>;

Best regards,

[Welskop]

Hi Mike,

sorry that was my mistake! I am using SQLBase 11.7.

What I wanted to say was that I am not using SQLBase 12.0 SP1 and that means that the fix will not help me at the moment!

Do you know when SQLBase 12 will be part of Team Developer installation? Maybe with TD 7.0? Or can you try to find out if it is possible to also get a fix for SQLBase 11.7?

Frank

[Mike Vandine]

Hi Frank,

I believe that 12.0 will be standard with TD7.0.

Best regards,

[Welskop]

Thank you Mike! As it is not known at the moment when TD 7.0 will be available it would be great to get a fix for SQLBase 11.7, too.

Frank