Bug #108126 SQLColumns still use server-side prepared statements, when no_ssps=1 is set
Submitted: 12 Aug 2022 1:23 Modified: 30 Mar 2023 12:23
Reporter: qingshun luo Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.0.29 OS:CentOS
Assigned to: CPU Architecture:x86
Tags: NO_SSPS, SQLColumns

[12 Aug 2022 1:23] qingshun luo
Description:
For some reason, my database server can't handle prepared statements well, so I have to use mysql-connector-odbc with no_ssps=1.
I found SQLColumns still use server-side prepared statements, when no_ssps=1 is set.
Catch network flow with tcpdump, we notice that prepared statements is sent before like this:

```
select TABLE_SCHEMA as TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_TYPE as TYPE_NAME,IF(ISNULL(CHARACTER_MAXIMUM_LENGTH), IF(DATA_TYPE LIKE 'bit',CAST((NUMERIC_PRECISION+7)/8 AS UNSIGNED),NUMERIC_PRECISION), CHARACTER_MAXIMUM_LENGTH) as COLUMN_SIZE,CHARACTER_OCTET_LENGTH as BUFFER_LENGTH,NUMERIC_SCALE as DECIMAL_DIGITS,IF(ISNULL(NUMERIC_PRECISION), NULL, 10) as NUM_PREC_RADIX,IF(EXTRA LIKE "%auto_increment%", "YES", IS_NULLABLE) as NULLABLE,COLUMN_COMMENT as REMARKS,IF(ISNULL(COLUMN_DEFAULT), "NULL", IF(ISNULL(NUMERIC_PRECISION), CONCAT("'", COLUMN_DEFAULT, "'"),COLUMN_DEFAULT)) as COLUMN_DEF,0 as SQL_DATA_TYPE,NULL as SQL_DATA_TYPE_SUB,CHARACTER_OCTET_LENGTH as CHAR_OCTET_LENGTH,ORDINAL_POSITION,IF(EXTRA LIKE "%auto_increment%", "YES", IS_NULLABLE) AS IS_NULLABLE,CAST(CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH AS SIGNED) AS CHAR_SIZE FROM information_schema.COLUMNS c WHERE 1=1 AND c.TABLE_SCHEMA  LIKE ? AND c.TABLE_NAME LIKE ? ORDER BY ORDINAL_POSITION;
```

How to repeat:
I have repeat the same phenomenon  with mysql-connector-odbc 8.0.29 and 8.0.28.

My code does not do anything special. Connection string is like
  "SN=myodbc8w;DRIVER={/home/qsdb/code/install/mysql-connector-odbc/lib/libmyodbc8w.so};SERVER=127.0.0.1;port=60017;DATABASE=test;UID=test;PASSWORD=test;CharSet=utf8;Protocol=TCPIP;no_ssps=1;"

Use SQLColumns in my code.
[12 Aug 2022 1:26] qingshun luo
simple test code

Attachment: odbc.c (application/octet-stream, text), 5.80 KiB.

[12 Aug 2022 1:27] qingshun luo
network flow catched by tcpdump during my test

Attachment: test-flow.pcap (application/octet-stream, text), 6.85 KiB.

[31 Aug 2022 3:39] qingshun luo
I can repeat the same phenomenon with mysql-connector-odbc 8.0.30.
By add no_i_s=1,which is now deprecated, I can avoid use server side prepare statement.
Still suggest to use exec_stmt_query directly in SQLColumns same as other function like SQLTables here, because I can't tell the necessary  of prepare mark for catalog or table name.
[27 Sep 2022 10:59] MySQL Verification Team
Hello qingshun luo,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil
[28 Sep 2022 2:32] qingshun luo
Thank you for your reply.
[21 Oct 2022 7:33] Bogdan Degtyariov
Posted by developer:
 
SQLColumns() will now use SSPS depending on the connection option NO_SSPS.
The patch is pushed to the source tree.
[21 Oct 2022 8:19] Bogdan Degtyariov
Posted by developer:
 
Using the connection option NO_SSPS=1 will cause SQLColumns() function no to use SSPS for getting column information. In such cases the column information will be retrieved through running a normal query.
[5 Dec 2022 22:37] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.32 release, and here's the proposed changelog entry from the documentation team:

SQLColumns() would use prepared statements with NO_SSPS=1.

Thank you for the bug report.
[30 Mar 2023 12:23] qingshun luo
Thank you very much!