Bug #47332 SessionStateProvider produces tables with wrong collation
Submitted: 15 Sep 2009 16:39 Modified: 18 Sep 2009 13:06
Reporter: Poul Bak Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.1.2 OS:Any
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: SessionStateProvider

[15 Sep 2009 16:39] Poul Bak
Description:
This bug is actually the same as http://bugs.mysql.com/bug.php?id=36444 - only this time it's SessionStateProvider.
SessionStateProvider autocreates tables, fine, but it uses the SERVER's default collation, not the DATABASE's default collation.
ALWAYS use the database default!

In my case, I'm on a shared host, meaning I can only control my own database and I do want all my tables and coloumns to use utf-8. This gives a clear consistent view and use and often prevents strange errors.
I do realize that in this case it's purely visual, since the only coloumn using a collation is the session id column, which probably only contains ascii characters anyway. But, as I said, consistency prevents errors - and the right collation to use for new tables IS the database's default.
Non-americans are moving to unicode, please let us do that as easily as possible.

How to repeat:
Just use a MySqlSessionStateProvider. Set the database' default collation to for instance 'utf-8'. Notice the autogenerated tables will use the server's default collation (normally latin1_swedish_ci) - not the database's default (in this case utf-8).

Suggested fix:
Read http://bugs.mysql.com/bug.php?id=36444 for the solution.
[16 Sep 2009 8:02] Tonci Grgin
Hi Poul and thanks for your report.

From what I see in trunk, the problem is in file "schema5.sql" where "my_aspnet_Sessions" table is defined with "DEFAULT CHARSET=latin1;" (so it's nothing to do with server default).
What's missing in "schema5.sql", to my opinion, is "ALTER TABLE my_aspnet_Sessions CONVERT TO CHARACTER SET DEFAULT;" as can be observed in "schema4.sql" (added as a fix to your previous bug I presume).
Please see if adding this line helps.

Verified on the basis of inconsistency in schema generation scripts.
[16 Sep 2009 10:55] Poul Bak
Sounds ok, Membership ans RoleProviders work fine.
[16 Sep 2009 20:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83524
[16 Sep 2009 20:04] Reggie Burnett
fixed in 6.1.3
[18 Sep 2009 13:06] Tony Bedford
An entry has been added to the 6.1.3 changelog:

When tables were auto created for the Session State Provider they were set to use the MySQL Server's default collation, rather than the default collation set for the containing database.