Tuesday, February 19, 2013

MySql error message: Parameter '@A' must be defined using .NET connector

I upgraded my MySql .NET connector (MySql.Data.dll) from 1.0.7.30072 to a much newer 6.5.4.0 version. I was expecting complete backward compatibility, but I was completely wrong in my assumption. Instead I started getting the following error.

Fatal error encountered during command execution.  

  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

The problem is that “Fatal error encountered during command execution” was not particularly useful. As it turned out we had accidentally deployed the new MySql.Data.dll to QA and production without noticing that some queries failed with the above message. Then I tried to reproduce the problem on my laptop and could not. My project in Visual Studio 2010 had a reference to v1 of the MySql.Data.dll. Once I figured out there was a different version that snuck into our development environment and made it to production I figured that was the issue. So, I changed the reference in VS2010, but it seemed to be using the older version still. I’m not sure exactly how and I didn’t take the time to figure out why, and only made everything more confusing. In the end, I created a command line app, and added the new version of the MySql.Data.dll to the project and added just the snippet of code that was breaking. Thank goodness I could now reproduce the error on my laptop. Now I looked at the inner exception, and I see what the real error message is:

Parameter '@A' must be defined.

Okay, now we are getting somewhere. After a bit of searching I figured out that I had to add “Allow User Variables=True” to my connection string (not the SQL, but the connection string in the config file).

The solution: Just add

Allow User Variables=True

to your config file and you can now use user defined variables in your sql statements that you pass to the MySql .NET connector.

2 comments:

Osama Mursleen said...
This comment has been removed by the author.
Osama Mursleen said...
This comment has been removed by the author.