Query editor -- Each field in different line (more visual friendly)

Hi !
It will be great to have in the query/view editor, each field on different line (like in MySQL workbench). Hi think is more visual friendly when you edit with a lot of fields.

Thanks for your great soft and works !!
;)

Sorry, should to be in Features request !!

Looks like you prefer Workbench :)

Sorry, but is not beacause I like features in other software that I prefer other software.

I very like HeidiSQL and use it each day. Is just suggestions for my PREFERED SQL software to be even more great !

Thanks !

We're talking about the VIEW editor, right? That VIEW body is a bit tricky to get from the original query, as the MySQL server rewrites the body when you save a VIEW. But Heidi already tries to load it from the .frm file on the server. Can you please paste here what you see in the SQL log at the bottom when you open the VIEW?

Hi !
Is what I see in the SQL log:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vueTamponFC.frm'));
SHOW /*!32332 FULL */ COLUMNS FROM `vueTamponFC`;

But in the view editor, I mean the "Field" section. Each field like this:

`tblservicescommande`.`idService` AS `idService`,

...is on a separate line like this:

`tblservicescommande`.`idService` AS `idService`,
`tblservicescommande`.`idService2` AS `idService2`,
`tblservicescommande`.`idService3` AS `idService3`,
`tblservicescommande`.`idService4` AS `idService4`,
`tblservicescommande`.`idService5` AS `idService5`,
etc...

Is more readable on each line than all on the same line like this:

`tblservicescommande`.`idService` AS `idService`,`tblservicescommande`.`idService2` AS `idService2`,`tblservicescommande`.`idService3` AS `idService3`,`tblservicescommande`.`idService4` AS `idService4`, etc...

Thank you very much and keep the good work ! ;)

That's what I meant. The server modifies a VIEW body when you save it. Later checks for SHOW CREATE VIEW show the whole body in a one-liner. That's bad, but not really fixable by HeidiSQL. Though HeidiSQL already does its best to retrieve the originaly body, by issuing such a vicious query:

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

However, that returns an empty string in many cases, probably because of missing file privileges on the server.

Now, in HeidiSQL, you have the "Reformat SQL" feature (see the white main toolbar button with a pencil on it, 6th one counted from the right). Clicking that, or pressing its shortcut Ctrl+F8 brings the SQL in any editor in order again.

Ok thanks ! I use already de "Reformat SQL".

Thanks for helping and taking time to check an explain that !
;)

He just don't work for the fields list. But for the rest is great !
Thanks !

If you have full access to the server, then you can also try to find a fix for the non-working LOAD_FILE() command mentioned above. You could check what the file "/path/to/mysql-data/bdtic/vueTamponFC.frm" contains and/or if it exists at all.

Ok I will check that thanks! ;)

Yes it exist. They content some information on the query like the definer, enconding, and the query (on one line for each info).

That is something I can do ?

The problem is, you seem not to have the privileges to read that file with the LOAD_FILE command. Probably you do not have the "FILE" privilege? You can check with HeidiSQL > Tools > User manager.

Ok thanks I check that !

After check, I have all the rights...and the "File" one.
My Server is on Linux.

Thanks !

I think I cath the thing....

If you edit first the view with Heidi, after the fields are correct on each line like I did. But for me, this work only in Xammp locally on my computer and not on my Linux server.

Thanks !

I think it's someting with ASCII Line Return code in UNIX vs in Windows.

My line is recorded ok in a server in Windows but not in Linux.

Thanks !

No news on this ?
I made other testing and effectively,if the server is on Windows the view keep the view body like you mention, but if it's a Linux server he doesn't.
I check the "FILE" privilege, I have all privillege, and even with root access. Not working...

But, like I said in my last post, I found that is probably
the ASCII New Line/line return code that is different in UNIX vs Windows who make the troubles...


Thanks and keep the good works !

Ok you should post both VIEW bodies here, the one from your Windows box and that from your Linux box. That way I can probably see what's wrong here. I'm unsure if the LOAD_FILE command works or not. But we'll see when you post your queries here.

Also, you could post more from your SQL log when you doubleclicked your VIEW. Is there an error message after the "SELECT LOAD_FILE..." ?

Hi !
BODY from Windows MySQL:
------------------------

select
`tblservicescommande`.`idService` AS `idService`,
`tblservicescommande`.`isservice_ratache` AS `isservice_ratache`,
`tblservicescommande`.`noCommande` AS `noCommande`,
`tblservicescommande`.`duree_serv` AS `duree_serv`,
`tblitems_2_prix`.`competence` AS `competence`,
`tblitem`.`Nom` AS `Nom`,
if((`tblservicescommande`.`isservice_ratache` is not null),
`tblservicescommande`.`isservice_ratache`,
`tblservicescommande`.`idService`) AS `ID1`,
`tblservicescommande`.`nblogement` AS `nblog`
from
((`tblservicescommande`
left join `tblitems_2_prix` ON ((`tblservicescommande`.`idItem` = `tblitems_2_prix`.`idItem`)))
left join `tblitem` ON ((`tblservicescommande`.`idItem` = `tblitem`.`idItem`)))
where
((cast(`tblservicescommande`.`dateService` as date) >= now())
and ((`tblitems_2_prix`.`nbLogement` = `tblservicescommande`.`nblogement`)
or (`tblitems_2_prix`.`nbLogement` = 0)))


