Question 63760 in PhpMyAdmin Question posted in
The official documentation can be found here.

MariaDB Import maximum key length – Phpmyadmin

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search