| 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: | |
| 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: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!
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.