Remove Sys Database after upgrade to from MySQL 5.7 to MariaDB 10.3?

AM2015

Active Member
Jan 1, 2015
34
4
58
cPanel Access Level
Root Administrator
I just ran an upgrade from MySQL 5.7 to MariaDB 10.3 via WHM v. 88.09 -- and everything went smoothly. Very quick & I've checked all database-driven sites on the server and all seem to be functioning as expected.

However, I am puzzled by this:

During the course of the upgrade, I was given the message:

"MariaDB does not utilize the sys schema. If you upgrade from MySQL 5.7 to MariaDB, you must manually remove the sys database, because it can cause unnecessary errors during certain check table calls."

I assumed that means I am supposed to get rid of the "sys" database -- but when I go to phpMyAdmin and click the link to "Databases" -- the little box to the left of the "sys" database is grayed out -- so I can' simply click the box and drop the database.

So my questions are this:

1. Does "must" mean that it is necessary for me to remove this database? (Or does it just mean that removal is optional, but if I want to remove it, I will have to do it manually)?

2. And if I do need to remove the database, how should I do it? Is that something that can be done via phpMyAdmin, or would I need to use a command-line operation? Or would it work to simply remove the "sys" file from the /var/lib/mysql/ directory?

I don't want to mess things up -- so assuming that I need to take this extra step, I'd like to know the safest and easiest way to do it.
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,296
1,252
313
Houston
1. Does "must" mean that it is necessary for me to remove this database? (Or does it just mean that removal is optional, but if I want to remove it, I will have to do it manually)?
This does indeed mean necessary.

2. And if I do need to remove the database, how should I do it? Is that something that can be done via phpMyAdmin, or would I need to use a command-line operation? Or would it work to simply remove the "sys" file from the /var/lib/mysql/ directory?
You should be able to manage it through PHPMyAdmin in WHM but it's pretty simple to drop the DB - DROP DATABASE
 

AM2015

Active Member
Jan 1, 2015
34
4
58
cPanel Access Level
Root Administrator
Thank you.

You should be able to manage it through PHPMyAdmin in WHM
As I noted in my original post, the select checkbox for the sys database was grayed out in phpMyAdmin -- so if there was a way to drop the database there, I wasn't able to figure it out.

I was able to drop the database as a command line operation, however- so all is well -- though I did not find the link you provided helpful. (But I was able to find an appropriate tutorial page via Google).
 

andrew.n

Well-Known Member
Jun 9, 2020
328
71
28
EU
cPanel Access Level
Root Administrator
I just came across this recently and I had the same problem. From WHM using PHPMyadmin it was not allowing me to drop the DB but via CLI.
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,296
1,252
313
Houston
As I noted in my original post, the select checkbox for the sys database was grayed out in phpMyAdmin -- so if there was a way to drop the database there, I wasn't able to figure it out.
Yes I did see that but as *I* noted previously:

You should be able to manage it through PHPMyAdmin in WHM but it's pretty simple to drop the DB - DROP DATABASE
The link I provided shows you how to drop a database through the CLI so if you couldn't do so through PHPMyAdmin you have the instructions on how to do so via the MySQL CLI
 

AM2015

Active Member
Jan 1, 2015
34
4
58
cPanel Access Level
Root Administrator
That is probably true, but I don't have enough experience with working through the CLI to make sense of those instructions. I needed to Google to find simpler, step by step instructions.

But no problem, I appreciate the help - the main concern I had was that I was hesitant to remove the table without being absolutely certain that I wasn't going to break anything on my server. It was easy enough to find instructions appropriate for my server OS, geared to my less sophisticated level of understanding.
 

Spirogg

Well-Known Member
Feb 21, 2018
77
18
8
chicago
cPanel Access Level
Root Administrator
@cPanelLauren This is all I did to Drop the Database sys that you all are referring too.

you will see like below photo - after upgrading to Mariadb in phpMyAdmin > the DATABASE sys (it will be greyed out)

Capture.PNG




goto phpMyAdmin and select SQL
then type in
Code:
DROP DATABASE sys;
then at the bottom right as shown in the photo click on Go

that's it..


now the database sys is removed (dropped) that is required this must be done after upgrading to Mariadb

1605500356090.png


After you click Go it will remove the sys database like below.

1605501466101.png

then go to Home »Restart Services »SQL Server (MySQL)
and restart the mysql database

Done..

Hope someone else finds this helpful..
"Because the database is greyed out so you have to do it via command in SQL"


**also you might want to remove strict mode in Mariadb

using terminal in WHM or another ssh tool

login as root and type:
Code:
nano /etc/my.cnf
then scroll to the bottom and type in last line :

Code:
sql_mode=
then hold ctrl + x
then press the enter button twice.

this will save your changes and exit from the file my.cnf

restart mysql server.

to double check if your settings are complete you can do so by going to phpMyAdmin and clicking on Variables
in the search field type : sql
then you will see sql mode and nothing to the right. means you did it correctly
see photo below
1605502959430.png


all set.. you now have disabled strict mode in Mariadb

@cPanelLauren sorry for the long writing with photos but someone will find this helpful I am sure of it :)


Thanks
Spiro
 
Last edited:

Nick57

Well-Known Member
Jul 19, 2005
103
0
166
cPanel Access Level
Root Administrator
I did the upgrade from mysql5.7 to mariadb10.2 used the Unattended upgrade all is fine, I did not see anything bad in the log file.
 
Last edited: