The MySQL 8.0.16 Release Notes are very
interesting and sadly not read enough. One thing that may have
escaped attention is that you no longer have to run
mysql_upgrade after updating the binaries.
Let me repeat: you no longer have to
run mysql_upgrade after updating the
binaries.
From the release notes:
Previously, after installation of a new version of MySQL, the
MySQL server automatically upgrades the data dictionary tables at
the next startup, after which the DBA is expected to invoke
mysql_upgrade manually to upgrade the system tables in the mysql
schema, as well as objects in other schemas such as the sys
schema and user schemas.
The server, starting 8.0.16, does the work previously done by
mysql_upgrade for itself. And mysql_upgrade
itself is …
As you may have noticed, MySQL 8.0.16 has been released today !
One of the major long expected feature is the support of
CHECK contraints
.
My colleague, Dave Stokes, already posted an article explaining how this works.
In this post, I wanted to show how we could take advantage of this new feature to validate JSON values.
Let’s take the following example:
So we have a collection of documents representing rates from a user on some episodes. Now, I expect that the value for the rating should be between 0 and …
[Read more]
Before MySQL 8.0.16 you could put constraint checks into your
Data Definition Language (DDL) when creating tables but the
server ignored them. There was much gnashing of teeth as
taunts of "It is not a real database" from other databases
taunted the MySQL Community.
But with 8.0.16 this has all changed. You can now have your data
constraints checked by the server. Below is an example table with
two constraints.
mysql>CREATE TABLE parts
(id int, cost decimal(5,2) not null check (cost >
0),
price
decimal(5,2) not null check (price > 1.0)
);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into parts (id,cost,price) values …
Please join Percona’s Principal Support
Engineer Sveta Smirnova as she presents Billion Goods in Few Categories: How
Histograms Save a Life?
We store data with the intention to use it: search, retrieve, group, sort, etc. To perform these actions effectively, MySQL storage engines index data and communicate statistics with the Optimizer when it compiles a query execution plan. This approach works perfectly well unless your data distribution is uneven.
Last year I worked on several tickets where data followed …
[Read more]MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.
Which convention that is the correct depends on your personal
preference and use case. Between case sensitivity and case
insensitivity, it basically boils down to whether
mydb
, MyDB
, and MYDB
should be the same identifier or three different ones. Since …
I gave a thirty minute talk at Drupalcon this week on the
features in MySQL 8.0 that would be of interest to developers and
for such a short talk (and I cut slides to allow for audience
questions) I could only cover the highlights. One attendee gently
chastised me over no including their favorite new MySQL 8.0
feature -- functional indexes.
What is a Functional Index?
The manual says MySQL 8.0.13 and higher
supports functional key parts that index expression values rather
than column or column prefix values. Use of functional key parts
enables indexing of values not stored directly in the
table.
There are some cool examples in the documentation on setting up
some functional indexes, as can seen below.
CREATE TABLE t1 (
col1 INT, col2 …
We saw yesterday that when an issue (like network splitting), it’s possible to remain with a partitioned cluster where none of the partition have quorum (majority of members). For more info read how to manage a split-brain situation.
If your read the previous article you notice the red warning about forcing the quorum. As an advice is never too much, let me write it down again here : “Be careful that the best practice is to shutdown the other nodes to avoid any kind of conflicts if they reappear during the process of forcing quorum“.
But if some network problem is happening it might not be possible to shutdown those other nodes. Would it be really bad ?
YES !
Split-Brain
Remember, we were in this situation:
We …
[Read more]Everywhere I go to present MySQL InnoDB Cluster, during the demo of creating a cluster, many people doesn’t understand why when I’ve 2 members, my cluster is not yet tolerant to any failure.
Indeed when you create a MySQL InnoDB Cluster, as soon as you have added your second instance, you can see in the status:
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
Quorum
Why is that ? It’s because, to be part of primary partition (the partition that holds the service, the one having a Primary-Master in Single Primary Mode, the default mode), your partition must reach the majority of nodes (quorum). In MySQL InnoDB Cluster (and many other cluster solutions), to achieve quorum, the amount of members in a partition must be > (bigger) than 50%.
So when we have 2 nodes, if there is a network issue between the …
[Read more]Please join Percona’s Bug Analyst, Lalit Choudhary as he presents his talk MySQL 8.0 Architecture and Enhancement on Tuesday, April 9th, 2019 at 6:00 AM PDT (UTC-7) / 9:00 AM EDT (UTC-4).
The release of MySQL 8.0 offers much more to users compared to previous releases. There are major changes in architecture as well as adding differentiating features, and improvements to manage the database more efficiently.
In our talk we will go through, MySQL 8.0 …
[Read more]
The last blog entry was very popular and there were
lots of requests for some introductory information on the spatial
data types.
Well Known Text Or Binary
I am going to use the GEOMETRY data type over POINT, LINESTRING,
or POLYGON as it can store any of those three while the other
three can only contain data matching their name (so POINT can
holds only point data, etc.). The values are stored in an
internal geometry format but it takes wither WKT or WKB formatted
data.
Those are Well-Known Text (WKT) or Well-Known Binary (WKB)
formats repectively. I am hoping most of your are better with
text than binary so the following examples demonstrate how to
insert geometry values into a table by converting WKT values to
internal geometry format.
So let us start with a …