Bug #105504 SQLTables either crashes or returns an empty result
Submitted: 9 Nov 2021 16:32 Modified: 21 Jul 11:07
Reporter: Pierre le Riche Email Updates:
Status: Analyzing Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:8.0.29 OS:Windows
Assigned to: MySQL Verification Team CPU Architecture:Any

[9 Nov 2021 16:32] Pierre le Riche
Description:
The SQLTables call is broken in the 8.0.27 ODBC driver. Examples:
1) Calling SQLTables on the following connection string causes a crash inside the ODBC driver code (access violation at 0x566f13f6: read of address 0x00cde000):
 Driver=MySQL ODBC 8.0 ANSI Driver;UID=root;PWD=rootpassword;Server=localhost;Database=sys;Option=0

2) Calling SQLTables on this connection string returns an empty result set instead:
 Driver=MySQL ODBC 8.0 ANSI Driver;UID=root;PWD=rootpassword;Server=localhost;Database=sys;Option=1048576

This is a regression since 8.0.25, which works correctly.

How to repeat:
Call SQLTables when connected using the following connection string:
 Driver=MySQL ODBC 8.0 ANSI Driver;UID=root;PWD=rootpassword;Server=localhost;Database=sys;Option=0

(replace "rootpassword" with the actual root password)

Suggested fix:
It works fine under 8.0.25, so the bug must have crept in since.
[9 Nov 2021 16:49] Pierre le Riche
Important Clarification: The crash in the first test case occurs during the SQLFetch call when the result is fetched after the SQLTables call, not inside the SQLTables call itself.
[9 Nov 2021 16:52] Pierre le Riche
Sample of the crash that occurs during the SQLFetch call to fetch the result of SQLTables:

Error
-----
Access violation at address 566F13F6 in module 'myodbc8a.dll'. Read of address 00DFF000
--
OK

Again, no such crash under 8.0.25 or older drivers.
[9 Nov 2021 21:02] Pierre le Riche
I've traced the crash to a memory management issue in the driver. Since 8.0.26, if SQLFreeStmt(statementhandle, SQL_UNBIND) is called before the first call to SQLBindCol then the internal buffers of the driver seem to become corrupted.

SQLFreeStmt(statementhandle, SQL_UNBIND) should have no effect if there are no current bindings, and indeed has no effect in 8.0.25 and before.
[9 Nov 2021 21:23] Pierre le Riche
Please refer to the documentation for SQLMoreResults:
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlmoreresults-function

In particular, the last line of this paragraph:
"Any bindings that were established for the previous result set still remain valid. If the column structures are different for this result set, then calling SQLFetch or SQLFetchScroll may result in an error or truncation. To prevent this, the application has to call SQLBindCol to explicitly rebind as appropriate (or do so by setting descriptor fields). Alternatively, the application can call SQLFreeStmt with an Option of SQL_UNBIND to unbind all the column buffers."

It should therefore be safe to call SQLFreeStmt with SQL_UNBIND even if no fields are currently bound.
[9 Nov 2021 21:55] Pierre le Riche
This test case should show the crash:

DECLARE_TEST(t_bug105504)
{
  SQLUINTEGER in= 4255080020UL, out;
  SQLCHAR buff[128];

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug105504");
  ok_sql(hstmt, "CREATE TABLE t_bug105504 (a INT UNSIGNED)");

  sprintf((char *)buff, "INSERT INTO t_bug105504 VALUES ('%u')", in);
  ok_stmt(hstmt, SQLExecDirect(hstmt, buff, SQL_NTS));

  ok_sql(hstmt, "SELECT * FROM t_bug105504");

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));

  ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_ULONG, &out, 0, NULL));
  ok_stmt(hstmt, SQLFetch(hstmt));

  is_num(out, in);

  expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA);

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug105504");

  return OK;
}
[16 May 11:38] MySQL Verification Team
Thank you for the bug report. Please try latest version 8.0.29. Thanks.
[16 May 12:28] Pierre le Riche
There is nothing in the commits since 8.0.27 that suggests that this bug has been fixed, and indeed it hasn't.

Calling SQlFreeStmt with parameter SQL_UNBIND before the first call to SQLBindCol still causes mayhem inside the ODBC driver. This bug appears to have been introduced in 8.0.26.
[17 May 10:06] Bogdan Degtyariov
Hi Pierre,

Thank you for providing the thorough description of the problem and the C test coded in Connector/ODBC unit test framework.

However, we were unable to repeat the crash or any problem at all using your code.
Also, the test case does not contain SQLTables() call at all.
If it is a mistake can you please update it?

Thanks.
[17 May 11:37] Pierre le Riche
Thank you for looking into this. The SQLTables call was a red herring. After opening the bug report I traced the cause of the crash to the SQLFreeStmt(hstmt, SQL_UNBIND) call before the first call to SQLBindCol, so the title of the bug report is now perhaps misleading.

The attached test case was adapted from your testing framework. If you're not seeing the crash then it may be influenced by the connection string settings. In particular I have the NO_CACHE (0x100000) and MULTI_STATEMENTS (0x4000000) options enabled. As mentioned earlier this issue crept into the 8.0.25 ODBC driver. I cannot reproduce the problem with 8.0.24 or earlier versions.

