Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
13
Erster Beitrag:
vor 4 Jahren, 11 Monaten
Letzter Beitrag:
vor 4 Jahren, 11 Monaten
Beteiligte Autoren:
Al, GuenterP, Charles U. Schneiter, Arie, JP, Fabrice Harari

[WD16] AutoId's generating huge numbers

Startbeitrag von Al am 03.05.2013 01:49

Hello All

At one client site, a few of the files are generating impossibly high autoid numbers. I am still downloading the data so I haven't had a chance to look too deeply.

I am assuming I can fix this by either forcing a correct AutoID or writing the data into a clean empty file structure.

My question is:
Does anyone have any thoughts as to how it might have happened ?
The site runs MS server 2008. The workstations are a mix of Vista and Win7. The workstations connect across the LAN with 6-10 users, there are no terminal sessions.
The file system is HyperFile Classic. Its an industrial site so power fluctuations are possible, the server and switch are on a UPS but the workstations are not.
Our software has been running there for about 12 years without incident, they updated their server about 6 months ago and have made no recent changes to it.

Regards
Al

Antworten:

Hello Al

on a HF classic DB, this MAY happens if you have a hardware problem just when adding a record. In that case, the value written in record 0 of the table (last autoID) may be incorrect. It's very rare but it's possible...

When the value is lower than what it should, it creates duplicates error and NEED to be corrected.

When it's higher, there is no NEED to correct, as the value are still valid, BUT it means that the hardware needs to be look at (hard drive, power supply, UPS, etc)

And yes, you can "repair" by forcing the IDs, but in this particular case, there is no compelling reason to do so

Best regards

von Fabrice Harari - am 03.05.2013 11:13
Hello Fabrice

The error in this case (on two separate files) was a number so large 9042521604784173 it was impossible to keep going because it could not be used as a foreign key in any other file

I tried every combination of adding and modifying records with HSetIDAuto and HForceIDAuto and although it would change the current record to the correct sequence or write a new record with the correct sequence any records added after repeated (not incremented) the large number. Reindexing using all the options did nothing either.

Clearly the file header was corrupt, so I tried a hex editor to see if the key was visible - no luck. In the end I opened a backup copy of the file and using the hex editor copied the block from the end of the guid number in the header to the start of the data in the field imediately after the autoid field in the first record in the file and copied this into the damaged file over the same space.

To my surprise it worked perfectly. All I had to do then was add a record and force its autoid to the correct sequence and everything is working well

To anyone needing an hex editor HxD editor http://mh-nexus.de/en/ was very useful in this endeavour.

On the other hand, it would probably take PCSoft's programmers about 15 minutes to add an option into WDMap to write a nominated value into the autoid block of the file header.

Regards
Al

von Al - am 03.05.2013 11:51
Hello

This situation keeps getter worse. I created a process to test what happens if the autoid is incremented in each file and found about 40 files with problems. Some I can fix with HSetIDAuto but the others will require a header replacement. The header replacement option works but it worries me because it would be very easy to misplace a byte, so I will also look at writing the data out into a clean file and force the AutoId's.

This is the only site we have with a combination of Vista and Win7 workstations and MS Server 2008 running over the LAN. All our other sites of any size use terminal server sessions.
We advise all our clients to turn off oplocking and to run SMB1 but I suspect that this hasn't occured and this problem will turn out to be related to the SMB2 protocol and oplocking.
From the reponses to Frans index problem, I am now aware of SMB3 so I will look into that also.


Regards
Al

von Al - am 04.05.2013 02:00
Hi Al,

once again, this supports my basic principle of not using any database's auto-IDs at all. I know, when inserting a new file into the analysis, invariably, the Auto-ID check box is shown checked - uncheck it! Always. Invent your own unique key to the file! I know auto-IDs from a few other 4GLs (e.g. TAS until v2.x, after that they switched to Btrieve) and they had their fatal flaws there too. Novell's Btrieve 5.x, imho the most stable database in PC-history, had nothing to offer like an auto-ID. Wise guys.

The bad thing in dBase-type 'relational' database systems (HyperFile is one of the numerous dBase derivatives) is that the last auto-id used is written as the last write operation to the main data file's header. For a new auto-id, the last one used is read from the header - not from the last record written, of course. It's a question of speed but it's THE weak point in using auto-ids.

You could replace the auto-id by your own ID. Read the last record with the highest ID (using HReadLast(..) on the ID's index), add +1 and later on HAdd the new record to the file. This way you could ensure a correct unique ID - except for cases where the database itself is corrupted and contains records with higher IDs which do not show up at HReadLast time! But: file write ops will be slower, of course, because the file's header has to be updated anyway.

However, auto-ids or the ID described above are meaningless, they just tell us the (time) sequence of records written to the file. Why not build a date+time combination as one of the keys? A certain Date+Time combination will never recur, provided you'd set the PC's correct date+time via web on program's start (for the how-to see: http://www.windev.at/wp_de/?page_id=388 ). It's a unique key and you have a correct time stamp in your records too. In order to be able to merge two files you just have to add a user a/o a computer# to the key. computer+user+date+time tells it all and is always unique!

There's an alternative - a GUID. GUIDs are always unique, merging of serveral files is no problem too.

For making sure that self-invented unique keys are built and written on each and every write operation, just make a trigger on HAddd and add your code there.

Kind regards,
Guenter

von GuenterP - am 04.05.2013 06:50
Hello Guenter

I know you are right, but its a bit like chocolate, I know its not good for me but I still eat it :)

