[Bug Report] - View Definitions on Export

[expired user #7823]'s profile image [expired user #7823] posted 10 years ago in Import/Export Permalink
I have a dev and live database, and occasionally when the dev gets out of date I dump the whole live db to dev using heidi. When view definitions get saved in heidi, the database name gets added to them. When I export the database, including views, the database name is in the exported view definition. This makes the view definition unsuitable for import into dev. Now the view definition on dev pulls data from live.
ansgar's profile image ansgar posted 10 years ago Permalink
I recall I fixed that some time ago. Are you on the current version of HeidiSQL?
[expired user #7823]'s profile image [expired user #7823] posted 10 years ago Permalink
I am, but it is possible that the view definition was created using heidisql 2 updated ago, but no older. Maybe I should try recreating the view on the latest version.
[expired user #8097]'s profile image [expired user #8097] posted 10 years ago Permalink
I have the same problem, right after actualization to latest portable version (8.3.0.4775 (32 Bit) @ Windows 7 Home Premium + SP1). In source database are views without database name, in destination (exported) with them.
ansgar's profile image ansgar posted 10 years ago Permalink
What? You have a destination database which holds views with an (additional!) database name?
[expired user #7823]'s profile image [expired user #7823] posted 10 years ago Permalink
I am still table to recreate this problem, but it is not an issue for me since I use the mysqldump command line program to migrate databases now.

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).
ansgar's profile image ansgar posted 10 years ago Permalink
Ah, I get what you mean now. The VIEW definition has database references in it. Yes, that's done by the server. Not sure but I don't think Heidi is actually causing that, but the server does. Will have a look into that.
[expired user #7823]'s profile image [expired user #7823] posted 10 years ago Permalink
A simpler way to recreate the problem from scratch that I just performed:

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, '') */;
[expired user #8097]'s profile image [expired user #8097] posted 10 years ago Permalink

Ah, I get what you mean now.


Sorry for unclear explanation. PhpMyAdmin doesn't this issue. If it is caused by server, please add to Heidi's export dialog checkbox to remove database references from views.
[expired user #9554]'s profile image [expired user #9554] posted 8 years ago Permalink

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.