sphinxql: syntax error, unexpected ';', expecting $end near ';'

[Andyyy777]

Software versions
MySqlConnector version: MySqlConnector v2.1.5
Server type (MySQL, MariaDB, Aurora, etc.) and version: MySQ 5.7.3
.NET version: .NET framework 4.5

Describe the bug
Hi there, I'm trying to execute a simple insertion sphinxql query "INSERT into rt_table1 (id) VALUES (1700111)" by using mysqlcommand.ExecuteNonQuery(). However, it always shows the error

"sphinxql: syntax error, unexpected ';', expecting $end near ';'"

for every time the ExecuteNonQuery() is called, and adding a ";" at the end of the query does not bring any changes. Moreover, when I typed the same command text in cmd, the command text was able to be successfully executed. Thank you so much for the help!

Exception

Exception while inserting:    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken)
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__106.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.ExecuteNonQuery()
   at TestSphinxRTIndexer.Main.VB$StateMachine_8_InsertBatchIntoIndex.MoveNext() in C:\Users\User\Desktop\New_folder

Code sample

Private Async Function InsertBatchIntoIndex(Index As Index, strRowDataToBeInserted As String, ByVal comDB As MySqlCommand) As Task

        Try
            Dim conDB As New MySqlConnection(connectionstring)
            Await conDB.OpenAsync()

            comDB.Connection = conDB

            Dim attribs As New ArrayList(Index.GetAttributes())
            'Insert and name the primary key column identified as 'id' in sphinx, as the first column
            attribs.Insert(0, "id")

            Dim strOfAttribs As String() = attribs.ToArray(GetType(String))

            comDB.CommandText = "INSERT into rt_posts_big_chunk_part1 (id) VALUES (1704996)"

            Console.WriteLine(comDB.CommandText)
            If (conDB.State = ConnectionState.Open) Then
                Console.WriteLine("keeps open")
                Dim result As Integer = comDB.ExecuteNonQuery()
            End If

            TryCloseDBCon_MySQL(conDB)
        Catch ex As Exception
        ...
        End Try

    End Function

Expected behavior
The insertion query can be successfully executed by using executeNonQuery(), and the corrected row can be added.

[bgrainger]

Server type (MySQL, MariaDB, Aurora, etc.) and version: MySQ 5.7.3

How are you getting a "sphinxql" error message from MySQL? Is your server software actually Sphinx? Some kind of plugin?

[Andyyy777]

Yep, the actual server is sphinx but I use MySQL to connect with it.

[Andyyy777]

And I just saw an another post about this issue as well: manticoresoftware/manticoresearch#314
Hope it could help.

[bgrainger]

This is likely a problem introduced in 378c4b8 for batching, and might be fixed as a side-effect of #946.

[bgrainger]

It's very odd that SphinxQL claims to be compatible with MySQL, but doesn't accept semicolons, even though the example documentation shows them: https://sphinxsearch.com/docs/sphinx3.html#running-queries-via-mysql-shell

Perhaps MySQL CLI treats them as a statement delimiter and doesn't send them on the wire, even when entered manually by a user?

[Andyyy777]

Thank you @bgrainger !!! Definitely will try that :)

[bgrainger]

Fixed in 2.1.7.

[winghang1992]

Yep, the actual server is sphinx but I use MySQL to connect with it.

Software versions MySqlConnector version: MySqlConnector v2.1.5 Server type (MySQL, MariaDB, Aurora, etc.) and version: MySQ 5.7.3 .NET version: .NET framework 4.5

Describe the bug Hi there, I'm trying to execute a simple insertion sphinxql query "INSERT into rt_table1 (id) VALUES (1700111)" by using mysqlcommand.ExecuteNonQuery(). However, it always shows the error

"sphinxql: syntax error, unexpected ';', expecting $end near ';'"

for every time the ExecuteNonQuery() is called, and adding a ";" at the end of the query does not bring any changes. Moreover, when I typed the same command text in cmd, the command text was able to be successfully executed. Thank you so much for the help!

Exception

Exception while inserting:    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken)
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__106.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.ExecuteNonQuery()
   at TestSphinxRTIndexer.Main.VB$StateMachine_8_InsertBatchIntoIndex.MoveNext() in C:\Users\User\Desktop\New_folder

Code sample

Private Async Function InsertBatchIntoIndex(Index As Index, strRowDataToBeInserted As String, ByVal comDB As MySqlCommand) As Task

        Try
            Dim conDB As New MySqlConnection(connectionstring)
            Await conDB.OpenAsync()

            comDB.Connection = conDB

            Dim attribs As New ArrayList(Index.GetAttributes())
            'Insert and name the primary key column identified as 'id' in sphinx, as the first column
            attribs.Insert(0, "id")

            Dim strOfAttribs As String() = attribs.ToArray(GetType(String))

            comDB.CommandText = "INSERT into rt_posts_big_chunk_part1 (id) VALUES (1704996)"

            Console.WriteLine(comDB.CommandText)
            If (conDB.State = ConnectionState.Open) Then
                Console.WriteLine("keeps open")
                Dim result As Integer = comDB.ExecuteNonQuery()
            End If

            TryCloseDBCon_MySQL(conDB)
        Catch ex As Exception
        ...
        End Try

    End Function

Expected behavior The insertion query can be successfully executed by using executeNonQuery(), and the corrected row can be added.

So could you fix the issue ? i've encounter the same issue.