Bug #108126 SQLColumns still use server-side prepared statements, when no_ssps=1 is set
Submitted: 12 Aug 1:23 Modified: 28 Sep 2:32
Reporter: qingshun luo Email Updates:
Status: Verified 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 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 1:26] qingshun luo
simple test code

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

[12 Aug 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 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 10:59] MySQL Verification Team
Hello qingshun luo,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil
[28 Sep 2:32] qingshun luo
Thank you for your reply.