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

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

Mustard007 posted 2 years ago in General
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 !!
;)
Mustard007 posted 2 years ago
Sorry, should to be in Features request !!
ansgar posted 2 years ago
Looks like you prefer Workbench :)
Mustard007 posted 2 years ago
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 !
ansgar posted 2 years ago
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?
Mustard007 posted 2 years ago
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`;
Mustard007 posted 2 years ago
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 ! ;)
ansgar posted 2 years ago
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.
Mustard007 posted 2 years ago
Ok thanks ! I use already de "Reformat SQL".

Thanks for helping and taking time to check an explain that !
;)
Mustard007 posted 2 years ago
He just don't work for the fields list. But for the rest is great !
Thanks !
ansgar posted 2 years ago
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.

Mustard007 posted 2 years ago
Ok I will check that thanks! ;)
Mustard007 posted 2 years ago
Yes it exist. They content some information on the query like the definer, enconding, and the query (on one line for each info).
Mustard007 posted 2 years ago
That is something I can do ?
ansgar posted 2 years ago
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.
Mustard007 posted 2 years ago
Ok thanks I check that !
Mustard007 posted 2 years ago
After check, I have all the rights...and the "File" one.
My Server is on Linux.

Thanks !
Mustard007 posted 2 years ago
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 !
Mustard007 posted 2 years ago
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 !
Mustard007 posted 1 year ago
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 !
ansgar posted 1 year ago
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.
ansgar posted 1 year ago
Also, you could post more from your SQL log when you doubleclicked your VIEW. Is there an error message after the "SELECT LOAD_FILE..." ?

Mustard007 posted 1 year ago
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 !
Mustard007 posted 1 year ago
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();
Mustard007 posted 1 year ago
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)
ansgar posted 1 year ago
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?
Mustard007 posted 1 year ago
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!
ansgar posted 1 year ago
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'));

Mustard007 posted 1 year ago
The result of this is: (NULL)
Mustard007 posted 1 year ago

Mustard007 wrote: The result of this is: (NULL)


On Windows server I have: TYPE = VIEWquery...etc..
Mustard007 posted 1 year ago
So.. whats wrong Doc? wink
Mustard007 posted 1 year ago
I think its a BUG on MySQL; see this:

http://bugs.mysql.com/bug.php?id=38403
ansgar posted 1 year ago
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?

Mustard007 posted 1 year ago
Yes the path and file exist, and mysql is the owner.
ansgar posted 1 year ago
Please fire the above mentioned query and post results here.
Mustard007 posted 1 year ago
There is:

/var/lib/mysql/bdtic/vue_competences.frm
ansgar posted 1 year ago
And SELECT LOAD_FILE('/var/lib/mysql/bdtic/vue_competences.frm')
returns NULL?
Mustard007 posted 1 year ago

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



Yes: (NULL)
ansgar posted 1 year ago
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.
Mustard007 posted 1 year ago
YEP, it's a MySQL BUG...

So thanks again Ansgar for all your help !

;)
Mustard007 posted 1 year ago
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 !
Mustard007 posted 1 year ago
I give you a beer ;)
Numéro de confirmation : 9U103714RJ872863J
ansgar posted 1 year ago
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.
Mustard007 posted 1 year ago
Good !
Thanks ansgar !

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