Bug #112032 Using C program to execute query and report errors
Submitted: 10 Aug 12:57 Modified: 15 Aug 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

[10 Aug 12:57] xiang wang
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
[10 Aug 12:59] xiang wang
The files used for the above tests

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

[10 Aug 13:00] xiang wang
The files used for the above tests

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

[10 Aug 13:24] xiang wang
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
[10 Aug 13:53] MySQL Verification Team
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.
[14 Aug 10:20] Demon Chen
It seems that internal_tmp_disk_storage_engine affect the behavior.
[15 Aug 6:16] Demon Chen
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?
[15 Aug 6:34] xiang wang
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.
[16 Aug 11:53] MySQL Verification Team
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.