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: | |
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
[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.