Troubleshooting

Collation Issues

My database shows utf8mb3_unicode_ci instead of utf8mb4_unicode_ci

This can occur if:

Your database was created before the collation defaults were updated
Custom MariaDB settings were specified that override the defaults

Resolution

New databases created after the collation defaults update automatically use utf8mb4_unicode_ci.

For existing databases, you can migrate to utf8mb4:

-- Check current collation
SELECT
  SCHEMA_NAME,
  DEFAULT_COLLATION_NAME
FROM
  INFORMATION_SCHEMA.SCHEMATA
WHERE
  SCHEMA_NAME = 'your_database_name';

-- Migrate to utf8mb4
ALTER DATABASE your_database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- For each table in the database
ALTER TABLE your_table_name
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Migration can take significant time and cause downtime. Test in non-production first. Consider using pt-online-schema-change for large tables.

Connections are suddenly closed

If your connections are suddenly closed by the server and the ProxySQL logs:

ProxySQL error

MySQL_Session.cpp:4028:handler___status_NONE_or_default(): [ERROR] Unexpected packet from client xx.xx.xx.xx . Session_status: 6 , client_status: 0 Disconnecting it
MySQL_Session.cpp:318:kill_query_thread(): [WARNING] KILL CONNECTION 156092 on mariadb-service.svc:3306

This might be because ProxySQL does not support the connection parameter useBatchMultiSend set to true. This is a known issue of ProxySQL.

MariaDB Server advertises version 8.0

If you use a High Availability setup, a ProxySQL is provisioned in front of the MariaDB instances. The ProxySQL advertises itself as MySQL with an older version, e.g. 8.0 (ProxySQL). The upstream MariaDB instances are in the version you specified, but ProxySQL reports another value. It is currently not possible to change this. If you have logic that depends on the MySQL/MariaDB version, the best fix is to disable the version detection and hard code your specified MariaDB version.

SQL_MODE contains ONLY_FULL_GROUP_BY

To change the SQL_MODE in VSHNMariaDB, you can do the following:

set SQL_MODE

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNMariaDB
metadata:
  name: mariadb-development
  namespace: my-namespace
spec:
  parameters:
    service:
      version: "11.8"
      mariadbSettings: |
        sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION