MySql.Data.MySqlClient.MySqlException: Parameter ‘@id’ must be defined

I found out a funny thing today. Or maybe not so funny when I think about it…

The order page on one of my sites have not been working since early November, almost 6 months!! As I’m sure you understand it’s not a very busy site, but usually I get a few orders per month so I should have noticed this a long time ago…

The site is running on IIS/ASP.NET and MySQL server 5.0.45 using the MySQL .NET Connector and I’m using a SQL statement like this on the page:

START TRANSACTION;
INSERT INTO table1 (companyname,street,city,zip) VALUES (‘XS Tech’,’test’,’test’,’12345′);
SELECT @id:=id FROM table1 where id = last_insert_id();
INSERT INTO table2 (id,contactname) VALUES (@id,’Max”);
COMMIT;

This statement was throwing an exception error: “MySql.Data.MySqlClient.MySqlException: Parameter ‘@id’ must be defined”

Since I could not really remember – ie had not properly documented – what changes I may have done to that page six months ago, it made trouble shooting a bit harder. But to make a long story short(er) – It turns out I had upgraded the MySQL .NET Connector and this was causing the error. The site was using MySql.Data.dll version 5.2.1.0. I tried to upgrade to 6.0.3.0 to no avail. But when downgrading to 5.1.4.0 it started to work just fine again.

Finally, I  found that this was indeed a change in the .Net Connector. (I’m not sure what version number but starting from 5.2.0 would be my guess)
Starting from version 5.2.2 of the Connector you should add the Allow User Variables=True Connection String Setting in order to use User Defined Variables in your SQL statements.

Example of Connection String:

Database=testdb;Data Source=localhost;User Id=root;Password=hello;Allow User Variables=True

66 comments

  1. Thanks – just found this through Google and it saved me a hell of a lot of time (as I just upgraded .NET connector versions and my stuff broke for a similar reason).

    Reply

  4. Many thanks, i was getting this error after upgrading the Mysql Connector… still i think this is foolish, Mysql user variable have always been defined by using an ‘@’, they should have used some other method to identify user parameters…

    Reply

  10. AHAHAHAHHA Thank you
    I was using a local var to store last_insert_id within a transaction and was battling thinking the last_insert_id / my sql syntax must be the problem. Low and behold, upgrading is a bitch

    Reply

  11. This article saved my bacon, I’ve got the driver running database create scripts and this kept happening – got to love how to ‘do’ anything in mysql you have to stuff something in the connection string.

    Reply

  13. Thanks a lot, we got into the same issues and you were spot.

    Just a suggestion if you not mind, you need to have declared variables in your queries and could have done this way.

    START TRANSACTION;
    INSERT INTO table1 (companyname,street,city,zip) VALUES (‘XS Tech’,’test’,’test’,’12345′);
    INSERT INTO table2 (id,contactname) VALUES (last_insert_id(),’Max”);
    COMMIT;

    Reply

  23. Saved me a ton of time! Thanks for posting this. Glad you came up near the list top with my Google Search phrase, which was very close to what you posted. -mj

    Reply

  27. Great solution, i was browsed by hours and, thanks a lot man, when I added that line of code I was all great.

    Reply
  36. Pingback: Parameter '@s' must be defined | BlogoSfera

  40. I’m using ASP.NET4 with MySQL via Visual Studio 2010 and everything worked fine locally. As soon as I uploaded to my host, I had the same parameter errors.

    Adding “Allow User Variables=True” to my connection string fixed everything.

    THANK YOU!!!

    Reply

  41. I’m still getting getting below error message, even with this code added. I’m using ASP.NET C# Express version.

    MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.

    Reply

  53. This bug has bothered me for the last 5 hours. Thank you so much.

    For info:
    My seeding of an automatic initialized MySQL database, using EF, was failing on my AppVeyor build server but not in local development. Simply caused by the missing tag in the connection string in one of the configurations.

    Reply

  62. Thanks, I spent 8 hours trying to understand that the problem was not in the stored procedure parameters, but the connector manager!

    Reply

  64. Amazing that it was something so simple! I was trying to call a Stored Proc and adding Allow User Variables=True to the connection string stopped the error I kept getting. Thank you!! I can’t believe that over 10 years later that this is STILL an issue?!

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *