OpenLampTech is your media source for original and curated MySQL, PHP, and LAMP stack-related content. This week’s newsletter has many many great reads. Thank you for reading and making the publication a success. Please share with others!

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

This week in OpenLampTech issue #61, we have articles covering:

• Harsh realities of using Linux
• WooCommerce before and after add-to-cart hooks
• MySQL table name case
• Common PHP design patterns
• And a whole lot more

Want to …

- Read Replicas with Load Balancer
- Replication Filters
- Replication Sources Without GTIDs
- MySQL HeatWave for AWS

This presentation is just a summary of new features in MySQL HeatWave.

For a more thorough and exhaustive view please read the following:

- https://docs.oracle.com/en-us/iaas/releasenotes/services/mysql-database/

- https://dev.mysql.com/doc/relnotes/heatwave/en/

The post MySQL HeatWave Report – December 2022 update first appeared on dasini.net - Diary of a MySQL expert.

Recently, while doing a test process to examine resource usage and the time required to alter the table using pt-osc, it leads to a loss of triggers even though the –preserve-triggers option is specified. I made the decision to recreate the identical circumstance so that everyone could see it.

Test environment : 

OS: Amazon Linux 2

MySQL version: 5.7.40

pt-online-schema-change version: 3.1.0

Before digging deeper, we must first obtain the whole picture. Here is an illustration of the table’s structure.

Table structure:

This is an update for MySQL 8 Stored PSM to add the ONLY_FULL_GROUP_BY mode to the global SQL_MODE variable when it’s not set during a session. Here’s the code:

/* Drop procedure conditionally on whether it exists already. */
DROP PROCEDURE IF EXISTS set_full_group_by;

/* Reset delimter to allow semicolons to terminate statements. */
DELIMITER $$

/* Create a procedure to verify and set connection parameter. */
CREATE PROCEDURE set_full_group_by()
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Set connection parameter when not set.'
BEGIN

  /* Check whether full group by is set in the connection and
     if unset, set it in the scope of the connection. */
  IF EXISTS
    (SELECT TRUE
     WHERE NOT REGEXP_LIKE(@@SESSION.SQL_MODE,'ONLY_FULL_GROUP_BY'))
  THEN
    SET @@GLOBAL.SQL_MODE := CONCAT(@@SESSION.sql_mode,',ONLY_FULL_GROUP_BY');
  END IF;
END;
$$

/* Reset the default delimiter. */
DELIMITER ;

You can call the …

Using a MySQL HeatWave Database Service instance in OCI as an asynchronous replica is very useful. It allows testing the service with always updated data from on-premise production or from another cloud environement. It can be used in the process  of migrating with minimal downtime to OCI and finally, it can also be used between […]

As a MySQL database administrator, have you ever faced a brute force attack on your database server or been the target of a DDOS-like connection flow on port 3306? If so, you will quickly understand why this plugin distributed with MySQL can be very useful in such situations. Indeed, the connection-control plugin allows the administrator […]

Looking at how people are using COUNT(*) and COUNT(col), it looks like most of them think they are synonyms and just use what they happen to like, while there is a substantial difference in performance and even query results. Also, we find a difference in execution on InnoDB and MyISAM engines.

NOTE: All tests were applied for MySQL version 8.0.30, and in the background, I ran every query three to five times to make sure that all of them were fully cached in the buffer pool (for InnoDB) or by the filesystem (for MyISAM).

Count function for Innodb engine:

Let’s have look at the following series of examples for InnoDB engine:

CREATE TABLE count_innodb (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  val_with_nulls int(11) default NULL,
  val_no_null int(10) unsigned NOT NULL,
  PRIMARY KEY idx (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

(mysql) > select count(*) from count_innodb; …

After having see how we can import data from PostgreSQL and Amazon Redshift, this time we will see how we can export data from Microsoft SQL Server and import it into MySQL HeatWave Database Service in OCI. This time we will use something extra (for fun but also because it’s practical): OCI Object Storage ! […]

Thank you so much for reading OpenLampTech and making it the success it is today. Wow! 600 developers reading each week! I am humbled to say the very least.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #60, we are looking at some fantastic articles covering:

• Laravel Eloquent and Query Builder tips
• Drupal’s updated CKEditor 5
• How Symfony powers Drupal
• Best SQL Editors
• WooCommerce Payment Gateways
• And much more

Want to help OpenLampTech be a success …

If you need test data for the database of your project, you can get a dataset from Kaggle or use a data generator. In the first case, if you need to process the data before inserting it into the database, you can use Pandas, a widely used Python library for data analysis. This library supports different formats, including CSV and JSON, and it also provides a method for inserting data into a SQL database.

If you choose a data generator instead, you can find one for MySQL in one of the repositories on our Percona Lab GitHub account. Are you using other database technologies? You can follow the guides I already published where I explain how to create your own data generator for …