MySQL Bugs: #70409: MySqlSessionStateStore : exception "Duplicate entry"

Bug #70409	MySqlSessionStateStore : exception "Duplicate entry"
Submitted:	24 Sep 2013 15:35	Modified:	28 May 2014 5:45
Reporter:	Anatole BAUDOUIN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.7.4	OS:	Windows (server 2008)
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any

Description:
Hello,

A bug occurs randomly with MySqlSessionStateStore for .net
Message: Duplicate entry 'flk2j5eo03bl2baetepjp3sc-3' for key 'PRIMARY'

I am not able to repeat it because it is random, but it is linked with expired sessions.

The bug occurs at least once a day depending on the number of visitors ...

Once the problem occurs, the site is completely blocked on the session and the only way is to close and reopen the browser to start a new session.

Another person had the same problem on StackOverflow:
http://stackoverflow.com/questions/18397376/mysql-session-state-provider-crashes-on-expire...

I try to delete/rebuilt "asp_net" database but the bug still occurs.

Connector version : 6.7.4
MySQL server version : 5.5.33
Serveur: Windows Server 2008
.net version : 4.5 (C#)

ERROR LOG :
Message : Duplicate entry 'flk2j5eo03bl2baetepjp3sc-3' for key 'PRIMARY'
Source : MySql.Data
StackTrace : MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'flk2j5eo03bl2baetepjp3sc-3' for key 'PRIMARY'
   à MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   à MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   à MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   à MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   à MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   à MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   à MySql.Web.SessionState.MySqlSessionStateStore.CreateUninitializedItem(HttpContext context, String id, Int32 timeout)
TargetSite : MySql.Data.MySqlClient.MySqlPacket ReadPacket()

How to repeat:
Unable to reproduce...

I have the same problem in many websites.

is easy to reproduce setting the sessionState timeout="2", open the website in the browser, wait 2 minutes and refresh the page.

I have the same problem. Any solution?

Same problem here!
MySql.Web version 6.7.4.0

MySql.Web.SessionState.MySqlSessionStateStore.CreateUninitializedItem(HttpContext context, String id, Int32 timeout)

Duplicate entry 'apvtkutpyj3np5zwau2aqt3c-1' for key 'PRIMARY'

This is still occurring with .net connector 6.8.3, but the problem does not occur with 6.5.4 and that's what I'm having to revert back to. It's got it's own host of problems, but it's been stable on iis for the past year.

In the file SessionProvider.cs:  By always using an INSERT query, the code is assuming that no row for the session key already exists in the database -- which works fine for when the session is created, but throws an exception if the session key gets re-used.

I fixed it with some similar code already within the same file, and I'm no longer getting the exception.  The modified code uses a REPLACE query instead of INSERT.

I am attaching my updated file and a patch.  (my base version is 6.8.3)

Patch for SessionStateProvider.cs

Attachment: SessionStateProvider.patch (application/octet-stream, text), 3.35 KiB.

Fixed SessionStateProvider.cs

Attachment: SessionProvider.cs (text/plain), 32.87 KiB.

Hi, 

Thanks for comment on using replace.

The striking thing is that duplicate entries are appearing in the first place, so far I have not been able to reproduce with stress tests.

The problem I see with using replace, is that it will be overriding an otherwise legitimate session with new values, ie. making a shopping cart for a user suddenly change to show the content picked by another user.

Still working at this, will keep updated.

@Fernando Gonzalez.Sanchez --

This bug is very easily reproduced:

1) Create a new asp.net project
2) Configure it to use the Mysql session provider. Set the session timeout for 1 minute.
3) Within the project, create a page that writes something to its Session property.
4) Start the project, making sure that it opens the page from step 3 in a browser.
5) Wait one minute plus a few seconds.
6) In the browser, refresh the page. The exception should be thrown.

I wouldn't call this "stress" testing at all. As a matter of fact, it's very straight forward.

