MySQL Linux to Windows

Uncategorized
| May 3, 2020

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 (more...)

Creating a re-usable Vagrant Box from an existing VM with Ubuntu and k3s (with the Kubernetes Dashboard) and adding mysql, using Vagrant and Oracle VirtualBox

Uncategorized
| Apr 30, 2020

In a previous article, I shared with you the steps I took, to get k3s installed (with the Kubernetes Dashboard) on top of an Ubuntu guest Operating System within an Oracle VirtualBox appliance, with the help of Vagrant.
[ https://technology.amis.nl/2020/01/15/rapidly-spinning-up-a-vm-with-ubuntu-and-k3s-with-the-kubernetes-dashboard-on-my-windows-laptop-using-vagrant-and-oracle-virtualbox/]

For training and demo purposes, I wanted to add mysql to my k3s demo environment and in order to test the environment, I also wanted to include the book service (RESTful Web Service (more...)

Aurora MySQL – Export data to S3

Uncategorized
| Apr 16, 2020

Using SELECT INTO OUTFILE S3 you can query data from an Aurora MySQL DB cluster and save it directly into text files stored in S3 bucket.

1. Create an IAM policy for S3.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "s3:DeleteObject",
        "s3:GetBucketLocation",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads",
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::bucket-name",
        "arn:aws:s3:::bucket-name/*"
      ]
    },
    {
      "Sid": "VisualEditor1",
      "Effect": "Allow",
      "Action": [
        "s3:ListAllMyBuckets",
        "s3:HeadBucket"
      ],
      "Resource": "*"
    }
  ]
}

2. Create (more...)

Java & MySQL 8.0.19

Uncategorized
| Apr 10, 2020

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 (more...)

Mysql Data Types and Sizes for String/ Text

Uncategorized
| Oct 2, 2019
MySQL or MariaDB has several data types for handling text-based characters. There are several data types for handling smaller strings such as CHAR and VARCHAR data types. For larger text-based strings there are BLOB based data types such as TEXT. It’s worth noting at this point that the below-quoted sizes do not necessarily represent the

Docker Compose yml for WordPress

Uncategorized
| Sep 6, 2019
Create a new directory and save the above file inside it as docker-compose.yml Run docker-compose up -d to fetch the images from the docker hub and create your wordpress instance. You’ll be able to access wordpress from a browser on port 8000.

When AWS SCT Unable to Connect to MySQL in RDS or EC2

Uncategorized
| Mar 22, 2019
AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.




2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
(more...)

Proactive Oracle Support?

Uncategorized
| Mar 1, 2019

You may not be aware but Oracle have been talking about their new ProActive support with user groups. It’s basically trying to show you best practices you should be implementing before stuff goes wrong, and many other useful bits of information.

It covers a lot of areas and is really worth a look, whether you’re using Oracle Database, Linux, MySQL, EBS, JDEdwards or indeed most Oracle products.

The open URL for headline information is: https://www. (more...)

Beware (Sort-Of) Ambiguous Column Names In Sub-Selects

Uncategorized
| Apr 30, 2018
This morning I received an UPDATE statement from a developer that I was testing. It ran without errors but then I saw that it updated 5 rows when it should have only updated 3. The reason gave me a little shock so I whipped up a simple test-case to reproduce the problem.

First we create two tables:

CREATE TABLE foo (
    id int
    , name varchar(30)
);
CREATE TABLE
CREATE TABLE (more...)

Global variable wsrep_OSU_method=RSU ignored on MariaDB Galera Cluster 10.0.32

Uncategorized
| Jan 20, 2018
Galera Cluster is great solution for application clustering but it has some limitations. One of the most critical limitations is how Galera Cluster performs model changes.

From Galera Cluster documentation:http://galeracluster.com/documentation-webpages/clusterstallonalter.html
What’s happening is a side effect of a multi-master cluster with several appliers. 
The cluster needs to control when a DDL statement ends in relation to other transactions,
in order to deterministically detect conflicts and schedule parallel appliers.
Effectively, the DDL statement (more...)

Optimizer Strategien für Subqueries für Oracle, Postgres und MySQL

