MySQL and Visual Studio 2010 Membership, Roles and User Profile Provider

9 Comments

In Visual Studio 2010 (VS2010) when you create a new Web Application Project using the default Web Template “ASP.NET Web Application” it creates an Account folder with the files Login.aspx, Register.aspx and ChangePassword.aspx.
These are files/pages for ASP.NET Membership which is ASP.NET’s built-in user handling. I have never used Membership before but have been curious to, so I saw this as my cue to do so. However since I’m running MySQL as my database server I knew I had some extra installing and configuring to do.

Now several hours later I have finally figured out what I needed to do…

1) Install the latest version of MySQL Connector/NET. Visual Studio support is from version 6.3 which is currently only available in beta, current version when writing this is 6.3.2 beta.

2) Use the MySQL Website Configuration Tool, follow instructions found by clicking this link. This tool will modify necessary configuration files (have not checked which, maybe only web.config) to use MySQL Membership, Roles, User Profile and/or Session State.

Important: (this took me quite some time to figure out) the MySQL Website Configuration Tool icon only shows up in the Solution Explorer if you create a Web Site Project in VS 2010. It does not show if you create a Web Application Project. I have reported this as a bug to MySQL.

The only workaround I found to use a Web Application instead of using a Web Site was to create a temp Web Site project, run the MySQL Web Configuration Tool and then use copy and paste to move changes from web.confg to my Web Application Project. I have provided these changes at the end of this post.

3) Done!

I am running Windows 7 64-bit, Visual Studio 2010 and MySQL Connector/NET 6.3.2 beta, but according to this thread other people with other versions of VS seems to be having the same problem.

These are the changes that are made to web.config:

  <connectionStrings>
    <remove name="LocalMySqlServer" />
    <add name="LocalMySqlServer" connectionString="server=localhost;User Id=root;database=users;password=xxx" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>
<system.web>
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login.aspx" timeout="2880" />
    </authentication>
    <membership defaultProvider="MySQLMembershipProvider">
      <providers>
      <clear />  
      <remove name="MySQLMembershipProvider" />
        <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.3.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="/" description="MySQL default application" connectionStringName="LocalMySqlServer" writeExceptionsToEventLog="True" autogenerateschema="True" enablePasswordRetrieval="True" enablePasswordReset="True" requiresQuestionAndAnswer="False" requiresUniqueEmail="False" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
      </providers>
    </membership>
    <profile defaultProvider="MySQLProfileProvider">
      <providers>
        <clear />
        <remove name="MySQLProfileProvider" />
        <add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.3.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="/" description="" connectionStringName="LocalMySqlServer" writeExceptionsToEventLog="False" autogenerateschema="True" />
      </providers>
    </profile>
    <roleManager enabled="false" defaultProvider="MySQLRoleProvider">
      <providers>
        <clear />
        <remove name="MySQLRoleProvider" />
        <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.3.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="/" description="" connectionStringName="LocalMySqlServer" writeExceptionsToEventLog="True" autogenerateschema="True" />
      </providers>
    </roleManager>
  </system.web>

  

MySQL Workbench Query Browser is super slow

10 Comments

MySQL GUI Tools which included the MySQL Query Browser and MySQL Administrator has now been replaced by the MySQL Workbench tool.

The Workbench edition which includes Query Browser (now called SQL Development) and Administrator (now called Server Administration) is only available in beta but it the old MySQL GUI Tools have already been pulled from Downloads. So I downloaded the latest beta version of MySQL Workbench 5.2.20 and gave it a try.

I’m sorry to say I was very disappointed.

This is what I have found using the SQL Development part (former Query Browser)

  1. It is super slow. Doing a query that usually takes a second in the old Query Browser can take minutes! These are just simple queries and it seems the time is not the query itself or time to fetch from server that makes it slow. This alone makes it impossible to use this tool.
  2. It is not intuitive. I find it hard to work with, maybe it just takes getting used to
  3. Features and functions from the Query Browser are gone, for example in Query Browser it was possible to open up a new tab to run a new query, you could then switch between tabs to see the different results. In the new SQL Development you can open up new query tabs but there is only one results tab. Another thing is that it does not show how many rows were affected by your query. Yet another missing feature is the ease to edit a row, in the old Query Browser all you did was click the Edit button. In the new SQL Development you have to do a specific “EDIT” query (which is even slower than the super slow SELECT) and then go thru several “ok” screens before your edit is finally written to database. I’m also missing is the system Tray Monitor and the Migration Toolkit.

I know it’s only a beta so hopefully they will get these things working in the end but for now I’m going to stick to the old Query Browser.

After some searching I found the old GUI Tools in the MySQL Download Archives.
It seems the latest/last version of GUI Tools was 5.1.12 and can be downloaded here.

PHP 5.3.1 upgrade on IIS7 and MySQL gives Internal Server Error

3 Comments

Solution

After a lot of googling and testing I finally found Bruce Kirkpatrick’s comment at the end of the PHP install manual

Seems it is no longer enough to have localhost in the servers local hosts file (C:\Windows\System32\drivers\etc\hosts) you also have to comment out or remove the IPv6 equivalent.

127.0.0.1 localhost
#::1 localhost

After I did this everything worked ok again. Thanks Bruce!

Problem

I just upgraded PHP on my Windows Server 2008 running IIS7 from PHP 5.2.10 to 5.3.1. Used the non-threaded install package and installed for FastCGI.

After the upgrade installation worked fine for some non-database driven pages and phpinfo worked fine but whenever a page tries to accessed the MySQL database the browser only gave me a 500 Internal Server Error.

Checking the php error log (C:\Windows\Temp\php-errors.log) I get messages like this:

[24-Feb-2010 13:44:36] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://localhost:3306) in C:\inetpub\wwwroot\XXX\init_page.inc on line 32
[24-Feb-2010 13:44:36] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
in C:\inetpub\wwwroot\XXX\init_page.inc on line 32
[24-Feb-2010 13:44:36] PHP Fatal error: Maximum execution time of 60 seconds exceeded in C:\inetpub\wwwroot\XXX\init_page.inc on line 32