As for your point of view on using replace, your approach couldn't be further from the way things actually work. The session *data* is not re-used. It's the session *ID* that's relevant. This is by design, and is well documented.

http://support.microsoft.com/kb/899918

How this works:

1) User accesses any page within the subject ASP.NET web site. Accordingly, if this is a first time, the page content is returned along with a session ID generated by the web server -- as a cookie. For this case, let's assume that on the back end, the code has populated the mysql session state database with some data that corresponds to the newly generated session id.
2) Moving along -- assume that the user does nothing on the client side such that time configured in the session timeout value elapses.
3) The user uses "refresh" in the browser. On this pass, the browser pushes the cookie previously described back to the IIS server. The ASP.NET library pushes this down to the session state provider. The session state provider discovers that this is an outdated (expired) session id (because it already exists in the database), and accordingly *wipes* any old session data associated with the ID, but *continues to use the session ID* for use with a *new* session data -- a blank slate.

It's really not that difficult. I didn't create it, but that's how it works.

@Fernando Gonzalez.Sanchez --

This bug is very easily reproduced:

1) Create a new asp.net project
2) Configure it to use the Mysql session provider. Set the session timeout for 1 minute.
3) Within the project, create a page that writes something to its Session property.
4) Start the project, making sure that it opens the page from step 3 in a browser.
5) Wait one minute plus a few seconds.
6) In the browser, refresh the page. The exception should be thrown.

I wouldn't call this "stress" testing at all. As a matter of fact, it's very straight forward.

As for your point of view on using replace, your approach couldn't be further from the way things actually work. The session *data* is not re-used. It's the session *ID* that's relevant. This is by design, and is well documented.

http://support.microsoft.com/kb/899918

How this works:

1) User accesses any page within the subject ASP.NET web site. Accordingly, if this is a first time, the page content is returned along with a session ID generated by the web server -- as a cookie. For this case, let's assume that on the back end, the code has populated the mysql session state database with some data that corresponds to the newly generated session id.
2) Moving along -- assume that the user does nothing on the client side such that time configured in the session timeout value elapses.
3) The user uses "refresh" in the browser. On this pass, the browser pushes the cookie previously described back to the IIS server. The ASP.NET library pushes this down to the session state provider. The session state provider discovers that this is an outdated (expired) session id (because it already exists in the database), and accordingly *wipes* any old session data associated with the ID, but *continues to use the session ID* for use with a *new* session data -- a blank slate.

It's really not that difficult. I didn't create it, but that's how it works.

If I were to set the sessionstateprovider timeout in web.config to longer than the session timeout would that avoid this bug, at least as a temporary measure.

Richard Creer, that probably won't work.

Here's my change:

https://github.com/xavierjefferson/mysql-web-fixed

If you re-compile MySql.Web, you'll need the MySql.Data binary.  Or you can use the binaries I built (.NET 4.0)

Xavier, many thanks. I've implemented your dlls and we shall see!

Ah, I haven't implemented Xavier's dlls because I've put them in /bin and the originals are in the GAC with the same version number. So I guess I have two options 1) install Xavier's dlls in the GAC or 2) uninstall the originals from the GAC. This is outside my area of expertise so any advice would be much appreciated. This is perhaps not the best place to ask this question but it is relevant if anyone else is adopting this solution.

Richard, you could using <probing> or <dependentAssembly> in your app.config / web.config to tell the runtime to look for the DLLs in a specific place (other than GAC and BIN folder).

http://msdn.microsoft.com/en-us/library/823z9h8w.aspx

http://msdn.microsoft.com/en-us/library/twy1dw1e(v=vs.110).aspx

I'm getting well out of my comfort zone here but from my reading of those links probing comes into effect only if the dll is not in the GAC, Assuming you are referring to <dependentassembly><bindingredirect> I don't see that working because both dlls have the same version.

Perhaps I uninstall the "official" 6.8.3.

