Live data from Hacker News

Time for a WTF MySQL Moment

gbl08ma.com

21–30 of 121 posts

Re: Time for a WTF MySQL Moment

#21
> This format is even less wieldy than the current one, requiring multiplication and division to do basically anything with it, except string formatting and parsing – once again showing that MySQL places too much value on string IO and not so much on having types that are convenient for internal operations and non-string-based protocols.

Not necessarily an odd choice in the Olden Days, after all BCD representation used to be pretty popular. By modern standards it's insane, but at a time where binary to decimal conversions could be a serious performance concern it might have made sense. For instance if you had a date in "hours, minutes, seconds" and wanted to add or subtract one of these TIME values, you could do it without a single multiply or divide.

Now I was 8 when MySQL first released in 1995, so I can't really comment on whether that choice really made sense back then. 1995 does seem a bit late for BCD shenanigans, but maybe they based their design on existing applications and de-facto standards that could easily go back to the 80's.

Re: Time for a WTF MySQL Moment

#23
post #18
post #8

Earlier quoted context omitted.

> "this is highly unexpected behaviour" The behavior in question is having an upper and lower bound on a time interval. This strikes me as highly expected behavior. The maximum on the interval is lower than the author expected; and reading the docs quickly cleared up what the interval maximum is. The entire rant boils down to "MySQL's choice to keep backwards compatibility is stupid, because I think this interval lim…

I agree it's not a necessary change. But, on the other hand, how much compatibility would it really break? I cannot imagine much applications are dependent on MySQL throwing an error at 800-odd hours (and it would arguably be a very strange design decision).

> how much compatibility would it really break

Probably only a few applications; but that's still too many.

And backwards compatibility does matter. A change here means that some few dozen developers have to now troubleshoot a previously-stable application which now fails silently in odd corner cases.

Re: Time for a WTF MySQL Moment

#25
post #8

Earlier quoted context omitted.

> "this is highly unexpected behaviour" The behavior in question is having an upper and lower bound on a time interval. This strikes me as highly expected behavior. The maximum on the interval is lower than the author expected; and reading the docs quickly cleared up what the interval maximum is. The entire rant boils down to "MySQL's choice to keep backwards compatibility is stupid, because I think this interval lim…

The unforgivable thing here isn't the anachronism -- it's the lack of a suitable replacement. If you want a full i64 worth of duration at a reasonable resolution, you're on your own.

Reading the article it seems like the core issue is:

>To make matters worse, it appears that the most popular EF Core MySQL provider maps .NET’s TimeSpan to TIME by default, despite the fact that TimeSpan can contain intervals in the dozens of millennia (it uses a 64 bit integer and has 10-8 s precision)

It's this mapping is bogus. If the ranges don't match TimeSpans should be mapped to something else.

MySQL's BIGINTs are 64bits, so that might be a more reasonable target. Of course you lose some "typing" information in the schema, but it's probably worth it.

But I suppose it would make sense for MySQL to provide a "BIGTIME" alias for this purpose, and deprecate the old one maybe.

Re: Time for a WTF MySQL Moment

#27
post #8

Earlier quoted context omitted.

> "this is highly unexpected behaviour" The behavior in question is having an upper and lower bound on a time interval. This strikes me as highly expected behavior. The maximum on the interval is lower than the author expected; and reading the docs quickly cleared up what the interval maximum is. The entire rant boils down to "MySQL's choice to keep backwards compatibility is stupid, because I think this interval lim…

The unforgivable thing here isn't the anachronism -- it's the lack of a suitable replacement. If you want a full i64 worth of duration at a reasonable resolution, you're on your own.

It's a type with a minimum and maximum. How in the world is this an "unforgivable" sin? Shall I rail on C for the `size_t` type only having 16 bits?

> lack of a suitable replacement

MySQL's various string "alternatives" have proven how stupid having multiple types with different limits really can be.

Re: Time for a WTF MySQL Moment

#29
post #14
post #11

Earlier quoted context omitted.

Edit. Fuck HN.

MySQL not having a proper type to express time spans seems like a fault to me, and "poor design". Of course you can just use an integer for it, but that is a slippery slope, in the end you'll find that you can use strings or byte arrays for everything and you end up with no type system at all. The surprise here is not that the type has limits but that they are so awkward and that there is no better strongly-typed alt…

A missing feature is not the same thing as poor design. If you need time spans in your project it could be a fault, but every product has missing features and choosing a product that is missing features you want might be a poor decision - depending upon other tradeoffs you're making.

Re: Time for a WTF MySQL Moment

#30
post #6

Edit. Fuck HN.

The issue is that the authors expectations didn't match with reality, because he hadn't yet read the docs. The moment the author wrote "I normally use PostgreSQL" I knew that this would devolve into a "MySQL Bad" even if it's well defined and documented behavior.

The author had no specific expectations. The author received a report of an error, investigated, found that MySQL's TIME did not match EF Core's expectations for a timespan (hence causing the error):

> for a brief moment I assumed the incorrection in this value was the hundreds of hours, as one could reasonably assume that maybe TIME values were capped at 24 hours, or that a different syntax was needed for values spanning multiple days

And then it turned out that wasn't the case and they basically got nerd-sniped by the issue into finding out why the type had such weird bounds.

Post reply on HN