Transfer Virtualserver aborted if MySql View references to another DB

Submitted by stephanw on Fri, 04/19/2019 - 14:28

Under "Server Configuration" => "Transfer Virtual Server" you can easy transfer a virtual Server to a new Virtualmin Host. If the Server contains 2 MySql Databases and a view is defined in Database #1 which references to Database #2, then the Transfer will be aborted cause the second Database does not exist on the destination at the moment of creation of the view.

Maybe it could be a good idea to use the -f Option (force) in the mysql client to ignore such errors. Otherwise it isn't possible to transfer the VirtualServer to a new Host.

Status: 
Active

Comments

Submitted by JamieCameron on Sat, 04/20/2019 - 14:23

Interesting, we never considered that cross-database reference possibility.

Is the -f option used when backing up, or when restoring?

Submitted by stephanw on Sat, 04/20/2019 - 14:31

I don't know how virtualmin restore the database on the destination Host. But man mysql shows me, that the -f Option could force an import to ignore errors. So it should be used while restore at the destination.

Submitted by JamieCameron on Sat, 04/20/2019 - 14:32

That's kind of risky though, as during a domain move it could cause real errors to be ignored, resulting in an incomplete move.

Submitted by stephanw on Sat, 04/20/2019 - 14:45

I don't know how to solve it another way then with the -f Option. Until it is fixed i have the following workaround for this:
- Export the views to a local SQL File
- Delete the views in the DB
- Transfer virtual server (all tables and databases will be created)
- Import all views from local SQL File

You can get a list of your views with:

SELECT vws.table_schema,vws.table_name
FROM (
    SELECT *
    FROM  information_schema.tables
    WHERE table_type='VIEW'
  ) vws

Submitted by JamieCameron on Sat, 04/20/2019 - 18:54

Would it work better if all the domains with view-linked databases were transferred at once? Or does that still fail, because they are restored one by one?

Submitted by stephanw on Sun, 04/21/2019 - 04:07

No. In Virtualmin it isn't possible to give crossdomain access right to databases (as far as i know). So the problem occurs only inside the same domain with multiple databases.

The backup/restore has to be in the following order:
- Export all tables to dbname-tablename.sql
- Export all views to dbname-viewname.sql
- Import all tables
- Import all views

This are two interesting query to get the dumps of the tables / views:
All except Views:

SELECT CONCAT('mysqldump -u username -ppassword -h host [some options here] `',`TABLE_SCHEMA`,'` `',
`TABLE_NAME`,'` > ',`TABLE_SCHEMA`,'-',`TABLE_NAME`,'.sql') AS `sql`

FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_TYPE` != 'VIEW'
AND `TABLE_SCHEMA` NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA','mysql');

All Views:

SELECT CONCAT('mysqldump -u username -ppassword -h host [some options here] `',`TABLE_SCHEMA`,'` `',
`TABLE_NAME`,'` > ',`TABLE_SCHEMA`,'-',`TABLE_NAME`,'.sql') AS `sql`

FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_TYPE` = 'VIEW'
AND `TABLE_SCHEMA` NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA','mysql');

Joe's picture
Submitted by Joe on Sun, 04/21/2019 - 15:49 Pro Licensee

I would definitely be opposed to making restores ignore errors. If it were to be added, it should be optional and not enabled by default (though I hate to add more options).

Submitted by JamieCameron on Sun, 04/21/2019 - 15:51

Ok .. and by "all tables" you mean all tables across all databases being backed up?

If so, that's going to be really hard for Virtualmin to support, as the current code backs up each domain separately and completely before going on to the next one.

Submitted by stephanw on Sun, 04/21/2019 - 16:11

No, you are right.... mysql doesn't know, which tables are owned from which domains. So there could be no query to return this information. That makes it impossible to handle it easy...

I have no more idea to save this problem... if you would add an option to ignore errors, ok. But as you said, this is not a good solution. Thank's for your support anyway and for all of your work on virtaulmin.. have to say that once here :-)

Submitted by JamieCameron on Sat, 04/27/2019 - 20:01

What I'll do is make it so that when the "ignore errors" option is checked when making a Virtualmin backup, that mysql errors like this are also skipped.

Submitted by stephanw on Sun, 04/28/2019 - 05:01

That's fine! I'll test that. Thank's and have a nice day!