Bug #36444 'autogenerateschema' produces tables with 'random' collations
Submitted: 1 May 2008 0:59 Modified: 8 Jul 2008 15:25
Reporter: Poul Bak Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.2.1 OS:Windows
Assigned to: CPU Architecture:Any
Tags: autogenerateschema roleprovider membershipprovider

[1 May 2008 0:59] Poul Bak
Description:
Client: Windows XP sp2, Visual Web Developer Express 2008, MySql connector 5.2.1
Server: Linux ?, MySql 5.0.27-standard

First the good news: I successfully created a membership- and roleprovider using the 'autogenerateschema' option (even though the database is external, accessed through web - I'm impressed :-). 
It works through Vwd's Asp.net configuration, really great.
However:
When checking the autogenerated tables, I noticed that some use the 'utf-8 danish_ci' collation (as they should=standard for the database), but some use 'latin1-swedish_ci' !!!
I don't know where the connector has got that swedish from, not used in my database at all.

How to repeat:
Install the connector 'from scratch' using the 'autogenerateschema' option to generate new tables. Check the collations.

Suggested fix:
Make sure the tables use the 'default' table collations.
[2 May 2008 8:15] Tonci Grgin
Hi Poul and thanks for your report.

I don't believe it's c/NET related as 'latin1-swedish_ci' is the default collation built in server. I presume it's because of your server / connection settings.

Please provide full test case and my.ini / my.cnf file used to start MySQL server.
[2 May 2008 21:40] Poul Bak
Quote: "'latin1-swedish_ci' is the default collation built in server."

That's the bug! You should NOT use the server's default collation, but the database's default collation (when the database already exists, as in my case).

I'm on a webhotel/shared server and can only access via phpMyAdmin - 2.11.2.1.
I have set the database's default collation to 'utf-8 danish_ci'.
I just created a table to test (without specifying collation), it gets the 'utf-8 danish_ci' collation, as it should.

So basically, you should double-check the commands you send to the server, making sure you use the correct collation, which, as mentioned earlier, is the database's default, not the server's default.
[3 May 2008 17:31] Poul Bak
Update:
Just for you, I deleted the tables and started from scratch again, autogenerating the tables. As mentioned, some tables are fine, some not.

The following have the correct collation (utf-8 danish_ci):
my_aspnet_Applications
my_aspnet_Profiles
my_aspnet_SchemaVersion
my_aspnet_Users

The following have the wrong (latin1 swedish_ci):
my_aspnet_Membership
my_aspnet_Roles
my_aspnet_UserInRoles

So, there must be a difference in the way, they get created.

Hope this will help you solving the problem.
(I have manually changed to correct collations now, so it's not urgent).
[19 May 2008 11:14] Tonci Grgin
Poul, can you please give me steps to reproduce this problem in VS2005?
[19 May 2008 22:09] Poul Bak
I downloaded the source and found the bug!

In the schema you specifically set the character sets to 'latin1':

CREATE TABLE mysql_Roles(`Rolename` varchar(255) NOT NULL,
                `ApplicationName` varchar(255) NOT NULL, 
                KEY `Rolename` (`Rolename`,`ApplicationName`)
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Change that, and it will work.
[20 May 2008 12:31] Tonci Grgin
Hi Poul. Verified as described.
[20 May 2008 15:15] 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/46866
[20 May 2008 15:16] Reggie Burnett
Fixed in 5.2.3
[8 Jul 2008 15:25] Tony Bedford
An entry has been added to the 5.2.3 Changelog:

The autogenerateschema option produces tables with incorrect collations.