New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySQL VIEW definition's UTF-8 problem #61

Closed
endo64 opened this Issue Dec 30, 2017 · 3 comments

Comments

Projects
None yet
2 participants
@endo64

endo64 commented Dec 30, 2017

UTF-8 chars are broken in MySQL / MariaDB View definitions when open in HeidiSQL View Editor:

Expected behavior

UTF-8 chars should be correct on view editor, db export to a file and to another database.

Current behavior

UTF-8 chars are broken.

Failure information (for bugs)

Please help provide information about the failure if this is a bug. If it is not a bug, please remove the rest of this template.

Steps to reproduce

Create a view:

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

Click on that view in HeidiSQL and see that Turkish characters are broken, shown as

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

This can be fixed by using below SQL:

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

related forum post: https://www.heidisql.com/forum.php?t=24623

  • HeidiSQL version: 9.5.0.5201 (64bit)
  • Database system + version: 10.2.10-MariaDB / MySQL 5.7
  • Operating system: Windows 8.1 x64
@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Dec 30, 2017

Collaborator

Should be working in the next HeidiSQL build.

One thing I also want to fix along with this issue is that the .frm file on the server's hard disk has some encoding for international characters like ü, ö etc. HeidiSQL tries to load testü.frm but the file is named test@1o.frm. Perhaps I'll find a way to fix that.

Collaborator

ansgarbecker commented Dec 30, 2017

Should be working in the next HeidiSQL build.

One thing I also want to fix along with this issue is that the .frm file on the server's hard disk has some encoding for international characters like ü, ö etc. HeidiSQL tries to load testü.frm but the file is named test@1o.frm. Perhaps I'll find a way to fix that.

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Dec 30, 2017

Collaborator

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.

I found a helpful blog article from Kolbe Kegel at MariaDB, about demystifying identifier mappings. But to repeat his steps HeidiSQL would need some internal mapping table for characters and probably even a own procedure. Even better would be a native Delphi procedure which does the same encoding. But that is too much effort only for getting the original formatted VIEW code in cases where the view name has international characters.

Collaborator

ansgarbecker commented Dec 30, 2017

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.

I found a helpful blog article from Kolbe Kegel at MariaDB, about demystifying identifier mappings. But to repeat his steps HeidiSQL would need some internal mapping table for characters and probably even a own procedure. Even better would be a native Delphi procedure which does the same encoding. But that is too much effort only for getting the original formatted VIEW code in cases where the view name has international characters.

@endo64

This comment has been minimized.

Show comment
Hide comment
@endo64

endo64 Dec 30, 2017

I'm not sure if this helps but, in MySQL Reference Manual, it says

The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

Please see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_load-file and especially https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_filesystem which says:

The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8'.

which could help.
If not we might consider integrating an external SQL command line formatter tool, something like http://architectshack.com/PoorMansTSqlFormatter.ashx (or a similar one)

endo64 commented Dec 30, 2017

I'm not sure if this helps but, in MySQL Reference Manual, it says

The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

Please see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_load-file and especially https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_filesystem which says:

The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8'.

which could help.
If not we might consider integrating an external SQL command line formatter tool, something like http://architectshack.com/PoorMansTSqlFormatter.ashx (or a similar one)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment