Heidisql: SQL export is broken lower_case_table_names set to 2 and capitalized table names

[fifonik]

Expected behavior

Export database as SQL should work for MySQL with option lower_case_table_names set to 2.

Current behavior

In HeidiSQL 9.5.0.5199 and above it does not work for tables with capitalized names showing error message "Could not find table or view .. Please refresh database tree"

Failure information (for bugs)

I have dev MySQL installed locally with lower_case_table_names=2 in my.ini (for better compatibility with our prod *nix environment, this setting is NOT default for windows version of MySQL).

I have table and column names like this: Table, Table.Id, etc.
With the setting for table 'Test' there are files 'Test.frm' and 'test2.ibd' on disk.

Dumping database using HeidiSQL worked fine until 9.5.0.5199. In 9.5.0.5199 and above it shows errors for the tables with capitalized names: "Could not find table or view .. Please refresh database tree". For lowercased table names all works as expected.
I still can click on the table with capitalized name and use Data tab in 9.5.0.5199 without issues. Looks like the only Export is affected.

Steps to reproduce

1. Add lower_case_table_names=2 into my.ini and restart MySQL (could be tricky if you already have databases on the server)

2. Run HeidiSQL 9.5.0.5199 or above

3. Create database test

4. Create tables in the database use simple script (notice lowercased table name for one table and capitalized table name for another):

CREATE TABLE test1 (Id INT);
INSERT INTO  test1 (Id) VALUES (1);
CREATE TABLE Test2 (Id INT);
INSERT INTO  Test2 (Id) VALUES (2);

1. Refresh database tree

2. RMB over database 'test' | 'Export database as SQL' | enter filename | 'Export'

3. Notice that table 'test1' dumped successfully, but 'Test2' -- not. In error message HeidiSQL will show incorrect table name test.test2 (lowercased, should be test.Test2).

4. Install HeidiSQL 9.5.0.5198 and check that the database dumped successfully.

Context

• HeidiSQL version: 9.5.0.5199 & above
• Database system + version: MySQL 5.7.19
• Operating system: Windows 10 x64

Failure Logs

"Could not find table or view .. Please refresh database tree"
Screenshots can be found here:
https://www.heidisql.com/forum.php?t=24715#p24722

[fifonik]

Any news?

[ansgarbecker]

Build 5199 maps to 19a1c3fbb4e96e3d7953de9e436f8829b0818db7, which was meant as a solution to #53 , but probably had a side effect on the SQL export, not sure.

[ansgarbecker]

That message _Could not find table or view..._ is normally just a harmless thing, saying that the tree on the left is somehow missing that tree node. Obviously there is some search logic broken which searches for lowercase nodes only.

The curious thing is that this harmless error cascades to missing INSERTs in the SQL export. Also, the ALTER TABLE ... ENABLE KEYS is missing:

-- Dumping data for table test.Test2: ~1 rows (approximately)
/*!40000 ALTER TABLE `Test2` DISABLE KEYS */;
< Missing INSERTs and ALTER TABLE >
-- Dumping structure for table test.nexttable

[fifonik]

Does it mean that the issue will not be fixed?

[ansgarbecker]

In that case I would have closed this issue.

There is just not enough time for too many issues currently, in case you refer to the inactivity here.

[fifonik]

Sorry,
I'm just sad as I cannot use more recent build with some useful changes because of the issue.
As a developer (sorry, I'm not Delphi developer) I'm usually prioritizing bug fixes if I can find them. In this exact case I did my best to report the issue as good as possible (created simple demo, narrowed the issue to the exact version change).

I understand that this is non-commercial project and you are alone so I must not push it at all.
Sorry once again.

[fifonik]

BTW, I'm not sure why you said that 'Could not find table or view... is normally just a harmless thing'.
With the lower_case_table_names option set to 2 the table names are becoming case sensitive and if a table was created as 'Table', you cannot do 'SELECT * FROM table'.

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

[onidlo]

Ansgarbecker, it is not just a harmless thing. I'm not able to dump database because of this (only CREATE TABLE scripts are genereted, no INSERT INTO...).

[ansgarbecker]

Hm? I didn't say so. I said

The curious thing is that this harmless error cascades to missing INSERTs...

[renerlemes]

This is not a harmless mistake. It is something that bothers and disrupts development. Really will not be fixed and we will have to use an old version and missing out on several new features of updates?

[mahkali]

An updates on this? The issue also occurs on MySQL 5.6 Windows 10 64 bit.

[bytebodger]

Just a note to indicate that this is effecting me as well. I don't really have any new detail to add. I'm experiencing the exact same issue. It's killing my backup process because I typically save exports of my dev database into my source control with each commit.

[ansgarbecker]

So I just found out that the field structure returned by libmysql's function mysql_fetch_field_direct has its "org_table" string converted to lower case, when lower_case_table_name is either 1 or 2. HeidiSQL is at a relevant point in the code comparing the upper case name with that one, fails, and shows that error.

Solution will be to tell HeidiSQL to respect that lower_case_table_name value, and compare case insensitive for 1 and 2, while sticking to case sensitiveness for 0.

[renerlemes]

What is the deadline to correct this problem, since now you have found a solution and why was the problem occurring?

[ansgarbecker]

There is no deadline. But I'm just working on it.

[renerlemes]

@ansgarbecker good news. Waiting for fix!

[ansgarbecker]

Well, I have my second glass of wine here, so don't expect wonders ;)

[ansgarbecker]

Just committed a fix, which respects both settings for case sensitive and insensitive comparison.
Please update Heidi to the upcoming nightly build and report back if all is ok now.

[fifonik]

Installed 5280 and looks like the issue is fixed.
Thanks a lot.
Going to test the build as it were a lot of changes since 5198 I used (the last build that was not affected).

[bytebodger]

Works great for me now. Thank you SO MUCH for the work on this!

On Tue, Jun 26, 2018 at 6:29 PM fifonik [e-mail] wrote:

Installed 5280 and looks like the issue is fixed.
Thanks a lot.
Going to test the build as is were a lot of changes since 5198 I used (the
last build that was not affected).

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/HeidiSQL/HeidiSQL/issues/86#issuecomment-400483481,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AT97hsQZoIVQec1QhHbetQlZPIb_E7s0ks5uArWzgaJpZM4RbjeM
.

[Samuelyaks]

What exactly did you installed 5280? am having issues on case sensitivity on tables. Thanks