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 stackframe, 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.

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 to a block containing pointers to blocks of file block numbers and so on.

The block list inside …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the seventh episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll discover and use the OCI Bastion service to provide a restricted and time-limited access to administer our MySQL instance.

The post Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session first appeared on dasini.net - Diary of a MySQL experts.

Here’s a second post focusing on the performance of MySQL on ZFS in cloud environments. In the first post, MySQL/ZFS Performance Update, we compared the performances of ZFS and ext4. This time we’ll look at the benefits of using ephemeral storage devices. These devices, called ephemeral in AWS, local in Google cloud, and temporary in Azure, are provided directly by the virtualization host. They are not network-attached and are not IO throttled, at least compared to regular storage. Not only can they handle a high number of IOPs, but their IO latency is also very low. For simplicity, we’ll name these devices local ephemeral. They can be quite large: Azure lsv2, Google Cloud n2, and AWS i3 instance types offer TBs of fast NVMe local ephemeral storage.

The main drawback of local ephemeral …

in this tutorial, We’ll learn about how to add add, edit, delete functionality using Bootstrap 5, PHP and MySQL.I am extending previous tutorial Ajax Pagination with Search and Sort. We have already added functionality to listing, searching, and sorting into datatable, So Let’s add functionality to CRUD operation without page refresh. We have already added […]

The post Ajax Datatable CRUD Operation Using PHP and MySQL appeared first on Phpflow.com.