MySQL VIEW definition UTF8 character problem

Hi, UTF8 (Turkish) characters are broken when HeidiSQL shows a view's definition:

Below SQL returns correct result on Query window:

SELECT
    'ĞÜŞİÖÇ' AS col1,
    'ğüşıöç' AS col12

But when I put this into a view, then click on that view its definition looks as below:

SELECT
    'ĞÜŞİÖÇ' AS col1,
    'ğüşıöç' AS col12 

This doesn't happen on other clients like DBeaver or Navicat.

More importantly characters are broken on database export to a file or to another database. That makes export useless and even worst dangerous:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS
SELECT
    'ĞÜŞİÖÇ' AS col1,
    'ğüşıöç' AS col12 ;

Note that SHOW CREATE VIEW returns correct result:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS
select 'ĞÜŞİÖÇ' AS `col1`,'ğüşıöç' AS `col12`

HeidiSQL 9.5.0.5201 (64bit)

Windows 8.1 x64

10.2.10-MariaDB

MySQL 5.7.17

Thank you for your great product!

Perhaps you are one of the few lucky ones getting a result for HeidiSQL's LOAD_FILE command when initializing the view editor:

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

This is a problem in your case as the LOAD_FILE command perhaps uses the wrong encoding.

Could you please file this as a new ticket on Github?

A CONVERT(LOAD_FILE(...) USING utf8) could help here. But that all should go in a ticket.

Hi, I just tried below and it works correct:

SELECT CAST(LOAD_FILE(
    CONCAT(
        IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')),
        'test/v_test.frm'
    )
) AS CHAR CHARACTER SET utf8);

I'm filling a bug report on github.

Here it is: https://github.com/HeidiSQL/HeidiSQL/issues/61

By the way, why not use SHOW CREATE VIEW syntax? To keep the formatting?

Exactly, due to lost formatting. People complained about that, so I found a way to load the very original code from the view.

I see, clever way :) We can also consider integrating a third-party SQL formatter tool.

Here it is: HeidiSQL/issues/61

61 is closed, so I reply here.

Regarding "There seems to be no handy SQL function for encoding an identifier just like MySQL or MariaDB do it when storing .frm files with international characters."

See this bugreport and especially the comment from Marko.

MDEV-17088

(still not allowed to post links)

`SET NAMES utf8;

SELECT CONVERT(CONVERT('測試資料' USING filename) USING binary),

CONVERT(_filename '@6e2c@8a66@8cc7@6599@5eab' USING utf8); `

I hope this helps