Bug #73383 mysqldbcopy fails when views contain sub-views which are not yet copied
Submitted: 25 Jul 2014 0:16 Modified: 2 Sep 2014 21:00
Reporter: Ron Taylor Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S1 (Critical)
Version:1.4.3 OS:Windows (server 2008)
Assigned to: CPU Architecture:Any
Tags: cannot operate on view object, error 1146, mysqldbcopy

[25 Jul 2014 0:16] Ron Taylor
Description:
Suppose you have a schema with the following objects:

Tables:
tbl_a
tbl_b
tbl_c
Views:
view1 (has v_tbl_a with a join to v_tbl_c)
v_tbl_a
v_tbl_b
v_tbl_c

When using mysqldbcopy the operation will fail upon trying to copy view1.  The error message will say that v_tbl_a does not exist.  Of course it does not exist yet, because it has not been copied.

There is loss of functionality because databases cannot be copied that have views which use joins of alphabetically subsequent view names.  I looked for an ignore error option, but did not find anything.  There does not appear to be any available workaround.

How to repeat:
To re-create the bug
What I did:
I used the database called world.

=============
I created 3 views with the following code:

delimiter $$ CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `world`.`v_aquery` AS select `v_country`.`Code` AS `Code`,`v_country`.`Name` AS `Name`,`v_country`.`Continent` AS `Continent`,`v_country`.`Region` AS `Region`,`v_country`.`SurfaceArea` AS `SurfaceArea`,`world`.`countrylanguage`.`Language` AS `Language`,`world`.`countrylanguage`.`IsOfficial` AS `IsOfficial`,`world`.`countrylanguage`.`Percentage` AS `Percentage` from (`world`.`v_country` left join `world`.`countrylanguage` on((convert(`v_country`.`Code` using utf8) = convert(`world`.`countrylanguage`.`CountryCode` using utf8))))$$

delimiter $$ CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `world`.`v_city` AS select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city`$$

delimiter $$ CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `world`.`v_country` AS select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`country`$$

=============
Next go to mysql utilities and enter the following at the prompt:
mysqldbcopy --source=root:[enter password here without brackets]@localhost:3306 --destination=root:[enter password here without brackets]@localhost:3306 world:world_clone

=============
An error will be reported like this:
ERROR: Cannot operate on VIEW object. Error: Query failed. 1146 (42S02): Table '
world_clone.v_country' doesn't exist

Suggested fix:
Fix the program to allow copying databases that have views which use joins of alphabetically subsequent view names.
[28 Jul 2014 6:12] MySQL Verification Team
Hello Ron Taylor,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[28 Jul 2014 6:14] MySQL Verification Team
// OL6, WB Utilities 1.4.3

How to repeat:

Download  world database (InnoDB version, used in MySQL certifications and training) from http://dev.mysql.com/doc/index-other.html
## Extract and place it in /tmp

mysql> create database world;
Query OK, 1 row affected (0.00 sec)

mysql> use world;source /tmp/world_innodb.sql;
Database changed
..
..

## rename tables to lowercase

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> rename table City to city;
Query OK, 0 rows affected (0.04 sec)

mysql> rename table Country to country;
Query OK, 0 rows affected (0.07 sec)

mysql> rename table CountryLanguage to countrylanguage;
Query OK, 0 rows affected (0.02 sec)

CREATE  VIEW `world`.`v_country` AS select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`country`;
CREATE  VIEW `world`.`v_aquery` AS select `v_country`.`Code` AS `Code`,`v_country`.`Name` AS `Name`,`v_country`.`Continent` AS `Continent`,`v_country`.`Region` AS `Region`,`v_country`.`SurfaceArea` AS `SurfaceArea`,`world`.`countrylanguage`.`Language` AS `Language`,`world`.`countrylanguage`.`IsOfficial` AS `IsOfficial`,`world`.`countrylanguage`.`Percentage` AS `Percentage` from (`world`.`v_country` left join `world`.`countrylanguage` on((convert(`v_country`.`Code` using utf8) = convert(`world`.`countrylanguage`.`CountryCode` using utf8))));
CREATE  VIEW `world`.`v_city` AS select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city`;

mysql> 
mysql> CREATE  VIEW `world`.`v_city` AS select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city`;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE  VIEW `world`.`v_country` AS select `world`.`country`.`Code` AS `Code`,`world`.`country`.`Name` AS `Name`,`world`.`country`.`Continent` AS `Continent`,`world`.`country`.`Region` AS `Region`,`world`.`country`.`SurfaceArea` AS `SurfaceArea`,`world`.`country`.`IndepYear` AS `IndepYear`,`world`.`country`.`Population` AS `Population`,`world`.`country`.`LifeExpectancy` AS `LifeExpectancy`,`world`.`country`.`GNP` AS `GNP`,`world`.`country`.`GNPOld` AS `GNPOld`,`world`.`country`.`LocalName` AS `LocalName`,`world`.`country`.`GovernmentForm` AS `GovernmentForm`,`world`.`country`.`HeadOfState` AS `HeadOfState`,`world`.`country`.`Capital` AS `Capital`,`world`.`country`.`Code2` AS `Code2` from `world`.`country`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE  VIEW `world`.`v_aquery` AS select `v_country`.`Code` AS `Code`,`v_country`.`Name` AS `Name`,`v_country`.`Continent` AS `Continent`,`v_country`.`Region` AS `Region`,`v_country`.`SurfaceArea` AS `SurfaceArea`,`world`.`countrylanguage`.`Language` AS `Language`,`world`.`countrylanguage`.`IsOfficial` AS `IsOfficial`,`world`.`countrylanguage`.`Percentage` AS `Percentage` from (`world`.`v_country` left join `world`.`countrylanguage` on((convert(`v_country`.`Code` using utf8) = convert(`world`.`countrylanguage`.`CountryCode` using utf8))));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| v_aquery        |
| v_city          |
| v_country       |
+-----------------+
6 rows in set (0.00 sec)

[root@ushastry mysql-5.6.21]# mysqldbcopy --source=root@localhost --destination=root@localhost --drop-first world:world_clone
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# WARNING: A partial copy from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all option and do not specify a list of databases.
# Copying database world renamed as world_clone
# Copying TABLE world.city
# Copying TABLE world.country
# Copying TABLE world.countrylanguage
# Copying VIEW world.v_aquery
ERROR: Cannot operate on VIEW object. Error: Query failed. 1146 (42S02): Table 'world_clone.v_country' doesn't exist
[2 Sep 2014 21:00] Philip Olson
Fixed as of the upcoming MySQL Utilities 1.5.2 release, and here's the changelog entry:

When copying views that depended on sub-views that were not yet copied,
some utilities such as "mysqldbcopy" would fail. The dependency related
code was fixed to solve this problem.

Thank you for the bug report.