Bug #69983 Another 6.7.4 Error (like many others)....

• Submitted: 11 Aug 2013 16:50
• Modified: 14 Aug 2013 0:26
• Reporter: Christian Benner
• Status: Not a Bug
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 6.7.4
• OS: Windows
• Assigned to: Fernando Gonzalez.Sanchez
• CPU Architecture: Any

[11 Aug 2013 16:50] Christian Benner

Description:
Connector throws inner exception during SELECT @Variabel;
(Same works without any error with MySQL Workbench)

How to repeat:
SET AUTOCOMMIT=0;
START TRANSACTION;
SET @LID=LAST_INSERT_ID();
INSERT INTO `Database`.`table` (id,name,date) VALUES('65',@LID,'2013.8.11 18:33:7');
SELECT @LID;
COMMIT;
SET AUTOCOMMIT=1;

(VB.NET Part)

Try
            conn.Open()
            Dim cmd As New MySqlCommand(strSQL, conn)
            Dim LID As String = CStr(cmd.ExecuteScalar)

            Return LID
        Catch ex As MySqlException
            Return ex.Message
        Finally
            conn.Close()
        End Try

[11 Aug 2013 20:17] Fernando Gonzalez.Sanchez

Have you tried appending to the connection string ";Allow User Variables=true;"?

[11 Aug 2013 20:34] Christian Benner

Allow User Variables=True is already in my conn string.

[11 Aug 2013 20:35] Christian Benner

Allow User Variables=True is already in my conn string.

[14 Aug 2013 0:26] Fernando Gonzalez.Sanchez

The culprit is that you cannot execute more than one SQL statement in a single MySqlCommand.Execute call, either:
a) Wrap the code in a stored procedure and call it
b) Use MySqlScript class, sample follow

Dim strSQL As String =
            "SET AUTOCOMMIT=0; START TRANSACTION; SET @LID=LAST_INSERT_ID();" & _
            "INSERT INTO `Database`.`table` (id,name,date) VALUES('65',@LID,'2013.8.11 18:33:7'); " & _
            "SELECT @LID; COMMIT; SET AUTOCOMMIT=1;"
        Dim conn As New MySqlConnection("server=localhost; database=database; userid=root;")
        Try
            conn.Open()
            'Dim cmd As New MySqlCommand(strSQL, conn)
            'Dim LID As String = CStr(cmd.ExecuteScalar)
            Dim script As New MySqlScript(conn, strSQL)
            Dim LID As String = CStr(script.Execute())

        Catch ex As MySqlException

        Finally
            conn.Close()
        End Try

This sample works, so it is not bug. Thanks for your feedback.