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.
formatting of views
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
Please login to leave a reply, or register at first.