HeidiSQL multi-tasking and exporting (my two cents) :)

I am a huge fan and wanted to submit my comments for possible consideration. :)

Multi-tasking
Well i see that HeidiSQL seems to not refresh its UI until it has completed various tasks. It is quite apparent when i connect to my website server, exporting data out of the database, and i believe it must do this in various other places also. I compare this mysql-front 2.5 and can easily see the difference. Just to be clearer, if i was exporting from mysql-front 2.5, i could see the count of the number of records it had exported as it is working, while in HeidiSQL the UI freezes until its completed. Firstly i thought this was just because i was jumping to another application and coming back, but nope it happened just while i sat there waiting for it to complete.

Exporting SQL
Well i exported some data from my online database (3.23.58) and when trying to import it into my pc database i got the following error.

SQL Error: You have an error in your SQL syntax near '0x5350454349414C495A45204558504552544953452045535441424C4953484D454E540D0A534545'

It had converted a 'text' field to hex and that was the result. Below is the exact line of SQL. (if possible can it be possible not convert things to hex from the export table dialog)

INSERT INTO `employer` (`id`, `profile`, `disable_date`) VALUES (18,_ 0x556C74696D617465204D6 (etc. - shortened by forum moderator) ,NULL);

Also in the 'Export Tables' dialog box, i would like to suggest that you have a check box asking whether or not it should be a full insert (including fieldnames) or a quick insert, as i prefer not to see the field names on every line. Of course this would also effect replace and update.

Exporting CSV
When exporting to CSV and then back into the database, i see that it has quite a bit of problems. Most particularly on NULL valued fields as it represents it as "", but also for example i had a field which was enum with two values (ex. 'Y','N'), of course heidisql shows it in the grid as a checkbox, and when it outputs it, it outputs it as TRUE or FALSE rather than the actual value. I used to like this feature when it was first introduced, but after using it for some time it has become a problem and if possible, can it be disabled in the preferences. If possible can 'export to CSV' and 'import from CSV' be added to the right-click of a table.

For my last suggestion, it would be nice to have the entire right-click table menu list available also when you right-click on a table in the left panel, as i was emptying a table multiple times, while i was in the 'data' tab and each time i had to go back to the 'database' tab in order to do it. :)

SQL Error: You have an error in your SQL syntax near '0x5350454349414C495A45204558504552544953452045535441424C4953484D454E540D0A534545'

It had converted a 'text' field to hex and that was the result. Below is the exact line of SQL. (if possible can it be possible not convert things to hex from the export table dialog)

That issue was recently fixed by rosenfield:
https://sourceforge.net/tracker/?func=detail&atid=832347&aid=1708421&group_id=164593

Also in the 'Export Tables' dialog box, i would like to suggest that you have a check box asking whether or not it should be a full insert (including fieldnames) or a quick insert, as i prefer not to see the field names on every line. Of course this would also effect replace and update.

We dropped the option "Complete INSERTs" and always generate complete INSERTs some time ago. What's the bad effect you see in a complete INSERT? The short INSERTs without fieldnames can indeed lead to errors if the table structure changes before reimporting the file.

When exporting to CSV and then back into the database, i see that it has quite a bit of problems. Most particularly on NULL valued fields as it represents it as ""

That's quite ok for a CSV file which cannot transport NULL values. I wouldn't say we should export a string containg "NULL" instead - that would be very ugly, wouldn't it?

but also for example i had a field which was enum with two values (ex. 'Y','N'), of course heidisql shows it in the grid as a checkbox, and when it outputs it, it outputs it as TRUE or FALSE rather than the actual value.

Thanks a lot, that's a real bug - will be fixed ASAP.

If possible can 'export to CSV' and 'import from CSV' be added to the right-click of a table.

The CSV exports are not always based on the complete tables. It's more the idea of exporting what you currently see in the datagrid and that data can be filtered by a WHERE filter. If we would do the export always for the complete table we would nuke that feature.

but also for example i had a field which was enum with two values (ex. 'Y','N'), of course heidisql shows it in the grid as a checkbox, and when it outputs it, it outputs it as TRUE or FALSE rather than the actual value.

Thanks a lot, that's a real bug - will be fixed ASAP.

