This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

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?

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.