MySQL Bugs: #115570: Disable to write multibyte char to field of varchar type by MySQL Odbc Drive

Bug #115570	Disable to write multibyte char to field of varchar type by MySQL Odbc Drive
Submitted:	12 Jul 2024 5:14	Modified:	17 Jul 2024 8:20
Reporter:	ou reitetsu	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	from mysql odbc 5.3.11.00	OS:	Windows
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
**Problem Summary**:
When inserting a Unicode string containing multibyte characters using the MySQL ODBC Driver, the following error occurs:

[HY000][MySQL][ODBC 8.0](w) Driver][mysqld-8.0.36] Incorrect string value: 'x82' for column 'your_column' at row 1

**Steps to reproduce**:
1. Execute an INSERT statement containing a Unicode string in a Windows environment with MySQL ODBC Driver version 8.0.33.00.
2. Verify that the above error occurs.

**Expected behavior**:
- I hope that MySQL ODBC Driver will correctly handle multibyte characters and insert them into the database without errors.
- I hope that a bug fix or configuration change will result in normal behavior.

**Environment information**:
- Operating System: Windows 11 Pro
- ODBC Driver Version: MySQL ODBC 8.0.33
- MySQL Server Version: 8.0.36

- Connection string settings (charset, etc.):
DSN=mydsn;UID=user;PWD=password;charset=cp932;

table:
column data type: varchar 
character set:cp932

How to repeat:
Program:
--------------
#include <iostream>

#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <iostream>
#include <tchar.h>
void extract_error(const char *fn, SQLHANDLE handle, SQLSMALLINT type) {
	SQLINTEGER i = 0;
	SQLINTEGER native;
	SQLWCHAR state[7];
	SQLWCHAR text[256];
	SQLSMALLINT len;
	SQLRETURN ret;

	std::cout << "The driver reported the following diagnostics whilst running " << fn << "\n";

	do {
		ret = SQLGetDiagRec(type, handle, ++i, state, &native, text, sizeof(text), &len);
		if (SQL_SUCCEEDED(ret)) {
			std::wcout << state << L":" << i << L":" << native << L":" << text << "\n";
		}
	} while (ret == SQL_SUCCESS);
}
int main() {
	SQLHENV hEnv;
	SQLHDBC hDbc;
	SQLHSTMT hStmt;
	SQLRETURN ret;
	SQLWCHAR outConnStr[1024];
	SQLSMALLINT outConnStrLen;
	const SQLWCHAR* sql = L"INSERT INTO usqlmysqlcscp93201 (char01) VALUES (?)";

	// Allocate environment handle
	SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
	SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

	// Allocate connection handle
	SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

	// Connect to the DSN
	SQLWCHAR connStr[] = L"DSN=WatMySQLOdbc;UID=wat01;PWD=waha01;charset=cp932;";
	ret = SQLDriverConnect(hDbc, NULL, connStr, SQL_NTS, outConnStr, sizeof(outConnStr), &outConnStrLen, SQL_DRIVER_COMPLETE);
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		std::wcout << L"Connected to database." << std::endl;

		// Allocate statement handle
		SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

		// Prepare SQL statement
		SQLPrepare(hStmt, (SQLWCHAR*)sql, SQL_NTS);

		// Bind parameter
		SQLWCHAR* param = (SQLWCHAR*)L"あ";
		//SQLWCHAR* param = (SQLWCHAR*)L"a";
		SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR, 0, 0, param, 0, NULL);

		// Execute statement
		ret = SQLExecute(hStmt);
		if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
			std::wcout << L"Data inserted successfully." << std::endl;
		}
		else {
			std::wcout << L"Error inserting data." << std::endl;
			extract_error("SQLExecute", hStmt, SQL_HANDLE_STMT);
		}

		// Free handles
		SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
	}
	else {
		std::wcout << L"Failed to connect to database." << std::endl;
	}
	// Disconnect and free handles
	SQLDisconnect(hDbc);
	SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
	SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

	return 0;
}
-------------

create table usqlmysqlcscp93201(
 CHAR01 CHAR(10)  DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=cp932

Disenable to write multibyte char to field of varchar type for MySQL ODbc Driver

update title

update title

Hello!

Thank you for the bug report.
Create table statement of "usqlmysqlcscp93201" has column of type char() but You are trying to insert data of type varchar() which is not supported by char(). Please clarify the issue and provide us correct create table statement and data you are trying to insert.

Regards,
Ashwini Patil

Thanks for your mail.
You can create table by the following DLL:
----------------------------
create table usqlmysqlcscp93201(
 CHAR01 VARCHAR(10)  DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=cp932

------------
Thanks

Is MySQL Server 5.x thread-safety?
Is MySQL Server 8.x thread-safety?
Is MySQL Connector/ODBC 5.x thread-safety?
Is MySQL Connector/ODBC 8.x thread-safety?