On Monday April 27th, MySQL released a much-anticipated patch release 8.0.20. Along with many bug fixes and improvements, a new property was introduced – binlog-transaction-compression. During our own internal testing we have discovered an incompatibility with our Continuent Tungsten products when this property is enabled.

The newly-released binlog-transaction-compression feature is really interesting because it compresses transaction payloads before being written into the binary logs, which in turn reduces the disk space overhead required for storage. I’m sure many users will be keen to implement this, however at this time the use of binlog-transaction-compression=ON will prevent Replication from functioning correctly.

This …

Since the beta of Galera Cluster 4 with MySQL 8 has been released, we’ve had people asking questions as to how to install it on Ubuntu 18.04. This blog post will cover just that.

Prerequisites

• All 3 nodes need to have Ubuntu 18.04 installed
• Firewall (if setup) needs to accept connections on 3306, 4444, 4567, 4568 (a default setup has the firewall disabled)
• AppArmor disabled (this is as simple as executing: systemctl stop apparmor and systemctl disable apparmor).

Installation and Configuration

We have good installation documentation as well as a quick how to get this installed in AWS (though this is CentOS centric).

First, you will need to ensure that the …

My students want to transfer their course setup MySQL files from the Linux VM that I provide to Microsoft Windows 10. This post explains how because I found a couple small errors in the Google’d posts they would most likely see.

The first step is required because when I didn’t assign a name or domain to the the Fedora VM, which allows it to run as localhost on any student machine. In tandem, I didn’t assign a static IP address but opted for dynamic IP assignment. That means, the first step to securely copy the files requires you to find the assigned IP address. You can do that with the following Linux command:

ifconfig -a | grep 'inet[[:blank:]]' | head -1 | cut -c 14-30

It would return something like:

192.168.147.198

After you have discovered the IP address, you need to download PuTTy from their web site because includes the pscp (PuTTy …

MySQL NDB Cluster 8.0.20 new features 

This post is about changes in the implementation of NDB Cluster from MySQL NDB Cluster 8.0 through 8.0.20, as compared to earlier release series. We have included only those of MySQL NDB Cluster 8.0.20 new features which are really interesting and can directly influence / make an impact to performance, scalability and reliability:

• MySQL NDB Cluster  development is in parallel with the development of  MySQL Server going forward. What does that mean for MySQL customers globally NDB 8.0 is developed in, built from, and released with the MySQL 8.0 source code tree and numbering scheme for NDB Cluster 8.0 releases follows the scheme for MySQL 8.0 ( starting with version 8.0.13).
• As of NDB 8.0.18, The identifiers can use up to 64 bytes for databases and tables (the 63-byte limit on identifiers is removed ).
• Generated names for foreign keys …

A time for new beginnings beckons upon us, and Codership is pleased to announce the much anticipated Release Candidate of MySQL 8.0 that comes with Galera 4. It comes with MySQL 8.0.19 and includes the Galera Replication Library 4.5 Release Candidate and wsrep API version 26. 

Galera 4 and MySQL 8.0.19 have many new features, but here are some of the highlights:

• Streaming replication to support large transactions by splitting transaction replication then applying them in smaller fragments.
• Improved foreign key support, as write set certification rules are optimised and there will be a reduction in the number of foreign key related false conflicts in certifications.
• Group commit is …

It’s the in-between term time and we’re all stuck at home. I decided to update the image for my Fedora 30 virtual machine. I had a work around to the update issue that I had encountered last October in Bug #96969 but it was not required with the current version. However, after updating from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed.

The $CLASSPATH value was correct:

/usr/share/java/mysql-connector-java.jar:.

The first error that I got was the my reference to MySQL JDBC driver was incorrect. The error message is quite clear:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Cannot connect to database server:
The server time zone …

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

1. You set a session variable on a single line with the following …

Six years ago I wrote a common lookup post to illustrate the effectiveness of things used throughout your applications. Now, I’m updating my student image with a more complete solution to show how to avoid update anomalies.

In the prior post, I used a while loop in PHP, like the following:

do {
      ...
} while($stmt->next_result());

Using PHP Version 7.3.8 and MySQL 8.0.16, that now raises the following error message:

Strict Standards: mysqli_stmt::next_result(): There is no next result set. Please, call mysqli_stmt_more_results()/mysqli_stmt::more_results() to check whether to call this function/method in /var/www/html/app/library.inc on line 81

You can see this type of error when you set the following parameters in your file during testing:

ini_set('display_errors',1); …

Somebody didn’t like the MySQLi Update Query example on the tutorialspoint.com website because it use the procedure mysqli_query style. Here’s a simple example of using the object-oriented method version. More or less, instead of query it uses the more intuitive execute() method.

The update_member function contains the logic and below it is a call to the test the function. It relies on a MySQLCredentials.inc file that contains the hostname, user name, password, and database name. You can create create member table, like my example in MySQL 8, or any other table in your MySQL database.

<?php /*
||  Function Name: update_member
*/
function update_member($account_number, $member_type, …

Somebody asked me how to expand a prior example with the static variables so that it took arguments at the command line for the variables. This example uses Python 3 new features in the datetime package.

There’s a small trick converting the string arguments to date data types. Here’s a quick example that shows you how to convert the argument list into individual date data type variables:

#!/usr/bin/python3

# include standard modules
import sys
from datetime import datetime

# Capture argument list.
fullCmdArguments = sys.argv

# Assignable variables.
beginDate = ""
endDate = ""

# Assign argument list to variable.
argumentList = fullCmdArguments[1:]

# Enumerate through the argument list where beginDate precedes endDate as strings.
try:
  for i, s in enumerate(argumentList):
    if (i == …