As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys.

In our example, we have the following parent table in a MySQL 5.7.21 server:

mysqld3-(root@localhost) [sakila]> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

and a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax:

mysqld3-(root@localhost) [sakila]> show create …

Every MySQL database programmer should learn and apply the newly added MariaDB and MySQL Window Functions and Common Table Expressions(CTEs) in their daily work. Both CTEs and window functions enable easy solutions to many query challenges that in prior releases have been difficult and sometimes impossible to surmount. Mastering these features opens the door to query solutions that are more robust, execute faster, and are easier to maintain over time than prior solutions using older techniques.

In our last blog we compared User Roles in MySQL 8 vs. MariaDB.  Today, we will here compare Window Functions and Common Table Expressions in both databases.

Window Functions

While all database administrators are familiar with aggregate functions like COUNT(), SUM(), and AVG(), far less people make use of window functions in their queries.  Unlike aggregate functions, which operate on an entire table, window …

In part 1 of this series we looked at implementing support for the caching_sha2_password authentication plugin using the libmyqlclient C library.  This is possible for C based connectors or connectors that can make use of an external C library.  For some, this is not possible.  For example, Java and C# both function better if they don’t have to thunk out to an external library.  For these, implementing the MySQL client/server protocol natively is required.  This part 2 is about implementing support for the caching_sha2_password plugin natively.  It is expected that you already have a working connector and understand the client/server protocol.  For reference please internal documentation …

Join Shree Nair, Product Manager at Webyog, as he demonstrates the new ‘Audit Log Analysis’ feature introduced in Monyog v8.5.0 and rolls out the roadmap for the upcoming releases.

Date & Time: Wednesday, March 21, 2018 at 11:00 am EDT

Register Now!

What to expect from the webinar?

– Quick walkthrough of Audit Log feature on a live Monyog instance.

– You will learn about the upcoming product roadmap for Monyog.

– You can take part in Q&A session and submit your feature requests too.

If you can’t attend this webinar live, register anyway and we’ll send you a link to the recording.

You can …

With MySQL 8.0, engineers improved the visibility of data locking. Usually it was not that easy to deal with the output of SHOW ENGINE INNODB STATUS and mixing Information_Schema tables like INNODB_LOCKS and INNODB_LOCK_WAITS.

So in MySQL 8.0 we instrument data locks in Performance_Schema and we changed the SYS Schema view related to the InnoDB Locks Wait too.

This means that when a transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. What we added is extra instrumentation to expose which data is locked (R), who owns the lock (A), and who is waiting for the data (B).

Testing

Let’s have a look at this simple scenario in MySQL 5.7.21 and 8.0.4 to compare:

2 clients starting a …

Some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL 5.7 Security series, we will see how to to establish a policy for password expiration with MySQL 5.7 Password Management.

Security is one of the hottest topics lately, and in this blog post, I will walk you through what needs to be configured to have a working three-node Percona XtraDB Cluster running with InnoDB Tablespace Encryption enabled.

This article will not cover the basics of setting up a cluster nor will it cover how to create SSL certs and keys since both of these topics have been well explained here and here.

Just to give you a brief history, InnoDB tablespace encryption was introduced in MySQL 5.7.11, and starting from …

For many programming languages, it's pretty easy to figure out which Connector to use. Java has Connector/J, if you're using C# or ASP you'll opt for Connector/.NET: the clue is in the name.

For PHP however, things aren't quite so straightforward. If you want to write a PHP application that communicates with a MySQL database, you have a choice of PHP extensions that you can use: mysql, mysqli, and PDO_MySQL.

We can simplify that list right away by discounting the mysql extension. This is old and was intended for use with MySQL versions before 4.1. It does not support many of the things that you can take for granted with the other two, such as improved MySQL authentication protocols or the ability to create prepared statements to prevent against SQL injection. It was dropped from PHP 7. In fact, you would have to jump through a couple of hoops just to get it to work with a later version of MySQL. So don't bother!

The mysql …

Percona announces the release of Percona Monitoring and Management 1.8.1. PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

This release contains bug fixes only and supersedes Percona Monitoring and Management 1.8.0.

Improvements

• PMM-2051: The ProxySQL Overview dashboard enables selecting more than one host group in the Hostgroup field.
• PMM-2163: Dashboards based on the …

Please join Percona Technical Account Manager Tim Sharp, as he presents How Percona Maintains Optimal Customer Health on Thursday, March 8, 2018 at 11:00 am PST (UTC -8) / 2:00 pm EST (UTC -5).

Register Now

How do you guarantee optimal database performance for your critical applications and services? Proactive monitoring and intervention is one way.

Percona Technical Account Managers (TAM) are expert DBAs that provide proactive assistance to our Managed Service customers – helping to guarantee performance, …