Feed Aggregator

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
]:

Disabling Deadlock Detection On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

And about the parameter innodb_deadlock_detect itself
[2
] itself:

This option is used to disable deadlock detection. On …

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
  
    FETCH cCleanUp INTO vId;
    IF vNotFound = 1 THEN 
      LEAVE lGetRecord;
    END IF;
  
    DELETE FROM test WHERE id = vId;
  END LOOP lGetRecord;
  CLOSE cCleanUp;
END //
DELIMITER ;

CALL cleanup('Bla1Trx');

Stored Procedure with SUPER PRIVILEGE

Disabling binary log on a per session base requires the SUPER privilege:

SQL> SET SESSION sql_log_bin = off;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

If you do NOT want to GRANT the SUPER …

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 various advices in the Internet about how to get rid of this Note:

innodb_lru_scan_depth        = 1024, 256
innodb_buffer_pool_instances = 1, 8
innodb_io_capcity            = 100, 200 or 1000
innodb_page_cleaners         = 1, 4 or 8

But non of these changes made the Note go away in our case. I only found one voice claiming it could be an external reason which makes this message appear. Because we are actually running on a Cloud-Machine the appearance of this message could really be an effect of the Cloud and …

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 you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for focmm).

Any feedback, statements and testimonials are welcome as well! Please send them to …

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 architecture solving the described problem.
• 3
  . Evaluating a product or solution which is specialised solving the described problem. Check if the product/solution matches your problem.
  Caution: In this topic we are strongly MariaDB/MySQL biased!
• 4
  . Do a Proof of Concept (PoC) to verify the chosen solution will fix your described problem (or not).

Splitting Database Tables (BLOB/TEXT)

Sometimes it might be useful to split 1 entity (table) into 2 separate tables. This makes sense when you have either:

• hot …

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 MariaDB and MySQL (fpmmm) can be downloaded from here. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In case you find a bug in the FromDual Performance Monitor for MariaDB and MySQL please report it to the FromDual Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for fpmmm).

Any feedback, statements and testimonials are welcome as well! Please send them [to us](mailto:feedback@fromdual.com?Subject=Feedback for fpmmm “Feedback 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 more FromDual customers consider a migration from MySQL to MariaDB we were testing some migration paths to find the pitfalls. One upgrade of some test schemas led to the following warnings:

# mysql_upgrade --user=root
MariaDB upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv                                 OK
...
mysql.user                                         OK
Phase 2/7: Installing used storage engines
Checking for tables with unknown …

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 ${DEV}
eth0 Link encap:Ethernet  HWaddr d0:50:99:70:f8:8e
     inet addr:192.168.1.135  Bcast:192.168.1.255  Mask:255.255.255.0
     UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
     RX packets:4005096 errors:0 dropped:365956 overruns:0 frame:0
     TX packets:3293230 errors:0 dropped:0 overruns:0 carrier:0
     collisions:0 txqueuelen:1000
     RX bytes:2897493579 (2.8 GB)  TX bytes:461542567 (461.5 MB)

As we all learned ifconfig is old and thus bad so we should use the new command ip instead. See …

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';
GRANT RELOAD ON *.* to 'logrotate'@'localhost';

This user needs a password file:

#
# /root/.logrotate.cnf (chmod 600)
#

[mysql]
user     = logrotate
password = secret

And this is my log rotation configuration file:

#
# /etc/logrotate.d/mysql
#

/var/log/mysql/error.log {
  compress
  notifempty
  size 100M
  missingok
  create 640 mysql mysql
  rotate 5
  maxage 365
  postrotate
    /usr/bin/mysql --defaults-extra-file=/root/.logrotate.cnf --execute='FLUSH ERROR LOGS'
  endscript
} …

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
...
  progress      Print progress information to STDOUT.
...

A backup for example is done as follows:

# ./bin/bman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102

The Recovery Manager progress indicator logs to STDOUT:

# ./bin/rman --target=brman:secret@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql

Reading configuration from /etc/mysql/my.cnf …

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 utf8mb4 Character Set. In the same MySQL consulting engagement we considered to upgrade from MySQL 5.6 to MySQL 5.7 as well
[ Lit.
]. We decided to split the change in 2 parts: Upgrading to 5.7 in the first step and converting to uft8mb4 in the second step. There were various reasons for this decision:

• 2 smaller changes are easier to control then one big shot.
• We assume that in 5.7 we experience less problems with utf8mb4 because the trend given by MySQL was more towards utf8mb4 in 5.7 than in MySQL 5.6. So …

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 Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for focmm).

Any feedback, statements and testimonials are welcome as well! Please send them to …

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 FromDual Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for brman).

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 2.x to 2.2.1

shell> cd ${HOME}/product
shell> tar xf /download/brman-2.2.1.tar.gz
shell> rm -f brman
shell> ln -s brman-2.2.1 brman

Changes in FromDual Backup and Recovery Manager 2.2.1

This release is a new minor release. It contains only bug fixes. We have tried to …

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:

SQL> SELECT plp.id, plp.related_type, plp.date_modified, plp.deleted
     , pl.name
  FROM prospect_lists_prospects AS plp
  JOIN prospect_lists AS pl ON plp.prospect_list_id = pl.id
 WHERE plp.related_id = @uid
;

3. Update entry in Target List table:

SQL> START TRANSACTION;
SQL> UPDATE prospect_lists_prospects
   SET deleted = 1
 WHERE related_id = @uid
   AND id = '5b3047b2-3b45-1a6e-e6fb-5d3fe1efa93b'
;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
SQL> …

FromDual has the pleasure to announce the release of the new version 2.2.0 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 FromDual Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for brman).

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 2.x to 2.2.0

shell> cd ${HOME}/product
shell> tar xf /download/brman-2.2.0.tar.gz
shell> rm -f brman
shell> ln -s brman-2.2.0 brman

Changes in FromDual Backup and Recovery Manager 2.2.0

This release is a new major release series. It contains some new features. We have tried …