MySQL Bugs: #47332: SessionStateProvider produces tables with wrong collation

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

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.

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.

Sounds ok, Membership ans RoleProviders work fine.

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

fixed in 6.1.3

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.