MySQL Bugs: #105504: SQLTables either crashes or returns an empty result

Bug #105504	SQLTables either crashes or returns an empty result
Submitted:	9 Nov 2021 16:32	Modified:	30 Mar 2023 9:38
Reporter:	Pierre le Riche	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.29	OS:	Windows
Assigned to:		CPU Architecture:	Any

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.

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.

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.

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.

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.

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;
}

Thank you for the bug report. Please try latest version 8.0.29. Thanks.

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.

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.

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.

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.

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?

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.

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.

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

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.

Hello Pierre,

Thank you for the details.
We once again tried to reproduce your issue but we couldn't repeat it. If you can provide more information, feel free to add it to this bug.

Thank you for your interest in MySQL.

Regards,
Ashwini Patil

Hi Ashwini,

I just tried to reproduce the issue in 8.0.32 and was unable to, so it seems the issue has been fixed. Perhaps a fix went in for a different issue that simultaneously addressed this issue.

Thank you for following up.

Best regards,
Pierre