Gaps in UserID sequence

If you have questions about using mojoPortal, you can post them here.

You may want to first review our site administration documentation to see if your question is answered there.

This thread is closed to new posts. You must sign in to post in the forums.
6/14/2014 5:57:30 AM
Gravatar
Total Posts 503
feet planted firmly on the ground

Gaps in UserID sequence

I've noticed that the UserIDs for the most recent users who have registered on a site are not sequential, and, bizarrely, the last few are at 1000 intervals. Everything was as expected until May, then it started going strange.

SELECT TOP 25 UserID, DateCreated

FROM [mojoPortal_BreconFans].[dbo].[mp_Users]

ORDER BY DateCreated DESC

produces:

UserID DateCreated
9420 2014-06-13 08:41:19.453
8420 2014-06-13 06:59:05.763
7420 2014-06-10 20:22:40.647
6421 2014-06-10 00:45:44.473
6420 2014-06-09 19:55:49.363
6419 2014-06-05 17:37:49.390
6418 2014-06-03 10:12:36.750
6417 2014-05-28 10:34:21.777
5417 2014-05-27 11:59:12.527
4417 2014-05-24 20:15:26.150
3417 2014-05-22 20:35:23.763
2419 2014-05-15 20:04:08.737
2418 2014-05-13 22:36:06.893
2417 2014-05-13 19:57:03.253
2416 2014-05-12 02:23:52.940
2415 2014-05-11 18:39:56.487
2414 2014-05-11 09:37:48.143
1418 2014-05-08 16:15:36.837
1415 2014-05-06 15:51:21.337
1414 2014-05-06 12:37:04.970
414 2014-04-30 15:43:19.527
413 2014-04-23 05:57:16.797
412 2014-04-20 08:15:28.283
411 2014-04-16 22:42:38.283
410 2014-03-30 12:32:15.657

The database structure appears to be fine (not sure why the seed is 4, but that should not cause what we are seeing above):

[dbo].[mp_Users]

[UserID] [int] IDENTITY(4,1) NOT NULL

and there is nothing in the System Log.

Any ideas? Should I be concerned? Or could this indicate something wrong in the code - failed inserts resulting in gaps in the sequence?

mojoPortal Version 2.4.0.2 MSSQL
Operating System Microsoft Windows NT 6.3.9600.0
ASP.NET Info v4.0.30319 Running in Full Trust
 

6/24/2014 1:21:13 PM
Gravatar
Total Posts 18444

Re: Gaps in UserID sequence

That is strange, in my db the identity seed is 1 and the increment value is 1, which should start at 1 and increment by 1 for each new row.

I have no explanation for what you are seeing, you might want to put a sql trace on the database and see if something else could be adding and deleting rows.

Sorry for the delayed response there was a problem with our forum notification email not working

6/27/2014 1:47:38 PM
Gravatar
Total Posts 115

Re: Gaps in UserID sequence

This database was originally a SQLCE, then migrated to SQL Server, so that might explain the odd seed value, but that doesn't explain the add sequence. It is still happening:

14423 2014-06-26 20:40:15.853
13423 2014-06-25 21:36:50.967
12423 2014-06-24 10:03:07.233
11425 2014-06-23 04:08:36.647
11424 2014-06-22 17:34:20.477
11423 2014-06-22 17:19:30.600
10423 2014-06-21 17:22:57.583
9423 2014-06-19 22:00:17.297
9422 2014-06-15 13:08:41.120
9421 2014-06-14 19:34:37.860
9420 2014-06-13 08:41:19.453
8420 2014-06-13 06:59:05.763

and SELECT IDENT_CURRENT( 'mp_Users' ) returns 15422 showing that another thousand failed inserts have taken place since the last record was added. I say failed inserts because those would increment the identity value, but so would creating and deleting a row of course. I'm guessing something is regularly trying to create accounts in my site, and gives up after 999 attempts. However the site shows no other signs of being under attack (no spikes in Google Analytics etc).

After adding a new user manually, this goes to 15423 as expected. I cannot make a failed insert through the registration UI - validation appears to prevent attempts to write to the DB, as it should. However I don't see what else could be causing this. I'm not sure whether/how to add a trace to the database hosted on Arvixe?  I'll look into this, but any tips appreciated.

6/27/2014 1:58:42 PM
Gravatar
Total Posts 18444

Re: Gaps in UserID sequence

could there be a developer machine that has a copy of the site and the connection string pointing to the production db?

I would start with the IIS logs looking for any clues I could find, if that is happening from web requests to the site then there should be some kind of clue in the log about how it is happening and the time stamps could be correlated.

if nothing in the IIS log then it must be happening by a direct connection to the db

6/28/2014 6:01:54 AM
Gravatar
Total Posts 115

Re: Gaps in UserID sequence

There's no dev environment connecting to the DB on Arvixe. I've had a quick look in IIS logs and there are a lot of hits on register.aspx from one i.p. that could possibly correspond to the problem, but it's not conclusive as there are lots of log files with only a handful of hits on this page.  I need to load the logs into a database and get forensic, if I can find time.

I am also wondering whether it could be something to do with the Janrain social sign-in, which is enabled on this site, but there are not huge numbers of hits on OpenIdRpxHandler.aspx (which I assume is used for this?). What should I look for?

I've had a very quick scan of the mojo code for things that might be re-trying 999 times against the mp_Users table, but not spotted anything in the User business areas. DBSiteUser.UpdateUser gets called a lot more than I expected during registration, but that is obviously not the problem!. In passing though I spotted some other things that may possibly be worth noting - perhaps bugs:

  • DateOfBirth is not present in the public properties of SiteUser class (SiteUser.cs).
  • On creating a user, if the entered DateOfBirth does not parse as a date in the CurrentCulture, it is instead added to the mp_UserProperties table. If the value does parse, it is added to mp_Users.DateOfBirth, and an empty value is added to the mp_UserProperties table. That doesn't seem quite right?
  • The calendar control uses the profile settings datePickerYearRange="c-100:c+0" to control what years are available. However on opening the calendar the actual range is calculated from the value in the control, if one is present. This means that the years available change depending on what already entered. Presumably it should be relative to the current date, not the value in the control. I would like to have it work for users between 16 and 80 years old, for example, and this goes crazy if we set the range to ="c-80:c-16" because the year entered then cannot exist in the calendar when it is re-opened.

I'll post more if I find the cause of the UserID gaps.

 

 

3/22/2015 6:19:35 AM
Gravatar
Total Posts 503
feet planted firmly on the ground

Re: Gaps in UserID sequence

For info, here's the explanation, and possible solutions (if you can control the SQL Server).

http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is

In my case this started when Arvixe migrated the database onto SQL 2012.

You must sign in to post in the forums. This thread is closed to new posts.