MySQL Bugs: #111921: Reusing prepared statement for different data types, different outcomes are obse

Bug #111921	Reusing prepared statement for different data types, different outcomes are obse
Submitted:	31 Jul 2023 7:57	Modified:	1 Aug 2023 14:12
Reporter:	Rahim Kanji	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When reusing prepared statement (INSERT) in MySQL, different outcomes are observed depending on the order of execution and the data type used. Specifically, if the prepared statement is executed with TIMESTAMP or DATE data and then executed with a STRING containing a larger date-time value "2021-01-01 12:00:00", MySQL server writes the truncated data "2021" to the table without reporting any errors. However, when the order is reversed, and the prepared statement is first executed with the STRING containing the larger data, MySQL server reports an 1265 error stating "Error Data truncated for column '*' at row 1" and disregards the data.

How to repeat:
Scenario 1:
* Create a table with a column of data type "YEAR".
* Create a prepared statement to insert TIMESTAMP or DATE data, such as "2021-01-01", into the "YEAR" column.
* Reuse the same prepared statement to execute a STRING value containing "2021-01-01 12:00:00".
* No error will be raised, and the truncated data "2021" will be written to the column.

Scenario 2:
* Create a table with a column of data type "YEAR".
* Create a prepared statement to insert a STRING value containing "2021-01-01 12:00:00" into the "YEAR" column.
* Error 1265, which reports data truncation, and the insertion process is disregarded.

Suggested fix:
Creating a fresh prepared statement for each distinct data type can be a viable approach to circumvent this situation. However, in cases where a proxy, such as ProxySQL, is employed and relies on cached prepared statements, this solution may not be effective.

Hi Mr. Kanji,

Thank you for your bug report.

However, it is not a bug. This is expected behaviour.

Data types DATETIME and YEAR are not compatible data types and you can not enter a DATETIME value in the YEAR data type, nor the other way around.

This is fully described in our Reference Manual.

Not a bug.

Thank you for promptly getting back to me.

I understand that these data types are incompatible, but they are utilized in MySQL connector J tests (testsuite.simple.DateTimeTest), resulting in test failures on ProxySQL (due to cached prepared statement).

My primary concern is the inconsistency in error 1265 between the two cases. I think this error should be reported and sql mode (strict mode in this case) should be respected

What are your thoughts on this matter?

Hi Mr. Kanji,

If you read our Reference Manual, you will find that there are functions for extracting YEAR out of the DATETIME data types. The other way around, there no such functions, because year is just part of the entire date.

Our thoughts on this is that you should do application and DML designing with a 100 %  strict data type consistency.

Not a bug.

Updated sample code to reproduce this bug

Attachment: prepare_statement_bug.cpp (text/plain), 4.17 KiB.

Hi Mr. Kanji,

Thank you for your uploaded file.

What you are reporting is a bug in the server, because this is a category that you have chosen in the bug. This is not a bug in any Connector. 

Hence, please send us a test case in the plain SQL. Also, please do not include any changes in the sql_mode that are already obsolete and not recommended for use. This is all explained in our Reference Manual.

Thank you in advance.

Mr. Kanji,

One more significant information.

If the queries do not pass SQL test, they will not pass any C/J application neither.

This is just for your info.

I would like to add my input on this, and clarify the issue explaining it in a different way.

"please send us a test case in the plain SQL"

What seems to be a bug in the server is triggered by the use of prepared statements, therefore a test case in plain SQL makes no sense.
The bug is triggered when using BINARY protocol, not TEXT protocol.

"please do not include any changes in the sql_mode ..."

The change in SQL_MODE is done to be very explicit in what SQL_MODE is being used. I will describe why this is relevant shortly.

"... that are already obsolete and not recommended for use. This is all explained in our Reference Manual."

Why are you saying they are obsolete? The reference manual says they are the default:
From https://dev.mysql.com/doc/refman/8.0/en/faqs-sql-modes.html#faq-mysql-what-default-mode:
The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.
Furthermore, this bug was identified why running Connector/J automated testing, and this is the sql_mode that Connector/J itself sets by default.

When sql_mode is set to STRICT_TRANS_TABLES , Strict Mode is enabled in the **server** (this is why it is a server bug, and not a connector bug).
From https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict :
"For transactional tables, an error occurs for invalid or missing values in a data-change statement"

Therefore, according to the documentation, for invalid value the **server** generates an error.
In general, this seems to be true with prepared statements too.
The attached test case shows how MySQL server rejects invalid values (as expected!) , but also that is possible to bypass the data validation checks and successfully insert invalid data also 

In the test case associated to this bug report, when trying to INSERT the value "2021-01-01 12:00:00" using a bind type MYSQL_TYPE_STRING into a YEAR column, it generates an error: the error is expected because of SQL Strict Mode!!

In the same test case, if the prepared statement tries to INSERT the value "2021-01-01" using a bind type MYSQL_TYPE_DATE into a YEAR column, MySQL server will insert the value 2021 . To me, this is already a bug: SQL Strict Mode should not allow that!
Then, if the **same** prepared statement tries to INSERT the value "2021-01-01 12:00:00" using a bind type MYSQL_TYPE_STRING into a YEAR column, MySQL server doesn't generate any error. This is surely a bug, because as described earlier MySQL Server **does** generate an error if the prepared statement only used bind type MYSQL_TYPE_STRING .

