Fatal error encountered during command execution while updating blob to mysql

Question

String query = "";
        string constr = ConfigurationSettings.AppSettings["MySQLConnectionStringForIMS"];
        using (MySqlConnection con = new MySqlConnection(constr))
        {
            //string query = "INSERT INTO user(name, files,contentType) VALUES (@name,@files,@contentType)";
            if (update == "mainSec")
            {
                query = "update main_section set contentType=@contentType,fileData=@fileData,fileNameAfterUploading=@fname,haveDir=@dir where id=@id";
            }
            else
            {
                query = "update sub_section set subContentType=@contentType,subFileData=@fileData,fileNameAfterUploading=@fname,haveDir=@dir where MainSecId=@id and id=@subId";
            }
            using (MySqlCommand cmd = new MySqlCommand(query))
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@contentType", contentType);
                cmd.Parameters.AddWithValue("@fileData", data);
                cmd.Parameters.AddWithValue("@fname", filename);
                cmd.Parameters.AddWithValue("@dir", 1);
                cmd.Parameters.AddWithValue("@id", mainId);
                if (update == "subSec")
                {
                    cmd.Parameters.AddWithValue("@subId", subId);
                }
                con.Open();
                int st = cmd.ExecuteNonQuery();
                if (st == 1)
                {
                    //Uri uri = new Uri(url, UriKind.Absolute);
                    //System.IO.File.Delete(uri.LocalPath);
                }
                con.Close();
            }
        }

We are using MySql.Data.dll version 6.9.5.0.

This fails with the error: mysql Fatal error encountered during command execution. Any ideas on why this would fail?

Could you give us the full error stacktrace please? Likely unrelated, but you should probably change the if (update == "subSec") to if (update != "mainSec") in order to ensure the param binding is in synch with the correct query defined above. — StuartLC
– StuartLC, Commented May 30, 2018 at 16:34
@StuartLC I could see that being the issue here. If it sees update != 'mainSec', it will use the subSec query. However, if update is also != to 'subSec', then it won't assign a '@subId' value and would cause a fatal error. — Ryan Gibbs
– Ryan Gibbs, Commented May 30, 2018 at 17:56
Please follow all the instructions at this page to capture the exception details, including all inner exceptions, then edit your question with that information: idownvotedbecau.se/noexceptiondetails — Bradley Grainger
– Bradley Grainger, Commented May 31, 2018 at 0:54
Thanks, Guys! I found the root cause of the issue. The value of "update" was coming as "SubSec". Due to which comparison is not done properly. Appreciate your inputs @StuartLC and Ryan Gibbs — Vishal S. Patel
– Vishal S. Patel, Commented Jun 1, 2018 at 4:02

StuartLC · Accepted Answer · 2018-06-04 05:26:10Z

Tl;DR

Because of mismatched branch comparisons, you are executing a query with 6 unbound variables, but you are only binding 5 parameters.

Detail

There wasn't really sufficient information provided in the stack trace / exception to answer definitively, but it seems the guess about the bad practice in the branching above was the root cause, i.e. in these two branches:

if (update == "mainSec")
{
    query = ... Query has 5 unbound variables
}
else
{
    query = ... Query has 6 unbound variables
}

and

if (update == "subSec")
{
     ... bind the 6th parameter here
}

.. because the update type / mode string wasn't constrained to a range of mainSec or subSec, there is a branch which used the sub_section query with 6 parameter tokens, but which didn't bind the 6th token, causing the error.

In situations like this, I would recommend that instead of using weakly constrained strings, that you rigidly constrain the range of inputs of your update, e.g. with an enum:

enum UpdateMode
{
    Invalid = 0, // This will be the default, and can be used to ensure assignment
    MainSection,
    SubSection
}

Since there's only two possible modes, you could avoid the first query assignment branch with a conditional assignment, i.e.

Contract.Assert(updateMode != UpdateMode.Invalid);
var query = updateMode == UpdateMode.MainSection
      ? "update main_section set contentType=@contentType ... "
      : "update sub_section set subContentType=@contentType ... ";

This has the benefits that the declaration and assignment of query can be tied together (and provides additional compiler guarantees that query must be assigned).

(And if there were more than two queries (and more than two enum states) then a static IReadOnlyDictionary<enum, string> would allow this pattern to be extended.)

The binding would also change to

if (updateMode == UpdateMode.SubSection)
{
    cmd.Parameters.AddWithValue("@subId", subId);
}

Some notes

con.Close(); isn't needed, since you already have a using around the new Connection - Dispose will call .Close if it's open
I know this is commented out, but I would strongly recommend against doing File IO at this point

 if (st == 1)
 {
     // File.IO
 }

Since

From a separation of concerns point of view, deleting files belongs elsewhere. If the deletion is dependent on exactly one row being updated, then this can be returned from this Blob update method.
The I/O would be in the scope of the using block, this will potentially hold up the release of a MySql Connection (to the connection pool)
The IO could fail, and depending on any transaction control, this could leave your system in a problematic state, where the record is deleted but the file is not.

Thanks, @Stuart for sharing such valuable programming methodology.

Collectives™ on Stack Overflow

Fatal error encountered during command execution while updating blob to mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related