Experienced “Unknown collation: utf8mb4_0900_ai_ci” when i was trying to import a database on a new server. Didn’t realize that the dump was created on a server with MySQL8 and i was trying to import it in MariaDB-10.5.
FYI – The “ai” in the collation name stands for “accent insensitive” and the “ci” stands for “case insensitive.
One of the solutions i stumbled upon, recommended i replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“ and then re-run the import, but this didn’t work for me. After some more searching,
Upon some more searching, i found out this very useful answer:
In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB.
Don’t use “_general_ci”, as it does not correctly sort or compare according to any version of Unicode and have been superseded decades ago.
When MySQL introduced utf8mb4_0900_ai_ci based on comparison and sorting rules in Unicode 9.0, MariaDB chose not to follow at the time. The new rules did not change much as Unicode’s sorting and comparison rules have been pretty stable for several generations of Unicode now. Choosing the “unicode_520_ci” rules will in almost all situations have the same result and this collation can be used in both MySQL and MariaDB.
MariaDB is currently introducing collation rules from Unicode 14.0 – called simply uca1400_as_ci (you don’t need the “utf8mb4_” part anymore). You can upgrade to these if you have a new version of MariaDB.
Make sure you use “utf8mb4” as the character encoding (and not “utf8mb3” or “utf8”, which represent the older flawed/deprecated implementation of UTF8).
So i ended up doing just one sed replace:
sed -i ‘s/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g’ backup.sql
This one was a little tricky. The same system where the junior admin made a little mess of the MariaDB server, there was this long string of errors about “InnoDB: Ignoring tablespace for because it could not be opened.” Apparently, somehow the db got deleted manually from the filesystem and we got left with a zillion of these:
2023-08-22 19:04:31 0 [Warning] InnoDB: Ignoring tablespace for `foo`.`core_layout_link` because it could not be opened.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./foo/core_layout_update.ibd’ OS error: 71
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Could not find a valid tablespace file for “foo`.`core_layout_update“. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2023-08-22 19:04:31 0 [Warning] InnoDB: Ignoring tablespace for `foo`.`core_layout_update` because it could not be opened.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./foo/core_resource.ibd’ OS error: 71
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Could not find a valid tablespace file for “foo`.`core_resource“. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2023-08-22 19:04:31 0 [Warning] InnoDB: Ignoring tablespace for `foo`.`core_resource` because it could not be opened.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘./foo/paypal_ipn_log.ibd’ OS error: 71
2023-08-22 19:04:31 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-08-22 19:04:31 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-08-22 19:04:31 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-08-22 19:04:31 0 [ERROR] InnoDB: Could not find a valid tablespace file for “foo`.`paypal_ipn_log“. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
Now, “foo” being the db name, apparently i need to recreate the file structure in /var/lib/mysql and then i can go ahead and restart the sql server and delete the db and everything should go back to normal.
Because they were too many of these, i had to do a little bash wizardry to automate the process and make it a little faster:
cat mysql.err |grep “Could not find a valid tablespace file” |cut -d ‘`’ -f 5 >> list
This would get me a list of the tables, while using ‘`’ as the delimiter for the field i was trying to find. i just cycled between the fields numbers before i got to the right one
So we go to the MySQL folder and re-create the foo db folder (where foo is the db name):
cd /var/lib/mysql
mkdir foo
Then i made this script, that would create the required files:
cd foo
nano x.sh
#!/bin/bash -x
list=$(cat ./list)
# Loopty loop
for item in $list; do
touch $item.frm
touch $item.ibd
done
chmod 700 x.sh
./x.sh
Now we have to adjust permissions on the db folder and the files inside:
cd /var/lib/mysql
chown -R mysql:mysql foo
restart mysql and go ahead and delete the foo db and the errors should be gone.
Apparently somehow they managed to start 2 instances of MariaDB that ran concurrently and they were each locking the files for another and this error kept popping in the log: “Can’t lock aria control file ‘/var/lib/mysql/aria_log_control”
Very surprised to see this one, but appeared on a server where a junior admin made a mess of a database, so i was called in to help.
In order to fix, i had to stop the monitoring scripts, and the automatic SQL restart, then i checked for all MySQL running processes, killed them, and started the db properly, while verifying in the logs to see if anything got broken due to the abrupt interruption.
A good practice after this is to run a repair on all databases, to make sure everything is in order
