Bug #80815 SQLPrimaryKeys is only returning 1 row
Submitted: 21 Mar 2016 21:50 Modified: 4 Apr 2016 13:33
Reporter: Linda Thompson Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.3.6 OS:Windows (Windows 7)
Assigned to: CPU Architecture:Any
Tags: SQLPrimaryKeys

[21 Mar 2016 21:50] Linda Thompson
Description:
Databases: MySQL 5.5.32, MySQL 5.7.11
Database drivers: MySQL ODBC 5.1.13, 5.2.7, 5.3.4, 5.3.6

SQLPrimaryKeys only returns 1 row even though there are two columns in the primary key

Sounds a lot like the bug supposedly fixed below:
Some catalog functions (such as SQLColumns(), SQLStatistics(), and SQLPrimaryKeys()) would only return one row, when called after pre-execution failed. (Bug #12824839)

How to repeat:
CREATE TABLE `ods`.`123sda` (
  `User_ID` VARCHAR(50) NOT NULL,
  `Max_Update_Time` DATETIME NOT NULL,
  `Sum_Sec` INT NULL,
  `Update_Type` VARCHAR(254) NULL,
  PRIMARY KEY (`User_ID`, `Max_Update_Time`));

code
		SQLRETURN   rc;
		UWORD       supported;
		SQLWCHAR    column_name[132], pk_name[132];
		SQLSMALLINT key_seq=0;
		SQLLEN      rv[3];
		SQLHANDLE   hstmt = 0;
		bool        done = false, found_pkcols = false;
		String		m_Out = L"";

		rc = SQLAllocHandle(SQL_HANDLE_STMT, m_pSessionData->h_dbc, &hstmt);
		if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
			goto exit_label;

		if (SQLBindCol(hstmt, 4, SQL_C_WCHAR,  (SQLPOINTER)column_name, sizeof(column_name), &rv[0]) != SQL_SUCCESS ||
			SQLBindCol(hstmt, 5, SQL_C_SSHORT, (SQLPOINTER)&key_seq,    0,                   &rv[1]) != SQL_SUCCESS ||
			SQLBindCol(hstmt, 6, SQL_C_WCHAR,  (SQLPOINTER)pk_name,     sizeof(pk_name),     &rv[2]) != SQL_SUCCESS)
			goto exit_label;

const std::pair<wchar_t *, SQLSMALLINT> &catalogName(ConvertToSqlString(m_pSQL->m_strCatalogOnly));
const std::pair<wchar_t *, SQLSMALLINT> &schemaName(ConvertToSqlString(m_pSQL->m_strOwnerNameOnly));
const std::pair<wchar_t *, SQLSMALLINT> &tableName(ConvertToSqlString(m_pSQL->m_strTableNameOnly));

rc = SQLPrimaryKeys(hstmt, schemaName.first, schemaName.second,
									   catalogName.first, catalogName.second,
								       tableName.first, tableName.second);
			}
		}
		if (rc  != SQL_SUCCESS)
			goto exit_label;

		do
		{
			rc = SQLFetch(hstmt);
			if (!(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO))
			{
				if (rc == SQL_NO_DATA_FOUND)
					done = true;
			}
			else
			{
				String strPKCol = ConvertToString(column_name);
			}
		} while (!done);
[22 Mar 2016 7:59] Bogdan Degtyariov
test case

Attachment: bug80815.c (text/plain), 5.90 KiB.

[22 Mar 2016 8:05] Bogdan Degtyariov
Hello Linda,

Unfortunately, we could not reproduce the problem using MySQL Connector/ODBC 5.3.6 and MySQL Server 5.7.12. Your test case was not complete, therefore we came up with our own test, which uses your table definition.

It creates the table named `123sda` with all the keys/columns and then uses SQLPrimaryKeys() to retrieve the list of columns that the primary key consists of.

Here is the output:
--------------------------------------------------------------
Connected!
Creating table...
Requesting Keys...
ROW # 1
Table: 123sda, Column: User_ID, Key Seq: 1, Key Name: PRIMARY
ROW # 2
Table: 123sda, Column: Max_Update_Time, Key Seq: 2, Key Name: PRIMARY
DONE!
--------------------------------------------------------------

As you can see the ODBC driver returned both primary key components.

Please check the source C file (bug80815.c) attached in the previous message and let us know if it works in the same way as it does for us.

Note, you need to change the connection string. In our test environment we have the DSN named "test" for connecting, but for you it could be something else.

We are looking forward to receiving your feedback.
Thanks.
[24 Mar 2016 14:22] Linda Thompson
So with the code you provided if you the following code between Creating the Table and Requesting the Keys you will see the failure.  It is the call to SQLNumResultsCols that somehow interferes with the SQLPrimaryKeys call.

		ok_dbc(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1)); 	
		rc = SQLPrepare(hstmt1, L"Select * from `123sda`", SQL_NTS);
		if (!(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO))
		{
		}
		else
		{
			SQLSMALLINT nColumns = 0;
			SQLNumResultCols(hstmt1, &nColumns);
		}
[29 Mar 2016 7:39] Bogdan Degtyariov
Hi Linda,

Thanks for your feedback.
Your code is not doing things in the way MySQL Server is expecting.
MySQL Server does not support the concurrent queries within the same connection.
You have two statements there hstmt and hstmt1.
SQLForeignKeys() basically runs "SHOW KEYS" statement on the table whilst SQLNumResultCols() implicitly executes the prepared statement in order to determine the number of resulting columns. Before executing anything on an independent statement (hstmt) the statement operation (for hstmt1) must be completed by SQLExecute().

Putting SQLExecute(hstmt1) before SQLPrimaryKeys(hstmt,...) resolves the problem. Otherwise, I would recommend using two concurrent connections, so the context of one non-completed statement could not affect another statement.
[29 Mar 2016 13:35] Linda Thompson
I run SQLGetInfo with SQL_MAX_CONCURRENT_ACTIVITIES and it returns 0.  According to the ODBC documentation when a 0 is returned it is unlimited or undefined.  How does one check MySQL for this limitation?

SQL_MAX_CONCURRENT_ACTIVITIES(ODBC 1.0)
An SQLUSMALLINT value that specifies the maximum number of active statements that the driver can support for a connection. A statement is defined as active if it has results pending, with the term "results" meaning rows from a SELECT operation or rows affected by an INSERT, UPDATE, or DELETE operation (such as a row count), or if it is in a NEED_DATA state. This value can reflect a limitation imposed by either the driver or the data source. If there is no specified limit or the limit is unknown, this value is set to zero.
[30 Mar 2016 11:02] Bogdan Degtyariov
You are right, the value for SQL_MAX_CONCURRENT_ACTIVITIES attribute should be set to 1.
Although most ODBC operations still can use multiple statements in the same connection safely, some exceptions exist and they assume the value for the above-mentioned attribute as 1.

Setting "Verified" status.
[4 Apr 2016 13:33] Linda Thompson
Does this mean you are going to fix something?  If so, when might that be and "what"
[21 Dec 2022 9:32] Bogdan Degtyariov
Posted by developer:
 
Determined the root cause of the problem, which was not with SQLPrimaryKeys().
Reported a new bug (#109448/#34916959).