This week's MySQL was a great one for novices looking to level up from beginner level SQL development to a higher level.  

    The problem:  Find the customers with more than one unreturned rented movies that are past their return due date.  You might to look at this for the example of finding an overdue rentals. 

The answer:

First we need to get the customer_id from the customer table.  Then it takes a bit of struggle to get the information on the rental.   It is often easier to write queries by determining the needed output columns, then the 'qualifiers'  or stuff on the right of the WHERE clause before determining what has to be joined to get between the two.

The part of the query to find the overdue entries requires the rental date where it …

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

This is about marking a query so that it can be identified in source and attributed to its origin in the codebase.

In my scenario, I have control over the ORM or DAO. I can look at the stackframe, identify the caller of the execute function and put …

In the article, we are going to discuss data types including string, numeric, date and time, spatial, and JSON supported by MySQL. Also, we’ll provide examples of their usage and see how to change a data type for the table column using dbForge Studio for MySQL. Contents What is a Data Type Data Types in […]

The post Data Types in MySQL: Tutorial and Full List with Examples of Data Formats appeared first on Devart Blog.

     This week's MySQL uses the Sakila database (details on how to get this data) and this week's quiz is a  great one for those wanting to move from beginner level SQL development to a higher level.  There will be lots of tables to joins.

    The problem:  Find the customers with more than one unreturned rented movies that are past their return due date.  You might to look at this for the example of finding an overdue rentals.    You will need to display the customer's ID number, the number of overdue videos, and the names of the videos!  Bonus points for the customer name!

An answer will be posted Monday.

All opinions expressed in this blog are those of Dave …

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

This is about marking a query so that it can be identified in source and attributed to its origin in the codebase.

In my scenario, I have control over the ORM or DAO. I can look at the stack frame, identify the caller of the execute function and put …

When doing migrations or failovers in MySQL, there is usually a need to do a topology change and repoint replica servers to obtain replication data from a different server.

For example, given servers {A, B, and C} and the following topology:

If you need to repoint C to be a replica of B, i.e:

You can follow the next steps:

Note: log_replica_updates should be enabled on the soon-to-be primary as it is a prerequisite for chain replication.

Note: It is assumed that both replicas only stream from Server A and there are no conflicting replication filters in place that might break replication later on.

If Using File/Position-Based Replication:

1) Stop B and C

STOP REPLICA;

2) If replicas are multi-threaded, correct MTS gaps and make them …

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

But this is about query execution in the server, and the instrumentation available to me in MySQL 8, at least to get things started. So we take the tour of performance schema, and then run one example query (a simple join) and see what we can find out about this query.

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

But this is about query execution in the server, and the instrumentation available to me in MySQL 8, at least to get things started. So we take the tour of performance schema, and then run one example query (a simple join) and see what we can find out about this query.

Storing data is but one part of many in application development. Once data is stored, interested parties will likely want to see it. This is the Read aspect of CRUD – reading (or viewing) the data. Continue reading and see examples using CodeIgniter 4 built-in Model methods…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

In part 1 of this series, CodeIgniter 4 CRUD with MySQL: Create, I used the Model insert() method to store new rows of data. Now that the data is present in the table, we want to see that data.

There are 2 built-in Model …

Like I said, I never had much reason to use table compression, and only recently looked into the topic. MySQL Page Compression looks a lot easier at the database end of things, but relies on hole punching support in the file system. Let’s have a look at what that means.

Files, Inodes and Arrays of Blocks

The original Unix filesystem saw the disk as a sea of blocks, which were represented in a free map as an array of bits. Files have numbers, which are an index into an array of so-called inode structures. Inodes store the files metadata and contain an array of block numbers, which make up the actual file. The array is folded multiple times, to optimize for the more common case of small files: The first few block numbers were stored in the inode, followed by a pointer to a block containing file block numbers, then a pointer …