[Maria-discuss] Error when switching to MariaDB
Hello, First of all, here is what I'm working with: Original machine: MySQL Server 5.7.18-0ubuntu0.16.04.1 Destination machine: MariaDB 10.0.30-MariaDB-0+deb8u2 These two machines have no filesystem or communication with with other. I am moving to MariaDB with about 10 to 15 databases that were created in MySQL and I have had a very odd problem with the destination machine running MariaDB where the applications using these databases were having connection errors with their databases. I looked very carefully at what I had done. I exported the databases from MySQL using: $ mysqldump --all-databases > all.sql and imported the collection on the new machine with $ mysql -u root -p < all.sql When I log in as root, all appears well when I inspect the databases, but all is not well. The applications the use these databases cannot connect. I enter the command line to connect using the SQL user accounts, and guess what, they don't log in with their respective passwords. I can log in on the MySQL server using the same account/password pairs. I think it might be a permissions issue. I log into the MariaDB server as root and flush privileges, just to make sure, and I get a terribly vague error message (with no details dropped off in /var/log/mysql/ either). MariaDB [(none)]> flush privileges; ERROR 1105 (HY000): Unknown error When I reach this point, I perform something akin to these steps: sudo su service stop mysql apt-get purge mariadb-server mariadb-server-10.0 mariabd-comon mariadb-client mariadb-client-10.0 mariadb-client-core-10.0 mariadb-server-core-10.0 rm -rf /etc/mysql rm -rf /var/lib/mysql apt-get install mariadb-server mariadb-client exit and I'm asked for root password, etc and also run mysql_secure_installation with all suggested values. Voila, flush privileges works again on the MariaDB server, at least until I attempt to reinsert the databases. Are these problems somewhat unique? Is there a way to pry additional detail from mariadb, such as launching the server in a debug mode? What scenarios have people seen cause this in the past? Where do I look for detailed symptoms? Timothy D. Legg
On 6/6/2017 3:43 PM, Timothy D. Legg wrote:
Hello,
First of all, here is what I'm working with:
Original machine: MySQL Server 5.7.18-0ubuntu0.16.04.1 Destination machine: MariaDB 10.0.30-MariaDB-0+deb8u2
These two machines have no filesystem or communication with with other.
I am moving to MariaDB with about 10 to 15 databases that were created in MySQL and I have had a very odd problem with the destination machine running MariaDB where the applications using these databases were having connection errors with their databases.
I looked very carefully at what I had done. I exported the databases from MySQL using:
$ mysqldump --all-databases > all.sql
This is your problem. You are overwriting the system tables with values which will not work in MariaDB 10.0.30. MySQL 5.7 permissions are, supposedly (I haven't tested), compatible with MariaDB 10.2, but not 10.0. In that all.sql file, remove any CREATE, DROP or ALTER statements to the mysql system database (which is the permissions system). Alternatively, do not dump the mysql system database and create those permissions by hand before the import using CREATE USER and/or GRANT statements. Brian
I was afraid of that, but I was too tired to experiment along those lines, else I could have screwed up in a spectacular way. I examined the 900+ lines of mysql.sql and found all the ALTER strings were commented in C-style, but removing the CREATEs would have eliminated some of the key purposes of the file's existence. I decided to manually repopulate the user table. I successfully granted the permissions to the respective users (except one that will require a string copy of mysql.user.authentication_string since I'm not certain anymore of the locations of that particular password in the configuration files (possibly in two locations). Things have been mostly sorted out and I greatly appreciate the help. I still have a problem to sort out, but it is distinct enough that it warrants a separate thread. Thank you very much! Timothy D. Legg
On 6/6/2017 3:43 PM, Timothy D. Legg wrote:
Hello,
First of all, here is what I'm working with:
Original machine: MySQL Server 5.7.18-0ubuntu0.16.04.1 Destination machine: MariaDB 10.0.30-MariaDB-0+deb8u2
These two machines have no filesystem or communication with with other.
I am moving to MariaDB with about 10 to 15 databases that were created in MySQL and I have had a very odd problem with the destination machine running MariaDB where the applications using these databases were having connection errors with their databases.
I looked very carefully at what I had done. I exported the databases from MySQL using:
$ mysqldump --all-databases > all.sql
This is your problem. You are overwriting the system tables with values which will not work in MariaDB 10.0.30.
MySQL 5.7 permissions are, supposedly (I haven't tested), compatible with MariaDB 10.2, but not 10.0.
In that all.sql file, remove any CREATE, DROP or ALTER statements to the mysql system database (which is the permissions system). Alternatively, do not dump the mysql system database and create those permissions by hand before the import using CREATE USER and/or GRANT statements.
Brian
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (2)
-
Brian Evans -
Timothy D. Legg