News

On StackExchange for Database Administrators I recently have seen a question which attracted my interest.

The question puzzled me a bit because the answer seems too easy. Further the question was not so clear. An all theses factors smell dangerous…

About time - was, is and will be

How can I find out if the database “was” shutdown slowly? This is quite easy: Look into your MySQL Error Log and there you will find a log sequence similar to the following:

2020-03-30T08:03:36.928017Z 0 …

Recently we had a new customer who has had from time to time massive database problems which he did not understand. When we reviewed the MySQL configuration file (my.cnf) we found, that this customer had disabled the InnoDB Deadlock detection (innodb_deadlock_detect).

Because we have advised against doing this so far, but I never stumbled upon this problem in practice, I have investigated a bit more about the MySQL variable innodb_deadlock_detect.

The MySQL documentation tells us the following
[1
]: …

MariaDB/MySQL Stored Language is called SQL/PSM.
There are 4 different types of Stored Language: Stored Procedures, Stored Functions, Triggers and Events.

Stored Procedures

Stored Procedure with a Cursor:

DELIMITER //
CREATE PROCEDURE cleanup(IN pData VARCHAR(48))
BEGIN
  DECLARE vId INTEGER;
  DECLARE vNotFound INTEGER;

  DECLARE cCleanUp CURSOR FOR
    SELECT id FROM test WHERE data = pData;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET vNotFound = 1;
  
  OPEN cCleanUp;
  lGetRecord: LOOP …

On 1 March 2020 FromDual became 10 years old! Sincere thanks are given to all our customers, partners and interested person for their support and good cooperation in the last 10 years. And we would be pleased to advise and support you again competently in the coming 10 years.

Your FromDual Team

Picture by kalhh on Pixabay

Recently we migrated a database system from MySQL 5.7 to MariaDB 10.3. Everything went fine so far just the following message started to pop-up in the MariaDB Error Log File with the severity Note:

InnoDB: page_cleaner: 1000ms intended loop took 4674ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.)

I remember that this message also appeared in earlier MySQL 5.7 releases but somehow disappeared in later releases. I assume MySQL has just disabled the Note?

You can find …

FromDual has the pleasure to announce the release of the new version 0.9.3 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB and MySQL.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA’s and System Administrators to better manage their MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center …

When it comes to MariaDB/MySQL Database consulting engagements we often see, that our customers are very confused by many different technologies advertised to them.

Below you will find some simple concepts and ideas we use during our consulting engagements to solve some issues and answer some questions.

General problem solving approach

• 1
  . Written description of the problem to solve. This helps to learn about your problem and better understand your problem.
• 2
  . Searching for a suitable method, technology or …

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular Database Performance Monitor for MariaDB, MySQL and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB and MySQL (fpmmm) enables DBAs and System Administrators to monitor what is going on inside their MariaDB and MySQL databases and on their machines where the databases reside.

More detailed information your can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for …

You are using an old version of the FromDual Performance Monitor for MariaDB and MySQL (fpmmm). Please consider using a more recent version.

Your InnoDB Redo Log Buffer is too small for large transactions. Make the InnoDB Redo Log Buffer (innodb_log_buffer_size) bigger if you have enough RAM available to avoid additional I/O.
1 Mibyte is good for databases with small transactions. 8 Mibyte is good for medium size transactions. 64 MiByte is good for large transactions.

See also for MariaDB MySQL.

Table of Contents

• Before you start
• Infinite Loop Example

Before you start

SQL> SET SESSION sql_mode='ORACLE';

Infinite Loop Example

DELIMITER /

BEGIN

LOOP
  SELECT 'Hello world from MariaDB anonymous PL/SQL block!';
END LOOP; 

END;
/

DELIMITER ;

Up to version 5.5 MariaDB and MySQL can be considered as “the same” databases. The official wording at those times was “drop-in-replacement”. But now we are a few years later and times and features changed. Also the official wording has slightly changed to just “compatible”.
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and …

Table of Contents

• ifconfig
• ip
• ethtool --statistics
• nstat
• netstat --statistics
• ss
• Reasons for dropped packets
• Features of Network card
• Features of Network driver
• Network driver ring buffer
• TCP socket received and send buffer
• Flow Control
• Check over time: sar
• Dropwatch
• perf
• tcpdump
• /proc/net/netstat
• System Tap (stap)
• Literature

When we do MariaDB and MySQL Database server analysis we see from time to time systems with a lot of dropped packets on a network interface:

ifconfig

# export DEV=enp0s25
# ifconfig …

Modern Linux Systems have a mechanism called logrotate to rotate different log files.

The general configuration file is located under /etc/logrotate.conf and specific changes are under /etc/logrotate.d/

By default the logrotate job is started once a day by a O/S cron.daily job: /etc/cron.daily/logrotate

Because the default log rotation configuration does not exactly what I want I have adapted it a bit:

First I need a database user for log rotation:

CREATE USER 'logrotate'@'localhost' IDENTIFIED BY 'secret'; …

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman users who were not happy with the default mysql behavior.

You can check your current rman version as follows:

# ./bin/rman --version
2.2.1

As with all FromDual tools you get a command overview with examples with the --help option:

# ./bin/rman --help | less
... …

Table of Contents

• Introduction
• Character Sets
• Steps to convert Character Set to utf8mb4
  • Analyzing the Server
  • Analyzing the Application and the Clients
  • Preparation of the Server Settings and the Application
  • Convert Tables to utf8mb4
  • Testing of new Character Set
• MySQL Pump
• MySQL Master/Slave Replication for Character Set conversion
• MySQL Shell, mysqlsh Upgrade Checker Utility

Introduction

Recently we had a consulting engagement where we had to help the customer to migrate from latin1 Character Set to …

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA’s and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops …

FromDual has the pleasure to announce the release of the new version 2.2.1 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

Table of Contents

• Unlink multiple records from Target List
• Find Target Lists of Target/Prospect
• List all email addresses of all accounts
• Objects without Target List
• Find PLP without Objects
• Find Prospect List Entries per Prospect Type
• Quick set-up on Ubuntu 18.04
• Add targets from old target lists to new target list
• Undelete accounts

Unlink multiple records from Target List

1. Find id of record to unlink.

SQL> SET @uid = 'f232e8d0-9b82-20f7-7c35-5c1a5ce94e0a';

2. Find entry in Target List table: …