About two weeks ago the power went out at the office. This power outage caused some issues with a MySQL database we use for local development. I tried for a few days to resolve the issue, but I never did. It seems like some of the hard-drive sectors went bad, and I couldn’t fix that remotely.
I then proceeded to install MariaDB in hopes it would write new files to new sectors of the hard drive. The computer is running Ubuntu 18.04 and MariaDB 10.1.44 installed fine. I was up and running!
Luckily, I had made some backups so I could transfer a few databases over to a Raspberry Pi on my home network. I tried restoring one of the database and got the following error message. I’ve seen it before and it is annoying. There is a post on Laravel News on how to fix it from a Laravel perspective. However, I didn’t want to change the code, I wanted the database to work.
Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
After spending several hours trying numerous variations of suggestions from multiple sites I decided to upgrade MariaDB. It turns out MariaDB v10.2.* doesn’t have the same key length issues as the previous versions. I ended up upgrading to version 10.4.
sudo apt-get install software-properties-common sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc' sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.4/ubuntu bionic main' sudo apt update sudo apt install mariadb-server
Source: Setting up MariaDB Repositories