MySQL Bugs: #61784: STORED PROCEDURES DONT WORK IN Visual studio 2010 ultimate

Bug #61784	STORED PROCEDURES DONT WORK IN Visual studio 2010 ultimate
Submitted:	7 Jul 2011 13:50	Modified:	14 Dec 2011 7:26
Reporter:	paul meadows	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	MYSQL 5.5	OS:	Windows (Vista 64 bits Home Premium)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	MySQL, stored procedures, VisualStudio 2010

Description:
when trying to rebuild the standard stored procedures which are available in SQLSERVEREXPRESS from the Membership/RoleModel security model, i encountered that even the simplest stored procedures are not working in the VisualStudio 2010 Ultimate environment...This was also the case in Navicate PREMIUM SQL manager and also in Ems Sql manager.

How to repeat:
Just open up Vs2010, make 2 connections: 1 to sqlserverExpress. 1 to MYSQL database. Open the standard sqlserver MDB file and access the stored procedures available for ASP 4.0 Configuration Manager within. Copy one and rebuild it conform MYSQL syntax. Try to save it, it wont.
Do this in Navicate or EMS sqlManager you will get the same result.

Build  new simple SP , it wont work.

 

Suggested fix:
Give decent working examples for the developper, or links to decent sites. I've been around the ICT for more than 25 years and its really sad to see how far we've come,lol

What exact version of MySQL Connector/Net, x.y.z, do you use? What kind of error do you get?

im using connector 6.4.0 the bugsequence began when investigating  this
sp, in the conversion proccess to MYSQL.

This sp is from standard sqlserverexpres from Microsoft.

The Mysqlserver database security tables are all available and accessible. The issue is creating a simple sp in Visual studio 2010 ultimate: it just cant be saved in Mysql database schema. There is still that weird sp-syntax issue and there are no simple examples available pointing out what to do.

as i said earlier . Building and saving a simple sp in NAVICATE SQL MANAGER and EMS SQL MANAGER won work either.

 ALTER PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @UpdateLastLoginActivityDate    bit,
    @CurrentTimeUtc                 datetime
AS
BEGIN
    DECLARE @IsLockedOut                        bit
    DECLARE @UserId                             uniqueidentifier
    DECLARE @Password                           nvarchar(128)
    DECLARE @PasswordSalt                       nvarchar(128)
    DECLARE @PasswordFormat                     int
    DECLARE @FailedPasswordAttemptCount         int
    DECLARE @FailedPasswordAnswerAttemptCount   int
    DECLARE @IsApproved                         bit
    DECLARE @LastActivityDate                   datetime
    DECLARE @LastLoginDate                      datetime

    SELECT  @UserId          = NULL

    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF (@UserId IS NULL)
        RETURN 1

    IF (@IsLockedOut = 1)
        RETURN 99

    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate

    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
    BEGIN
        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @CurrentTimeUtc
        WHERE   UserId = @UserId

        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @CurrentTimeUtc
        WHERE   @UserId = UserId
    END

    RETURN 0
END

Error when trying to save a sp

Attachment: Knipsel.JPG (image/jpeg, text), 35.85 KiB.

Its a good service to give users a tool for auto-create the basic security/membership tables when switching to MYSQL server and a connector . ....BUT THERE ARE OF COURSE stored procedures involved, because Microsoft setup this whole integrated security/membership/role concept in the ASP area.

The least MYSQL could have done is to emphasize this aspect and point out a direction how this solve this, when users/developpers want to use this model in a MYSQL database.

There are infact 56 sp involved in this whole transition , just open the standard sqlserverexpress database and loc at the proc table.
Any example in this conversion would be a win-/win for both sides.

THAT REALLY would be a SERVICE.

