[Bug Report] - View Definitions on Export
HeidiSQL 8.3.4.4771
I have a few copies of an identical database for dev, staging, and live, etc. All are mostly identical, including a view that works properly on all 3.
I right click on the view, click on export, and choose a different database to export the view to.
I make sure Drop Table and Create Table are chosen.
I export the view definition, and when I check the view definition in the target database, it has references to the source database.
Like I said, it is no longer an issue for me since the command line works much faster at exporting the data (It looks like Heidi actually downloads the data to my machine, and then uploads the data to the database for some reason).
1. Create a brand new db with 1 table, and 1 view referencing this table.
2. Create a second brand new empty db.
3. Right click on test_source_db and click export database as SQL.
4. Choose to output data to the test_dest_db database, and make sure drop and create tables is selected.
5. choose export and then check the definition of the view in the destination table.
SQL Log here:
/* Delimiter changed to ; */
/* Connecting to localhost via MySQL (SSH tunnel), username root, using password: Yes ... */
/* Attempt to create plink.exe process, waiting 4s for response ... */
/* Connected. Thread-ID: 1355577 */
SHOW STATUS;
SHOW VARIABLES;
SHOW DATABASES;
USE `fotopigeon_cj`;
/* Entering session "Rackspace Pigeonly" */
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='fotopigeon_cj';
SHOW TABLE STATUS FROM `fotopigeon_cj`;
SHOW FUNCTION STATUS WHERE `Db`='fotopigeon_cj';
SHOW PROCEDURE STATUS WHERE `Db`='fotopigeon_cj';
SHOW TRIGGERS FROM `fotopigeon_cj`;
SHOW EVENTS FROM `fotopigeon_cj`;
SHOW COLLATION;
SHOW VARIABLES LIKE 'collation_server';
CREATE DATABASE `test_source_db` /*!40100 COLLATE 'latin1_swedish_ci' */;
SHOW DATABASES;
/* Entering session "Rackspace Pigeonly" */
USE `test_source_db`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test_source_db';
SHOW TABLE STATUS FROM `test_source_db`;
SHOW FUNCTION STATUS WHERE `Db`='test_source_db';
SHOW PROCEDURE STATUS WHERE `Db`='test_source_db';
SHOW TRIGGERS FROM `test_source_db`;
SHOW EVENTS FROM `test_source_db`;
SHOW ENGINES;
SHOW VARIABLES LIKE 'collation_database';
CREATE TABLE `test_table` (
`pri_id` INT NOT NULL,
PRIMARY KEY (`pri_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test_source_db';
SHOW TABLE STATUS FROM `test_source_db`;
SHOW FUNCTION STATUS WHERE `Db`='test_source_db';
SHOW PROCEDURE STATUS WHERE `Db`='test_source_db';
SHOW TRIGGERS FROM `test_source_db`;
SHOW EVENTS FROM `test_source_db`;
SHOW CREATE TABLE `test_source_db`.`test_table`;
/* Entering session "Rackspace Pigeonly" */
SHOW CREATE TABLE `test_source_db`.`test_table`;
SELECT CURRENT_USER();
CREATE ALGORITHM = UNDEFINED VIEW `test_view` AS SELECT * from test_table ;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test_source_db';
SHOW TABLE STATUS FROM `test_source_db`;
SHOW FUNCTION STATUS WHERE `Db`='test_source_db';
SHOW PROCEDURE STATUS WHERE `Db`='test_source_db';
SHOW TRIGGERS FROM `test_source_db`;
SHOW EVENTS FROM `test_source_db`;
SHOW CREATE VIEW `test_source_db`.`test_view`;
SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'test_source_db/test_view.frm'));
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='test_view' AND TABLE_SCHEMA='test_source_db';
/* Entering session "Rackspace Pigeonly" */
SHOW VARIABLES LIKE 'collation_server';
CREATE DATABASE `test_dest_db` /*!40100 COLLATE 'latin1_swedish_ci' */;
SHOW DATABASES;
/* Entering session "Rackspace Pigeonly" */
USE `test_source_db`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test_source_db';
SHOW TABLE STATUS FROM `test_source_db`;
SHOW FUNCTION STATUS WHERE `Db`='test_source_db';
SHOW PROCEDURE STATUS WHERE `Db`='test_source_db';
SHOW TRIGGERS FROM `test_source_db`;
SHOW EVENTS FROM `test_source_db`;
SHOW CHARSET;
SELECT 'test_source_db' AS `Database`, 'test_table' AS `Table`, 0 AS `Rows`, 0 AS `Duration`;
/*!40101 SET @OLD_LOCAL_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
DROP TABLE IF EXISTS `test_dest_db`.`test_table`;
SHOW CREATE TABLE `test_source_db`.`test_table`;
CREATE TABLE IF NOT EXISTS `test_dest_db`.`test_table` (
`pri_id` int(11) NOT NULL,
PRIMARY KEY (`pri_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40000 ALTER TABLE `test_dest_db`.`test_table` DISABLE KEYS */;
SELECT * FROM `test_source_db`.`test_table` LIMIT 104857600;
/*!40000 ALTER TABLE `test_dest_db`.`test_table` ENABLE KEYS */;
SELECT 'test_source_db' AS `Database`, 'test_view' AS `Table`, -1 AS `Rows`, 0 AS `Duration`;
DROP VIEW IF EXISTS `test_dest_db`.`test_view`;
SHOW CREATE VIEW `test_source_db`.`test_view`;
SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'test_source_db/test_view.frm'));
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='test_view' AND TABLE_SCHEMA='test_source_db';
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `test_dest_db`.`test_view` (
`pri_id` INT(11) NOT NULL
) ENGINE=MyISAM;
SELECT 'test_source_db' AS `Database`, 'test_view' AS `Table`, -1 AS `Rows`, 0 AS `Duration`;
DROP VIEW IF EXISTS `test_dest_db`.`test_view`;
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `test_dest_db`.`test_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_dest_db`.`test_view` AS select `test_table`.`pri_id` AS `pri_id` from `test_table`;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_LOCAL_SQL_MODE, '') */;
This issue still exists. If you use the export tool for copying a source db into a target db, then the views in the target db will reference the tables in the source db. But they sould reference the tables in the target db. If you look at the views in the target db with HeidiSQL then everything looks OK. Only a 'show create view' statement reveals it:
CREATE VIEW v1
AS select source_db
.t1
.id
AS id
from source_db
.t1
But it should be
CREATE VIEW v1
AS select target_db
.t1
.id
AS id
from target_db
.t1
I'm using HeidiSQL 9.3.0.543
Please login to leave a reply, or register at first.