Fixed in revision 626: http://svn.sourceforge.net/heidisql/?rev=626&view=rev

We dropped the option "Complete INSERTs" and always generate complete INSERTs some time ago. What's the bad effect you see in a complete INSERT? The short INSERTs without fieldnames can indeed lead to errors if the table structure changes before reimporting the file.

Well about the complete inserts, they are pointless if you are doing a table and data sql output as it simply eats up unnecessary bytes. As an example, lets take a complete backup of the forum with complete inserts and with short inserts and you can easily see the difference. I have to SQL dump a table each week which has 65 fields in it and each SQL lines is huge. And it gets worse if you were exporting the table and data from maybe your desktop database to an online database.

That's quite ok for a CSV file which cannot transport NULL values. I wouldn't say we should export a string containg "NULL" instead - that would be very ugly, wouldn't it?

About the CSV, well of course normal CSV dont take null values but i simply use it as an alternative to an SQL dump and would like to be able to export it out of a database and then back into a database without having to run UPDATE for all the mistakes issues, especially when mysql's import function does understand NULL.

The CSV exports are not always based on the complete tables. It's more the idea of exporting what you currently see in the datagrid and that data can be filtered by a WHERE filter. If we would do the export always for the complete table we would nuke that feature.

Well i see where you are coming from with the csv exports, but i do believe its a good feature as i have seen it in other mysql guis, and it would save me time from having to go to data view to export csv. Well my main suggestion was to have the same table right-click menu on a table in both the right and left areas.

Didnt hear anything about the multitasking issue, so i guess its something that cant be helped i guess.Thanks for the reply and keep up the great work.

We dropped the option "Complete INSERTs" ... The short INSERTs without fieldnames can indeed lead to errors if the table structure changes before reimporting the file.

Well about the complete inserts, they are pointless if you are doing a table and data sql output as it simply eats up unnecessary bytes.

Well they're not pointless as I said, when the table structure has changed inbetween.

Any chance that you try the "Extended INSERTs" instead? That generates big INSERTs with one column-lists and several values-lists, up to a limit of what you gave the server as max_allowed_packet (default 5 MB).

About the CSV, well of course normal CSV dont take null values but i simply use it as an alternative to an SQL dump and would like to be able to export it out of a database and then back into a database without having to run UPDATE for all the mistakes issues, especially when mysql's import function does understand NULL.

I see. So what should the CSV contain for a NULL value? You will see that it's not possible to get the LOAD DATA command to treat some string value as NULL value. So I guess what you intend to do is not possible?

Well i see where you are coming from with the csv exports, but i do believe its a good feature as i have seen it in other mysql guis, and it would save me time from having to go to data view to export csv. Well my main suggestion was to have the same table right-click menu on a table in both the right and left areas.

Hmm doesn't sound too bad. We'll see what we can do.

Well they're not pointless as I said, when the table structure has changed inbetween. 
Any chance that you try the "Extended INSERTs" instead? That generates big INSERTs with one column-lists and several values-lists, up to a limit of what you gave the server as max_allowed_packet (default 5 MB).

Yes, i have seen the extended insert option, but when running the sql data on an external db, it would have to load the complete line of extended inserts in order to process that one line of SQL, in comparison of loading single lines one by one. You have provided the LONG insert and the EXTENDED insert, so why not give us the option for the REGULAR/SHORT insert. :)

I see. So what should the CSV contain for a NULL value? You will see that it's not possible to get the LOAD DATA command to treat some string value as NULL value. So I guess what you intend to do is not possible?

Well it would have all none NULL values in quotes and NULL values as simply NULL. Yes i have seen that LOAD DATA has treated some fields with the NULL value as blank strings, which really blows my mind. Well anyway here is an example of the CSV outputs from my favourite MySQL frontends.

MySQL Front 2.5
favourite_film,photo_url,profile
The Thin Red Line (1998),NULL,NULL
Dark City (1998),NULL,
Lost Highway (1997),NULL,
NULL,NULL,another test
The Shawshank Redemption (1994),NULL,

HeidiSQL
"favourite_film","photo_url","profile"
"The Thin Red Line (1998)","",""
"Dark City (1998)","",""
"Lost Highway (1997)","",""
"","","another test"
"The Shawshank Redemption (1994)","",""

