MySql "SET @variable" throwing Fatal Error in C# code

Question

I am getting a Fatal Error on using a ExecuteNonQuery(). I'm new to working with sql and as such im not sure why this SET command is causing a problem.

I'm using MySql 5.5.17, and I'm using C# .Net Framework 3.5 in the VS2010 IDE.

Exception Message = Fatal error encountered during command execution. The Error Line = SET @oldguid = 250006;

the content from the sql file im using is as follows minus the "comments" that i removed:

DELETE FROM `disables` WHERE `sourceType`=0 AND `entry`=61904;
INSERT INTO `disables` (`sourceType`, `entry`, `flags`, `comment`) VALUES(0, 61904, 8, 'Magma Totem TEST - can crash client by spawning too many totems');
SET @oldguid = 250006;
SET @newguid = 202602;
UPDATE `creature` SET `guid`=@newguid WHERE `guid`=@oldguid;
UPDATE `creature_addon` SET `guid`=@newguid, `path_id`=@newguid*100 WHERE `guid`=@oldguid;
UPDATE `waypoint_data` SET `id`=@newguid*100 WHERE `id`=@oldguid*100;
UPDATE `areatrigger_teleport` SET `target_orientation`=2.255664 WHERE `id`=4386;
UPDATE `gameobject` SET `spawnMask`=3 WHERE `guid`=22674;

the guid column is a unsigned int(10)

the C# code I am using to process this .sql file is as follows:

filename = lstBox_SqlFiles.SelectedItem.ToString();
mysql = new MySqlConnection(connection + Database);
string line;

OpenDatabase();
using (StreamReader reader = new StreamReader(filename))
{
  StringBuilder parsedLine = new StringBuilder();
  int count = 0;
  while ((line = reader.ReadLine()) != null)
  {
    if (line.Length > 0 && !line.StartsWith("--"))
    {
      if (line.EndsWith(";"))
      {
        if (parsedLine.Length > 0)
          line = parsedLine.ToString() + line;
        try
        {
          count++;
          lbl_LineQuery.Text = line;
          lbl_QueryCount.Text = String.Format("Count: {0}", count);

          MySqlCommand cmd = new MySqlCommand(line, mysql);
          cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
          string msg = String.Format("Source FileName: SqlUpdater.cs\nSql FileName: {0}\nError Line: {1}\nException Message: {2}\n", filename, line, ex.Message);
          MessageBox.Show("cmd.ExecuteNonQuery() Error!\n" + msg, "MySql Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
          return;
        }
        sbClear(parsedLine);
      }
      else
      {
        parsedLine.Append(line);
        continue;
      }
    }
    else
    continue;
  }
}

Can anyone see a problem with my code? Is there some special manipulation of the "SET @var" string that I need to do?

Any Help is appreciated Thanks in advance

roadmaster

Edit * as a side note i should point out that if i use a sql management program like SQLYog it processes the same sql file with no problem so im assuming that the problem is somewhere in the manipulation of the string in my C# code.

odd. the set lines work perfectly when run manually in the mysql monitor. maybe the C# mysql library has an issue. maybe an alternate `select @oldguid := 250006;` would get around it, if that's case. — Marc B, Oct 29 '11 at 18:53
Wait a second, is that C# code running each line individually? — Rangoric, Oct 29 '11 at 19:00
You should try to run yor code step by step and check the values of each variable to detect an eventual error. Also, what does the sbClear function do?? — GianT971, Oct 29 '11 at 19:01
I posted a solution, it had a mistake and I edited the answer, it will be ok now — GianT971, Oct 29 '11 at 20:47
GianT971 the sbClear function is just a custom function to clear the StringBuilder for re-use. thanks for your help... — roadmaster, Oct 30 '11 at 00:22

score 8 · Answer 1 · answered May 02 '18 at 00:02

8

add ;Allow User Variables=True to the connection string.

answered May 02 '18 at 00:02

Paul

397
5
9

score 5 · Answer 2 · answered Jul 28 '16 at 10:24

This is a problem I discovered when upgrading MySql.Data.dll from 1.0 to 6.9. For example:

MYSQL:

SET @foo = 'foo'

Message: Fatal error encountered during command execution.

I suspect the MySql.Data is trying to interperate the @foo as a prepared statement variable, and wants me to populate it. On pain of death. The solution therefore seem to be to tell MySql.Data that this is a literal '@' and not a token.

There may be other ways, but the pragmatic solution I have found is to quote the variable name in back-quotes:

SET @`foo` = 'foo'
SELECT @`foo`

I hope this helps.

Gjermund Dahl · Answer 3 · 2014-11-20T21:54:52.557

Андрей's suggestion worked for me.

I have a rather complex procedure in my MySQL database which I dont want to include in my C# code. Here is how I do it:

//Open connection
string conString = "server=myserver; Uid=myusername; database=myscheme; Pwd=mypwd";
MySqlConnection con = new MySqlConnection(conString);

try
{
    //Set first variable, @START
    string sql = "SET @START = '" + date1 + "'";
    MySqlScript cmd = new MySqlScript(con, sql);
    cmd.Connection.Open();
    cmd.Execute();

    //Set second variable, @END
    sql = "SET @END = '" + date2 + "'";
    cmd = new MySqlScript(con, sql);
    cmd.Execute();

    //Send procedure call
    MySqlDataAdapter MyDA = new MySqlDataAdapter();
    sql = "call MY_DB_PROCEDURE";
    MyDA.SelectCommand = new MySqlCommand(sql, con);

    //From here you can process the DB response as you want
    //For instance, display the results in a datagridview
    DataTable table = new DataTable();
    MyDA.Fill(table);

    BindingSource bSource = new BindingSource();
    bSource.DataSource = table;

    //"dataGridView1" is already placed in the active form
    dataGridView1.DataSource = bSource;
    dataGridView1.Visible = true;

}
catch (Exception e)
{
    //Error handling procedure
}
finally
{
    if (con.State == ConnectionState.Open)
    {
        con.Dispose(); // return connection to the pool
    }
}

EDIT

A more elegant solution is to add "Allow User Variables=True" to the connection string. Example as follows:

//Open connection
string conString = "server=myserver; Uid=myusername; database=myscheme; Pwd=mypwd; Allow User Variables=True";
MySqlConnection con = new MySqlConnection(conString);

try
{
    //Open connection
    con.Open();

    //Set first variable, @START
    //Set second variable, @END
    //Send procedure call
    string sql = "SET @START = '" + date1 + "';" +
                 "SET @END   = '" + date2 + "';" +
                 "CALL MY_DB_PROCEDURE";

    MySqlDataAdapter MyDA = new MySqlDataAdapter();
    MyDA.SelectCommand = new MySqlCommand(sql, con);        

    //From here you can process the DB response as you like
    //...


}
catch (Exception e)
{
    //Error handling procedure
}
finally
{
    if (con.State == ConnectionState.Open)
    {
        con.Dispose(); // return connection to the pool
    }
}

GianT971 · Accepted Answer · 2011-10-29T20:45:58.977

Actually, I don't think that you can pass this line SET @oldguid = 250006; into a mysqlcommand object (actually I don't know). What you should do is have your program put these values in a local variable, and then replace the parameter in your update queries.

