Showing entries 11 to 20 of 206
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: MySQL 8.0 (reset)
Using the new MySQL Shell Reporting Framework to monitor InnoDB Cluster

With MySQL Shell 8.0.16, a new very interesting feature was released: the Reporting Framework.

Jesper already blogged about it and I recommend you to read his articles if you are interested in writing your own report:

  • https://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/
  • https://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-defined-reports/

I this post, I will show you one user-defined report that can be used to monitor your MySQL InnoDB Cluster / Group Replication.

Preparation

Before being able to use the report, you need to download 2 files. The first one is the …

[Read more]
MySQL Server 8.0.16: Thanks for the Contributions

Tweet

MySQL 8.0.16 was released last week and includes many great features including support for CHECK constraints and upgrades without the need of mysql_upgrade. As usual there are also several contributions from the community. These are the ones, I would like to highlight in this blog to say “thank you for the contributions”.

Facebook has contributed with another two patches and so has Daniel Black. Additionally, there are patches from Yuhui Wang, Wei Zhao, Yan Huang, and Dirkjan Bussink. The contributions are:

  • mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR environment variable, which may be set to specify a unique-IDs directory to be used as the common location …
[Read more]
What configuration settings did I change on my MySQL Server ?

This post is just a reminder on how to find which settings have been set on MySQL Server.

If you have modified some settings from a configuration file or during runtime (persisted or not), these two queries will show you what are the values and how they were set. Even if the value is the same as the default (COMPILED) in MySQL, if you have set it somewhere you will be able to see where you did it.

Global Variables

First, let’s list all the GLOBAL variables that we have configured in our server:

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED';

This is an example of the output:

Session Variables

And now the same query for the session variables:

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, …
[Read more]
Bye Bye to mysql_upgrade, change to skip_grant_tables, and One Year of MySQL 8.0 GA

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 …

[Read more]
MySQL 8.0.16: how to validate JSON values in NoSQL with check constraint

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]
MySQL 8.0.16 Check Constraints

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 …

[Read more]
Watch Webinar: Billion Goods in Few Categories – How Histograms Save a Life?


Please join Percona’s Principal Support Engineer Sveta Smirnova as she presents Billion Goods in Few Categories: How Histograms Save a Life?

Watch the Recorded Webinar

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]
Install MySQL 8 on Linux with lower_case_table_names = 1

Tweet

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 …

[Read more]
Putting the 'Fun' In Functional Indexes

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 …

[Read more]
MySQL InnoDB Cluster : avoid split-brain while forcing quorum

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]
Showing entries 11 to 20 of 206
« 10 Newer Entries | 10 Older Entries »