Mysql to mariadb upgrade failed. please help!

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
13,529
Location
Maastricht
I tried to upgrade mysql 5.7 to mariadb 10.x via the procedure I found on the forums.

I made copy of /var/lib/mysql.
Then in options.conf changed to mariadb.
Then ./build update and ./build mariaddb but now my server is down because I got failures.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")
FATAL ERROR: Upgrade failed
Click to expand...

I tried rebuilding mysql, same issue.

I don't have a clue what to do.

Anybody?
 
Richard G said:
I tried to upgrade mysql 5.7 to mariadb 10.x via the procedure I found on the forums.

I made copy of /var/lib/mysql.
Then in options.conf changed to mariadb.
Then ./build update and ./build mariaddb but now my server is down because I got failures.



I tried rebuilding mysql, same issue.

I don't have a clue what to do.

Anybody?
Click to expand...

I can give more info on the issue not the exact solution ... in the 5.7 mysql database there is no password column, default tables for the mysql db are innodb and mariadb is not starting the innodb (in my case when i tried that) for a reason I do not have time to debug fully (there is more info in error log in mysql folder) but usual workaround so next I did a clean install of Maria DB and import couple of bases manually and some users


If this is production you can go back to where you were by installing back mysql 5.7 and putting old folder back then plan for tomorrow if some one has better solution
 
Last edited:
Thank you for your answer.

I don't know how to do a clean install of Maria DB. But I did try to go back to mysql 5.7. Then it says that won't work.
I have to install 5.5 first and then upgrade to 5.7.

