0

Old server is MariaDB 10.6 New server is MariaDB 10.3

I mysqldump the old DB which is still the master copy and functioning.

I made sure the new server DB is set to InnoDB (like the old one) using this in the cnf file:

[mysqld]
innodb_buffer_pool_size=1G

Then with a fresh new maria DB (10.3 mind you), I do:

  1. Log in and create database newdatabase
  2. sudo mysql -u root -p newdatabase < media_data_master.dump

The result is:

ERROR 1071 (42000) at line 266: Specified key was too long; max key length is 1000 bytes

The stackoverflow post I linked above talks about VARCHARs being all 255 and that causing the issue. Big problem however is that I don't know how I would ever fix that. The people who use the database are not even in my department, I'm just trying to move the thing to a new server.

I had also tried create database my_db character set utf8 collate utf8_bin; before I did the import but same problem.

If the old DB is still functional AND our replica appears to still be replicating perfectly, is there something I'm missing here that will allow me to import the dump and avoid this error?

Improve this question
2
  • For what reason do you not want to install the same version of MariaDB?
    – mustaccio
    Sep 14, 2022 at 12:08
  • @mustaccio The problem was that we couldn't get 10.6 installed from the particular distro repositories. Its a stupid problem
    – postuple
    Sep 14, 2022 at 14:29

1 Answer 1

Reset to default
1

That's a significant downgrade. This problem may not be the only one.

Anyway, see the following for 5 ways of addressing what I susupect it the problem: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

On the other hand, "1000" is a better clue. You are using MyISAM. Don't. Instead, move to ENGINE=InnoDB.

For further discussion, please provide SHOW CREATE TABLE for any tables with that problem.

Search your my.cnf (on both servers) for "engine" to see if it is being forced.

Improve this answer
6
  • Great thanks. I will figure out how to use innodb because I thought I was already. ON the original DB, the only "engine" string I found was "#default_storage_engine=InnoDB". Same with the new one
    – postuple
    Sep 12, 2022 at 20:42
  • I tried again from empty mysql directory, with "default_storage_engine=InnoDB" under [mysqld] and [mariadb] in the /etc/my.cnf.d/mariadb-server.cnf but its still 1000 bytes. Is the "1000 bytes" a definite and unmistakable indicator of myisam?
    – postuple
    Sep 12, 2022 at 20:51
  • @postuple - 1000 shows up twice in that blog, both probably limited to MyISAM. I don't know of such in InnoDB. 1000 columns per table? If you can figure out which table is involved, please provide SHOW CREATE TABLE.
    – Rick James
    Sep 13, 2022 at 5:19
  • :D how about this table with a varchar(4096)?? Should I tell the users that thats just not acceptable or is that a normal thing to do with this kind of database? Thanks for the command, I found the issue right away
    – postuple
    Sep 13, 2022 at 20:23
  • @postuple - VARCHAR(marge max) -- If the data allows, lower the max. Else remove the INDEX. Large max -- may as well be TEXT, which can't be indexed either.
    – Rick James
    Sep 14, 2022 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.