beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Quality of database code, maybe bug?
Hi, I'm currently evaluating your product. On the whole it seems very good in comparison to the few other .Net based products in the market, however I have some concerns over the quality of the database implementation in particular [spRegister]. I'm not sure what the reasoning behind this is (maybe it's a bug), but the following is not a robust or sensible way to insert an incrementing primary key and WILL lead to failure under certain conditions. SELECT @Mem = Max(Mem)+1 FROM pgd_Members INSERT INTO pgd_Members ( Mem, ... ) Values ( @Mem, ...) Please could this be updated in a future patch or release to use a auto-incrementing seed identity on the Mem column? There's no way we could use this as is. Thanks, Mike
|
beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Indecently the same issue occurs in [spCopyForum].
|
ErEf
Version 3 user
- Total Posts : 931
- Reward points : 8015
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
there was a reason for it.. long long ago I asked the same. Just don't know it any more or if the reason still is the same. But you say it WILL lead to problems .. until now it did not.
post edited by ErEf -
|
beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
I'd be interested to hear a valid argument for it.
It certainly won't break every time, but on the odd occasion where 2 people happen to register at the same time can you end up with multiple threads trying to insert the same id into the pgd_Members table leading to a SqlException.
In my opinion this is a bug and really needs to be resolved.
|
APGvNext (Sam)
vNext Dev
- Total Posts : 12482
- Reward points : 218330
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Hi, The reason that we chose not to use identity field a long time ago (it was the SQL 2000 era, 8 years ago) was due to difficulties in data copying / DTS replication. SQL server up to 2005 (never tested data migration on 2008) has a serious problem migrating identity fields correctly and that was why I decide not to use identity fields for certain tables (those important ones that you don't want to lost during migration). http://connect.microsoft....-in-export-data-wizard I have seen this many times already whenever our clients need to send data from one server to the other, data inconsistency issues occurred on 'identity' columns. I agree with you that spRegister implementation can be optimized (I have an idea that does not require identity), but so far we have never heard of problems reported about the member table, even on really big forums.
|
beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Thanks for the reply.
Maybe I'm missing something but does the forum software have any features that utilise SSIS? I don't think this is a valid argument for introducing a bug in the code. There are a number of possible alternatives and you don't even necessarily need a seed identity column to fix the bug, but it certainly is the most sensible solution.
If we were to purchase the software can we expect the bug to be fixed, and in what time frame?
As I say we couldn't use this codebase "as is" for our client as it's an error waiting to happen which is not acceptable. This means we have to patch it ourselves and this in turn affects the ease with which it can be upgraded and therefore adds risk to our project in choosing your product. It would be more reassuring if we knew this would be fixed in the near future.
Do we need to raise this through an alternative channel? Please let me know.
Thanks,
Mike
|
APGvNext (Sam)
vNext Dev
- Total Posts : 12482
- Reward points : 218330
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
thanks Mike for your input. It is best to put these code related questions in the trial support forum instead of here. But it is fine.
To address your concern: as I mentioned in my previous reply, I have an idea how to deal with this. I will implement a simple mod to this stored procedure in the final 3.8 build which will feature a new mobile theme too. It will be available in a month or so.
Let me make a few things clear: this is not an intentionally introduced bug. And even though the software does not use SSIS, we have found the need to make client's data migration tasks easier as many are using the software on shared hosts.
For any implementation, we need to evaluate practical situations and we have found that actions performed on these tables are largely read and update, with very little "insert", percentage wise.
|
beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Ok thanks for your reply, good to hear a fix will be on the way.
I appreciate it's not an intentional bug and the likelihood of it occurring is minor in most scenarios, it's just that we have exceptionally high standards in the work we produce for our clients and this needs to carry over to any third party components we integrate with.
|
APGvNext (Sam)
vNext Dev
- Total Posts : 12482
- Reward points : 218330
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Thanks for your explanation, and rest assure this will be dealt with in 3.8.
|
beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Just to reinterate this in a another real world example...
I'm currently trying to write migration scripts to transfer many 1000s of users and posts from another system. The lack of seed identity is a real headache in moving data, and the more I look at the current implmentations it's really making me cringe. I hope that you'll consider using seed identity in the future as that's how sql server solves this particular problem, and it solves it very well. There are ways to make this work with SSIS.
|
APGvNext (Sam)
vNext Dev
- Total Posts : 12482
- Reward points : 218330
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
The implementation I have might have an impact on importing from another system without using our built in stored procedures (I don't know how you are doing it, I am just guessing), but have you considered importing the user ID and message ID along with the rest of the data from the old db without relying on an id field?
Since you already have ids for everything, without id fields on the forum db it is actually a lot easier if you want to migrate from another system because it will be a lot easier to create redirects for your messages, since the ids will remain the same.
|
beelineuk
Version 3 user
- Total Posts : 16
- Reward points : 1620
- Joined:
- Status: offline
Re:Quality of database code, maybe bug?
(permalink)
Hi,
I'm importing from 3 systems so I can't just import the existing ids unfortunately. The lack of seed identity is an issue and the fact that your using a lookup table in places to create your own incremental id implementation is very worrying (and not thread safe). Please please please change this in the future, it's a major flaw in the schema design (in my opinion). Seed identity really is the way to solve this, and it's not like it can't be turned off while scripting ids if necessary.
For now I'm patching some of the tables/procs to use seed identity myself. I'm also using cursors where possible so that I can call the ASPPG stored procedures and avoid id issues. I'm also inserting content directly into some tables in places where cursors and not workable due to performance reasons, and in this case updating your pgd_Helper table to stop everything breaking.
Thanks,
Mike
|