distal-attribute
distal-attribute
distal-attribute
distal-attribute

[Bug Report] - View Definitions on Export

User, date Message
Written by metateck
6 months ago
Category: Import/Export
4 posts since Fri, 28 Feb 14
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.
Written by ansgar
6 months ago
4973 posts since Fri, 07 Apr 06
I recall I fixed that some time ago. Are you on the current version of HeidiSQL?
Written by metateck
6 months ago
4 posts since Fri, 28 Feb 14
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.
Written by tiso
3 months ago
2 posts since Thu, 22 May 14
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.
Written by ansgar
3 months ago
4973 posts since Fri, 07 Apr 06
What? You have a destination database which holds views with an (additional!) database name?
Written by metateck
3 months ago
4 posts since Fri, 28 Feb 14
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).
Written by ansgar
3 months ago
4973 posts since Fri, 07 Apr 06
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.
Written by metateck
3 months ago
4 posts since Fri, 28 Feb 14
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, '') */;

Written by tiso
3 months ago
2 posts since Thu, 22 May 14

ansgar wrote: 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.
 

Please login to leave a reply, or register at first.