Bug: Editor Auto-Inserts Date/Time into Fields

djdjohnson posted 6 years ago in Feature discussion
I've mentioned this before, and the concept that this is a bug was rejected, but it caused my company a HUGE problem today...

When editing data in a table, if you tab into a DATE, TIME, or DATETIME field it automatically inserts the current date, time, or date/time if the field is empty. HeidiSQL ought to leave the field blank.

Why is this such a problem? My company produces point-of-sale software and two days ago somebody edited the sales tax rate for our stores for Canada, and accidentally added an ending date to the sales tax rate while tabbing through the fields of the table in HeidiSQL, causing all of the stores in the country to stop charging sales tax until we detected the problem and got it fixed.

My company is potentially liable for all of the money that should have been collected for tax yesterday. HUGE problem! In my opinion, HeidiSQL should not be adding any data that the user hasn't entered.
ansgar posted 6 years ago
Do you recall which issue id that was? If you find it I will see if I can add some code to the inplace editor which reverts the current value to NULL if no keyboard or mouse input was done.
jfalch posted 6 years ago
In my opinion, if you want data entry for your commercial system that has checks to prevent such errors etc, get a custom data entry system for it, and do not rely on an open-source general tool to behave exactly as your company needs it. I, for one, regularly enter long lists of expenses into mysql tables, and do much like the "default to current date" feature, and would really miss it.
djdjohnson posted 6 years ago
The previous request was quite some time ago, and basically I asked for the feature, and was told that it didn't seem necessary. I don't have the ID number.

In my company, the change is very necessary. In discussing with one of my employees, we've had this same sort of problem happen many, many times. It is especially bad in columns which appear past the right edge of the window because when tabbing the current date is inserted before the field is ever even visible.
djdjohnson posted 6 years ago
My company is still in growth mode and is very much understaffed and we don't have the time to write a web front end for every little part of our system that needs to be configured, especially those that are only touched once every few years. There are hundreds of tables. Why waste thousands of dollars to develop a custom page for something we almost never touch? Eventually, maybe... but when we have far more important things to do it doesn't make any sense to use those resources for something so trivial.

MySQL has a feature to automatically insert the current date into a date field when no value has been provided. It makes more sense to me to define that in the database than to assume that every date field needs to default to the current date. There are many types of dates that aren't based on the current date... fields like a birth date, or scheduling any event that happens in the future, for example, should never default to the current date, because it never going to be right. We in particular use a NULL value on a Start or End date field to indicate that they are open ended. HeidiSQL makes it difficult to maintain those NULL values if the tables are edited in any way.

In my opinion it's best to let the table designer decide when a date should default to the current date, not have the front end tool make that decision. Adding data that the user never intended to be there would, at least in my mind, constitute a bug.

If the table specifies that the current date should be the default, sure, let HeidiSQL populate it for me. But when there is no default value, and especially when the default value is NULL, it probably shouldn't be making assumptions about what data I want there.
jclaussen posted 6 years ago
I would have to agree with djdjohnson on this one. While using HeidiSQL I, too, have accidentally entered the current date without even realizing it. When I notice I've done it, it's no big deal and I can just nullify it. Unfortunately, I have entered a date unwittingly before when I've randomly clicked into the Heidi window to begin looking in the database again after performing some other task. The tabbing over has happened to me a few times as well.

I feel the appropriate fix would be to leave the cell NULL, or in whatever state it is in. If there is already a date in the field and one clicks in it to edit it, it does not suddenly jump to the current date because there is already data inputted. I submit that NULL is just as valid of data in a date field as any actual date. It records the valuable fact that no date has been entered yet. It does not mean it needs a date at this point.
ansgar posted 6 years ago
Btw, the cause of having the current date in a cell where you have tabbed into is that a) tabbing automatically starts the inplace editor and b) the date editor does not work with an empty value (GUI wise - if it would be empty, the mask editor would at least automatically add some " - - : : "). My idea is to offer the current date when it's NULL but to revert that back to NULL when leaving the inplace editor and the user did not touch the value.

My initial thought was that the user should not just be lame and just tab into that cell if he doesn't want to enter something. Even if that accidentally happens, it seems a bit odd that in a critical situation you don't see that there is a value now and that you just leave it in the cell, although you know that your application does critical things when there is a (wrong) value.

