If you ever had to deal with performance and/or disk space issues related to temporary tables, I bet you eventually found yourself puzzled. There are many possible scenarios depending on the type of temporary table, settings, and MySQL version used. We have observed a pretty long evolution in that matter due to a couple of reasons. One of them was the need to completely eliminate the need to use the deprecated MyISAM engine, and at the same time introduce more performant and reliable alternatives. Another set of improvements was required related to InnoDB, where it was necessary to lower the overhead of temporary tables using that engine.

For that reason, I decided to gather them in a sort of summary which may help to troubleshoot their usage. Due to vast changes between major MySQL releases, I divided the article by them.

…

Hey friends over the weekend (Friday actually) I’ve published another issue of my weekly newsletter, OpenLamp.tech. Continue reading for more information…

This issue is full of great curated reads as well as a specific question I’ve asked readers and the community. Looking for some suggestions on planned migrations for upgrades to my WordPress site.

The Newsletter for PHP and MySQL Developers

In this weeks newsletter, there’s information on:

Here’s the full issue for you to read. You can always subscribe (it’s free) and have the weekly issue of OpenLamp.tech delivered directly to your inbox. Just think, no more waiting for me to post it here each Monday.

Visit the sign-up form below to …

While exporting the table with MySQL native utility, we don’t have any control on the process, and also there will be no progress update as well on the process completion. So when exporting the larger table will consume high resource utilization and also the disk space usage will also be high.

MySQL shell utility will make the process easier. It will export the table and we can import the data back with a parallel thread and also will provide the current progress status on export/import progress.

util.exportTable() utility was introduced in Shell – 8.0.22 version, will export the data in a controlled manner. We can store the data in either local or Cloud Infrastructure Object Storage bucket as well.

We will see about the compression ratio along with the time taken for native MySQL vs Shell utility

Feature :

• Compression
• Progress status
• Supported output …

We all tried various alternative methods for modifying the table structure, but pt-online-schema-change (pt-osc) is the most convenient and preferred method for performing the alter online. It has more granular control too. But it may lead to data loss if proper precautionary steps are not taken care of.

In this blog, we are going to modify a column to a unique key using pt-osc, below I have shared the table structure.

mysql> show create table test\G
* 1. row *
Table: test
Create Table: CREATE TABLE test (
Personid int(11) NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255) DEFAULT NULL,
Age int(11) DEFAULT NULL,
PRIMARY KEY (Personid)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I have inserted the data of 1000 rows using …

Generally, most all web applications are going to follow the elements of CRUD: Create, Read, Update, and Delete. CodeIgniter 4 is a powerful PHP web development framework that provides a rapid development environment. The CodeIgniter 4 Models come enriched with built-in CRUD functionality, which is covered in this compilation of Medium posts…

Image by Dariusz Sankowski from Pixabay 

Disclaimer: I originally published these articles first over on …

Over the weekend, I’ve published the 1st issue of my new weekly free newsletter, OpenLamp.tech. I’m so excited about this newsletter. Read on to learn more…

What is the OpenLamp.tech newsletter

OpenLamp.tech is a free newsletter unlike any other that I’m aware of. If it existed, I would be reading it.

Instead, I created it. Let me explain…

Image by Mahesh Patel from Pixabay

Developer, Writer, …

Hi all, just some words to let you know that FOSDEM’22 will take place February 5th and 6th. Like last year, this 2022 edition will be an online edition.

As MySQL, we have submitted our candidature for a devroom/track. We are waiting for the approval list.

As soon as we know FOSDEM’s decision, the eventual Call-For-Papers will be opened.

Stay tuned.

Meanwhile, enjoy MySQL !

When I went over my example of using the WITH clause to solve how to use a series of literal values in data sets, some students got it right away and some didn’t. The original post showed how to solve a problem where one value in the data set is returned in the SELECT-list and two values are used as the minimum and maximum values with a BETWEEN operator. It used three approaches with literal values:

• A list of Python dictionaries that require you to filter the return set from the database through a range loop and if statement that mimics a SQL BETWEEN operator.
• A WITH clause that accepts the literals as bind variables to filter the query results inside the query.
• A table design that holds the literals values that an analyst might use for reporting.

It was the last example that required elaboration. I explained you might build a web form that uses a table, and the table could allow a …

A few weeks ago and in MySQL 5.7, I had an ALTER TABLE that failed with a duplicate entry error.  This is old news as it is happening since MySQL 5.6, but I only saw this recently because I am normally using online schema change from the Percona Toolkit (pt-osc) or GitHub's online schema migration (gh-ost).  I do not like that and I am disappointed this has not been improved, so this

In October 10th was released the latest GA version of MySQL: 8.0.27.

As usual, it’s highly advised to read the release notes to get informed about the changes and bug fixed.

MySQL is Open Source and each release contains contributions from our great Community. Let me thanks all the contributors on behalf of the entire MySQL Team: Thank you !

MySQL 8.0.27 includes contributions from Adam Cable, Lukasz Sanek, Kaiwang Chen, Shaohua Wang,
Øystein Grøvlen, Zheng Lai, Huqing Yan, Biran Yue, Xiaoyu Wang, Hong Wang,
Kevin Michel, Björn Michael, Kaige Ye, Yubao Liu and Facebook.

Once again, thank you all for your great contributions.

Here is the list of the above contributions and related bugs:

Clients / Connectors

• …