SQLyog
favourite_film,photo_url,profile
"The Thin Red Line (1998)",NULL,NULL
"Dark City (1998)",NULL,""
"Lost Highway (1997)",NULL,""
NULL,NULL,"another test"
"The Shawshank Redemption (1994)",NULL,""

Well i just came up with a great idea for the right-click menu in the DATA tab. I love that its possible to arrange fields in the tab, so i was thinking that you could also make it possible to remove fields (was thinking possibly by right-clicking on the column name, or possibly as a menu item called 'Filter Fields' or something like that, which would list all the field names and you could click on it to enable/disable it, but if you use the latter, then i would also suggest you give the ability to enable/disable fields within a dialog box, for easier enabling/disabling of multiple fields and incase the field list is long), and this would also help in the CSV output, as their isnt a means of selecting which fields you wanted outputted in the 'export results-set' window.

I love that its possible to arrange fields in the tab, so i was thinking that you could also make it possible to remove fields (was thinking possibly by right-clicking on the column name, or possibly as a menu item called 'Filter Fields' or something like that, which would list all the field names and you could click on it to enable/disable it, but if you use the latter, then i would also suggest you give the ability to enable/disable fields within a dialog box, for easier enabling/disabling of multiple fields and incase the field list is long), and this would also help in the CSV output, as their isnt a means of selecting which fields you wanted outputted in the 'export results-set' window.

That's a quite good idea I think. We could even make these column-disablings permanent by storing them in the registry, so the table gets displayed with the same fields everytime you click on it.

Wasnt sure if you were being sarcastic or not, but i'm leaning more towards the sarcasm side? :oops:

Hu? I'm never sarcastic :)

No, really, I already implemented the first half of the above mentioned feature. It's a very useful and time-saving feature I think. Especially if the displayed columns get saved and restored everytime you browse a table you don't have to walk through all the less important fields, scroll to the right and probably oversee some important data.

However, one thing we should do is to signalize the user that there are some disabled columns, maybe with some red message on top of the grid or so.

Hu? I'm never sarcastic :)

Great to hear it.

No, really, I already implemented the first half of the above mentioned feature. It's a very useful and time-saving feature I think. Especially if the displayed columns get saved and restored everytime you browse a table you don't have to walk through all the less important fields, scroll to the right and probably oversee some important data.

Sounds great

However, one thing we should do is to signalize the user that there are some disabled columns, maybe with some red message on top of the grid or so.

Well if you are going to have something to signalize that, you should also have something that signalizes that the filter is set, as i have come back to a table and forgotten i had the filter on.

About the CSV import/export (yep i know you'd be saying, "is this guy actually going back to that issue"), i was thinking that the default 'CSV-Options' from 'Preferences' be added as default settings in the 'Import Textfile' dialog box. I would also say that 'ignore' be checked also as all CSV output from HeidiSQL has that in it.

Well i have to suggest something else. This problem has happened to me twice and i am very embarrassed to admit it but here is what happened. I went into the query tab and typed an sql statement, and at that point the sql/blob-editor area was hiding the results area, and i went and executed some SQL looking for results which just didnt appear, and was loosing my mind thinking that no results had turned out from the SQL query.

Columns are now selectable in revision 648. See here for details:
http://svn.sourceforge.net/heidisql/?rev=648&view=rev

Great to hear it. So is there a new version coming out anytime soon so i can try someone of my suggestions out. :)

Any considerations of implementing any of my other suggestions?

you should also have something that signalizes that the filter is set

How could that look like? Suggestions? Currently the filter memo is brought to front so we at least ensure it's visible. Maybe red borders or so? We should NOT use red background :)

that the default 'CSV-Options' from 'Preferences' be added as default settings in the 'Import Textfile' dialog box.

Good point. And should be quite easy to implement :)

I would also say that 'ignore' be checked also as all CSV output from HeidiSQL has that in it.

Not only HeidiSQL but also other applications do that. So, I agree, it should be checked by default.

Great to hear it. So is there a new version coming out anytime soon so i can try someone of my suggestions out. :)

We intend to make some more needed bugfixes and changes before releasing a new RC. Should not take longer than 2 or 3 weeks. Maybe I'll set up a link to a developer exe on the download page sooner.

