Menu
▾
▴
#2794 (ok 3.1.1) Importing a view in a different database
If you create a view in phpmyadmin 3.0.1.1 eg:
create view test_view select * from some_table;
then the database can be backed up with export but the exported file will break when it is imported back into an empty database with an SQL syntax error claiming that the table (sic) with the view name already exists.
The view will be incorrectly left in the partially restored database as a table and all remaining statements in the script will be ignored.
views are a highly desirable element of mysql so I see this as a major issue with phpmyadmin.
The attached file is an example of a full backup (export) of a simple database that cannot be imported again because of this problem.
Discussion
example script
The error is in the exported create view statement. The original database name is included in the statement. This is not done anywhere else in the export (which is good because it allows the scripts to be used to import the data into different locations) - the use of the database name needs to be consistent to avoid errors and I vote you don't use the database name - I believe this is what most users are expecting.
Fixed in subversion, thanks for reporting.
It is perfectly legal to have SQL queries join several databases, so the database name
in the view export is valid and even sometimes expected (read on...)
The information_schema view definition shows the database name
(as does a quick peek in the .frm file)
It feels to me like the original bugreport was based upon importing into
another database on the same server with privileges on both the new and old database,
the mysql error is valid because the view/table got dropped in the wrong (new) database.
Including the database name in exports should be optional,
when dumping several databases at once from server level importing is relying on
the USE <database> statements to get the tables/views/data back into the right database,
using partial import (not that strange when several huge databases have to be imported)
skipping records seems to be skipping the USE <database> statements too,
causing data/tables/views to end up in the wrong database, correct me if I am wrong.
Manual splitting of huge dumps suffers from the same problem.
Should this be a feature request (optional include of database name in exports)?