I hope this clarifies why this is a **server** bug.

Thanks

To make more clear how serious is this bug, in the test case please replace "2021" with "0000" .
Despite STRICT_TRANS_TABLES and NO_ZERO_DATE , year 0000 is inserted by MySQL server without any error.

This is what happens:

$ ./prepare_statement_bug 
>> Executing statement with DATE and STRING type ...
execute_statement_as_string() is NOT failing and is incorrect behaviour
>> Done
>> Executing statement with STRING type only ...
File prepare_statement_bug.cpp, line 45, Error: 1265:Data truncated for column 'y' at row 1
execute_statement_as_string() is failing and is correct behaviour
>> Done
$ mysql -u root -proot -h 127.0.0.1 -P3306 -e "SELECT * FROM testDB.testTable"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| y    |
+------+
| 0000 |
| 0000 |
+------+

HI Mr. Cannao,

If you are reporting a bug in server, you can always use SQL statements for the prepared statements, like PREPARE, EXECUTE and similar.

We agree that a strict mode should not allow a DML in the prepared statements that it disallows in the normal statements.

We have checked this with SQL statements and we decided to verify this report.

It affects both 8.0 and 8.1.

Verified.

Thank you!

Actually, this is not a bug.

The chapter on PREPARE statement in the reference manual warns against possible problems when using different parameter types:

Using a different data type for a given parameter or user variable within a prepared statement for executions of the statement subsequent to the first execution causes the statement to be reprepared. This is less efficient; it may also lead to the parameter's (or variable's) actual type to vary, and thus for results to be inconsistent, with subsequent executions of the prepared statement. For these reasons, it is advisable to use the same data type for a given parameter when re-executing a prepared statement.

Notice that the SQL standard only allows very specific parameter types for different derived parameter types. MySQL goes beyond this standard, but not so that we perform a full reprepare for every execution (which would have been silly and prohibit any performance improvement by using prepared statements).

For proxysql, the recommendation could be to use cached prepared statements only when the parameter types are equal.

For the data type YEAR, the implicit conversion semantics may be a bit unclear. For a DATE/DATETIME value sent using the binary protocol, it may be reasonable to pick out the YEAR value and include that. We might do the same with DATE/DATETIME values in a STRING parameter, but the rules for implicit conversion may not detect this.

Notice also that NO_ZERO_DATE does not apply to YEAR columns. Oddly enough, this is only applicable to DATE, DATETIME and TIMESTAMP columns. Notice this sentence in the description of zero values in the chapter Date and Time Data Types:

For temporal types that include a date part (DATE, DATETIME, and TIMESTAMP), use of these values may produce warning or errors.

Hi Roy,

Thank you for the explanation.

"it is advisable to use the same data type for a given parameter when re-executing a prepared statement" : I full agree on this. Although, nothing prevents a client from using different data type, and I believe it is the server's responsibility to perform the right action.

"For a DATE/DATETIME value sent using the binary protocol, it may be reasonable to pick out the YEAR value and include that" : I think it is reasonable.

I think there is still some inconsistency that needs to be addressed, and I believe that the inconsistency is a bug.
For a column type YEAR:
* if a COM_STMT_EXECUTE sends a value using MYSQL_TYPE_STRING : the server replies with an error (to me this is expected, and Connector/J tests expects an error too).
If a COM_STMT_EXECUTE sends a value using MYSQL_TYPE_DATE and later sends a value using MYSQL_TYPE_STRING : no more error from the server.

I think that when MYSQL_TYPE_STRING is used, the behavior of the server should be consistent. Either always an error (as Connector/J and I expect), or always an implicit conversion (if reasonable).
I think the behavior should be consistent.
When "Using a different data type for a given parameter or user variable within a prepared statement for executions of the statement subsequent to the first execution causes the statement to be reprepared" , perhaps data validation checks is lost?

HI All,

This looks more and more like a feature request. Hence, we suggest that this report remains verified, but that it gets the severity of S4 (Feature request).

Hi ALL:

I was led to believe the inconsistent behavior of the server happened after the commit: https://github.com/mysql/mysql-server/commit/24fdf31121a1bfdfe66a06bd508b48efe40d1941.

for my test case:

```

   public static void setMixed(Connection conn) throws SQLException {
    	PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement("INSERT INTO test.testTable VALUES (?)");
            ps.setDate(1, new java.sql.Date(123, 1, 1));
            ps.addBatch();
            ps.setString(1, "2021-02-01 12:00:00");
            ps.addBatch();
            ps.executeBatch();
            
        } finally {
            ps.close();
        }
    }
    
```   

1. MySQL server reports an 1265 error stating "Error Data truncated for column '*' at row 1" before version 8.0.28
2. MySQL server writes the truncated data "2021" to the table without reporting any error after version 8.0.28

Hi,

However, MySQL server reports warnings , which all application programmers should check !!! This is stated in our Reference Manual.

Also, let us repeat, that using a different data type for a given parameter or user variable
within a prepared statement for executions of the statement subsequent
to the first execution causes the statement to be re-prepared. Hence, that is what you should do .....