MySQL: VIew's issue in databases with dash in the name

Misha v.3 posted 2 years ago in General

If I create database 'test-dev', the directory 'test@002ddev' will be created for the database. As a result, the code that retrieves view's code will not work as expected as it executes:

SELECT LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'test-dev/Test.frm'));

And LOAD_FILE will not find '.../data/test-dev/Test.frm' (actual file name is '.../data/test@000ddev/Test.frm').

Then Heidi fallbacks to:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE   TABLE_NAME='Test' AND TABLE_SCHEMA='test-dev';

And will show view's code. Unfortunately, all code formatting (EOLs and indentations) will be lost and with big view the code will be absolutely unreadable.

It's a shame that LOAD_FILE not escaping filespec in the same way automatically.

As a simple workaround '-' could be replaced by '@002d'. This should do the trick. However, proper escaping should be required.

ansgar posted 2 years ago

Is there some documentation on how that escaping is done, probably via SQL command ESCAPE_LOCAL('test-dev') or something? I don't want to implement an escape sequence for a single character, and tomorrow for the next one, and so on.

Misha v.3 posted 2 years ago

The issue is not only with database names but with other object names as well (table, view, etc). If we create view 'view-dev' the file name view@002ddev.frm will be created.

https://dev.mysql.com/doc/refman/5.7/en/identifier-mapping.html Unfortunately, looks like there is no function in mysql api that can be used for such conversion :(

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