When I upload a dump made on 5.7.26-log MySQL Community Server to 10.1.38-MariaDB-0 + deb9u1 Debian 9.8 I get I get
#mysql -uroot db_zona<db_zona_dump.sql
ERROR 1709(HY000) at line 515: Index column size too large.The maximum column size is 767 bytes.


innodb_large_prefix is ​​enabled on the target machine:

#mysql -u root -e"show variables" | grep innodb_large_prefix
innodb_large_prefix ON


How to win?

1 Answers 1

innodb_large_prefix try adding to config
  • On the machine I'm trying to fill in:
    #mysql -u root -e"show variables" | grep innodb_large_prefix
    innodb_large_prefix ON
    – Monty84 Jul 18 '19 at 15:25
  • Monty84, then try this:
    innodb_file_format=Barracuda
    innodb_large_prefix=1
    innodb_file_per_table=ON
    – Eager Elk Jul 18 '19 at 15:27
  • Eager Elk, I found a problem, I now have innodb_file_format Antelope.If I change it to Barracuda, will the bases that already spin on the target machine break? – Monty84 Jul 18 '19 at 15:38
  • Monty84, should not, but make a backup – Eager Elk Jul 18 '19 at 15:41
  • Monty84, and in the dump, how are the tables described? An example in the studio – Eager Elk Jul 18 '19 at 15:58
  • Eager Elk, registered like this:

    innodb_file_format=Barracuda
    innodb_file_format_max=Barracuda
    innodb_large_prefix=1
    innodb_file_per_table=ON


    After a restart at the Gulf the same error.

    Here are the values ​​of variables in runtime:
    innodb_file_format Barracuda
    innodb_file_format_max Barracuda
    innodb_large_prefix ON
    innodb_file_per_table ON


    I do not understand what is wrong.
    – Monty84 Jul 18 '19 at 16:00
  • Eager Elk, this is where the dump bay stumbles:

    CREATE TABLE `users`(
      `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT'(DC2Type: uuid)',
      `login` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `password` varchar(511) COLLATE utf8mb4_unicode_ci NOT NULL,
      `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `second_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `phone` varchar(127) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `api_token` varchar(511) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `vk_id` varchar(511) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `ok_id` varchar(511) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY(`id`),
      UNIQUE KEY `UNIQ_1483A5E9AA08CB10`(` login`),
      UNIQUE KEY `UNIQ_1483A5E9E7927C74`(` email`),
      UNIQUE KEY `UNIQ_1483A5E9444F97DD`(` phone`),
      UNIQUE KEY `UNIQ_1483A5E97BA2F5EB`(` api_token`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    /*! 40101 SET [email protected] saved_cs_client * /;
    – Monty84 Jul 18 '19 at 16:07
  • Monty84, you can try to add to the tables
    ROW_FORMAT=DYNAMIC
    – Eager Elk Jul 18 '19 at 16:10
  • Eager Elk, it is planned to replicate data to this database from another server(from where this database was merged) - won't it swear at different table formats? – Monty84 Jul 18 '19 at 16:13
  • Monty84, I don’t know about this, but you can always change the format via ALTER TABLE – Eager Elk Jul 18 '19 at 16:30
  • Eager Elk, yes, it turned out to fill in and yes, it replicates.Thanks for the help! – Monty84 Jul 18 '19 at 17:17