Uncategorized
| Jan 2, 2018
Chris Antognini hat in seinem Blog eine sehr spannende Untersuchung zur Frage durchgeführt: wie gut kommen die Optimizer unterschiedlicher RDBMS mit relativ einfachen Subqueries klar? In der umfangreichen Untersuchung betrachtet er sechs unterschiedliche Subquery-Typen:
  • Scalar subqueries with equality predicate
  • Scalar subqueries with inequality predicate
  • Uncorrelated subqueries with either IN or EXISTS
  • Uncorrelated subqueries with either NOT IN or NOT EXISTS
  • Correlated subqueries with either IN or EXISTS
  • Correlated subqueries with either NOT IN or NOT (more...)

How Well a Query Optimizer Handles Subqueries?

Uncategorized
| Dec 31, 2017

At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence:

Simple sub-queries that are not correctly optimized were observed.

It goes without saying that such a sentence leaves a lot of questions open. After (more...)

Oracle Week 2017: Docker Concepts for Oracle/MySQL DBAs and DevOps (slides)

Uncategorized
| Nov 28, 2017

Oracle Week in Israel is a reason for celebration. Every year (and for the past 24 years), between a 1500 and 2000 Oracle professional are gathered for a 5 days conference on Oracle related educational topics. Each day is a full-day seminar on a specific topic – 9am to 4:30pm talking about something related to Oracle technologies. This year, I had 4 full-day back-to-back seminars – which is way more than the average speaker. Some (more...)

Beware of intensive slow query logging when using – log_queries_not_using_indexes

Uncategorized
| Oct 21, 2017
MySQL slow query log is great for identifying slow queries that are good candidates for optimisation. Slow query logging is disabled by default, but it is activated by DBA's or developers on most environments.

You can use slow query log to record all the traffic but be careful with this action as logging all traffic could be very I/O intensive and could have negative impact on general performance. It is recommended to record all traffic (more...)

Delete large amounts of data on Galera Cluster using pt-archiver

Uncategorized
| Sep 28, 2017
Galera Cluster is excellent virtually synchronous multi-master database cluster. It has many benefits which you can check on GaleraCluster.
But beside benefits it has some limitations and one of them is handling large transactions.

Large replication data sets could degrade performance of whole cluster causing cluster freezing, increased memory consumption, crashing nodes, etc. To avoid this issues it is recommended to split large transactions into smaller chunks.

In this post I want to show you (more...)

Wanted: RDBMS superpower summary for app developers

Uncategorized
| Nov 13, 2015

At last night's WWCode Cincinnati panel, I recommended that developers talk to their DBA about what advanced capabilities their RDBMS can offer, so that they don't end up reimplementing functionality in the app that are already available (better and more efficiently) in the database itself. Devs can waste a lot of effort by thinking of databases as dumb, inert data boxes.

I was asked an excellent question: "Where can a dev quickly familiarize herself with (more...)

Couchbase 4.0 and related subjects

Uncategorized
| Oct 15, 2015

I last wrote about Couchbase in November, 2012, around the time of Couchbase 2.0. One of the many new features I mentioned then was secondary indexing. Ravi Mayuram just checked in to tell me about Couchbase 4.0. One of the important new features he mentioned was what I think he said was Couchbase’s “first version” of secondary indexing. Obviously, I’m confused.

Now that you’re duly warned, let me remind you of aspects of (more...)

Confusion and problems with lost+found directory in MySQL/Galera cluster configuration

Uncategorized
| Oct 7, 2015
The lost+found directory is filesystem directory created at root level of mounted drive for ext file systems. It is used by file system check tools (fsck) for file recoveries.

In MySql world it can cause confusion or possible problems with synchronisation in Galera cluster configuration.

Let’s check some examples.

I have MySQL database with datadir=/data in configuration file. I have deleted lost+found directory and restarted MySQL service.

When I list my databases this is result:

(more...)

Test MySQL on AWS quickly

Uncategorized
| Feb 19, 2015

Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. 

Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)

MariaDB – Measure Replicaton Lag and Check / Fix Replication Inconsistencies using Percona tools

Uncategorized
| Feb 5, 2015
Percona Toolkit is collection of command-line tools to perform many MySQL tasks like creating backups, finding duplicate indexes, managing replication, etc.

In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools:
pt-heartbeat
pt-table-checksum
pt-table-sync


I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.


Install Percona Toolkit on both nodes:


$ sudo wget percona.com/get/percona-toolkit.deb

$ (more...)