In April, when I updated from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed. That’s because of a change in the JDBC driver, which I blogged about then. Starting yesterday, I began updating a base Fedora 30 configuration again to MySQL 8.0.20. I wrote a testing program for the Java JDBC file last time, and when I ran it this time it told me that I didn’t have the JDBC driver installed, or in the $CLASSPATH. My Java diagnostic script, MySQLDriver.java, returned the following error message:

Error: Could not find or load main class MySQLDriver

The Java JDBC test program code is in the prior post. It simply loads the user, password, database, host, and port statically for my student …

Join Sveta Smirnova, MySQL Engineer at Percona, as she discusses modern solutions for modern database loads.

MySQL is famous for working well in high performing environments. This is the reason why it is the most popular backend for web applications. But our view of what to call high-performance changes over the cycles. Every year we get faster data transfer speed; more devices, connected to the Internet; more users and, as a result, more data.

The challenges MySQL developers have to solve are getting harder over time.

In this session, Sveta will show how use-case scenarios are changing over 25 years of MySQL history. She will show what did MySQL engineers do to keep the product up to date and cover topics such as handling a large number of active connections and high volumes of data as well as how the latest MySQL versions handle increased load better.

After attending this …

After I updated a Fedora 30 instance, I could no longer connect to the MySQL database. An attempt to connect raised the following error:

Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

My guess was correct, the mysqld.service got removed during the update (a synonym for upgrade). So, I ran the following command as a sudoer user:

sudo systemctl enable mysqld.service

It creates the following symbolic link:

Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

That would start the MySQL Daemon (mysqld) on the next restart of the OS. However, I didn’t want to restart to have access to the service. I simply started it with the following command:

sudo systemctl start mysqld.service

Then, I could connect to the MySQL database. As always, …

As the name indicates, an undo log record contains information about how to undo the recent changes by a transaction. When a transaction writes data, it always makes writes on the tablespace files. InnoDB Undo log stores copy of data that is being modified by any current transaction. So, at this point in time if any other transaction queries for the original data (row) which is being modified,  the undo logs provide the same and serve the purpose. This is what provides a consistent read view ( based on isolation ) during any data modifications.

Here in the above representation, Transaction T1 modifies the data (Data-1). During the time of modification to ensure the reads are consistent, transactions T2  and T3 are given access only to the copy (previous row version) of Data -1 which is stored in the “UNDO” …

In this continuing series of posts of fundamental DBA security tasks,  we will look at passwords again, the good, the bad and the outright ugly. This group of tasks will help you find insecure users with poor passwords and provide … Continue reading →

Usually, database people are familiar with table fragmentation with DELETE statements. Whenever doing a huge delete, in most cases, they are always rebuilding the table to reclaim the disk space. But, are you thinking only DELETEs can cause table fragmentation? (Answer: NO).

In this blog post, I am going to explain how table fragmentation is happening with the INSERT statement.

Before going into the topic, we need to know that with MySQL, there are two kinds of fragmentation:

• Fragmentation where some of the InnoDB pages are completely free inside the table.
• Fragmentation where some of the InnoDB pages are not completely filled (the page has some free space).

There are three major cases of table fragmentation with INSERTs :

• INSERT with ROLLBACK
• Failed INSERT statement
• Fragmentation with page-splits

…

In this post, I’ll cover examples of the MySQL TRIM() function. TRIM() removes specific characters – or spaces – from a given string, at either: the beginning, ending, or potentially in both locations depending on several factors. With an optional keyword argument that controls which character(s) – if any – are removed, TRIM() can be tricky so let’s gain understanding with several easy-to-digest examples…

Photo by Peter Beukema on Unsplash

OS, Software, and DB used:

• OpenSuse Leap 15.1
• MySQL 8.0.20

Self-Promotion:

If you enjoy …

A few days ago, a customer got in touch asking how they could use Percona Monitoring and Management (PMM) to monitor the roles played by each node in an InnoDB cluster. More specifically, they wanted to check when one of the nodes changed its role from Primary to Secondary, or vice-versa. PMM allows for a high level of flexibility and customization through its support for custom queries, and we just have to be creative on how we approach the problem. In this post, we present the steps we did to test our solution, including the deployment of a 3-node InnoDB Cluster hosted in the same server (for testing) and a PMM 2 server, and connecting them together. Even though this has already been covered in other blog …

Passwords lists abound on the net. They exist to: Get a laugh out of silly passwords… 123456 anyone? Develop a dictionary file to check user passwords either for validation or cracking. From MySQL 5.6 the validate_password plugin has been available. … Continue reading →

In the second part of this series of DBA security tasks, we will continue to look at fixing mysql db users with an insecure setup. These are not tips that you read with your morning coffee and then forget about, … Continue reading →