Any considerations of implementing any of my other suggestions?

See above posting.

you should also have something that signalizes that the filter is set

How could that look like? Suggestions? Currently the filter memo is brought to front so we at least ensure it's visible. Maybe red borders or so? We should NOT use red background :)

Well the filter memo being brought to front wont help if its touching the bottom of the window, as i mostly have it, but yes it does pop to it if you click on a table where the filter is enabled. Well i would suggest that it be put next to the 'records total' beside the db name and table name. So it may look like 'XXX records total : XXX filtered records'. And if so, possibly having a clear filter button next to it. :)

Well on another note, i really get annoyed with the column width in the 'Data' view. I know the upcoming version tries to improve on this, but though we may set the size of a field to 100 and all the records are using a max of 15 characters in them, it stretching it to fit 100 characters will drive some people crazy.(i had some fields set to varchar(255) which took up the complete width of the grid). Possibly the width can be calculated by checking the max size of all the records in that field or better yet have the adjusted size saved somewhere, so it doesnt need to be re-adjusted very time we come back into the table. Also possibly the column arrangement also could be saved, and then a 'reset column arrangement' be added to the right-click.

You know that 'Confirm' dialog that apears everytime you try and change the ordering of fields in a table, please add a 'dont show me this again' checkbox because it can get a bit annoying. If possible, can you take this re-ordering idea to the next level, by allowing us to re-order the columns, looking similar to the columns list in the 'Import Textfile' dialog, and do one okay to confirm its rearrangement, as i remember one day when i had to see the 'Confirm' dialog 15 times trying to reorder the fields in a table some idiot created.

Well i know this is a shot in the dark but what the hell. You know how phpmyadmin will allow you to enter in a record (meaning: showing the fields and their values on separate rows), i was wondering if you would consider making a new tab called 'Data Form'. I have found the 'Data' tabular view great when i have a few columns to enter data into, but when there are alot of columns, tabular view isnt as efficient.

Was looking more into the filter and would like to suggest that if a filter is set when coming to the table data, it be added to the drop down menu, as it isnt added there when you newly open HeidiSQL. Also would like to suggest a 'apply last filter' to the right-click Quick Filter menu.

I just happened to open MS Access today and see that they have icon, similar to your 'Quick Filter' icon, in the menu bar and when a filter is on, the icon button is pressed in, and when pressed out, it disabled the filter. If this is added then there is no need for the clear filter button i suggested previously. :)

Was looking more into the filter and would like to suggest that if a filter is set when coming to the table data, it be added to the drop down menu, as it isnt added there when you newly open HeidiSQL.

Yes, makes sense.

Also would like to suggest a 'apply last filter' to the right-click Quick Filter menu.

... which does exactly what?

I just happened to open MS Access today and see that they have icon, similar to your 'Quick Filter' icon, in the menu bar and when a filter is on, the icon button is pressed in, and when pressed out, it disabled the filter.

Please look at the right-click menu when you're in the datagrid. There is a menuitem "Filter" which does nearly exactly what you meant: Just click it, write or select a where-clause from the dropdown and press F9.

Well i would suggest that it be put next to the 'records total' beside the db name and table name. So it may look like 'XXX records total : XXX filtered records'. And if so, possibly having a clear filter button next to it.

At the top of the datagrid you see a label with the text

db.table: XX records total, XX matching to filter, limited to XX"

But yes, a "Disable filter" button or link would be good there.

... Also possibly the column arrangement also could be saved

Yes, I already had the same idea: Storing column-widths on a per table basis.

You know that 'Confirm' dialog that apears everytime you try and change the ordering of fields in a table, please add a 'dont show me this again' checkbox because it can get a bit annoying.

yes....

... making a new tab called 'Data Form'.

... and yes. Both makes sense.

To be honest, I'm not able to keep track of all suggestions here in the forum. I wonder if you could post some of your ideas at the feature tracker? http://rfe.heidisql.com/

Also would like to suggest a 'apply last filter' to the right-click Quick Filter menu.

... which does exactly what?

Well i was thinking that if i have a filter on, then i right-click and drop the filter, i would like to able to right-click and re-enable the filter.

