MySQL Bugs: #36444: 'autogenerateschema' produces tables with 'random' collations

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

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.

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.

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.

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).

Poul, can you please give me steps to reproduce this problem in VS2005?

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.

Hi Poul. Verified as described.

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

Fixed in 5.2.3

An entry has been added to the 5.2.3 Changelog:

The autogenerateschema option produces tables with incorrect collations.