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

[3 May 2018 9:31] Jernej Pecjak
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.
[3 May 2018 12:31] Jernej Pecjak
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.
[1 Jun 2018 8:10] Chiranjeevi Battula
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.
[2 Jul 2018 1:00] Bugs System
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".
[19 May 2020 17:36] Jernej Pecjak
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...
[21 May 2020 21:26] Jernej Pecjak
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).
[6 Jul 2020 14:18] MySQL Verification Team
Please check with latest release. Thanks.
[7 Jul 2020 23:30] Jernej Pecjak
8.0.20 IS latest release. It doesn't work as I said...
[21 Aug 2020 15:34] Jernej Pecjak
Confirming on 8.0.22 Mysql & MyODBC same problem. Using myODBC 8.0.22 and MySql 5.7.27 it is working ok.
[2 Mar 8:19] Jernej Pecjak
In latest mysql still doesn't work. I can use ODBC 8.x but mysql 8.x breaks things.
[11 Mar 13:12] MySQL Verification Team
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
[12 Apr 1:00] Bugs System
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".
[12 Apr 8:22] Jernej Pecjak
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...
[23 Apr 12:39] Bogdan Degtyariov
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.
[24 May 1:00] Bugs System
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".