HeidiSQL Backup View Definitions

Hi,

How does HeidiSQL export the exact view definition? If I use mysqldump, the original query I used to create the view is not backed-up whilst when I export the whole database via HeidiSQL, the exact query is exported.

HeidiSQL Exported View Definition

SELECT
    a.col1,
    b.col2,
FROM
    table1 AS a
INNER JOIN
    table2 as b
    ON
        a.id = b.id

MySQLDump View Definition

select `a`.`col1` AS `col1`,`b`.`col2` AS `col2` from (`table1`) join....

As you can see, the query used was changed when exporting with MySQLDump. Any help is appreciated. Thanks.

HeidiSQL users wanted the original query in the VIEW definition, so I am reading that with a quite special query:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'yourdatabase/yourtable.frm'));

Interesting, is the original query as typed into the editor supposed to show when you go to edit the view in HeidiSQL? For me, the MySQLDump view definition is what shows in the editor, there is no way to edit the originally-typed query without saving it as an .sql file somewhere.

I'm using Percona Server 5.6.27, perhaps that stores the definition differently to regular MySQL?

I don't know if Percona does something differently here. I just wonder that the original query is used when exporting through HeidiSQL, but not when using the view editor. Is that correct, or are you talking about two different servers?

When "exporting" from HeidiSQL, via the "Export database as SQL" option, it exports in the "select a.col1..." format as shown by the OP.

This same format is also shown when clicking on the view in the left panel, and then clicking the "View: .." tab to edit it.

I checked the .frm file manually, and it contains this format, not the original formatted query.

Interesting, is the original query as typed into the editor supposed to show when you go to edit the view in HeidiSQL? For me, the MySQLDump view definition is what shows in the editor, there is no way to edit the originally-typed query without saving it as an .sql file somewhere.

I'm using Percona Server 5.6.27, perhaps that stores the definition differently to regular MySQL?

I am using MariaDB 10.1.19.

Yes, this is one of the merits using HeidiSQL to backup the MySQL database rather than using mysqldump. You get the original query used to create the view. Though I cannot find a way to automate this step. It would be great if HeidiSQL can support command-line parameters just to back-up the database as mysqldump is not good at performing its intended task.

HeidiSQL users wanted the original query in the VIEW definition, so I am reading that with a quite special query:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'yourdatabase/yourtable.frm'));

So you are doing additional steps to recreate the original statement to create the view. This is great. Thanks for sharing. I do hope if you have some spare time, you could add some support for command-line switch backup operation to alleviate the mysqldump limitation when it comes to exporting views.

When "exporting" from HeidiSQL, via the "Export database as SQL" option, it exports in the "select a.col1..." format as shown by the OP.

This same format is also shown when clicking on the view in the left panel, and then clicking the "View: .." tab to edit it.

I checked the .frm file manually, and it contains this format, not the original formatted query.

I checked the *.frm file for my view, it contains three options that looks like the query to create the view:

query -> contains the modified statement to create the view

source -> seems like the original SQL statement

view_body_utf8 -> the same as the query option

I just checked again, source in the .frm does indeed contain the original SQL. However, this is not what HeidiSQL v9.4.0.5125 is showing in the editor.

I just checked again, source in the .frm does indeed contain the original SQL. However, this is not what HeidiSQL v9.4.0.5125 is showing in the editor.

I see, seems like an inconsistent behavior. In my case, I can see the original query but, in your case, you are using Percona. Maybe Percona handles the views differently from MariaDB.

I just checked again, source in the .frm does indeed contain the original SQL. However, this is not what HeidiSQL v9.4.0.5125 is showing in the editor.

I see, seems like an inconsistent behavior. In my case, I can see the original query but, in your case, you are using Percona. Maybe Percona handles the views differently from MariaDB.

That's strange though, the .frm file does indeed contain the original query in the source= line. Maybe for some other reason the file is inaccessible via the LOAD_FILE statement, therefore it is defaulting to the modified query?

You can check that yourself, by firing the above SELECT LOAD_FILE query by hand. If it returns just NULL, then there is probably a file privilege issue on the server.

You can check that yourself, by firing the above SELECT LOAD_FILE query by hand. If it returns just NULL, then there is probably a file privilege issue on the server.

I did that, and indeed it returned NULL. But how can it be a file privilege issue when MySQL can clearly access it in order to execute the view?

I thought maybe it was because the table data is stored in /var/lib/mysql/{table_name} as opposed to /var/lib/mysql/data/{table_name}, but I modified the query to reflect that and it's still NULL.

Hi,

I have the same problem (but with mariaDb 10.1 - Clustered) - Seems there is a few that also have it (Definitely looks like a permissions problem because if i chmod the frm to 666 it load_files fine and I get the source) but then the permissions go back to what they were when I re-save the view via HeidiSQL.

I Think the answer is to use umask in the startup script, but I don't know enough to get this working.

Cheers, David