"Fatal error encountered during command execution" I'm using SET and SELECT xxx FROM (SELECT xxx FROM xxx)

Question

I have this code:

Function Get_Control_Station_Address(counter As Integer)
    Check_DB_Con() 'Check if the connection is okay
    SQL_Query = "SET @row_number = 0; " _
                & "SELECT hardware_add " _
                & "FROM (" _
                    & "SELECT " _
                    & "@row_number:=@row_number + 1 AS num, " _
                    & "hardware_add AS hardware_add " _
                    & "FROM teller_info" _
                & ") AS sub_query " _
                & "WHERE num = " & counter & ";"

    Dim MySQL_CMD As New MySqlCommand(SQL_Query, MysqlConn)

    Try
        MySQL_CMD.Connection.Open()
        MySQL_Reader = MySQL_CMD.ExecuteReader()

        While MySQL_Reader.Read
            MySQL_Result = MySQL_Reader("hardware_add")
        End While

        Return MySQL_Result
        MySQL_Reader.Close()
    Catch myerror As MySqlException
        Console.WriteLine("Failed to run query: " & myerror.Message)
        Return Nothing
    Finally
        MysqlConn.Close()
        MysqlConn.Dispose()
    End Try
End Function

I am receiving this error:

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
Failed to run query: Fatal error encountered during command execution.

I am sure that there's no basic errors here like database connection error, as a proof I have this function that I am 100% sure working and it is almost the same to the previous function which is not working, the only difference is the query.

Function Get_Control_Station_Total()
    Check_DB_Con() 'Check if the connection is okay
    SQL_Query = "SELECT COUNT(*) AS total_count FROM teller_info"
    Dim MySQL_CMD As New MySqlCommand(SQL_Query, MysqlConn)

    Try
        MySQL_CMD.Connection.Open()
        MySQL_Reader = MySQL_CMD.ExecuteReader()

        While MySQL_Reader.Read
            MySQL_Result = MySQL_Reader("total_count")
        End While

        Return MySQL_Result
        MySQL_Reader.Close()
    Catch myerror As MySqlException

        Return ("Failed to run query: " & myerror.Message)
    Finally
        MysqlConn.Close()
        MysqlConn.Dispose()
    End Try
End Function

So looking close at the problem it seems that this code is the root of error.

SQL_Query = "SET @row_number = '0'; " _
                & "SELECT hardware_add " _
                & "FROM (" _
                    & "SELECT " _
                    & "@row_number:=@row_number + 1 AS num, " _
                    & "hardware_add AS hardware_add " _
                    & "FROM teller_info" _
                & ") AS sub_query " _
                & "WHERE num = '" & counter & "';"

Actually I re-create that query directly using heidiSQL and it working great, so I am a little bit stuck here, maybe I am using SET @row_number = '0'; wrong?

In Summary:

I am sure that it is not MySQL connection problem
I am sure that the MySQL query is working (by testing it directly on HeidiSQL)
The process of getting data seems to be working because I just copy the way it gets data from a working function.

Edit: By following @Ken_White's comment of commenting console.writeline I am able to see clearly the error

Here's the error

    MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@row_number' must be defined.
   at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
   at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
   at MySql.Data.MySqlClient.Statement.BindParameters()
   at MySql.Data.MySqlClient.PreparableStatement.Execute()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at TCP_Client.Module_Database.Get_Control_Station_Address(Int32 counter) in C:\Users\xxxxx\xxx.vb:line 198

Why are you quoting `num` in your `WHERE` clause? `num` is a number, so you shouldn't need to surround it with quotes. (You have the same problem in both SQL statements, so I doubt the second is working either.) Change the last part of the query to `& "WHERE num = " & counter & ";"`, and see if that fixes it. If it doesn't, try to turn on detailed exceptions in your config file and run to see what the actual exception is that is occurring. And then **immediately** start doing some research on using parameterized queries so you can stop concatenating SQL statements (search *SQL injection*). — Ken White, Aug 28 '15 at 02:15
@KenWhite, ah yes. I just tried if it will work but originally I didn't include those. I removed them now but still not working. Could you please help me on how to turn on the detailed exception? Thanks for suggesting parameterized queries. — Cary Bondoc, Aug 28 '15 at 02:17
Start by commenting out your `Console.WriteLine` in your `catch` clause, and let the exception be raised. You're basically throwing away all of the useful information by only writing out a small part of it. Then search this site for *[vb.net] detailed exceptions*. — Ken White, Aug 28 '15 at 02:44
@KenWhite thanks! Please see my edited question. It seems that `Parameter '@row_number' must be defined.` is the problem. — Cary Bondoc, Aug 28 '15 at 02:51
@KenWhite thanks! I figured it out. It seems that by adding `Allow User Variables=True` to my database connection solves my problem. — Cary Bondoc, Aug 28 '15 at 02:58

Cary Bondoc · Accepted Answer · 2016-04-06T23:41:47.613

It seems that by adding Allow User Variables = True to my database connection solves my problem.

So instead of

Public Sub Check_DB_Con()
    'Declare database credentials so we can refresh it in recheck timer
    server = My.Settings.DB_Server
    username = My.Settings.DB_Username
    password = My.Settings.DB_Password
    database = My.Settings.DB_Database

    'Connection String
    MysqlConn.ConnectionString = "server=" & server & ";" _
    & "user id=" & username & ";" _
    & "password=" & password & ";" _
    & "database=" & database 
End Sub

I add Allow User Variables=True, so it will become

Public Sub Check_DB_Con()
    'Declare database credentials so we can refresh it in recheck timer
    server = My.Settings.DB_Server
    username = My.Settings.DB_Username
    password = My.Settings.DB_Password
    database = My.Settings.DB_Database

    'Connection String
    MysqlConn.ConnectionString = "server=" & server & ";" _
    & "user id=" & username & ";" _
    & "password=" & password & ";" _
    & "database=" & database & ";Allow User Variables=True"
End Sub

score 1 · Answer 2 · edited May 23 '17 at 12:07

Instead of setting the value of the rn in a 2 step SQL, use a cross join. I don't think the call handles the two statements correctly.

 SQL_Query = "SELECT hardware_add " _
            & "FROM (" _
                & "SELECT " _
                & "@row_number:=@row_number + 1 AS num, " _
                & "hardware_add AS hardware_add " _
                & "FROM teller_info" _
                & "CROSS JOIN (SELECT @row_number:=0) AS t " _
            & ") AS sub_query " _
            & "WHERE num = '" & counter & "';"

Stack Example: ROW_NUMBER() in MySQL

score 0 · Answer 3 · edited Mar 18 '19 at 20:05

0

Adding ConnectionTimeout and default command timeout in connection string fixed this issue for me.

eg.

ConnectionTimeout=300000; default command timeout=300000;

edited Mar 18 '19 at 20:05

Pika Supports Ukraine

3,612
10
26
42

answered Mar 18 '19 at 19:45

Bharathi

131
1
2

"Fatal error encountered during command execution" I'm using SET and SELECT xxx FROM (SELECT xxx FROM xxx)

3 Answers3