The symptoms are quite erratic. Sometimes the crash does not happen immediately, but only a few statement executions later. Sometimes it is an access violation, sometimes nonsense in a later result set. The symptoms suggest a corruption of the memory pool that is precipitated by calling SQLFreeStmt(hstmt, SQL_UNBIND) before the first call to SQLBindCol.
[17 May 11:40] Pierre le Riche
My previous comment mentions the bug crept into 8.0.25. That is incorrect. The bug crept into 8.0.26. 8.0.25 is still fine.

Unfortunately it does not seem possible to edit comments.
[17 May 12:43] Bogdan Degtyariov
I am still unable to repeat this problem.
Here is the corrected code, which takes into account your comments about the connection options:

DECLARE_TEST(t_bug105504)
{
  SQLUINTEGER in = 4255080020UL, out;
  SQLCHAR buff[128];
  DECLARE_BASIC_HANDLES(henv1, hdbc1, hstmt1);
  alloc_basic_handles_with_opt(&henv1, &hdbc1, &hstmt1, NULL, NULL,
    NULL, NULL, "OPTION=1048576");

  ok_sql(hstmt1, "DROP TABLE IF EXISTS t_bug105504");
  ok_sql(hstmt1, "CREATE TABLE t_bug105504 (a INT UNSIGNED)");

  sprintf((char*)buff, "INSERT INTO t_bug105504 VALUES ('%u')", in);
  ok_stmt(hstmt1, SQLExecDirect(hstmt1, buff, SQL_NTS));

  for (int i = 0; i < 100; i++)
  {
    ok_sql(hstmt1, "SELECT * FROM t_bug105504");

    ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_UNBIND));

    ok_stmt(hstmt1, SQLBindCol(hstmt1, 1, SQL_C_ULONG, &out, 0, NULL));
    ok_stmt(hstmt1, SQLFetch(hstmt1));

    is_num(out, in);

    expect_stmt(hstmt1, SQLFetch(hstmt1), SQL_NO_DATA);

    ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_CLOSE));
  }

  ok_sql(hstmt1, "DROP TABLE IF EXISTS t_bug105504");

  free_basic_handles(&henv1, &hdbc1, &hstmt1);

  return OK;
}

It also repeats the SELECT/BIND/FETCH/FREE 100 times, but I still see no crash and no faiure to check the result in "is_num(out, in);"
This was tested with versions 8.0.27 and 8.0.29

What else should I try?
[17 May 13:31] Pierre le Riche
Hi Bogdan,

Thank you again for looking into this. It is unfortunate that you're not seeing the crash in the simple test case. In the application code I am testing with the queries are more complex and I am also making use of parameters. I'm trying to narrow it down further, but it is quite elusive. I should add that I am testing on Windows, both 32-bit and 64-bit.

What I can confirm is this:
1) If I remove the NO_CACHE option then there are no issues in any of the ODBC drivers
2) If I enable NO_CACHE the 8.0.26 driver either returns garbage results, or crashes after a significant number of queries. I am trying to identify a pattern.
3) 8.0.25 and earlier (both 32-bit and 64-bit) have no issues with or without NO
CACHE.

Looking at the Git commit log I strongly suspect the issue lies somewhere in this commit:
Revision: 92a2da4f1bcf767a2876c76e7217dfb059b7f55d
Author: Bogdan Degtyariov <bogdan.degtyariov@oracle.com>
Date: 2021/05/28 09:53:52
Message:
Bug #27499789 ODBC PARAMETER NO_CACHE LEAD TO FUNCTION CALL ERROR NOT RETURN

Does that ring a bell at all?

I am working around the issue by not calling SQLFreeStmt(hstmt, SQL_UNBIND) before fields have actually been bound with SQLBindCol. However, I am bit uneasy with having such a workaround in place and would be more comfortable using the latest ODBC driver knowing that the issue has been addressed.
[21 Jul 9:57] Bogdan Degtyariov
Hi Pierre,

I am just wondering if you were able to come up with another test case, which would allow us to repeat the crash.

Thanks.
[21 Jul 10:51] Pierre le Riche
Hi Bogdan,

I have reorganised my code so that it never calls SQLFreeStmt(hstmt, SQL_UNBIND) before a field has been bound. Doing that solved all the stability issues I had with the driver, so I stopped digging further.

One thing I did not mention earlier: I am using the Ansi driver. Perhaps that is a factor? I sense there is less focus on the Ansi driver than the Unicode driver. I am currently using 8.0.27, because of https://bugs.mysql.com/bug.php?id=107766, which was introduced in 8.0.28.

Best regards,
Pierre
[21 Jul 11:07] Bogdan Degtyariov
Hi Pierre,

Thank you for your reply.
The ANSI and Unicode drivers share more than 90% of the code. The Unicode is basically just adding one extra layer for Wide character processing. So, in theory it should not be specific to ANSI or Unicode if the crash is happening in the code shared by both versions, but I could be wrong.

We are going to give anothery try with reproducing the crash since is a serious problem. However, if still unable to repeat we will have to close this report with "Can't repeat" status. It can be reopened at any time if you wish to add more details.

P.S: The bug https://bugs.mysql.com/bug.php?id=107766 is in the priority list and we intend to fix it soon, but cannot make any promises at this point.