Showing entries 1 to 10 of 62
10 Older Entries »
Displaying posts with tag: information_schema (reset)
MySQL: who’s filling my error log?

This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one:

2020-07-24T06:54:00.877128Z 46 [Warning] [MY-013360] [Server] 
Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in 
a future release. Please use caching_sha2_password instead'

The context was that the user upgraded from MySQL 5.7 to MySQL 8.0 and changed the authentication plugin of all users to caching_sha2_password.

As the warning message was written to the error log almost twice per second, I suspected a monitoring tool. The user said, that no application, no monitoring was using this server yet.

Of course we verified that all users had the plugin changed.

This is important when helping people (friends, colleagues, …

[Read more]
MySQL – Keep an eye on your auto_increment values

In MySQL, it’s very common and recommended to you an auto_increment as Primary Key.

However that integer can have different types each having their size limitation, and they can also be signed or not.

Many people when creating a table use by default the following syntax do define their auto_increment primary key:

id int auto_increment primary key

This is a signed integer where the maximum value is 2,147,483,647.

It’s recommended as a DBA to keep an eye on how fast your tables are filling up and how much records can still be added to your tables.

And this can happen even to the bests ! (see this Github Availability Report on May 5th at 00:45)

MySQL Shell Plugin

Once again, MySQL Shell comes to the rescue with an easy plugin: …

[Read more]
MySQL & InnoDB Disk Space

Yesterday, Bhuvanesh published an article about how to verify the difference between allocated diskspace for a tablespace and the the data in it.

I commented with an old post explaining how to get some similar info only using SQL in case you don’t have filesystem access.

And finally, my friend Bill Karwin, commented how this info is not always accurate. Which, of course, I agree with.

This is why, I checked …

[Read more]
MySQL: CPU information from SQL

Do you know that it’s possible to get information from the CPUs of your MySQL Server from SQL ?

If you enable the status for the  INNODB_METRICS table in INFORMATION_SCHEMA, you will be able to query CPU information.

First, check if those status are enabled:

MySQL> SELECT name, subsystem, status 
FROM INFORMATION_SCHEMA.INNODB_METRICS where NAME like 'cpu%';
+---------------+-----------+----------+
| name | subsystem | status |
+---------------+-----------+----------+
| cpu_utime_abs | cpu | disabled |
| cpu_stime_abs | cpu | disabled |
| cpu_utime_pct | cpu | disabled |
| cpu_stime_pct | cpu | disabled |
| cpu_n | cpu | disabled |
+---------------+-----------+----------+
5 rows in set (0.00 sec)
[Read more]
How to grant privileges to users in MySQL 8.0

It seems, that this is a question that regularly shows up in forums or stackoverflow.

To start, let’s highlight the fact that in MySQL 8.0 it’s not any more possible to create a user directly from the GRANT command (ERROR 1410 (42000): You are not allowed to create a user with GRANT).

This means that to grant some privileges, the user must be created first.

Let’s create a user ‘user1‘ with ‘ChangeMe‘ as password that the user will have to change:

mysql> create user 'user1' identified by 'ChangeMe' password expire;
Query OK, 0 rows affected (1.35 sec)

Let’s try to connect to MySQL using that new created user:

 $ mysql -u …
[Read more]
MySQL 8.0 and keywords

As you know, MySQL uses some keywords and some of them are also reserved.

Let’s have a look how to deal with that:

mysql> create table WRITE (id int auto_increment primary key, varying varchar(10), than int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'WRITE (id int auto_increment primary key,
varying varchar(10), than int)' at line 1

OK, it seems WRITE is a keyword I cannot use as table name. I’ve then two choices:

  • rename the table to something else like WRITE_TBL
  • use back-ticks (`) around the table like `WRITE`

Let’s use the first option:

mysql> create table WRITE_TBL (id int auto_increment primary key, varying varchar(10), than …
[Read more]
pre-FOSDEM MySQL Day 2019

For the third year in a row, we will take advantage of the mass presence of our MySQL Engineers during FOSDEM to organize the pre-FOSDEM MySQL Day.

The program of this 3rd edition is already on track, thank you to all the speakers who already confirmed their participation.

Start End Event Speaker Company Topic
Friday 1st February
09:30 10:00 MySQL Community Team Welcome
10:00
[Read more]
MySQL: size of your tables – tricks and tips

Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.

To summarize, below are the different queries you can run:

Dataset Size

I the past I was using something like this :

But now with sys schema being installed by default, I encourage you to use some of the formatting functions provided with it. The query to calculate the dataset is now:

SELECT sys.format_bytes(sum(data_length)) DATA,
       sys.format_bytes(sum(index_length)) INDEXES,
       sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER  BY data_length + index_length;

Let’s see an example:

[Read more]
Basic Internal Troubleshooting Tools for MySQL Server Webinar: Q & A

In this blog, I will provide answers to the Q & A for the Basic Internal Troubleshooting Tools for MySQL Server webinar.

First, I want to thank everybody for attending my February 15, 2018, webinar on troubleshooting tools for MySQL. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.

Q: How do we prevent the schema prefix from appearing in the show create view. This is causing issue with restore on another server with a different DB. See the issue …

[Read more]
MySQL 8.0: Improvements to Information_schema

Coinciding with the new native data dictionary in MySQL 8.0, we have made a number of useful enhancements to our INFORMATION_SCHEMA subsystem design in MySQL 8.0. In this post I will first go through our legacy implementation as it has stood since MySQL 5.1, and then cover what’s changed.…

Showing entries 1 to 10 of 62
10 Older Entries »