I just happened to open MS Access today and see that they have icon, similar to your 'Quick Filter' icon, in the menu bar and when a filter is on, the icon button is pressed in, and when pressed out, it disabled the filter.

Please look at the right-click menu when you're in the datagrid. There is a menuitem "Filter" which does nearly exactly what you meant: Just click it, write or select a where-clause from the dropdown and press F9.

Yes i am aware of the menu-item "filter" in the right-click. One major problem i had with it was that i keep the "Filter" tab at the very bottom, and i was clicking the menu-item a number of times and wondered what the hell it was doing. Of course i know what it does now, but my suggestion wasnt trying to tackle that issue. My issue here was trying to tackle the issue that i posted in the earlier post about identifying that the filter is on or not, and giving easy access to enabling/disabling it in a button. You agreed that a "Disable filter" button or link would be good there, in the other post, so i was trying to rap up as many features into that filter button.

So let me just start over and explain what i was thinking with the filter button. It would be pushed in if a filter is on. You press it and it disables the filter, you press it again and it enables the last filter that was disabled. If there is no last filter set, then it brings up the filter tab to view, as if you were clicking the right-click menu-item 'filter'. I wouldnt want to suggest that everthing related to filtering be added to this button, as i know you wouldnt consider that.

From the other post

At the top of the datagrid you see a label with the text

db.table: XX records total, XX matching to filter, limited to XX

Are you asking me if i see this, if yes, then i dont see this, but if your suggesting this, then yes i like that you added the "limited to" also to it.

Well i submitted them here, so that it would be a discussion as i dont think the feature request area has that. But i will take note of the link and submit simple feature requests there. :wink:

It would be pushed in if a filter is on. You press it and it disables the filter, you press it again and it enables the last filter that was disabled. If there is no last filter set, then it brings up the filter tab to view, as if you were clicking the right-click menu-item 'filter'.

Ah... makes more sense to me now. That idea definitely should get posted to http://rfe.heidisql.com/ .

At the top of the datagrid you see a label with the text

db.table: XX records total, XX matching to filter, limited to XX

Are you asking me if i see this, if yes, then i dont see this, but if your suggesting this, then yes i like that you added the "limited to" also to it.

Wasn't a question, it's implemented since 3.0 RC4:
- The "limited to X records" is displayed if the total number is higher than the limit you have specified via the "Limit" box at the top.
- The "X matching to filter" is displayed if the total number is not equal to the number of SQL_FOUND_ROWS
- If the filter doesn't actually filter any records out, it displays "filter matches all records"

We have to differentiate between the filter, which is a WHERE-clause, and the limit-number, which results in a LIMIT-clause.

Added the filter button idea to the request a new feature section.

Wasn't a question, it's implemented since 3.0 RC4:
- The "limited to X records" is displayed if the total number is higher than the limit you have specified via the "Limit" box at the top.
- The "X matching to filter" is displayed if the total number is not equal to the number of SQL_FOUND_ROWS
- If the filter doesn't actually filter any records out, it displays "filter matches all records"

We have to differentiate between the filter, which is a WHERE-clause, and the limit-number, which results in a LIMIT-clause.

Well i see the LIMIT-clause shows in the label but the WHERE-clause filter doesnt and thats why i suggested the below in the earlier post.

So it may look like 'XXX records total : XXX filtered records'.

To be sure: When you apply a WHERE-clause at the bottom panel, you don't see the label which says "X matching to filter"? It's located above the datagrid.

I just implemented some ideas for the CSV dialog:

http://svn.sourceforge.net/heidisql/?rev=719&view=rev

To be sure: When you apply a WHERE-clause at the bottom panel, you don't see the label which says "X matching to filter"? It's located above the datagrid.

Yes when i have a filter on in the 'Filter' bottom panel, i dont see 'X matching to filter' in the label. All i see is the 'records total'.

So you're using which HS version / revision?

Are we talking about the same label:


As I told above, the "x matching to filter" was already implemented in RC4.

I checked on final 3 [572] and the new revision [693] and here are images of what i see.

Before Filter


After Filter


SQL Filter

Well i enjoy the checkbox enum, but it has problems with keyboard accessibility. I am typing in a record and press tab to the checkbox, but the cursor stays in the last field and i cant check or uncheck the field without the mouse. I would like to be able to press the spacebar key in order to check or uncheck the field. Also if there is the ability to enable and disable this feature, it would be helpful.

