M
MySQL Server Team Blog
mysqlserverteam.com · Company blog · on HN since 2015
The MySQL Server team at Oracle posts about new features and changes in MySQL 8, such as instant column adds and utf8mb4.
What HN saysCommenters link it as the authoritative source on MySQL 8 features like instant add column, SKIP LOCKED, and character set defaults.
48HN people shared it
21linked it in comments
43stories submitted
694story points
5 years agolast shared
2015people per year2026
Most-shared posts
- 1MySQL 8 is getting INSTANT “add column”5 people · 5 points · first shared 2018 · discussion on HN
- 2Sushi = Beer? An introduction of UTF8mb4 support in MySQL 8.04 people · 1 points · first shared 2017 · discussion on HN
- 3Mysql 8 0 when to use utf8mb3 over utf8mb43 people · first shared 2017
- 4Mysql 8 0 1 using skip locked and nowait to handle hot rows3 people · first shared 2017
- 5MySQL 8.0: Retiring Support for the Query Cache2 people · 144 points · first shared 2017 · 83 comments on HN
- 6MySQL 8.0.0 milestone release2 people · 51 points · first shared 2016 · 20 comments on HN
- 7Data Masking in MySQL2 people · 4 points · first shared 2018 · 1 comments on HN
- 8MySQL InnoDB Cluster – MySQL HA: Out-Of-Box, Easy to Use High Availability2 people · 4 points · first shared 2016 · discussion on HN
- 9MySQL JSON Support announced2 people · 2 points · first shared 2015 · 1 comments on HN
- 10UUIDs support in MySQL 8.02 people · 2 points · first shared 2016 · 1 comments on HN
- 11MySQL 8.0: Retiring support for libmysqld2 people · 2 points · first shared 2016 · discussion on HN
- 12MySQL 8.0.2: Introducing Window Functions2 people · 2 points · first shared 2017 · discussion on HN
- 13Indexing json documents via virtual columns2 people · first shared 2016
- 14Mysql 8 0 labs recursive common table expressions in mysql ctes2 people · first shared 2016
- 15New defaults in mysql 8 02 people · first shared 2018
What HN people wrote when they linked it
dub · 2019-01-07
While it's clear that the author of the linked article isn't familiar with MySQL administration, it's worth noting that the most recent versions of MySQL finally support instant column adds: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-…
That won't save us from needing extra tooling like pt-online-schema-change or gh-ost for other types of schema changes in high availability environments, but it's a step in the right direction.
morgo · 2018-06-14
Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4…
The default for MySQL 8.0 (current GA) is utf8mb4. I also recommend reading this introduction post here: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-…
ngrilly · 2015-04-18
I understand this is or will be a part of WebScaleSQL [1]. But WebScaleSQL does not provide binaries. You have to build it yourself.
An alternative is the JSON Labs Release of MySQL 5.7.7 [2]. The differences are explained in the slides, at the end.
Another great alternative is using PostgreSQL that supports JSON columns stored in a binary format since version 9.4 [3].
That said, I really like the approach of Facebook with DocStore, because of its use of "document paths", instead of functions.
[1] http://webscalesql.org/
[2] http://mysqlserverteam.com/json-labs-release-overview/
[3] http://www.postgresql.org/docs/9.4/static/datatype-json.html
evanelias · 2021-08-06
MySQL 8 supports FOR UPDATE SKIP LOCKED [1], but does not have a RETURNING clause.
MariaDB 10.5+ supports a RETURNING clause, and 10.6 now has FOR UPDATE SKIP LOCKED. This is quite new though (10.6 went GA a month ago) so there might be some wrinkles.
[1] https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-t…
pizza234 · 2021-02-15
Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data.
¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-…
²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.h…
Thaxll · 2021-02-12
MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-…
paulez · 2020-09-16
That's a long standing problem with MySQL, DDL statements (ALTER TABLE and such) where not atomic until version 8.0. This required some serious work on InnoDB as you can read some details at https://mysqlserverteam.com/atomic-ddl-in-mysql-8-0/
donalhunt · 2019-11-05
previous discussion: https://news.ycombinator.com/item?id=17310690
additional context (which the OP didn't reference):
https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4…
https://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support…
https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb3.html
hint: "Exactly the same set of characters is available in utf8mb3 and ucs2."
cwyers · 2019-07-16
MySQL now has window functions:
https://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/
And MySQL has strict mode, which I believe is enabled by default on newer versions but available in any 5.x version:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict
johannes1234321 · 2019-07-03
I did quite some work with MySQL+srid, mostly with OpenStreetMap data, but also other, but I'm no expert there. Maybe your specific SRID wasn't supported? (While a huge number is supported)
Norvald has a few slide sets, which might be good addition to docs:
https://www.slideshare.net/mobile/NorvaldRyeng
There are also blogs on https://mysqlserverteam.com/category/gis/
Without details about your experiments it's hard to find the issue ...
ccleve · 2019-01-21
Don't do this. MySQL removed result caching because it caused problems. https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-c…
Arie · 2018-06-14
This is supposedly fixed in databases created with MySQL 5.7 and up.
> MySQL 5.7 (2015) added some optimizations such as a variable length sort buffer, and also changed InnoDB’s default row format to DYNAMIC. This allows for indexes on VARCHAR(255) with utf8mb4; something that made migrations more difficult prior.
https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4…
irishsultan · 2018-04-25
The table is about dependency checking for those constructs.
E.g. SELECT countries.code, countries.name, count(*) FROM countries JOIN countries_languages as co_lang on co_lang.country_code = countries.code GROUP BY countries.code
This isn't supported by Postgres, because countries.name is not in the group by clause, on the other hand it does make sense, because code will be unique in the countries table, so there will always be only one name for a given code.
(see https://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-reco… which is also linked from the original article)
talawahdotnet · 2018-04-19
One of the most interesting new features to me is full support of MySQL 8.0 as a JSON based document store using the new DevAPI[1].
There is a separate blog post highlighting it: https://mysqlserverteam.com/mysql-8-0-announcing-ga-of-the-mysql-docum…
1. https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduct…
lstamour · 2018-04-11
Some things are improving — for example: https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html
And https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
Previously on HN: https://news.ycombinator.com/item?id=5122299
Personally, I only use MySQL and derivatives where I have to (basically WordPress.)
chrisan · 2017-09-05
Has anyone used virtual columns with MySQL for a direct json-index substitute?
http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns…
misframer · 2017-07-01
MySQL also has SKIP LOCKED from 8.0.1 [0].
[0] http://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to…
lathiat · 2017-04-09
The good news is they are changing the defaults in 5.8:
http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-…
rwultsch · 2016-10-03
Postgres has had CTE for a while, but not that long. MySQL 8.0 (the next version) plans to them http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expre…
spudlyo · 2015-10-19
From my reading of the article, a lot of the performance improvements come getting rid of the mutex in front of their hash based metadata cache by using a lock free hashing algo. These changes originally came from Oracle's MySQL 5.7 work[1]. Looks like MariaDB merged the lock-free hashing stuff into their version, and applied it to the table definition hash which also suffered from lock contention.
These are changes that all architectures can benefit from.
[1]: http://mysqlserverteam.com/removing-scalability-bottlenecks-in-the-met…
hartzell · 2020-02-06
[yeah, not a frequent poster, apologies for the markdown markup]
Thanks for sharing this, it's been fun to play with. I've been poking around a bit, looks like `skip locked` was added back in v9.5.
For those who are interested in how it works, here are a few links that might be useful:
- [What is SKIP LOCKED for in PostgreSQL 9.5?][2ndquadrant]
- [Postgres 9.5 feature highlight - SKIP LOCKED for row-level locking][otacoo]
- [The Skip Locked feature in Postgres 9.5][pgcasts]
The 2ndquadrant post points out that Oracle and SQL Server have similar functionality. Mysql didn't at the time, but [seems to have added it in 8.0.1][mysql]
[2ndquadrant]: https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-…
[mysql]: https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-t…
[otacoo]: https://web.archive.org/web/20160626090321/http://michael.otacoo.com/p…
[pgcasts]: https://www.pgcasts.com/episodes/the-skip-locked-feature-in-postgres-9…