Showing entries 1 to 10 of 34
10 Older Entries »
Displaying posts with tag: 8.0 (reset)
MySQL 8.0 Data Dictionary Tables… and why they should stay protected

With MySQL 8.0, one key feature is the new Data Dictionary.

The system tables that were previously in MyISAM are now replaced by new protected ones in the DD.

My friend Giuseppe already explained how you could see those tables using sandbox  and he also warned you that you should not mess up with them in this post too.

I’ll explain you how you can see those tables and their actual content. But will also explain why we decided to protect them and why it should stay like that.

DD protected internal tables list

The easiest way …

[Read more]
Where does my MySQL configuration variable value come from ?

As you may already know, there are many different places where a MySQL configuration variables can be initialized.

In MySQL 8.0, we added in performance_schema a table allowing you to easily find where a variable was defined.

Let’s check this in action with max_connections for example.

I started mysqld and now I check the value of max_connections:

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

We can also use the performance.schema table called variables_info to get some more details about it:

mysql> SELECT t1.*, VARIABLE_VALUE 
       FROM performance_schema.variables_info t1 
       JOIN performance_schema.global_variables t2 
         ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
      WHERE …
[Read more]
MySQL Clients and emojis !

As you probably already know, MySQL 8.0 supports Unicode 9.0 and our default charset moved from latin1 to utf8mb4.

Recently, Gabi (certainly working on an amazing presentation for PHPWorld) asked me something about how the client deals with the emojis while checking Morgo’s post about 8.0 RC1.

I didn’t know the answer and had to play with it, here is the outcome of these tests.

First let’s try to use the standard MySQL client and paste the dophin emoji from emojipedia:

[Read more]
Fun with Bugs #57 - On MySQL Bug Reports I am Subscribed to, Part I

I've decided to stop reviewing MySQL Release Notes in this series, but it does not mean that I am not interested in MySQL bugs any more. At the moment I am subscribed to 91 active MySQL bugs reported by other MySQL users, and in this blog post I am going to present 15 of them, the most recently reported ones. I'd really want to see them fixed or at least properly processed as soon as possible.

In some cases I am going to add my speculations on how the bug had better be handled, or maybe highlight some important details about it. It is not my job any more to process/"verify" any community bug reports for any kind of MySQL, but I did that for many years and I've spent more than 5 years "on the other side", being a member of Community, so in some cases I let myself to share some strong opinion on what may be done …

[Read more]
MySQL 8.0 Atomic DDL – behavior change

In MySQL 8.0, we have now a new way of storing tables definitions: the Data Dictionary  !

Thanks to the Data Dictionary, DDLs in MySQL 8.0 are now Atomic. This means tat we have some changes in the behavior of the following DDLs for engines supporting  atomic DDL:

  1. DROP TABLES <tables> – All tables will be dropped, or none are dropped
  2. DROP SCHEMA – All entities in the schema will be dropped, or none are dropped
  3. DROP VIEW – All views are dropped, or none are dropped
  4. CREATE USER – All users are created, or none are created
  5. DROP USER – All users are dropped, or none are dropped
  6. GRANT – All users/roles in list are granted privileges, or none

This was detailed in …

[Read more]
Migrating to MySQL 8.0 for WordPress – episode 3: query optimization

Now that MySQL 8.0.3 RC1 is installed and that we saw how to verify the workload, it’s time to see if we can optimize some of the queries. As explained before, rewriting queries when using a product like WordPress is complicated but maybe we can do something for the indexes ?

So, do you remember how to check the query that was consuming most resources ? Let’s find it out again:

mysql> select t1.*, QUERY_SAMPLE_TEXT from statement_analysis as t1 
                join performance_schema.events_statements_summary_by_digest as t2 
                on t2.digest=t1.digest and t2.SCHEMA_NAME = t1.db where db = 'wp_lefred' …
[Read more]
Migrating to MySQL 8.0 for WordPress – episode 2: workload analysis

Now that MySQL is upgraded to 8.0 RC1, let’s have a look on how we could check the workload and see if we can optimize something by adding indexes for example. The same technique can be used to find inefficient queries requiring rewriting.

The first thing we will check is some information regarding the usage of the user used for lefred.be’s website:

mysql> select * from sys.user_summary where user= 'wp_lefred'\G
*************************** 1. row ***************************
user: wp_lefred
statements: 65060
statement_latency: 2.71 m
statement_avg_latency: 2.50 ms
table_scans: 234
file_ios: 17151
file_io_latency: 54.83 s
current_connections: 0
total_connections: 2541
unique_hosts: 1
current_memory: 52.27 MiB
total_memory_allocated: 12.50 GiB

This was after the upgrade, so not much info yet but if I compare with the other sites hosted on …

[Read more]
Migrating to MySQL 8.0 for WordPress – episode 1

Yesterday I decided to upgrade the database server of lefred.be running the latest WordPress to MySQL 8.0.

The previous version was 5.7.19.

My dedicated server is still running on CentOS 6.x, and I had just to enable the MySQL Community 8.0 yum repository and upgrade the rpms:

# yum update mysql-community-server --enablerepo=mysql80-community

After the upgrade, I had to modify my.cnf to remove the lines related to the non regretted Query Cache:

#query_cache_type=0
 …
[Read more]
👋 🐛 199 – bye bye bug #199, MySQL auto_increment is fixed !

It was expected for a long time…. here is the fix for bug #199 !!

The bug #199 submitted by PeterZ has been fixed in 8.0. Thank you to Zhang Simon for his contribution that inspired us to implement the fix.

Let’s have a look at the test case described in the bug report, I will reproduce it on MySQL 5.7.19 & MySQL 8.0.3.

The initial steps are exactly the same on both versions:

mysql> create table a(id int unsigned not null primary key auto_increment);
Query OK, 0 rows affected (0.17 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(10) unsigned …
[Read more]
Revisiting roles in MySQL 8.0

In my previous article about roles I said that one of the problems with role usage is that roles need to be activated before they kick in. Let's recap briefly what the problem is:

## new session, as user `root`

mysql [localhost] {root} ((none)) > create role viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant select on *.* to viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > create user see_it_all identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant viewer to see_it_all;
Query OK, 0 rows affected (0.01 sec)

## NEW session, as user `see_it_all`

mysql [localhost] {see_it_all} ((none)) > use test
ERROR 1044 (42000): Access denied for user …
[Read more]
Showing entries 1 to 10 of 34
10 Older Entries »