Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Exporting SQL views

anuragnz's profile image anuragnz posted 2 years ago in General Permalink

Hi there,

The export functionality is awesome. It would be great if we don't include schema name when exporting SQL views to another database.

Many thanks

anuragnz's profile image anuragnz posted 2 years ago Permalink

Can anyone reply to my concern please? It's quiet not convenient when exporting SQL views to another database. Which actually still pointing to original database.

If there is any setting available then please guide.

Many thanks

ansgar's profile image ansgar posted 2 years ago Permalink

Are you on the latest version, 10.1?

ansgar's profile image ansgar posted 2 years ago Permalink

I get this when I export a view from database "test" to "test2":

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
  VIEW `test2`.`v` AS SELECT * from countries ;

No source database name in that query.

anuragnz's profile image anuragnz posted 2 years ago Permalink

Thanks for your reply.

Yes, I am using latest version 10.1.0.5464.

Please check my screenshots of how I am getting this scenario.

Many thanks

3 attachment(s):
  • Screenshot_13
  • Screenshot_14
  • Screenshot_15
ansgar's profile image ansgar posted 2 years ago Permalink

Ok, I see the view code has the old database name.

And how does the export SQL code look like which HeidiSQL generates?

I assume the server adds that database name.

anuragnz's profile image anuragnz posted 2 years ago Permalink

Where can I see this? I'm using output to another database option.

If I use clipboard export option then this is the sql code:


-- Host: localhost -- Server version: 5.7.25-0ubuntu0.16.04.2 - (Ubuntu) -- Server OS: Linux -- HeidiSQL Version: 10.1.0.5464


/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET NAMES utf8 /; /!50503 SET NAMES utf8mb4 /; /!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' /;

-- Dumping database structure for Test1 CREATE DATABASE IF NOT EXISTS Test1 /!40100 DEFAULT CHARACTER SET latin1 /; USE Test1;

-- Dumping structure for view Test1.vw_customer -- Creating temporary table to overcome VIEW dependency errors CREATE TABLE vw_customer ( CustomerId INT(11) NOT NULL, Name VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci', Address VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci' ) ENGINE=MyISAM;

-- Dumping structure for view Test1.vw_customer -- Removing temporary table and create final VIEW structure DROP TABLE IF EXISTS vw_customer; CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW vw_customer AS select Customer.CustomerId AS CustomerId,Customer.Name AS Name,Customer.Address AS Address from Customer;

/!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 CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;

Many thanks

ansgar's profile image ansgar posted 2 years ago Permalink

You can see the view structure in your log:

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER
VIEW vw_customer AS
select Customer.CustomerId AS CustomerId,Customer.Name AS Name,Customer.Address AS Address from Customer;

HeidiSQL is at least not to blame here - the old database name obviously gets inserted by the server.

anuragnz's profile image anuragnz posted 2 years ago Permalink

Thanks very much.

Now the question is how to stop server to add old database name?

Please suggest the solution.

anuragnz's profile image anuragnz posted 2 years ago Permalink

Could you please advise a solution for not to add old database name when exporting SLQ views?

ansgar's profile image ansgar posted 2 years ago Permalink

I have no advice.

Apart from the fact that HeidiSQL normally works around that dependency issue by creating an exported view at first as a table, and after all other tables are created it drops the fake table and creates the real view. I can only guess you don't have that "Customer" table in the target db.

anuragnz's profile image anuragnz posted 2 years ago Permalink

Customer table is already there. Actually we have a multi tenant software and we often need to export Views to update tenant databases.

borre's profile image borre posted 2 months ago Permalink

having the same problem here.

When I create a view in DB1 like:

select id from tablename

there is no databasename in it.

When I do SHOW CREATE VIEW view1;

as result I get

select id from tablename

when I look in the view1.frm file, it says:

select DB1.tablename.id from DB1.tablename

when I export it to DB2

the result of the view1 in DB2 is

select DB1.tablename.id from DB1.tablename

I dont need the DB1 there.

Is there a way to disable this in mysql/mariadb?

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.