Getting started with Membership etc

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Getting started with Membership etc

Post by mobyscore » Mon 28 May 2007 11:31

I've been using MySQLDirect for a year now with no problems for general database access. But now I want to make use of the Membership, Roles and Profile providers. I am struggling to get this work even at the simplest level. What I have is:


MySQLDirect version 3.50.10
Visual Studio 2005
MySQL version 5.0
.NET V2


In my application web.config I have:







and









on the very simple login page I have the standard .NET "CreateUserWizard" control where you supply user name, password etc. The "MembershipProvider" property on this control is set to match the web.config entry - i.e. it is "AspNetMySqlMembershipProvider".

After entering user name, password etc in this form and submitting I get:


Keyword not supported: 'host'

as the login control appears to try and use SQL server to connect:

System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +1406482
System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +102
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52
System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +24
System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +125
System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +56
System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4
System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21
System.Web.DataAccess.SqlConnectionHolder..ctor(String connectionString) +40


I've looked at the single page of help on these providers you have in version 3.50, these forums, your FAQ and found no sample code in the sample projects.

Please can you help?

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 29 May 2007 07:13

Please use the latest version of MySQLDirect .NET data provider (3.55.23).

mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Post by mobyscore » Tue 29 May 2007 13:10

Thanks - I'm now on v3.55.23 and I've corrected an error of mine in the Provider section of web.config, so I've been able to add a user via the "CreateUserWizard".

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 29 May 2007 14:06

So there is no problem any more - right?

mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Post by mobyscore » Tue 29 May 2007 14:18

It's all fine now so many thanks for your prompt help.

Upgrading to the latest MySQLDirect version and then correcting my error in the provider defintion now means the VS "CreateUserWizard" control now actually creates a user in the MySQL database (rather than crashing). using your Membership provider.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 29 May 2007 15:05

Very well.

mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Post by mobyscore » Tue 29 May 2007 15:12

Too good to be true!

I've hit another stumbling block. The following attributes don't seem to work in the Membership provider configuration in web.config:

applicationName="MobyScore"
minRequiredNonAlphanumericCharacters="0"
passwordFormat="Encrypted"

of these the big problem one for me is "minRequiredNonAlphanumericCharacters" as if I can't set this to zero, it's going to make the passwords far too complicated.

Is it possible to set these provider attributes?

Thanks for your help.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 29 May 2007 15:44

Please check the spelling: should be "ApplicationName" and "minRequiredNonalphanumericCharacters". As for passwordFormat, I suppose the problem is different. You must specify a non-autogenerated machine key to store passwords in the encrypted format. Either specify a different passwordFormat, or change the machineKey configuration to use a non-autogenerated decryption key.

mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Post by mobyscore » Tue 29 May 2007 16:26

The case of your Membership provider attributes is different to both those used by the SQLServer provider and those listed in .NET documentation I have. These attributes do not appear in my MySQLDirect .NET documenation:

minRequiredNonAlphanumericCharacters -> minRequiredNonalphanumericCharacters

and

applicationName -> ApplicationName

Maybe something to add to your documentation to help others in the future?

Anyway it's all working fine now and I can even store the passwords in encypted form.

So thanks again.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 30 May 2007 07:28

You are right: we will fix 'applicationName' attribute. As for minRequiredNonalphanumericCharacters, it has correct spelling in correspondence with SQL Server membership provider.

mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Post by mobyscore » Mon 04 Jun 2007 09:14

Membership using your .NET provider now seems to be working fine - if used alone. Rather than use Profiles to store user data I need to have a table of my own. I have named this table of mine "Users". Once a new user is added via “Membership.CreateUser”, I then use “DotNETUser.ProviderUserKey.ToString()” to create an entry in my “Users” table with all the other information I need to store for that user.

My “Users” table has a column “`DotNetUserID` varchar(40)” so that I can link my users stuff to the Provider’s user. I also have a constraint in my “Users” table:

CONSTRAINT `users_DotNetUserID_ref` FOREIGN KEY (`DotNetUserID`) REFERENCES `aspnet_users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE

There is no problem in creating a new user.

I have a simple form that has a list box of all users. Selecting a user brings up the user information from both the Membership object (e.g. the user’s email address) and also from SQL Selects on my “Users” table.

Approximately 4 times out of 5 changing the user in the list box brings up details of the new user in the table correctly. But on the other occasions I get “Lost connection” errors either instantaneously or after the default 30 second command timeout. The errors are not always in the same place. Examples are:

(1) Error MySQL, 2013, Lost connection to MySQL server during query, SELECT COUNT(*) FROM Users WHERE DotNetUserID = '62401c6d-1b84-44c6-803c-b4c21dd0495b'

(2) Error MySQL, 2013, Lost connection to MySQL server during query, SELECT username FROM aspnet_users ORDER BY username LIMIT 4, 1

(3) “Packet order” and “incorrect packet count” errors.

I have tried the following with no effect:

(a) Removing the constraint above
(b) Increasing the “CommandTimeout” to 120 seconds
(c) Searching these forums for someone with a similar problem

All this seems to be happening at a lower level than I am working – either in the “Provider” or the standard “MySqlConnection”/”MySqlCommand” calls, both of which seems to work fine if used alone.

Any light you can shed on this would be appreciated.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 04 Jun 2007 10:34

Is it possible for you to send me a test project to reproduce the problem? Include the definition of your own database object.
Use e-mail address provided in the Readme file.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 22 Jun 2007 15:52

We have investigated your project. It seems that problems are caused by the fact that you do not close reader in the end of ExecuteSQLNonScalar function.
Last edited by Alexey on Mon 30 Jul 2007 09:08, edited 1 time in total.

mobyscore
Posts: 17
Joined: Mon 28 May 2007 11:05

Bizarre thing

Post by mobyscore » Mon 30 Jul 2007 08:32

I've just done a some code tidying by replacing this:

' Set up DB connection parameters
PropertyConnection = New MySqlConnection()
With PropertyConnection
.UserId = "uuuuuu"
.Password = "pppppp"
.Host = "localhost"
.Port = 3306
.Database = "mobyscore"
End With

with this:

' Set up DB connection parameters
PropertyConnection = New MySqlConnection()
PropertyConnection.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("mobyscoreConnectionString").ConnectionString

The "ConnectionString" is in the application level Web.Config and is also used by "AspNetMySqlMembershipProvider" and "AspNetMySqlRoleProvider"

The parameters on both connection methods are identical:






It works fine all the time on our live server. However on the delevopment server it fails on the first call to the database made the next day due to "lost connection". Some other facts:

(1) The "lost connection" is an immediate response

(2) Same versions of MySQL, MySQLDirect, .NET on both servers

(3) Differences: development server uses IIS5 not IIS6; top level applicaton on live server, in sub-folder on development system

(4) Alway the NEXT DAY and only once. Try again hours later on the same day and it's fine

Any ideas?

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 30 Jul 2007 11:47

The most common reason for the lost connection error is that the server timed out and closed the connection. By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.
The strange thing is that you say all subsequent server polls go fine.

Post Reply