MySQL Bugs: #97393: SQLPrimaryKeys doesn't work if I the specified table is not in current database

Bug #97393	SQLPrimaryKeys doesn't work if I the specified table is not in current database
Submitted:	28 Oct 2019 10:10	Modified:	10 Dec 2019 6:36
Reporter:	wu winnie	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.03.06	OS:	Windows
Assigned to:		CPU Architecture:	Any (X64)
Tags:	odbc interface, SQLPrimaryKeys

Description:
Hi,
   I found an issue when I was using MySQL odbc driver.
I have connected to MySQL server by odbc interface:

std::string ConnectionString = "DRIVER={MySQL ODBC 5.3 Driver (xxxxxx)};server=10.240.66.204;port=3306;database=test;UID=root;PWD=xxx";

SQLDriverConnect(dbc, NULL, (SQLCHAR*)ConnectionString .c_str(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

it was connected successfully.
note: I have set the default database as "test" in my connection string.

then I was trying to use SQLPrimaryKeys to fetch primary key info of a table located in other database.

int rc = SQLPrimaryKeys(stmt, NULL, SQL_NTS, "otherdb", SQL_NTS, "mytable", SQL_NTS);
this function returned with SQL_SUCCESS.

but when I try to fetch primary key info, SQLFetch returned 100(failed)

then I change to set the second parameter of this function: 
int rc = SQLPrimaryKeys(stmt, "otherdb", SQL_NTS, NULL, SQL_NTS, "mytable", SQL_NTS);

then I can get the primary key info successfully by SQLFetch.

SQLRETURN SQLPrimaryKeys(  
     SQLHSTMT       StatementHandle,  
     SQLCHAR *      CatalogName,  <-- why should I use this parameter?
     SQLSMALLINT    NameLength1,  
     SQLCHAR *      SchemaName,  <-- in MySQL, schema is database, correct?
     SQLSMALLINT    NameLength2,  
     SQLCHAR *      TableName,  
     SQLSMALLINT    NameLength3);  

I was wondering if this is MySQL's bug, or it is as designed. thanks.
	

How to repeat:
just see my description

Suggested fix:
yes

Any updates?

Hi Wu Winnie,

Thank you for your report.
There is a lot of confusion between the terms 'Catalog' and 'Schema'. Generally speaking a catalog in a relational database can contain several schemas that can be used for referencing tables as catalog_name.schema_name.table_name.

From point of view of some RDBMS such as Oracle a catalog would be a collection of data managed by the same instance of database engine.
MySQL Server uses databases as catalogs and we can say that in this case Catalog == Database. On a physical disk a MySQL Database is represented as a directory containing metadata information (such as table structure). Thus, the same instance of MySQL Server can operate over multiple databases. In MySQL there is no such thing as a Schema in the meaning of namespace inside of a database. However, the SQL ISO documentation uses the term Schema with the semantic that can be attributed to a Catalog. Also, introducing of INFORMATION_SCHEMA in MySQL Server added to the confusion.
The meaning of Schema is rather loosely interpreted by each particular Relational Database system. For instance: in case of Oracle the Schema would be a namespace in a database corresponding to a user account. In MySQL there is no such correspondence because the database user accounts are independent entities containing a set of access privileges.

The group of functions where SQLPrimaryKeys() belongs is called the "Catalog functions":

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/catalog-functions-in-odbc?...

In case of MySQL that would operate over MySQL databases. Therefore, it was decided that a Catalog is representing the meaning of a Database in this context.

I hope the above provides a satisfactory explanation of the historical reasons why the function behaves in the way it does.
For the sake of compatibility with the previous versions we keep this behavior unchanged.

Hi Bogdan, 
   Thank you very much for your reply, so for this ODBC API (MYSQL server):

SQLRETURN SQLPrimaryKeys(  
     SQLHSTMT       StatementHandle,  
     SQLCHAR *      CatalogName,   
     SQLSMALLINT    NameLength1,  
     SQLCHAR *      SchemaName,   
     SQLSMALLINT    NameLength2,  
     SQLCHAR *      TableName,  
     SQLSMALLINT    NameLength3);  

I should set CatalogName to be the database name and leave SchemaName to be empty, correct?