On another note, i was wondering why the default value of fields are not set in the datagrid when adding a new record.

Well, the rowcount statistics are displayed the way I told, if the server version is 4.0 or above. So, I'm sure you're using a pre-4.0 server, which can't handle SQL_CALC_FOUND_ROWS. Although HS could at least display the number of retrieved rows... OK, should be fixed, but won't fix the whole problem.

Revision 726 displays "x matching to filter" or "filter matches all records" also on pre-4.0 servers:
http://heidisql.svn.sourceforge.net/viewvc/heidisql?view=rev&revision=726

But there is one situation in which that sentence is lying: If you specify a limit which is less than the count of matching rows to the WHERE clause. So when the filter results in 4 records, your limit is 3, it says: "3 matching to filter" although it should be "4 matching to filter, limited to 3". The reason for that is the unsupported SQL_CALC_FOUND_ROWS keyword. But hey, who uses 3.x servers :)

Yes i know i must be one of the few still using a 3.x server. Thanks. :wink:

Any ideas about the checkbox enum issue i stated earlier? (post 1045)

Any ideas about the checkbox enum issue i stated earlier? (post 1045)

No idea how to fix that. The grid is an external component we didn't develop. I recently tried to update that component but had to roll that back because of new introduced issues.

Btw, please shout if someone knows of a well working, opensource'd alternative for the DBGrid component in HeidiSQL. :)

So is it possible to disable the checkbox option in the datagrid as i remember old versions of heidisql which didnt have the checkbox.

So i guess as the datagrid isnt your component, then there are alot of limited options that you have when it comes to functionality with it, as i had suggested defaults values to be added to a new record in the datagrid and i was going to suggested a bug fix of when you edit text in a field and then click on another table, the edited text wont be saved (this happened to me a few times, and i have to remember to press up or down to save the changes before moving onto something else, but i guess its if its taken as a way not to save changes to a record, then that would work).

Well i just noticed this issue. Was exporting a table from mysql 4.12 to mysql 3.23 and the dialog 'waiting for remote session to execute query' would remain there trying to execute SHOW VARIABLES LIKE "character_set_connection".

on the other opened window the following was execute which resulted in an error dialog

SHOW VARIABLES LIKE "max_allowed_packet"
/*!40100 SET CHARACTER SET cp1256*/;
/* SQL Error: You have an error in your SQL syntax near '; ' at line 1 */

tried doing the opposite, from mysql 4 to 3, and the following error showed up.

SHOW VARIABLES LIKE "max_allowed_packet"
/*!40100 SET CHARACTER SET binary*/;
/* SQL Error: You have an error in your SQL syntax near '; ' at line 1 */

not sure if i submitted this before as it seems quite familiar now that i think of it. :?

You know that 'Confirm' dialog that apears everytime you try and change the ordering of fields in a table, please add a 'dont show me this again' checkbox because it can get a bit annoying. If possible, can you take this re-ordering idea to the next level, by allowing us to re-order the columns, looking similar to the columns list in the 'Import Textfile' dialog, and do one okay to confirm its rearrangement, as i remember one day when i had to see the 'Confirm' dialog 15 times trying to reorder the fields in a table some idiot created.

That was solved in rev 883 - although in a slightly different way you proposed it.

Btw, please shout if someone knows of a well working, opensource'd alternative for the DBGrid component in HeidiSQL. :)

<shout> virtual tree view </shout>
maybe with an little extra-work for the data-holding....

We already thought to use VirtualTree for that purpose as we use it now for all kinds of lists in Heidi. Would be great as VT is such a pretty and fast component. But we lack a bit of experience in using it as a database component. Also I didn't find useful examples on that yet.

yes, the component is very great. i have it actually in 3 applications. but i always use it my own (calculated) data and not directly to database.

The longer I think of that idea the more pretty and wild it seems to me... We could even replace the underlying database layer (Zeos) again with something more lightweighted - probably the mysql dataset I recently began to implement is suitable. We'll need some additional knowledge on finalizing that for usage as a TDataset descendant and some knowledge on VirtualTrees with databases. Anyone here who can help out in either of these things?