Foreign key related issues are very common when dealing with DDL changes in MySQL using Percona toolkit. In this blog post, I will explain how the tool (pt-online-schema-change) handles foreign key constraints when executing a DDL change.

First of all, I would like to explain why foreign keys have to be handled at all before writing more about the “How”. Foreign key constraints are aware of table rename operations. In other words, if the parent table is renamed, the child table automatically knows it and changes the foreign key constraint accordingly. Please have a look at the below example, and you can see the table name is automatically updated in the child table after the rename operation on the parent table:

mysql> show create table prd_details \G
*************************** 1. row *************************** …

PALO ALTO, Calif., June 6, 2019 – ScaleGrid, the Database-as-a-Service (DBaaS) leader in the SQL and NoSQL space, has announced the expansion of their fully managed MySQL Hosting services to support Amazon Web Services (AWS) cloud. The platform allows MySQL AWS administrators to automate their time-consuming database operations in the cloud and improve their performance with high availability, disaster recovery, polyglot persistence, and advanced monitoring and analytics.

Over the years, migrating data to the cloud has become a top priority for organizations looking to modernize their infrastructure for improved security, performance, and …

If you just migrated to MySQL 8.0, you may have seen that the default authentication plugin has been changed to a more secure one: caching_sha2_password and I’ve already written some articles about it.

Now let’s discover how Perl users can deal with MySQL 8.0.

The driver to use MySQL with Perl is perl-DBD-MySQL. MySQL 8.0 is supported but the new authentication plugin might not be. This depends of the mysql library linked during compilation of the module.

problem connecting to MySQL 8.0

The error you may encounter is the following:

DBI connect('host=localhost','fred',...) failed: Authentication plugin
 'caching_sha2_password' cannot be loaded:  …

It used to be easy: a company developed a new application, chose a database solution, launched the new application and then tuned the chosen database solution. A team of DBAs looked at the infrastructure as well as the workload and made changes (or suggestions) as needed. The application then stayed in production for years and small tweaks were made as needed.

Those days are long gone.

As technology has evolved, so has the workflow and deployment strategy within the large enterprise.  Large, monolithic applications are being split into several microservices, generally decoupled but still working together and somewhat interdependent. Waterfall deployment strategies are replaced with agile methodology and continuous code deployment. Tuning and maintaining large installations of physical hardware has become less of the focus with the advent of virtualization, containerization, and orchestrated deployments.

…

Presentation of some of the new features of MySQL 8.0.16 released on April 25, 2019

Recently, I received a support request where the customer wanted to convert an INTEGER column to BIGINT on two tables. These tables are related by a foreign key, and it is a 3 node PXC cluster. These tables are 20GB and 82 GB in size and DDL’s on such tables in a production environment is always a challenge. We have options like direct ALTER or using pt-online-schema-change to get this done, but this is a very special case where none of them will be able to do this DDL. To demonstrate why this is so, please follow the table schema and the example below about DDL queries on foreign key columns. In the end, I will discuss an easy workaround, too.

Please take a look at table schema before reading further. In the below schema, the product_catalog_id column from the product_details table refers to catalog_id column from the product_catalog table. Both these fields are INT(11) …

(In the previous post, Part 6, we covered Replication.)

In this final blog post, we conclude our series of exploring MyRocks by taking a look at use case considerations. After all, having knowledge of how an engine works is really only applicable if you feel like you’re in a good position to use it.

Advantages of MyRocks

Let’s start by talking about some of the advantages of MyRocks.

Compression

MyRocks will typically do a good job of reducing the physical footprint of your data. As I mentioned in my previous post in this series about compression, you have the ability to configure compression down to the individual compaction layers for each column family. You also get the advantage of the fact that data isn’t updated once it’s written to disk. Compaction, which was …

So the issue of max_connections dropping from the value set in your my.cnf file down to 214 has been around for a little while on Ubuntu.

As an example, it was noted here back in 2015

• https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/

I ran into this again recently and was resolved with the following steps.


# cp /lib/systemd/system/mysql.service /etc/systemd/system/
# cd /etc/systemd/system/
# vi mysql.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

# systemctl daemon-reload
# systemctl restart mysql


Once those steps completed the MySQL connections were stable at the given parameter in the my.cnf file. 

Just a reminder for the shows hold this week where you can find either MySQL Community team or MySQL experts at. Please find them below:

• DevTalks Romania, Bucharest, Romania, Jun 6-7, 2019

  • Do not miss two MySQL talks, both given by Georgi Kodinov, the Team Lead, MySQL Server General Team on:
    • "What's New in MySQL 8.0 Security" 
    • "MySQL Enterprise Data Masking" 
  • Do not miss to stop by at our MySQL + Bronto booth to talk to both teams!
• BGOUG, Pravets, Bulgaria, Jun 7-9, 2019
  • We are Partner of BGOUG this year again. Do not miss the MySQL talk on "MySQL InnoDB …

The annual MySQL Community Awards were held in Austin, Texas at Percona Live on Wednesday May 29, 2019 during the lunchtime keynotes. The MySQL Community Awards initiative is an effort to acknowledge and thank individuals and corporations for their contributions to the MySQL ecosystem. The nominations for the awards come from the community itself.  It is a from-the-community, by-the-community, and for-the-community effort. There is no corporate involvement. Awards are given in the following three categories: Community Contributor (a person), Application (an application / project), and Corporate Contributor (a company). The committee is composed of an independent group of community members of different orientation and opinion, themselves past winners or known contributors to the community. These awards are not to be confused with the ones …