MySQL Bugs: #53174: Membership schema creation fails if default schema collation differs from Latin1

Bug #53174	Membership schema creation fails if default schema collation differs from Latin1
Submitted:	26 Apr 2010 19:04	Modified:	22 Jul 2010 15:29
Reporter:	Kostya Khodykin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.2.3.0	OS:	Windows
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	can't, collation, cp1251, create, user

Description:
I tried to use mysql aspnet membership using mysql connector.net on the database with cp1251 default collation. Autogenerateschema was turned on. After schema was generated, i tried to create user, but connector reported a DB related error. After experimenting wit connector sources i found out that schems atructure differs from expected by connector. That happens because the third script for schema creation fais to execute on the databases with default collation other then latin default. 

How to repeat:
1. Install Connector.Net v 6.2.3
2. Create schema on the server wit default collation e.g. cp1251.
3. Create web site and configure it to use mysql aspnet membership.
4. Make MySql connection string, which is used for membership, point to the schema, created in step2.
5. Launch Asp.Net site configuration tool.
6. Try to create some user

Actual result:
Asp.Net site configuration tool displays an error

Expected result:
User should be created succesfully.

Suggested fix:
I suggest to remove statements which set default collation to latin1 in the script that creates tables in the first schema creation script.

All these scripts are located in the connector's resources.

Hi Kostya and thanks for your report.

I see this could really present the problem, let me consult.

In the meantime, can you attach a sample project used to produce this error together with DDL/DML scripts for cp1251 database and an output of 'show variables like "%char%"' from your MySQL server.

Hi Tonci 

I've attached a configured web site, which is configured to use a db, specially created to reproduce this bug. Please try to open it in the visual studio and try to create an asp.net membership user, usting the asp.net configuration tool (Main menu: Web Site/Asp.Net configuration)

PS: If you need to run some scripts against the DB, you can get db credentials from the "ApplicationServices" connection string in the web config.

Thanks Kostya. In the meantime I reviewed schemax scripts and found that schema4.sql and schema6.sql have these lines:
ALTER TABLE my_aspnet_Membership CONVERT TO CHARACTER SET DEFAULT;
ALTER TABLE my_aspnet_Roles CONVERT TO CHARACTER SET DEFAULT;
ALTER TABLE my_aspnet_UsersInRoles CONVERT TO CHARACTER SET DEFAULT;
and
ALTER TABLE my_aspnet_Sessions CONVERT TO CHARACTER SET DEFAULT;

Is it possible that you did not run those scripts (or one of them)? Does your my_aspnet_SchemaVersion.version equal 6?

Hi again

After i failed to create a user, i tried to execute the scripts one by one on a clean db.
schema1.sql -> schema2.sql ->schema3.sql -> schema4.sql -> schema5.sql -> schema6.sql

and found that schema3.sql could not be executed due to the collation conflict (it ocurs in the update script).

Yes, my_aspnet_SchemaVersion.version is really equal 6, but because of the third script could not be executed, lot of tables weren't altered correctly and the schema differs from the one expected by the connector.

to be exact, the following scripts from the schema3 wasn't executed

/* make final changes to our tables */        
alter table my_aspnet_Membership
          drop column Username,
          drop column ApplicationName,
          drop column applicationId,
          add primary key (userId);
          
/* next we set our role id values appropriately */
update my_aspnet_UsersInRoles u, my_aspnet_Roles r set u.roleId = r.id where u.Rolename = r.Rolename and r.applicationId=u.applicationId;

/* now we make the final changes to our roles tables */                    
alter table my_aspnet_Roles
          drop column ApplicationName,
          change column Rolename name VARCHAR(255) NOT NULL;
          
alter table my_aspnet_UsersInRoles
          drop column ApplicationName,
          drop column applicationId,
          drop column Username,
          drop column Rolename,
          add primary key (userId, roleId);

Kostya, glad we found the reason. I think I should close the bug report now and let you find out how to run the failing commands on your host.

I now a workaround for this on my environment, but i suppose this is still a bug and it would be much better if connector could work on such environments without any workarounds

Kostya, sorry if I'm missing something but I do not see how the script caused your environment to fail... If you can prove to me that script itself is to blame, please do so, otherwise I'll close the report.

Hi Tonci.

Please try to run schema creation scripts one by one against the database
(connectionString="server=mysql301.1gb.ua;user id=gbua_bugtest;password=fddce09f;persist security info=True;database=gbua_bugtest" providerName="MySql.Data.MySqlClient")
and you will see that it fails.

Indeed, update statements in schema3.sql fail in such case, and server complains about different collations for "where a.name=m.ApplicationName" clause.
Verified by concatenating all schemaN.sql files and tryng to execute statements one-by-one.

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/113900

825 Vladislav Vaintroub	2010-07-19
      Bug #53174 Membership schema creation fails if default schema collation differs from Latin1 .
      
      UPDATE statements in schema3.sql cannot be executed, because collations are different in 
      "where a.name=m.ApplicationName" clause. The reason is that my_aspnet_Membership, 
      my_aspnet_Roles and my_aspnet_UsersInRoles have LATIN1 charset while  my_aspnet_Applications has the default charset.
      
      The fix is to convert membership table my_aspnet_Membership, my_aspnet_Roles and my_aspnet_UsersInRole to default charset prior to UPDATE.
      
      Note, that shema4.sql executed directly after schema3.sql does the same charset conversion, and
      it is fine to execute  conversion twice (in this case second conversion will be a logical no-op)

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/113903

825 Vladislav Vaintroub	2010-07-19
      Bug #53174 Membership schema creation fails if default schema collation differs from Latin1 .
      
      UPDATE statements in schema3.sql cannot be executed, because collations are different in 
      "where a.name=m.ApplicationName" clause. The reason is that my_aspnet_Membership, 
      my_aspnet_Roles and my_aspnet_UsersInRoles have LATIN1 charset while  my_aspnet_Applications has the default charset.
      
      The fix is to convert membership table my_aspnet_Membership, my_aspnet_Roles and my_aspnet_UsersInRole to default charset prior to UPDATE.
      
      Note, that shema4.sql executed directly after schema3.sql does the same charset conversion, and
      it is fine to execute  conversion twice (in this case second conversion will be a logical no-op)

pushed to 6.0, 6.1, 6.2, 6.3

An entry has been added to the 6.0.7, 6.1.5, 6.2.4, 6.3.3 changelogs:

Membership schema creation failed if the default schema collation was not Latin1.