I've uninstalled the 'official' 6.8.3.0 so it isn't in the GAC, Xavier's replacement DLLs are in \bin and it fails with "Could not load file or assembly 'MySql.Web, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)"
Any ideas where to go from here?

I really don't understand how you can use 7 months to fix this bug...
Yet it is really serious and easy to reproduce.
I remember to have already report a problem few years ago but it was before Oracle buy MySQL...
The team responded quickly (few weeks).

I think I will migrate my projects under MariaDB.

We need a reliable way to reproduce this, so far, has not worked out.

Some of the solutions provided are not quite acceptable (like using REPLACE instead of INSERT), for this will override user sessions.

This may have something to do with IIS process recycling, will check that theory...

Fernando Gonzalez Sanchez,

Overwriting the session data *IS THE EXPECTED BEHAVIOR*.

This behavior is put in such that if a user's session expires, if the user refreshes the page, the old session key is re-used.  It's very well documented and has been part of ASP.NET behavior from the very beginning.  You could compare the SQL Server provider and see that it does the same thing.  Wow, this issue is getting old.

The 25 Sep 2013, Alejandro Melis have said :
"is easy to reproduce setting the sessionState timeout="2", open the website in the browser, wait 2 minutes and refresh the page".

I have tested to modify the timeout in web.config and I just put 10 seconds.
Go on a page, wait 10 seconds, press F5 and the bug appears.

So It is really easy to reproduce...

We can't help you more like that.

Look, I appreciate your efforts and interest, on this.

I and other engineers have tried to reproduce, following those instructions and variations in timeouts with no luck.

If override sessions is acceptable, then the REPLACE solution may fit the bill.

Rebuilding Connector/NET is quite trivial, if you need details, I can provide, so shouldn't be a big blocking issue (I can even provide you with a non-official build with the fix mentioned).

*But* It is important to be able to reproduce, otherwise we just cannot send the fix to QA and say "here's this fix, but there's no way to repro the original prb, and thus verify if it is fixed" : s
(without QA approval, the build cannot be officially released).

Anyway, looking again at this...

A non-official build, as long as it can be installed, would be great because things are getting a bit desperate out here. However I must point out that I have tried but failed to install Xavier's fix due, as far as I can tell, to issues relating to version numbers.

Hi, I have shared a non-official build with the fix with Richard Creer, if anyone else who can repro, is interested in trying this, please answer.

Thanks.

I commented on this issue on 15 Nov 2013 9:39.
I ended up using a REPLACE instead of INSERT, but as you state Fernando this is probably not the best solution.
I'm interested in evaluating your unofficial fix.

I would also like to use the unofficial build.

Fernando Gonzalez Sanchez -
Could you please elaborate how and what has been fixed regarding the issue in this unofficial version?

Thanks

The fix is just using REPLACE instead of INSERT (in fact REPLACE was already used in some parts of this code before).

We are exploring other options like using INSERT ON DUPLICATE.

For now, the main obstacle for officially releasing this, is not being able to repro.

Verified, the key to reproduce was using <SessionState ... regenerateExpiredSessionId="true" ...> which by default is false.

A formal fix should be available in the next couple of weeks with release of Connector/NET 6.6.7 MR.

That's excellent news but why 6.6.7?

I thought about the regenerateExpiredSessionId parameter, but the default value is true so I didn't notice... 

Anyway, thank you very much for this fix !

Connector/NET 6.6.7 is next in our calendar, but eventually the fix will make it also into v6.7.6, v6.8.4 (both GA) and v6.9.1 (Alfa 2).

Fixed as of the upcoming Connector/Net 6.6.7/6.7.6/6.8.4/6.9.1 releases, and here's the changelog entry:

When creating a new ASP.NET project and the session timeout expired,
refreshing the web page would raise an exception about a "duplicate entry"
in the "MySqlSessionProvider".

Thank you for the bug report.