Body from Linux MySQL (One liner...):
----------------------
select `tblservicescommande`.`idService` AS `idService`,`tblservicescommande`.`isservice_ratache` AS `isservice_ratache`,`tblservicescommande`.`noCommande` AS `noCommande`,`tblservicescommande`.`duree_serv` AS `duree_serv`,`tblitems_2_prix`.`competence` AS `competence`,`tblitem`.`Nom` AS `Nom`,if((`tblservicescommande`.`isservice_ratache` is not null),`tblservicescommande`.`isservice_ratache`,`tblservicescommande`.`idService`) AS `ID1`,`tblservicescommande`.`nblogement` AS `nblog` from ((`tblservicescommande` left join `tblitems_2_prix` on((`tblservicescommande`.`idItem` = `tblitems_2_prix`.`idItem`))) left join `tblitem` on((`tblservicescommande`.`idItem` = `tblitem`.`idItem`))) where ((cast(`tblservicescommande`.`dateService` as date) >= now()) and ((`tblitems_2_prix`.`nbLogement` = `tblservicescommande`.`nblogement`) or (`tblitems_2_prix`.`nbLogement` = 0)))


Thanks !

From the SQL LOG after selecting view (Linux MySQL):

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vue_competences.frm'));
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='vue_competences' AND TABLE_SCHEMA='bdtic' ;
SELECT CURRENT_USER();

I think it's probably something to do with Windows/Linux format of Line return / Carriage return that is not the same ascii code. (Unix vs Windows format)

OK, no error after LOAD_FILE. This means that works, but you have stored the VIEW afterwards with the one-liner. Please modify the VIEW on your Linux MySQL, let's say with a few linefeeds. Then save it, go to another table or view and then back to this view. Linefeeds should have been preserved now, right?

No.. I already try that. This work only on Windows MySQL server. On Linux server we lost the formatting, it's not been preserved.

Like I said, I think the Unix vs Windows linefeed code is the problem.

Thanks!

Not for HeidiSQL - the editor displays Linux and Windows linebreaks (and Mac style and some others also). So, I wonder what the LOAD_FILE command gives you.

Please fire in a query tab and post the result here:

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

The result of this is: (NULL)

The result of this is: (NULL)

On Windows server I have: TYPE = VIEWquery...etc..

So.. whats wrong Doc?

I think its a BUG on MySQL; see this:

http://bugs.mysql.com/bug.php?id=38403

Yes, looks so. A NULL is also returned when the file does not exist, but I guess that path should exist.

What does

SELECT CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'bdtic/vue_competences.frm')

give you?

Yes the path and file exist, and mysql is the owner.

Please fire the above mentioned query and post results here.

There is:

/var/lib/mysql/bdtic/vue_competences.frm

And SELECT LOAD_FILE('/var/lib/mysql/bdtic/vue_competences.frm')
returns NULL?

And SELECT LOAD_FILE('/var/lib/mysql/bdtic/vue_competences.frm')
returns NULL?

Yes: (NULL)

Ok. Then this is definitely something you have to verify on the server itself. You say the path is right and mysql can access it. Probably you indeed hit the above server bug.

YEP, it's a MySQL BUG...

So thanks again Ansgar for all your help !

;)

Hi Ansgar !

So for this, do you think it's possible to upgrade the CTRL-F8 function to have just one field per line on the SELECT line and not just one command per line?

Now we have this:

SELECT `tblservicescommande`.`idService` AS `idService`,`tblservicescommande`.`IdTech` AS `IdTech`,`tblservicescommande`.`idItem` AS `idItem`,`tbl........

I think it will be great to have like this on multiple line:

SELECT
`tblservicescommande`.`idService` AS `idService`,
`tblservicescommande`.`IdTech` AS `IdTech`,
`tblservicescommande`.`idItem` AS `idItem`,
`tbl..........

Thanks Ansgar !

I give you a beer ;)
Numéro de confirmation : 9U103714RJ872863J

What the auto-formatter does is highly subject to personal preferences, in many details. There was already a discussion somewhere in the tracker, without a reasonable consensus. Finally I decided to leave it as it is. It's quite ok currently, and I am not wanting to serve hundreds of personal preferences for this cosmetic feature.

Good !
Thanks ansgar !