However, I think I can change that with the above mentioned approach, so HeidiSQL gets less auto-magicically here.
djdjohnson posted 6 years ago
The " - - : : " could be treated as a NULL value, could it not? If this was used for the default value for the masked edit it would be easy to visually see that the edit field is empty.

It would be more confusing than the current behavior to have the current date show up when tabbing in then have it disappear when tabbing out without modifying. If you have to fill in something, a value of "0000-00-00 00:00:00" would be much better than the current date/time.

The reality for us is that it is far, far faster to use the arrow and tab keys to navigate through a table than it is to use the mouse to select fields. So if you're editing column A, and need to edit column E, but column C is a date field, tabbing from A to E shouldn't add anything to C as you tab through it.

Most masked edit controls should give you the flexibility of having blank characters without giving an error. Personally I'm not a fan of masked edits... they get in the way more than they help in most cases. I've found it better to start with a blank field and parse the input after tabbing away from the control. Or if there is existing data, the format characters would be there already for them.

But bottom line is that we can't have HeidiSQL inserting data where we don't want data.
djdjohnson posted 6 years ago
Still not willing to help me on this?

This is a serious problem. I'm surprised it is being dealt with so lightly.
ansgar posted 6 years ago

Still not willing to help me on this?

Please stop being a pane in the ass. I already said above I have an idea how to change that. Please read it.

However, you have finally filed a bug report. Thanks!
djdjohnson posted 6 years ago
Sorry, I'm not trying to be difficult. This issue has caused my company a great deal of frustration. And it has put us in potentially very serious liability situations a few times now.

Thanks for your help.
kthanid posted 6 years ago
@djdjohnson: You really need to stop heaping your own personal business issues on the frontend software you're using. There's a fine line between a sensible bug report or polite feature request and being an entitled pain in the butt. Do you sit in Microsoft forums and demand similar changes to meet your specific business needs in Word and Excel? I don't love some of the new changes Google has made to the Gmail interface, but I'm certainly not sitting around bellyaching like they owe me a favor for some reason.

In this particular request, I don't even necessarily disagree with your request, but you certainly have a funny way of going about making it. The open source development community does not simply exist to modify their software to meet your specific business needs; just saying.

"it caused my company a HUGE problem today"

Seriously? Please take some responsibility for yourself and your own problems. Whether or not it makes the most sense for a date field in the GUI to default to the current date or NULL depends on how that software works, you're seriously going to help the blame on the GUI? It's not as if it's some hidden secret or that it isn't obvious when it's happening.
djdjohnson posted 6 years ago
Dude, I said I'm sorry!

I certainly didn't intend to come across as ungrateful or as if I'm entitled to the change. The request stemmed from a particularly frustrating experience and re-reading my posts some of that emotion came through.

I'm not blaming the software for our issue. Yes, it was a mistake by one of the employees of my company that put us in the situation we're in, and we're willing to accept that, but the way the software is setup now certainly didn't help. It currently makes it very difficult to leave a NULL field as NULL while editing, and you have to pay really close attention to even notice that it has made a change. Very easy to overlook.

You certainly don't expect Word or Excel to modify your data without asking it to. I'm just asking that HeidiSQL be modified so it behaves the way that other software does in that regard -- leaving the data you're editing intact. It doesn't seem to me like an unreasonable request.

I'll be very grateful if the anse makes the change, and that will follow with another nice donation to the project, but if he doesn't I'll download the source code and be happy to make it myself. It wouldn't be the first time -- I modified version 3.2 very heavily years ago with many features that have since been added by anse to newer versions. I just don't like forking away from the main project for something that could probably be useful to others.

I only brought it up here because I'm sure I'm not the only one that has had a problem with this. If it was just me (or my company) I would just have quietly downloaded the source, made the change myself, and never said anything.

But I'll thank you to leave me alone from this point. I've apologized a couple times now... please let it go.
djdjohnson posted 6 years ago
THANK YOU for fixing this!!!
ansgar posted 6 years ago
Thank you for your donation! Donations are never required but always welcome.

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