Nowadays everything is getting digitalized and migration toward the cloud is at its peak. There is a high chance of data leaks if we don’t tighten the security of the Database servers. It is mandatory to secure the database by restricting access to Database. Although we have restricted the access. We need to monitor the activity of the user to prevent the unwanted usage of data.

Security will be split into three layers

1. Network-level security.
2. OS level security.
3. Database level security.

OS level security will be handled by the AWS team. Since It is managed by the AWS Team. So all the security patching, minor version upgrades of OS, and kernel tuning will be governed by the AWS infra team.

Network-level security and database-level security are owned by the end user.

This post explains and demonstrates how to install, configure, and use the MySQL’s ODBC libraries and a DSN (Data Source Name) to connect your Microsoft PowerShell programs to a locally or remotely installed MySQL database. After you’ve installed the MySQL ODBC library, use Windows search field to find the ODBC Data Sources dialog and run it as administrator.

There are four steps to setup, test, and save your ODBC Data Source Name (DSN) for MySQL. You can click on the images on the right to launch them in a more readable format or simply read the instructions.

MySQL ODBC Setup Steps

1. Click the SystemDSN tab to see he view which is …

If you have worked with an RDBMS for some time, you will likely have come across the statement SELECT 1.

However, rarely is it correctly explained to engineers what the origin of SELECT 1 is, and why it’s useless and wasteful? A google search is not going to give you the response you would hope, these ranked responses are just as useless as the statement itself.

Bloat

Seeing a SELECT 1 confirms two things. First you are using a generic ORM framework, quote, and second, you have never optimized your SQL traffic patterns.

“Frameworks generally suck.
They CLAIM to improve the speed of development and abstract the need to know SQL.
The REALITY is the undocumented cost to sub-optimal performance, especially with data persistence.”

Connection Pooling

SELECT 1 comes from …

A student wanted a better example of writing a MySQL Persistent Stored Module (PSM) that maintains transactional scope across a couple tables. Here’s the one I wrote about ten years ago to build the MySQL Video Store model. It looks I neglected to put it out there before, so here it is for reference.

-- Conditionally drop procedure if it exists.
DROP PROCEDURE IF EXISTS contact_insert;

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$

SELECT 'CREATE PROCEDURE contact_insert' AS "Statement";
CREATE PROCEDURE contact_insert
( pv_member_type         CHAR(12)
, pv_account_number      CHAR(19)
, pv_credit_card_number  CHAR(19)
, pv_credit_card_type    CHAR(12)
, pv_first_name          CHAR(20)
, pv_middle_name         CHAR(20)
, pv_last_name           CHAR(20)
, pv_contact_type        CHAR(12)
, pv_address_type        CHAR(12)
, pv_city                CHAR(30)
, pv_state_province      CHAR(30)
, …

MySQL Shell For VS Code integrates the MySQL Shell directly into VS Code development workflows and was released last week. This extension to the popular VS Code platform enables interactive editing and execution of SQL for MySQL Databases and optionally the MySQL Database Service for several simultaneous sessions.  It is a preview release and not ready for production but it does have several features that may make the MySQL GUI of choice.

Installation

The installation itself is easy but you will need to download the code from here and not the usual places for MySQL products.  You will, of course, have to have VS Code installed first, and be warned that some of the more tantalizing links for things like documentation are not connected.

MySQL Shell for VS Code installation screen and yes, you will need VS Code installed first.

Usage

The interface is familiar to that of MySQL Workbench but …

I recently needed to add multiple columns to an existing table to store summary data calculations and wondered if I could do it in one MySQL ALTER TABLE statement. Turns out you can. And, it’s super simple. Convenient too. Continue reading and learn what I learned…

Image by Gerd Altmann from Pixabay 

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you …

Read reactions and praises by leading industry analysts towards the March 2022 new feature announcement for Oracle MySQL HeatWave, a fully managed service that enables customers to run OLTP and OLAP workloads directly from their MySQL Database. New capabilities announced are: in-database machine learning, real-time elasticity to scale up or down, and compression, enabling 2X more data per node and a lower price entry point.

The utf8mb4 character set is the new default as of MySQL 8.0, and this change neither affects existing data nor forces any upgrades.

Migration to utf8mb4 has many advantages including:

• It can store more symbols, including emojis
• It has new collations for Asian languages
• It is faster than utf8mb3

Still, you may wonder how migration affects your existing data. This blog covers multiple aspects of it.

Storage Requirements

As the name suggests, the maximum number of bytes that one character can take with character set utf8mb4 is four bytes. This is larger than the requirements for utf8mb3 which takes three bytes and many other MySQL character sets.

Fortunately, utf8mb3 is a subset of …

New features and capabilities being introduced with MySQL HeatWave ML.

I’ve once again published a massive issue of the OpenLampTech newsletter, the newsletter for PHP/MySQL developers. If you’re looking to learn more about PHP and MySQL, you’ve come to the right place…

Image by Clker-Free-Vector-Images from Pixabay

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 …