Find a way to mix your code and this one:

        // This line should be outside the While loop
        Dictionary<string, string> variables = new Dictionary<string, string>();


        if (line.Trim().ToUpper().StartsWith("SET"))
        {
            List<string> commandLine;
            commandLine = line.Trim(' ',';').Split().Distinct().ToList();
            commandLine.Remove(string.Empty);

            // After next line, the 'variables' dictionary contains the name 
            // of the variable you want to set, and its value
            variables[commandLine[1]] = commandLine[3];

            // ...
            // Do something here (try catch, chatever, but NO MySqlCommand)
            // ...
        }
        else
        {
            // If the line contains any of the variables you previously set,
            // i will be the index of this variable, -1 otherwise
            int i = line.ContainsAnyOfAt(variables.Keys);
            while(i>=0)
            {
                // Here we replace the parameter by its value, for example:
                // UPDATE `creature` SET `guid`=@newguid WHERE `guid`=@oldguid;
                // becomes (after all loops):
                // UPDATE `creature` SET `guid`=202602 WHERE `guid`=250006;
                line = line.Replace(variables.Keys.ElementAt(i), variables.Values.ElementAt(i));
                i = line.ContainsAnyOfAt(variables.Keys,i+1);
            }

            // ...
            // This is where you should put the code of MySqlCommand
            // ...
        }

And here is the extension method ContainsAnyOfAt :

        public static int ContainsAnyOfAt<T>(this global::System.String source, IEnumerable<T> values, int startIndex = 0)
        {
            if (source == null) throw new ArgumentNullException("source");
            for (int i = startIndex ; i < values.Count(); i++)
            {
                if (source.Contains(values.ElementAt(i).ToString()))
                {
                    return i;
                }
            }
            return -1;
        }

Please give it a try and give feedback. Greetings

This is a scoping problem. This answer won't solve that problem. — p.campbell, Oct 29 '11 at 20:52
Thanks GianT971, your example code was just what i needed, it took me a while and i had to make some modifications to get it to work, but it does work now, thanks alot. I basicly did away with ContainsAnyOfAt (just moved some of the example code around) and changed the while loop into a foreach(KeyValuePair kvpPair in variables). Thanks again for your example code. Id give your reply a vote but aparently you have to have 15 rep yourself before you can vote for someone else. — roadmaster, Oct 30 '11 at 00:01
Solved in this thread: http://stackoverflow.com/questions/5524632/how-can-i-use-a-mysql-user-defined-variable-in-a-net-mysqlcommand — Gjermund Dahl, Nov 20 '14 at 21:50

score 2 · Answer 5 · answered Oct 29 '11 at 20:46

The code basically does this:

load all SQL commands from a file into a collection of strings.
iterate through that collection from the top:
- make a new MySqlCommand (thereby creating its own scope!)
MySQL throws an error on the 3rd line of code, as the variable @oldguid hasn't been declared.

If you want to be able to use variables, use one MySqlCommand object, and call ExecNonQuery() once.

Your best bet is to create a stored procedure. Perhaps it'll need 2 parameters for your "guid"/integer values.

The reason it works in SQLYog is that all those statements are running in the same context, rather than in different sessions.

p.campbell thanks for your explanation, I never even thought about a MySqlCommand using scope, like i said im new to working with MySql, thanks again for your reply. — roadmaster, Oct 30 '11 at 00:03

score 1 · Answer 6 · answered Jan 31 '12 at 18:26

1

Wrote procedures containing variables (@ name_var). An exception was thrown. Helped change the class MySqlCommand class MySqlScript

answered Jan 31 '12 at 18:26

Андрей

21
1

I gave you a +1 to remove the minus, and added an example code. – Gjermund Dahl Nov 20 '14 at 21:37

score -1 · Answer 7 · answered Mar 07 '18 at 08:51

-1

add connection string ; Allow User Variables=True

answered Mar 07 '18 at 08:51

김민석

1

MySql "SET @variable" throwing Fatal Error in C# code

7 Answers7

EDIT