Aargh I can slap myself in the head for upgrading to mysql 5.7. I had 2 windows open and changed the wrong one. :(
I presume when having things back there is no way to downgrade from 5.7 to 5.6?
 
Yep, it's some innodb issue:
190905 0:48:58 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: No valid checkpoint found.
InnoDB: A downgrade from MariaDB 10.2.2 or later is not supported.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html
190905 0:48:58 [ERROR] Plugin 'InnoDB' init function returned error.
190905 0:48:58 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190905 0:48:58 [Note] Plugin 'FEEDBACK' is disabled.
190905 0:48:58 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
190905 0:48:58 [ERROR] Unknown/unsupported storage engine: InnoDB
Click to expand...

Might try to best return to mysql 5.5 and then upgrade.
 
that has a chance to work if you have the original columns for your mysql tables

I had limited time to play last month with dabases main differences are in mysql table columns for what I have as brief time to observer differences

I actually used cloudlinux mysql governor to switch to different bases versions there I can just rename mysql folder and tell the governor to change that makes clean install then I import the root new pass for the root user https://help.directadmin.com/item.php?id=45


It should work the same way with empty mysql forder with da installer (I'm not 100% sure) ... then you can try to import what is needed from a dump

I hope that I'm helpfull
 
Mysql won't even start. I just tried to do a fresh 5.5 install but that won't work either.

Do you want to delete it? (y is recommended)? (y,n) : y
mv: cannot move `/var/lib/mysql' to `/var/lib/mysql.backup/mysql': Directory not empty
Installing MySQL
error: File not found by glob: /usr/local/directadmin/scripts/packages/MySQL-server*.rpm
error: File not found by glob: /usr/local/directadmin/scripts/packages/MySQL-client*.rpm
error: File not found by glob: /usr/local/directadmin/scripts/packages/MySQL-devel*.rpm
ls: cannot access /var/lib/mysql/mysql/: No such file or directory
Data needs to be created in /var/lib/mysql/mysql ...
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
/usr/sbin/mysqld: /usr/local/lib/libz.so.1: no version information available (required by /usr/sbin/mysqld)
190905 1:54:26 [Warning] 'explicit-defaults-for-timestamp' is MySQL 5.6 compatible option. To be implemented in later versions.
190905 1:54:26 [Note] /usr/sbin/mysqld (mysqld 10.0.38-MariaDB) starting as process 5229 ...
Click to expand...

You're helpfull indeed. I'm at least glad you give me some hints to fixing this.

Best way is maybe to do a fresh install of mysql, but seems not to work.
 
Yes that is the error... just rename mysql folder install the version you want and add root password and da_admin user

that is the fastest to bring it up then it is import of user dabases if there is such and users if there is such
 
I would like to do that, but it does not work.
It every time wants to install mariadb instead of mysql.
That is what the script is doing.

I just deleted /var/lib/mysql, also in /etc the mysql files, then in /usr/local/directadmin/custombuild the mysql folder and still it wants to install mariadb.
Never experienced this before.
 
I actually do not play a lot with native direct admin as I installed mostly CloudLinux servers and there db came from mysql governor

but do you follow the excat procedure (i hate old docs but that has to be still correct) with remove rpms an seting maria and building maria as some times peopele will type build mysql by default even the docs say build mariadb

https://help.directadmin.com/item.php?id=676
 
Richard G said:
I tried to upgrade mysql 5.7 to mariadb 10.x via the procedure I found on the forums.
Click to expand...

Now it depends what "mariadb 10.x" is.

The MariaDB Knowledge Base states the following:

MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4 function as limited drop-in replacements for MySQL 5.7, as far as InnoDB is concerned. However, the implementation differences continue to grow in each new MariaDB version.

MariaDB 10.0 and MariaDB 10.1 function as limited drop-in replacements for MySQL 5.6, as far as InnoDB is concerned. However, there are some implementation differences in some features.

MariaDB 5.5 functions as a drop-in replacement for MySQL 5.5.
Click to expand...

So you was with MySQL 5.7. That means that you must directly upgrade to MariaDB 10.2 or it will not work.

I guess you did follow a guide for different MySQL version - probably one from the good old days when it was easy with MySQL 5.5 <-> MariaDB 5.5 being fully compatible. For example if somebody asks "how do I upgrade MySQL 5.5 to MariaDB 10.4" today, we will tell him "upgrade to MariaDB 5.5, then 10.0, then go straight to 10.4 or follow the path 10.1->10.2->10.3->10.4 if you think it will be safer). It will be a whole different story if the guy asks "how to I upgrade MySQL 5.6 to MariaDB 10.4" or, like in your case "MySQL 5.7 to MariaDB 10.4".
 
No I found one on the forum which would have worked.
Can't see it that fast now, but it was only 4 steps.

But even on a very new server, it's a harsh work to get back from Mariadb to mysql 5.5. It works but has password issues for some reason.
I think it might be that we have to wait for ticket support.
 
Nevermind my previous comment (I saw from your logs that you are using 10.2). I think it may be related to your my.cnf file. Check it out. On one place it was written to add or remove "local-inline=0" to/from it. Other thing may be the log files - check out for ib_logfile0 and ib_logfile1 files in the mysql folder and remove them.
 
Last edited:
Here is an example /etc/my.cnf file from my server which is running on MariaDB. Try it out:

Code: 
[mysqld]
local-infile=0
innodb_file_per_table
max_connections = 512
max_user_connections = 100
wait_timeout = 30
interactive_timeout = 30
key_buffer_size = 2048M
max_allowed_packet = 64M
table_open_cache = 15288
table_definition_cache = 15288
open_files_limit = 24576
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size  = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
#query_cache_size = 80M
#query_cache_limit = 256K
#query_cache_min_res_unit = 2k
#query_cache_type = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow-queries.log
long_query_time = 10
max_heap_table_size = 512M
tmp_table_size = 512M
innodb_buffer_pool_size = 4096M
skip-name-resolve
innodb_log_file_size = 512M
innodb_buffer_pool_instances = 4
sql_mode = "NO_AUTO_CREATE_USER"

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
After further reading around the net - upgrading to 10.2 and deleting the ib_logfile0 and ib_logfile1 files should do the trick. Most people point them to be the issue during upgrades or migrations.
 
Nope, it did build mariadb 10.2 now but...
mysql.engine_cost
Error : Table 'mysql.engine_cost' doesn't exist in engine
status : Operation failed
mysql.event OK
mysql.func OK
mysql.gtid_executed
Error : Table 'mysql.gtid_executed' doesn't exist in engine
status : Operation failed
Click to expand...
and loads more.
 
That's the output of the mysql_upgrade utility. That means that MariaDB is installed but it can't convert the databases. This probably includes the system dbs and that's why everything is dead.

The only thing that I would try in such case is to purge (delete) the data directory and install fresh system tables. Check out:

https://mariadb.com/kb/en/library/installing-system-tables-mysql_install_db/

Or maybe just remove the datadir and rebuild via Custombuild (it should do it for you).
 
I think the best thing is to remove all mariadb, install mysql 5.7 again and put the backup of /var/lib/mysql back and then things should work again correct?

Only thing is getting the mysql 5.7 back. Have to start with 5.5.
But did not succeed a couple of times so have to try again or wait for ticket support. But the most websites will be offline until... I don't know when they can fix it.
 
I don't think I wrote it properly in the last comment - by deleting the "data directory", I mean the /home/mysql one. That's where DirectAdmin stores all databases. So my suggestion was to delete them, then install MariaDB again.
 
Richard G said:
Only thing is getting the mysql 5.7 back. Have to start with 5.5.
Click to expand...

Not going to work to get back to 5.5 if the datadir is with 5.7. The mysql_upgrade utility is working only forward - it can't convert backwards.

It won't work without removing the data directory. With DA it is /home/mysql

But why going back to 5.5? You should be able to revert back to 5.7 directly!
 
You must log in or register to reply here.
Back
Top