The latest from the MySQL community

We observe a large number of messages of the kind

The table '../tmp/#sql…' is full

Before MySQL 8

In older Versions of MySQL, implied temporary tables are being created, whenever your EXPLAIN contained the phrase using temporary.

In this case, MySQL would create an in-memory temporary table to materialize an intermediate query result, and then continue to process the data from there. If that temporary table was larger than some configurable limit, the temporary table would instead be converted to a MyISAM table on disk, streamed out, and then work would continue with this.

This has a large number of disadvantages:

• A table in the MEMORY storage engine can have no variable length columns. That means, any VARCHAR column gets converted to a CHAR column in the MEMORY version of that table.
  …

Title: Procedure for MySQL Database Installation ( 5.7.xx – Enterprise) and setup for SSL on RHEL

Purpose of the Document

Procedure for MySQL Database Installation ( 5.7.xx – Enterprise) and setup for SSL on RHEL

Procedure

=====================================================================

MySQL Database Installation –RHEL 7.x – DB Version 5.7.x – Enterprise Edition

=====================================================================

The below procedure is related to MySQL Database Installation Version 5.7.x on RHEL 7.x. ( Enterprise Edition)

(1) Check the OS version of RHEL by using below command.

[root@DB_Server ~]# cat /etc/redhat-release

(2) Create local group for dba

groupadd -g 1020 dba

(3) Create local user for mysql

useradd -g dba -u 888 -d /export/home/mysql -m mysql or useradd -g dba -u 888 -d /data/mysql …

This blog post is inspired by Jesper's recent blog post on how the automatic update of InnoDB persistent statistics may never trigger if servers are restarted frequently. However, the matter is even worse! In this blog post I will show that even when the automatic recalculation is performed, there are no guarantees as to when the server will see the changes.

Myths about persistent statistics

I must admit that I, for a long time, believed in the myths I was told about when updates to InnoDB's index statistics become visible to the query optimizer. There were basically two variants:

• The updated statistics will only be visible to new connections (i.e., sessions that are started after the statistics was updated)
• When a new connection accesses the table, the updated statistics …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the sixth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll introduce the concept of Security Lists a virtual firewall to control traffic at the network packet level.

The post Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List first appeared on dasini.net - Diary of a MySQL expert.

In part 1 of the series about Point-in-Time Recovery in OCI MDS, we saw how to stream the binary log to Object Storage.

In this blog post, we will see how we can restore the data up to certain point.

The high level process is simple:

1. we need to restore the last backup just before the point we want to return to
2. find the last GTID that was executed in that backup (not 100% mandatory but can accelerate the process)
3. choose what is the objective:
   1. just skip one transaction (following transaction could depend on it)
   2. recover up to that transaction and skip all the following ones
4. replay the binary logs taking in consideration the chosen option of point 3

The scenario …

Since MySQL 5.6 InnoDB has supported persistent index statistics. This means that when you restart MySQL, InnoDB does not have to recalculate the statistics for the tables, rather it can read the statistics from its persistent storage. This has several advantages over the transient statistics, but as it turns out, there is also a catch: MySQL may under some circumstances never get around to update the index statistics. This particularly affects instances that are restarted frequently and tables with a large number of rows.

Information

Persistent statistics are the default, so unless you have explicitly chosen not to use them, then you are.

Background

Before I dive into why there are scenarios where persistent statistics are never updated, it is necessary to recap how persistent statistics work. Every time the statistics are updated, the result is stored in the mysql.innodb_index_stats table with …

     The main tool for tuning MySQL queries is the EXPLAIN statement (https://dev.mysql.com/doc/refman/8.0/en/explain.html) and one of the hurdles on the learning curve of the EXPLAIN statement is explaining what EXPLAIN is explaining.  Basically EXPLAIN is prepended to a SELECT, TABLE, DELETE, UPDATE, INSERT or REPLACE statement.  To add to an already steep learning curve is that there are many types of EXPLAIN in MySQL.

Let Me Explain

It is very simple to add EXPLAIN at the very beginning of a query to see how the server wants to execute a query. 

EXPLAIN SELECT col1, col2 FROM my_table;

The output will show the query plan (the actual query the optimizer will instruct the MySQL server to run) and some preliminary information about how the query plan was picked among the many possible options.  Learning to use EXPLAIN to tune queries is a long process and beyond the …

Database architecture and design are becoming an increasingly lost art. With new technologies and the push towards faster development cycles, people continue to take shortcuts, often to the detriment of long-term performance, scalability, and security. Designing how your application stores, accesses, and processes data is so fundamentally important, it can not be overlooked. I want people to understand that early design choices can have a profound impact on their applications. To that end, I will be exploring database design principles and practices over the next several months. I am starting with every developer’s favorite data format: JSON!

It seems that almost every database over the last few years has introduced various degrees of support for storing and interacting with JSON objects directly. While these features are designed to make it easier for application developers to write code faster, the implementations of each implementation …

I’ve been doing a lot of database stuff lately, and not much PHP, so when I returned to make my first Laravel project for a while, I had to check the docs to remind myself how some of this works. I noticed that the default approach to database credentials is still to use separate credentials for the host, port, and other variables. I’m using Aiven databases (because I work there and managed databases are great for demo apps as well as real ones!) which supply connection strings, but Laravel supports these too.

For MySQL, the database connection string from Aiven is in the format:

mysql://username:password@hostname:port/dbname

You can remove the configuration for DB_* and instead use the variable DATABASE_URL with the full string.

Pro-tip: Laravel’s built …

On MySQL Database Service (aka MDS) on Oracle Cloud Infrastructure (aka OCI), when you create a new instance, you also need to set the credentials for the administrator:

Let’s focus on that account…

Please don’t forget the password, it cannot be recovered !!

First of all that account’s username has some limitations. Indeed, some names are reserved as it’s mentioned in the manual. So you could not use the following usernames:

• administrator
• ociadmin
• ocirpl
• mysql.sys
• mysql.session
• mysql.infoschema

This list can evolve, you can get the list directly from the MySQL DB System creation’s screen:

In fact, those accounts are already existing in the MySQL DB …