The AutoID is just so damn convenient and in my case it doubles as a visible transaction number in data displayed to users.

Regards
Al

von Al - am 04.05.2013 07:34
I agree Guenter - I have always avoided the auto-id option and instead created my own. More work but just felt intuitively a safer solution. I have seen problems with auto-id in several languages. Hopefully SMB3 will give you the solution Al.

von JP - am 04.05.2013 09:41
Quote
Read the last record with the highest ID (using HReadLast(..) on the ID's index), add +1 and later on HAdd the new record to the file.


Guenter, I guess you just gave a brief desciption of the process, but this post is interesting.
You have to handle multi-user in this case. How do you do that?
If 2 or more users do the HReadLast at the same time and before the HAdd is done, you end up with duplicates. Even if the time between the HRead and HAdd is as short as possible, when things CAN happen they will. And you get one of those support calls, you can't get your hands on.

Well one way I use is keeping autoid's in a separate table. Exclusively lock the table, read, increment and save the value you need and finally unlock the table. Add some wait loops in case 2 users try to lock at the same time. One of them has to wait some extra milliseconds.
Oracle has "sequences" for this. I personally think the db-engine is the beste candidate for generating auto-id's. If implemented properly. Which, by nature, on a isam database it's not possible indeed.

But to be honoust: I'm using hfcs in almost all my applications AND the autoid feature.

von Arie - am 04.05.2013 14:53
Quote
Arie Mars
Quote
Read the last record with the highest ID (using HReadLast(..) on the ID's index), add +1 and later on HAdd the new record to the file.


Guenter, I guess you just gave a brief desciption of the process, but this post is interesting.
You have to handle multi-user in this case. How do you do that?
If 2 or more users do the HReadLast at the same time and before the HAdd is done, you end up with duplicates. Even if the time between the HRead and HAdd is as short as possible, when things CAN happen they will. And you get one of those support calls, you can't get your hands on.

Well one way I use is keeping autoid's in a separate table. Exclusively lock the table, read, increment and save the value you need and finally unlock the table. Add some wait loops in case 2 users try to lock at the same time. One of them has to wait some extra milliseconds.
Oracle has "sequences" for this. I personally think the db-engine is the beste candidate for generating auto-id's. If implemented properly. Which, by nature, on a isam database it's not possible indeed.

But to be honoust: I'm using hfcs in almost all my applications AND the autoid feature.


Hi Arie,

this has been just a short description what basically could be done to replace the Auto-ID. Personally, I do not use such a scheme, because it is a vulnerable one! What to do if the index of the file is damaged and HReadLast does not present the really last record of the file? You will end up with duplicates as well! However, if you want to use it in spite of the cons then probably a Transaction will be your friend! Of course, you can use locking as well in order to ensure that no one else accesses the file while reading / writing the record. So, maybe, keeping the ID in a separate file, is better because it will work though the index is corrupt. However, there are at least two file accesses which will take their time.

Personally, I'm using the computername+user+date+time scheme as described, this produces a unique key in any circumstance without accessing the file twice. Provided, computer names are not identical! In addition, there is a time stamp for finding out of who did what and when? My son prefers the GUID solution for producing a unique key to the file, e.g. for POS systems with many cash registers where you can't be sure that an idiot names two of the PCs the same. In both cases there are enough other keys (with duplicates) to use.

Kind regards,
Guenter

von GuenterP - am 04.05.2013 16:17
You are right. There are pros ans cons in every situation. And there is no "one size fits all".
As I said I use the hfcs autoid's for most tables. And use the separate file solution for numbers like invoice,receipts and so on, which must also be unique AND may not contain "holes" in the numbering.
Still we have problems every now and then. But that's due to those idiots you mentioned :rolleyes:

von Arie - am 04.05.2013 17:08
Hi Guenther, Arie,

Very interesting discussion.

In my former IDE (Alpha Five) I used the same scheme as Arie pointed out: Using a separate file (with 3 tries for good measure ;) ). Since there was/is no requirement for a strictly continuous numbering, I pulled the ID first, before saving the record. The only down turn: should the user cancel before saving, the respective ID would be 'wasted'.

However including user-id, machine-name, system time really does kill more than one fly with one fell swoop - why only haven't I thought of this myself ;) One kinda gets a journaling/history out of this practically free of charge... thanks Guenther for the tip.

@Guenther: You mention your son's preference for a GUID. Is he doing this by using WD's built in function GetGUID() or does he roll one of his own? I am using HF Classic as per now. I wonder, though, if I shoud take the pain of switching to HF C/S - but that's another story :confused:

Thanks for sharing!

von Charles U. Schneiter - am 06.05.2013 15:04
Hi Charles,

ref GUID. Originally, my son Alexander made a procedure to make a GUID in W-language, there have been algorithms for doing that in C / C++, because Wx didn't have one: http://www.windev.at/wp_de/?page_id=296 But for now GetGUID() seems to be easier to use. Both procedures return a nice GUID while basically executing the same code! ;)

Kind regards,
Guenter

von GuenterP - am 06.05.2013 15:54
Hi Guenther,

Many thanks for the link!
Will ponder over it and see what I get at :cheers:

von Charles U. Schneiter - am 07.05.2013 13:36
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.