Character conversion fails getting columns or restult sets
Configuration: Maria DB 10.2.36 on a OpenSuSE Leap 15.1 server
Connecting to the server succeeds but queries of data (result sets) and column names fail. The failures seem to be eminating from the server side of the ODBC connection. The first error from requesting columns returns:
('HY000', '[HY000] [ma-3.0.2][10.2.36-MariaDB]Ansi to Unicode conversion error occurred (95) (SQLDescribeCol)')
And table queries of data from the table returns: ('22018', '[22018] [ma-3.0.2][10.2.36-MariaDB]Invalid character value for cast specification (0) (SQLExecDirectW)')
My source code:
connStr = ("Driver=libmaodbc;" "Server=localhost;" "Database=wdmudbt;" "uid=odbctest;" "password=sekr3t;") try: conn = pyodbc.connect(connStr) except pyodbc.Error as e: print("unable to connect to db") print(e) exit(1) // Requesting the table columns for schema_migrations try: cursor = conn.cursor() cursor = cursor.columns(table='schema_migrations') print(cursor.fetchall()) except pyodbc.Error as e: print("***** get columns for schema_migs failed: ", e) // This returns 'HY000', '[HY000] [ma-3.0.2][10.2.36-MariaDB]Ansi to Unicode conversion error occurred (95) (SQLDescribeCol)' try: print("Query: ",query) cursor = conn.cursor() cursor.execute(query) for row in cursor: (version) = row print(version) except pyodbc.Error as e: print("unable to query db") print(e) // This second cursor.execute() returns ('22018', '[22018] [ma-3.0.2][10.2.36-MariaDB]Invalid character value for cast specification (0) (SQLExecDirectW)')
I've tried several of the connection.setencoding() and connection.setdecoding() suggestions, tried setting charset= in the connection parameters along with trying ansi=true and other nonstandard settings. My server reports the following regarding character sets:
MariaDB [(none)]> show variables like '%character%'; +--------------------------+------------------------------+ | Variable_name | Value | +--------------------------+------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mariadb/charsets/ | +--------------------------+------------------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]>
and the table itself:
MariaDB [(none)]> show create table wdmudbt.schema_migrations; +-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | schema_migrations | CREATE TABLE `schema_migrations` ( `version` varchar(255) NOT NULL, UNIQUE KEY `unique_schema_migrations` (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
The problem only occurs with Python, my Rails and PHP applications using this MariaDB server instance have no problem with data encoding.
What config setting controls how columns and data are translated?