0

I am on Ubuntu 20.04, trying to migrate from MySQL to MariaDB 10.5. I have mariadb installed correctly and I am trying to import the dump of all of my dbs in the new mariadb using mysql -u root -p < all_dbs.sql, but just it outputs:

ERROR 1005 (HY000) at line 87: Can't create table mysql.db (errno: 168 "Unknown (generic) error from engine")`

I am fairly new to database administration and I would appreciate some detailed instructions on how to solve this problem.

My Steps
1.) First, I dumped all my dbs into a .sql file mysqldump -u root -p --all-databases > all_dbs.sql
2.) Then, I removed the mysql server from ubuntu sudo apt purge mysql-server
3.) From here, I installed mariadb:
sudo apt update && sudo apt upgrade
sudo apt -y 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] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main'
sudo apt update
sudo apt install mariadb-server mariadb-client # I foolishly answered "no", since it was telling me it was safe to do so...
4.) I tried import my dbdumpfile.sql using mysql -u root -p < all_dbs.sql , but ran into this error 'ERROR 1698 (28000): Access denied for user 'root'@'localhost', so I used these instructions to solve that problem
5.) Which, of course, led to a new problem: Unknown collation: 'utf8mb4_0900_ai_ci' #1902, which I solved with sed -i all_dbs.sql -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g'
6.)And -now- when I run mysql -u root -p < all_dbs.sql, it outputs

ERROR 1005 (HY000) at line 87: Can't create table mysql.db (errno: 168 "Unknown (generic) error from engine")

Any tips? Originally asked here (Did not Receive detailed answer..)

Maestro223
  • 203
  • 2
  • 13
  • Check MariaDB's logs. – Michael Hampton Jan 17 '21 at 06:30
  • Happy to.. have any instructions on how to do that? – Maestro223 Jan 17 '21 at 06:32
  • Read /var/log/mariadb or /var/log/mysql or whatever is configured in config? Serverfault expects you have a *basic* knowledge of your system, that includes how to find and read logs, especially with so common and widely used software. – Nikita Kipriyanov Jan 17 '21 at 06:43
  • @NikitaKipriyanov There are no logs at //var/log/mysql ... completely new install. – Maestro223 Jan 17 '21 at 10:57
  • So check /var/log/mariadb then. – Michael Hampton Jan 17 '21 at 14:32
  • Obviously, I tried to do that, but /var/log/mariadb doesn't exists. by the way, I've run a LAMP machine on my laptop for 3 plus years, so I not mentally handicap because my individual knowledge of mysql isn't as high someone else's expectations. Between phpmyadmin and specific instructions on sites like this I do pretty well. My current problem is because I am trying to do some thing that, admittedly, usually requires a more indepth knowledge of mysql. – Maestro223 Jan 17 '21 at 15:50
  • I have done my homework... simply put, no one has published a definitive guide for migrating from LAMP (mysql) server in Ubuntu 20.04 to a LAMP (mariadb) servier in Ubuntu 20.04. Maybe, someone wants to prove their intelligence and get some more web traffic by publishing such a guide. – Maestro223 Jan 17 '21 at 15:54
  • I swear no special deep knowledge of MySQL/MariaDB/Percona/etc. is needed to do migration, it's just a couple of mysqldumps/mysql invocations. I work with MySQL for around 20 years and can't remember seeing error message like you encountered. You're doing something really strange, or there is something very strange in your database dump. That's why we asked you for logs, to have extended information from database what's going on and what it doesn't like. – Nikita Kipriyanov Jan 17 '21 at 20:02
  • I originally placed the same question on stackoverflow, but did not receive a detailed answer. As you can, I have done nothing strange. Answers / replies with specific steps, instead of opinions are helpful to newbies learning their way around. – Maestro223 Jan 17 '21 at 23:06
  • @NikitaKipriyanov I do respect your 20 yrs of experience, but I did notice that two other mysql experts noticed that my problem had no relation to mysql logs. However, I do owe you a big thx. Due to your opinionated feedback, I was able to brainstorm a better question, that received a detailed answer that will help me and potentially 1,000's of other users facing the same problem.. I recommend everyone checks it out, you may pick some useful tips https://askubuntu.com/questions/1308941/migrating-ubuntu-20-04-lamp-from-mysql-to-mariadb/1308947?noredirect=1#comment2227115_1308947 – Maestro223 Jan 19 '21 at 02:20

2 Answers2

3

Do not try to import these databases: mysql, information_schema, performance_schema.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Thanks for the reply...and simply answering the question. The problem is that my .sql dump file already has these contained within it. Can you provide detailed import instructions. Thx – Maestro223 Jan 17 '21 at 23:10
  • Are you willing to edit the dump file and remove the chunk(s) that reference those databases? dba.stackexchange.com would be a better site for this type of question. – Rick James Jan 18 '21 at 00:30
  • Thx for following up your answer Rick, the details help. Just so happens, I believe in redundant backups and set-up a shell script to make daily backups of all of my dbs. So in theory, what you are saying is that I should be able to individually import all of my working wordpress / drupal / phpmyadmin dbs..and just forget about my aggregated.sql file... – Maestro223 Jan 18 '21 at 08:53
  • @yupthatguy - There are different situations -- If you lose everything, reloading `mysql` _may_ be viable for relaoding the `GRANTs`. Reloading a single WP user's stuff is a matter of reloading his one database. – Rick James Jan 18 '21 at 18:34
  • I appreciate your follow-up though admittedly above my lowly head.. :-) in any case persistence pays off. Thx to the "opinion filled" comments below my original question, I was able to brain-storm a better question that got me 100% the exact answer that I have been looking for all over the web... I advise you check it out... my provide some useful tips... https://askubuntu.com/questions/1308941/migrating-ubuntu-20-04-lamp-from-mysql-to-mariadb/1308947?noredirect=1#comment2227115_1308947 – Maestro223 Jan 19 '21 at 02:11
  • @yupthatguy - That's a good one. Thanks. – Rick James Jan 19 '21 at 02:28
0

I think it's better to restore only the required databases one by one avoiding the MySQL native databases.

You can use the following shell script to quickly convert the DBs to avoid collation errors.

#!/bin/sh
echo "Converting database..."
fileName=$1
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' $fileName 
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' $fileName

You can run ./replace.sh dbname.sql

nrnw
  • 101
  • 3