MySQL Bugs: #90732: group_concat not working with myODBC

Bug #90732	group_concat not working with myODBC
Submitted:	3 May 2018 9:31	Modified:	26 May 2022 11:36
Reporter:	Jernej Pecjak	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.28	OS:	Windows (server 2019)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
When using myODBC with classic ASP and using group_concat with ADO without setting cursor to client (CursorLocation=3), there is error:

***

Provider error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

***

MySql 5.7 did not have this bug.

How to repeat:
Make classic ASp page like:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open  "Provider=MSDASQL;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;uid=xxx;pwd=xxx;database=xxx;OPTION=16384;"

SQLStmt = "SELECT group_concat(name) as name FROM names"
set rsX = Conn.Execute(SQLStmt)

name=rsX("name")

Make database table names with column name (char) and make few entries.

The error is in the last line.

The problem is the cursor if it is set to server (default), if I set it using ADODB.Command to client (instead of using ADO), it works.

But it should work with server side cursor also.

I am using myODBC 5.3.10 32 bit, as this is the latest 32-bit version. 

Suggested fix:
Please fix the group_concat in mysql8 so it works with myODBC properly.

I have just reverted to mySQL 5.7.22 64-bit, I have changed nothing else in my page, connection or myODBC and now the group_concat error is gone. So this is mysql but although using mysql directly without ODBC works, but in connection with myODBC it doesn't. I have quite complext page sql wise and this seem to be the only but that I could find, but it is sever enough to revert back, as I use the group_concat on many occasions and there is no other command that could replace it.

Hello Jernej Pecjak,

Thank you for the bug report.
I could not repeat the issue at our end using Connector / ODBC 5.3.10 & 8.0.11, MySQL 8.0.11  with Windows 10 and it worked without any issues.
Could you please provide repeatable test case (exact steps, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi there

Mysql 8.0.20 and MyODBC 8.0.20 (same problem with MyODBC 5.3.10)...

Group concat does not work at all using Classic ASP :-(

I tried now on windows server 2019 and same problem. When I switch to MySql 5.7 everything starts working...

Seems to be mySQL problem in cooperation with ODBC.

Went back to 5.7.30 and everything is fine using connector 8.0.20... So it is not only myODBC...

But using Navicat on this MySQL statement with group concat also works (no ODBC, but directly).

So it is mysql 8 that doesn't work in this regard with myodbc (5.3 or 8.0).

Please check with latest release. Thanks.

8.0.20 IS latest release. It doesn't work as I said...

Confirming on 8.0.22 Mysql & MyODBC same problem. Using myODBC 8.0.22 and MySql 5.7.27 it is working ok.

In latest mysql still doesn't work. I can use ODBC 8.x but mysql 8.x breaks things.

Hello Jernej Pecjak,

Thank you for the feedback.
Could you please provide repeatable test case (exact steps, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

My test is ok, doesn't work on the new MySQL :-( Why you need another test step by step, I already submited, how you can test this...

Unfortunately, the information you provided is not enough to reproduce the problem.
We created a table like this:

create table bug90732 (id int primary key auto_increment, name char(32));

Inserted some records:

insert into bug90732 (name) values ('abc text'), ('test text'), ('some more text');

Run the script:

...
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open  "Provider=MSDASQL;Driver={MySQL ODBC 8.0 Unicode Driver};server=localhost;uid=xxx;pwd=xxx;database=xxx;OPTION=16384;"

SQLStmt = "SELECT group_concat(name) as name FROM bug90732"
set rsX = Conn.Execute(SQLStmt)
name=rsX("name")

Response.Write("<p>Name: " & name & "</p>")
...

It printed the following:

Name: abc text,test text,some more text

Tested with MySQL Server 8.0.24, ODBC Driver 8.0.22 and 8.0.24

How long is the string produced by group_concat(name)?
It could happen that the result length exceeds the maximum allowed length (group_concat_max_len) set on the server.

In MySQL 8.0 the max length for group_concat() data is 1024.
In our test script we made the result string longer than 1024 and the only effect was trimming the result to 1024 symbols (I am not copy-pasting it here in order to keep the thread clean).

You can try extending it by running the following SQL query:

SET SESSION group_concat_max_len = 1000000;

Since this affects the current session only the above query needs to be executed in the same ASP script before all queries that use group_concat().

I also set the severity to S2 because S1 is only for situations when no workarounds are available at all. In your case using the client cursor solves the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have tried this with mysql 8.0.28 and ODBC 8.0.28 and still doesn't work. You don't even need database for that, you just try:

SQLstmt = "SELECT group_concat('a','b','c' SEPARATOR '') as test"
Set RS = Conn.Execute(SQLStmt)

response.write (RS("test"))

... and the result should be "abc". In reality, the result is:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

This works if I query mysql using Navicat for example and works in all 5.7.x and before.

Unfortunately I need group_concat and I can not move to the new mysql 8 :-(

With the above comment set, I can say that there is no error if I set the group_concat_max_len=100000....

But why does it need 100.000? The result is only less than 50 bytes long....

I found out if I set group:concat_max_len to more or equal to 10923, everything works. If I set it lower, I have the OLE DB error. Does this makes any sense?

I updated the versions

I am still not able to repeat the problem with the data length less than 1024 bytes. If the result is longer than that it gets truncated.

As mentioned before, the server puts the result of the group_concat() call into a buffer, which in the version 8.0 is reduced comparing to 5.7.

I am not sure why in your case concat_max_len has to be more or equal to 10923.

However, this VB code is working without any issues:

Sub bug90732()
    Dim rs As ADODB.Recordset
    Dim con As ADODB.Connection
    Dim strCon As String
    Dim SQL As String
       
    Set con = CreateObject("ADODB.Connection")
    
    strCon = "DSN=test"
    con.Open strCon
    
    SQL = "SELECT group_concat('a','b','c' SEPARATOR '') as test"
    
    Set rs = con.Execute(SQL)
    
    Do While Not rs.EOF
        Debug.Print "test: " & rs("test")
        Debug.Print
        rs.MoveNext
    Loop
    
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

The output is as follows:
--------------------------
test: abc

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".