formatting of views

I have a problem with the formatting of views. As long as the database is located on a Windows machine, the formatting of views is retained. If I put the database on my NAS (Linux) the formatting is lost (all newlines and indents are gone). For stored procedures, the formatting is retained, but not for views. Is this a problem with my settings or a fundamental problem with HeideSQL.

Neither nor. The code of a view is reformatted by the server when you create or update it. At least MySQL and MariaDB do that.

When you open such a view later, HeidiSQL tries to restore the original code from the .frm file on the server, which sometimes fails on some servers:

SELECT CAST(LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'databasefolder/viewname.frm')) AS CHAR CHARACTER SET UTF8);

The result (if any) contains a "source=..." line, which has the user-defined code.

You could fire that query manually and see what's the result. This is one on my own local server:

TYPE=VIEW
query=select `test`.`staff`.`id` AS `id`,`test`.`staff`.`name` AS `name`,`test`.`staff`.`team` AS `team` from `test`.`staff` order by rand()
md5=f4f1bedf89a01930afcb79f872b1298d
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=1
with_check_option=0
timestamp=2019-03-25 12:05:04
create-version=2
source=SELECT *  FROM staff ORDER BY RAND()
client_cs_name=utf8mb4
connection_cl_name=utf8mb4_general_ci
view_body_utf8=select `test`.`staff`.`id` AS `id`,`test`.`staff`.`name` AS `name`,`test`.`staff`.`team` AS `team` from `test`.`staff` order by rand()
mariadb-version=100312

Thank you for the fast feedback. Then it will probably lie on missing or false rights for the data directory.

Yes, that's also the case on a Linux based webserver with MariaDB here.

I never found a better alternative than reading the .frm file.