Notes/errata/updates for Chapter 5:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 5 is pages 135-177.

Notes:
If you did not load the sample databases in Chapter 3 as per page 102, you can download the file to import from http://tahaghoghi.com/LearningMySQL/Downloads/Files/Data/SQL_files_with_foreign_key_references/music.sql

On page 162, it says “In Chapter 6, we’ll discuss how to load data from existing tables or from external data sources.” but this content is in Chapter 8.

On page 169, it has an example of “DELETE FROM played;” which deletes all the rows of a table. This will not work if safe_updates are enabled. I always advise that if you …

Dear MySQL users,

MySQL Connector/J 8.0.8 Development Release is a development milestone
release for the 8.0.x series.

This release includes the following new features and changes, also
described in more detail on

https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-8.html

MySQL Connectors and other MySQL client tools and applications now
synchronize the first digit of their version number with the (highest)
MySQL server version they support.
This change makes it easy and intuitive to decide which client version
to use for which server version.

As always, we recommend that you check the “CHANGES” file in the download
archive to be aware of changes in behavior that might affect your application.

To download MySQL Connector/J …

Dear MySQL users,

MySQL Connector/Net 8.0.9 is the sixth development release that expands
cross-platform support to Linux and macOS when using Microsoft’s .NET Core
framework. Now, .NET developers can use the X DevAPI with .NET Core and
Entity Framework Core (EF Core) 1.0 to create server applications that run
on Windows, Linux and macOS. We are very excited about this change and
really look forward to your feedback on it!

MySQL Connector/Net 8.0.9 is also the eighth development release of MySQL
Connector/Net to add support for the new X DevAPI.  The X DevAPI enables
application developers to write code that combines the strengths of the
relational and document models using a modern, NoSQL-like syntax that
does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
…

Continuation of my previous Blog Post for MYSQL CLOUD SERVICE 2018 [1Z0-320] exam preparation, In this blog will discuss Database Design part for MySQL. Rather than rewriting given topics I have provided already exists good reference links for few topics.

Database Design

• Describe Datatypes in a MySQL database
• Create databases and tables
• Create basic SQL queries
• Maintain Databases, Tables, and Columns
• Configure Indexes and Constraints
• Join Tables
• Partition MySQL Tables

Describe Datatypes in a MySQL database

Reference Link:

Link1: …

In this blog post, we’ll look at how to avoid shared locks from subqueries.

I’m pretty sure most of you have seen an UPDATE statement matching rows returned from a SELECT query:

update ibreg set k=1 where id in (select id from ibcmp where id > 90000);

This query, when executed with

autocommit=1

, is normally harmless. However, this can have bad effects when combined with other statements in the same transaction that result in holding the shared locks from the SELECT query. But first, let me explain why the SELECT query would hold locks in the first place.

Due to InnoDB’s ACID properties, to make sure that the outer UPDATE statement has a consistent view of the matching rows from the SELECT query the server has to acquire a shared lock on those rows. No other thread should modify …

In my previous article about roles I said that one of the problems with role usage is that roles need to be activated before they kick in. Let's recap briefly what the problem is:

## new session, as user `root`

mysql [localhost] {root} ((none)) > create role viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant select on *.* to viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > create user see_it_all identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant viewer to see_it_all;
Query OK, 0 rows affected (0.01 sec)

## NEW session, as user `see_it_all`

mysql [localhost] {see_it_all} ((none)) > use test
ERROR 1044 (42000): Access denied for user 'see_it_all'@'%' to database 'test'

mysql [localhost] {see_it_all} ((none)) > show grants\G …

Sync MySQL Servers with ProxySQL Cluster

In the two previous blog posts about ProxySQL Cluster we described how to setup a Cluster, how to add users/rules and propagate them across the Cluster, how to provision new nodes, how to re-sync nodes after they have been out of the Cluster, and some features in the roadmap, especially around the concept of core nodes vs satellite nodes.
We also mention that there are 4 modules/tables that currently ProxySQL Cluster is able to sync:

• mysql_query_rules
• mysql_servers
• mysql_users
• proxysql_servers

We covered 3 of these, and the last one missing is how to sync mysql_servers, related to MySQL …

In this post, we will discuss how to fix cardinality for InnoDB tables manually.

As a support engineer, I often see situations when the cardinality of a table is not correct. When InnoDB calculates the cardinality of an index, it does not scan the full table by default. Instead it looks at random pages, as determined by options innodb_stats_sample_pages, innodb_stats_transient_sample_pages and innodb_stats_persistent_sample_pages, or …

This blog describes how to identify queries that cause a sudden spike in system resources as well as the user and host who executed the culprit query using the Monyog MySQL Monitor and Advisor.

How many times have you seen a system go live and perform much worse than it did in testing? There could be several reasons behind bad performance. For instance, a slow running query in MySQL can be caused by a poor database design or may be due to higher-than-normal latency in network communication. Other issues such as using too few or too many indexes may also be a factor. This blog will identify the types of poorly performing queries and outline some concrete strategies for identifying them using monitoring. Finally, some tips for improving performance will be presented.

The Effects of Misbehaving Queries

Typically, misbehaving queries will result in two possible outcomes: high CPU usage and/or slow execution. The two …

This blog describes how to identify queries that cause a sudden spike in system resources as well as the user and host who executed the culprit query using the Monyog MySQL Monitor and Advisor.

How many times have you seen a system go live and perform much worse than it did in testing? There could be several reasons behind bad performance. For instance, a slow running query in MySQL can be caused by a poor database design or may be due to higher-than-normal latency in network communication. Other issues such as using too few or too many indexes may also be a factor. This blog will identify the types of poorly performing queries and outline some concrete strategies for identifying them using monitoring. Finally, some tips for improving performance will be presented.

The Effects of Misbehaving Queries

Typically, misbehaving queries will result in two possible outcomes: high CPU usage and/or slow execution. The two …