Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Time for a WTF MySQL Moment

gbl08ma.com

Post reply on HN
Print view View original

61–70 of 121 posts

Re: Time for a WTF MySQL Moment

#61

Post by jakobdabo »

xeeeeeeeeeeenu wrote: post #24

A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt

Another one: if you happen to keep big numerical values in a VARCHAR field, be careful, the engine will convert them to DOUBLE when comparing to a big integer, and interesting things will happen. For example, "SELECT id, clmn FROM tbl WHERE clmn = 999999999999999999" may return records where `clmn` is '999999999999999999', also where `clmn` is, for example, '999999999999999998' (because those big integers are too big for DOUBLE and, when converted, they have the same representation).

So the correct query is "SELECT id, clmn FROM tbl WHERE clmn = '999999999999999999'"

Re: Time for a WTF MySQL Moment

#62

Post by speeder »

kochthesecond wrote: post #42
xeeeeeeeeeeenu wrote: post #24

A list of MySQL WTFs: https://grinnz.com/stuff/lolmysql.txt

Nice These three points has made me raving mad from working with mysql: - The default 'latin1' character set is in fact cp1252, not ISO-8859-1, meaning it contains the extra characters in the Windows codepage. 'latin2', however, is ISO-8859-2. - The 'utf8' character set is limited to unicode characters that encode to 1-3 bytes in UTF-8. 'utf8mb4' was added in MySQL 5.5.3 and supports up to 4-byte encoded characters.…

I am currently trying to fix a program that was made by a person that didn't knew those details of MySQL...

Most weirdly, the fact that the default collation is SWEDISH.

It is a complete freak show, the users kinda got used to it, butchering our language (portuguese) to use only characters valid in english, hoping MySQL won't barf spetacularly on them.

Re: Time for a WTF MySQL Moment

#63

Post by sfilargi »

“I am struggling to imagine the circumstances where ..... can break ....”

If only I had a penny for everyone I heard this argument and we ended up breaking regression tests or something really obscure in the qa or customer setup

Re: Time for a WTF MySQL Moment

#64

Post by Lvl999Noob »

falcolas wrote: post #33
masklinn wrote: post #30

Earlier quoted context omitted.

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 spa…

> I prefer PostgreSQL for reasons that will soon become self-evident This outlines his expectations, and his biases, quite clearly. Also see: the title of the article.

I don't think there were any biases here. The research into the docs came first, and the title seems like the conclusion rather than the hypothesis.

Besides, I don't use databases all that much and I don't write blog posts either, but if I did, and I came across this issue, this might be the exact article I would write (except the liking Postgres thing).

Re: Time for a WTF MySQL Moment

#66

Post by ThePadawan »

eyelidlessness wrote: post #60
eyelidlessness wrote: post #56

Earlier quoted context omitted.

I am also a big fan of Postgres, and tend to have a bit of fun picking on MySQL having been scarred by it in a past life. But since we're picking at Postgres warts... One which bit me recently, and is still utterly baffling to me, is that a column defined as an array type will accept values of that array's type in any number of dimensions greater than that specified for the column. In other words, `{{{{{{text}}}}}}`…

Also while we're picking on other DBs, another fun WTF I've encountered (this time in an external system): SQL Server stores timestamps to ~1/300th of a second resolution. This[1] StackOverflow question describes different behavior than I saw (it rounded differently), so apparently it's not even consistent. I'd assume across versions? IDK, never had time to look too deeply into this one either. [1]: https://stackover…

Note that only happens using the DATETIME data type.

As long as I remember, the documentation for DATETIME [0] has had a disclaimer at the top to not use it, but use DATETIME2 instead.

Of course, that doesn't excuse older systems, but this is an issue that can be avoided for new work.

[0] https://docs.microsoft.com/en-us/sql/t-sql/data-types/dateti...

Re: Time for a WTF MySQL Moment

#67

Post by WkndTriathlete »

cesarb wrote: post #55
saturn_vk wrote: post #38

Earlier quoted context omitted.

That's not really a problem though, at least from what i understand. even on 32bit linux systems, time_t is still 64bit. on windows it also seems to be 64bit

> even on 32bit linux systems, time_t is still 64bit No, on 32-bit Linux systems, time_t is 32 bits (except perhaps on newer ports like 32-bit RISC-V).

https://stackoverflow.com/a/60709400/571787 states otherwise with recent Linux kernels + glibcs.

Re: Time for a WTF MySQL Moment

#68

Post by Doctor_Fegg »

kstrauser wrote: post #54
simias wrote: post #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 us…

I'll comment: it absolutely didn't make sense back then, either. We didn't use BCD for pretty much anything in '95. If anything, all timestamps were 32 bit signed ints. Edit: plenty of things still stored dates as strings where the emphasis of the app was on displaying information. Int and float types carried the day whenever any kind of math was going to be used, or when you wanted to output the data in multiple for…

BCD was outdated when I got my first Amstrad CPC (1984). The assembly language textbooks all said “this is a weird holdover from the 8080, don’t bother with it”.

Re: Time for a WTF MySQL Moment

#69

Post by karmakaze »

jnwatson wrote: post #16
josefx wrote: post #7

> 1 bit sign (1= non-negative, 0= negative) First time I ever saw a number where the leading sign bit has to be set to 1 to indicate non-negative.

Sorts better that way.

It would binary sort weird with negatives Also not Offset binary which uses 0111 for -1 rather than MySQL's 0001.
Post reply on HN