ok, here is an update from my investigations in 1:
Visual studio ultimate 2010 (asp4.0 and C#)
2. Navicat premium
3. EMS SQL manager

Hardware: hp hdx Premium series, Vista Home premium 64 bits, 4GB internal Mem

Lets start with Vs2010:

1. still not able to open the stored procedures node in the development environment when openening the server explorer tab; only the 9 my_aspnet_ tables are accessible.
So the problem must be within the connector 6.4.0, which has been intgrated in my VS2010 ultimate.
2. the storedprocedure is accessible from the c# code; it took me quite a while
to figure out the communication interface between the c# code and the MYSQL sp -communiction setup. Imao this aspect is NOT WELL documented in the MYSQL/SP area an SHOULD BE REVIEWED and have a quality update ASAP. The weird "syntaxis rules" used in MYSQL and Navicat, causes a lot of confusion and frustration for a developper. Especially when AND HOW to use the backtick (`), the SINGLE QUOTE (') and the DOUBLE QUOTE (") in the IN/OUT parameters.

Testing SP in Navicat: you MUST embed the IN-PARAMETER with SINGLE-QUOATE (')
Testing SP in EMS SQL MANAGER: no need to enter the IN-PARAMETER with any quotes.

I think i would be a good idea to look at the standard stored procedures which Microsoft already provided in the Security/MemberShip/Role area and do some sample conversion to the MYSQL area. You already did this in a limited way for the 9 my_aspnet table structures (although you left out for obvious reasons some vital columns and 2 tables!)

i'm looking forward to see that the connector issue will be solved quickly coz right now its a showstopper to me.

Just a small update.

After being able to convert 2 essential stored procedures with NAVICAT/EMS sql manager i was finaly able to see my 2 sp's in storedprocedures node in Visual studio 2010. After double clicking on the sp-node i was able to open 1 sp in VS2010.What a luck a thought!

So i openened one stored procedure (my_aspnet_Membership_SetPassWord) in Visual studio 2010, made a small cosmetic change in the sp and then tried to save it VS2010, and then it happened. AN ERROR OCCURRED AND THE WHOLE STORED PROCEDURE was gone!!! Only 1 sp was visible in the StoredProcedure node in the Server Explorer tab.

2 Question?

 1: why are you using 2 types of Engines in the my_aspnet....Create tables DDL.
- InnoDB and MyISAM, while as of MYSQL 5.5 InnoDB is the default according to your documentation?

eg.
CREATE TABLE `my_aspnet_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `applicationId` int(11) NOT NULL,
  `name` varchar(256) NOT NULL,
  `isAnonymous` tinyint(1) NOT NULL DEFAULT '1',
  `lastActivityDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

CREATE TABLE `my_aspnet_membership` (
  `userId` int(11) NOT NULL DEFAULT '0',
  `Email` varchar(128) DEFAULT NULL,
  `Comment` varchar(255) DEFAULT NULL,
  `Password` varchar(128) NOT NULL,
  `PasswordKey` char(32) DEFAULT NULL,
  `PasswordFormat` tinyint(4) DEFAULT NULL,
  `PasswordQuestion` varchar(255) DEFAULT NULL,
  `PasswordAnswer` varchar(255) DEFAULT NULL,
  `IsApproved` tinyint(1) DEFAULT NULL,
  `LastActivityDate` datetime DEFAULT NULL,
  `LastLoginDate` datetime DEFAULT NULL,
  `LastPasswordChangedDate` datetime DEFAULT NULL,
  `CreationDate` datetime DEFAULT NULL,
  `IsLockedOut` tinyint(1) DEFAULT NULL,
  `LastLockedOutDate` datetime DEFAULT NULL,
  `FailedPasswordAttemptCount` int(10) unsigned DEFAULT NULL,
  `FailedPasswordAttemptWindowStart` datetime DEFAULT NULL,
  `FailedPasswordAnswerAttemptCount` int(10) unsigned DEFAULT NULL,
  `FailedPasswordAnswerAttemptWindowStart` datetime DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='2';

Compare it with this MS-Table when after having MYSQL installed:

CREATE TABLE `aspnet_membership` (
  `ApplicationId` char(36) NOT NULL,
  `UserId` char(36) NOT NULL,
  `Password` varchar(128) NOT NULL,
  `PasswordFormat` int(11) NOT NULL,
  `PasswordSalt` varchar(128) NOT NULL,
  `MobilePIN` varchar(16) DEFAULT NULL,
  `Email` text,
  `LoweredEmail` text,
  `PasswordQuestion` text,
  `PasswordAnswer` varchar(128) DEFAULT NULL,
  `IsApproved` tinyint(4) NOT NULL,
  `IsLockedOut` tinyint(4) NOT NULL,
  `CreateDate` datetime NOT NULL,
  `LastLoginDate` datetime NOT NULL,
  `LastPasswordChangedDate` datetime NOT NULL,
  `LastLockoutDate` datetime NOT NULL,
  `FailedPasswordAttemptCount` int(11) NOT NULL,
  `FailedPasswordAttemptWindowStart` datetime NOT NULL,
  `FailedPasswordAnswerAttemptCount` int(11) NOT NULL,
  `FailedPasswordAnswerAttemptWindowStart` datetime NOT NULL,
  `Comment` longtext,
  PRIMARY KEY (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2: Why are essential columns in the my_aspnet area created with NULL value as default, while the Microsoft aspnet tables had them created with default NOT NULL? 

e.g. LastPasswordChangedDate` datetime DEFAULT NULL : - This will lead to severe errors when using the ASP.NET website TOOLKIT when  setting up the website security/membership/role model.

UPDATE stored procedure weird result in MYSQL 5.5 and connector 6.4.0 in EMS sql manager.

A serious question regarding MYSQL syntax and execution of below stored procedure. 

When executing below sp in EMS SQL manager , i noticed that it's not updating the right record in the table 'my_aspnet_membership'. The key column in the table is userId.
The result will be that all colums in the SET from different membership users are updated, while infact only memberid = 13 should be updated.

Pls can you shed some light on this?
I also LIMIT , but this doesn't help either.

BEGIN
 	IF (UserId = 13 ) THEN  /*this was an extra constraint which is not necessary, but i've put it here after having the wird update result...when removing the IF the weird update still exist! */
   
    UPDATE my_aspnet_membership
   
    SET Password = NewPassword, 
        PasswordFormat = PasswordFormat, 
        PasswordKey = PasswordSalt,
        LastPasswordChangedDate = CurrentTimeUtc
   
     WHERE userId = Userid; /* Userid is an IN parameter */

upgraded to Connector 6.4.3.... 
upgrade  to EMS sql manager 5 lite free version, but 
the same weird UPDATE behaviour exist... Worked with MSSQL, ORACLE SQL, DB2 SQL, and i have never met this strange UPDATE behaviour ever before,  when there is only 1 primairy key in an updating table.

Paul,

I have two questions about this bug report:

1. In the "how to repeat" section you noted to open the standard
   SQL Server MDB file. Apparently it should already have some stored procedures.
   Is it supposed to be just ANY stored procedure (say dummy)?

2. Both Navicat Premium and EMS SQL Manager are commercial products.
   Is it possible to reproduce the bug using the free version of Navicat Lite
   or EMS Lite (which is only free for MSSQL)?

Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

i sent you feedback of the 11th of october already with regards to both of your questions. Today i received an info from the bug database that there was no feedback give by me????

1. Answer one: just start with an empty database and do some basic stuff
2.  Both the two free version will procudue the same errors

Please read my history report(S) carefully, ok.

If you need more information concerning this annoying bugs with the stored procedures, just send me an email asap.

Its been reported already by me in July 2011: a follow up message was sent on the 11th october on your request and a month later nothing has obviously been done? This sucks!!

So what is wrong with your QA/BUG FIXING monitoring control system?

Regards,  

Paul Meadows CEO Hla Consultants BV

Paul,

Thanks for your reply.
If you check this bug thread, there is no feedback sent by you on 11 Oct.
So, the system and MySQL engineers were not aware of any new messages/updates.

It is always a good idea to check if the message was actually sent because network problems on your side or something else on our side could cause such frustrating report closures.

Anyway, your answers should help me to repeat the bug.
I will let you know about my progress soon.

Paul,

As you instructed, I created a new stored procedure in MSSQL Express.
Copied it and tried to paste into MySQL Connection/Database/Functions section.

Nothing happened. It hardly can be considered as a bug in Connector/NET.

In fact Visual Studio does not allow neither copy nor paste (from/to) MySQL database.

It is not quite clear how to do copy and rebuild:

> Open the standard sqlserver MDB file and access the stored 
> procedures available for ASP 4.0 Configuration Manager 
> within. Copy one and rebuild it conform MYSQL syntax. 
> Try to save it, it wont.

How am I supposed to rebuild the stored procedure to conform MySQL syntax? 
Should I do it manually?

The same is about NaviCat Lite: see the attached screenshot.

Regarding your question about two different storage engines for my_aspnet_ tables: this has been already fixed in Connector/NET 6.4.3/6.4.4.

Screenshot of Navicat Lite

Attachment: navicat.jpg (image/jpeg, text), 278.04 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".