MariaDB Import maximum key length

Question

We are currently moving the DB to another Server but we got a problem. Our Database is quite big and when we want to Import it to our new MariaDB Server with PhpMyAdmin. We got an Error Code and don´t know how to solve it. Sorry that the Outcome is German, but it basically says that the key is too long The maximum key length is 767.

MariaDB Version: MariaDB-10.1.48Server Version: Ubuntu 18.04

Server Version: Ubuntu 18.04

If anyone could help with this Problem we would really appreciate an answer!

Example:

CREATE TABLE IF NOT EXISTS `accounts` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `money` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Outcome:

CREATE TABLE IF NOT EXISTS `accounts` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `money` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
MySQL meldet: Dokumentation

#1071 - Schlüssel ist zu lang. Die maximale Schlüssellänge beträgt 767

Answer 1

As far as I know, you are defining name VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL and setting it as Primary Key. It is embedded in utf8mb4_unicode_ciyou have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191.

Either you use VARCHAR(191) or not use it as your primary key

Answer 2

use

SET GLOBAL innodb_default_row_format='dynamic';

in your script or modify this parameter in my.ini

innodb-default-row-format = dynamic

with this change you can create index over columns up to 3072 bytes.