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

101–110 of 121 posts

Re: Time for a WTF MySQL Moment

#101

Post by lmm »

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

> - The default 'latin1' character set is in fact cp1252, not ISO-8859-1, meaning it contains the extra characters in the Windows codepage.

Oh you sweet summer child. No it isn't. It's somewhat like Windows CP1252, but it also defines 8 other extra characters that are not in cp1252.

Re: Time for a WTF MySQL Moment

#102

Post by Dylan16807 »

toast0 wrote: post #53
Sebb767 wrote: post #18

Earlier quoted context omitted.

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).

Having a type mean different things on different versions of the database is not good. If you want a new type that does new things, it should have a new name. If the old type is so bad, you could make an sql mode to blacklist it. Yes, that leaves a legacy of why should I use TIME2 or whatever instead of TIME, etc. That's the downfall of having a successful project over a long time frame and not having designed it per…

It would be a huge change in behavior to fix utf8.

Changing the limit from 838 to 999 wouldn't.

Re: Time for a WTF MySQL Moment

#103

Post by lmm »

chrisan wrote: post #95
TedShiller wrote: post #51

I gave up on MySQL a long time ago, when I realized that I had to activate special types of settings just to make Unicode characters work in tables. In Postgres, it just works out of the box.

Must have been more than 10 years ago when utf8mb4 was added where you don't have to activate any kind of special settings. I was using MSSQL prior to 2010 so I have no idea of MySQL unicode handling before that

> Must have been more than 10 years ago when utf8mb4 was added where you don't have to activate any kind of special settings.

Less than 10 years ago; MySQL 5.5 went GA in December 2010.

Re: Time for a WTF MySQL Moment

#105

Post by forcemajeure »

sfilargi wrote: post #63

“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

Fear the unknown unknowns!

Re: Time for a WTF MySQL Moment

#106

Post by nullsense »

sfilargi wrote: post #63

“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

I always catch myself when I'm saying it and try to make sense of the cognitive dissonance of saying nothing will break and being somewhat convinced that something, somewhere will.

Re: Time for a WTF MySQL Moment

#107

Post by nitramt »

That's yet another incompatibility with the ISO/ANSI SQL specification. In the specification, the TIME type is defined as containing HOUR, MINUTE and SECOND fields, representing the "hour within day", "minute within hour" and "second within minute" values, respectively, so, the valid range for that type supposed to be "00:00:00:00.00000..." to "23:59:59.99999...". It's not intended to represent an interval, although that seems to be the intended semantics for MySQL's TIME type:

(from https://dev.mysql.com/doc/refman/8.0/en/time.html):

> but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

For representing temporal intervals, the specification defines two kinds of INTERVAL types (year-month and day-time). Year-month intervals can represent intervals in terms of years, months, or a combination of years and months. Similarly, day-time interval, can represent intervals in terms of days, hours, minutes or seconds, or combinations of them (e.g, days+hours, days+hours+minutes, hours+minutes, etc.)

As a sidenote, the TIME and DATE types are related to the TIMESTAMP type in that TIMESTAMP can be thought of as combination of a DATE part (year, month, day) and a TIME (hour, minute, second) part.

Re: Time for a WTF MySQL Moment

#108

Post by sfilargi »

sfilargi wrote: post #63

“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

everytime* not everyone

And yes, I have done it so many times myself too

Re: Time for a WTF MySQL Moment

#109

Post by zeroimpl »

wnoise wrote: post #85
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}}}}}}`…

All of the array types are basically the same. The docs actually do mention this, but only in passing as a current limitation. https://www.postgresql.org/docs/13/arrays.html > The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or num…

The other key to understanding this is that multidimensional arrays in Postgres are not simply arrays of arrays like typical programming languages. That’s why you can’t do things like array_agg on an array column.

Re: Time for a WTF MySQL Moment

#110

Post by jarym »

Florin_Andrei wrote: post #75
jarym wrote: post #2

MySQL has come a long long way indeed but not enough to make me turn away from Postgres.

I used to see literally this same comment as far back as 10 years ago, maybe more. Just saying - I have no horse in this race.

Well I’m not wed to anything - maybe sometime we’ll see an article about reasons TO switch.
Post reply on HN