MySQL Bugs: #112032: Using C program to execute query and report errors

Bug #112032	Using C program to execute query and report errors
Submitted:	10 Aug 2023 12:57	Modified:	15 Aug 2023 6:34
Reporter:	xiang wang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
When the parameter internal_tmp_mem_storage_engine=memory, using C program execution statements uses temporary tables, while manually executing the same statement does not use temporary tables。

How to repeat:
#configuration my.cnf
[mysqld]
internal_tmp_mem_storage_engine=memory

#Execute the following command
mysql> source sctyao.sql

#You need to compile the C program first, and then execute the following command
./tst2053 127.0.0.1 3306 test app Root@123

#You will receive the following error message
mysql_stmt_execute fail, sqlcode = 1118, error = Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

#Executing command found Created_tmp_disk_tables will increase by 1
mysql>show global status like '%tmp%';

#Directly execute the same statement on the MySQL client
mysql>SELECT `K`, `C00`, `C01`, `C02`, `C03`, `C04`, `C05`, `C06`, `C07`, `C08`, `C09`, `C10`, `C11`,"
"`C12`, `C13`, `C14`, `C15`, `C16`, `C17`, `C18`, `C19`, `C20`, `C21`, `C22`, `C23`, `C24`, `C25`,"
"`C26`, `C27`, `C28`, `C29`, `C30`, `C31`, `C32`, `C33`, `C34`, `C35`, `C36`, `C37`, `C38`, `C39`,"
"`C40`, `C41`, `C42`, `C43` FROM SCTYAO;

#The statement can be executed successfully and Created_tmp_disk_tables not added

The files used for the above tests

Attachment: tst2053.c (application/octet-stream, text), 2.54 KiB.

The files used for the above tests

Attachment: sctyao.sql (application/octet-stream, text), 337.69 KiB.

Question:

1. Set parameter internal_Tmp_Mem_Storage_Engine=TempTable can solve the problem of C program execution errors

2. Why do manual and C programs execute the same statements? C programs use temporary tables, while manual execution does not use temporary tables

Hi Mr. wang,

Thank you for your bug report.

However, your test case using C API is incomplete.

First of all, you do not use EXPLAIN at all.

Second, you do not fetch nor output any results with your C program.

Please, provide a C program that will show the bug.

We have written our own C program with mysql_real_query() and mysql_store_result() and we have got the same EXPLAIN statement as with mysql CLI .

Hence, please provide a fully repeatable test case for your program written with our C API.

We are waiting for your feedback.

It seems that internal_tmp_disk_storage_engine affect the behavior.

Hi,
Is there any place in document illustrate that when use mysql_stmt_* related interfaces, the result will use tmp table to store the result?

Temporary tables were used due to the use of the CURSOR_TYPE_READ_ONLY flag.
---------------------------------------------------------------------------
The character set of table sctyao is utf8mb4. When using a temporary table, even if the field is empty, the length will be calculated by 4 bytes, ultimately exceeding the limit of 8126. The manually executed Insert calculates the length of a data row based on the actual number of bytes occupied.

Hi,

You might find this page helpful:

https://dev.mysql.com/doc/c-api/8.1/en/mysql-stmt-attr-set.html

Try all the variants that are mentioned.

Do note that this is not a bug, so please, do not post here any  questions for the solutions of your problems.