#2486 (ok 3.3.9) Copy database with view of a view
Hi,
We tested the copy database feature in phpmyadmin 2.10.3 which now supports copying views without errors. The fix works fine, but we found a new problem. This one is probably really hard to fix.
The problem happens when you copy a database in the operation tab and you have a view that does a select in another view which has not been copied yet.
Here is a sql example:
CREATE DATABASE \`test\` ;
CREATE TABLE \`table1\` \(
\`field\` TINYINT NOT NULL
\) ENGINE = MYISAM ;
CREATE SQL SECURITY INVOKER VIEW view2 AS SELECT \*
FROM table1;
CREATE SQL SECURITY INVOKER VIEW view1 AS SELECT \*
FROM view2;
Since view1 comes before view2 in alphabetical order, when you copy view1 to the new database, a sql error is generated saying view2 doesnt exist.
The problem doesnt happen with tables since you create the tables before creating the views.
Beside parsing the view creation syntax to find out if the there is uncreated views which the view depends on, I don't see how you could fix that problem. On my side, an easy workaround is to make sure the name of the views are ordered in a way that a view doesnt include another view unless it's name is coming first in the alphabet... ;)
Thanks
Logged In: YES
user_id=210714
Originator: NO
Problem still in 2.11.0.
Logged In: YES
user_id=1636250
Originator: YES
Hi,
I just check how mysqldump was doing it, I think it would be quite easy to do the same for you...
When they dump the database, they create a table (with the same name as the view) instead of a view with the same properties as the view.
After that, one by one, they drop the table and create a view instead which bypass the copy problem/dump/restore problem... Brilliant :)
Logged In: YES
user_id=210714
Originator: NO
phpMyAdmin 2.11 is doing the same thing as mysqldump, creating table placeholders for views. I am not sure however what mysqldump does with a view of a view.
Logged In: YES
user_id=210714
Originator: NO
Ok I checked and PMA 2.11 is doing the right logic when exporting, not when copying.
This bug was fixed in repository and will be part of a future release; thanks for reporting.