Feed Aggregator

Hi Please resolve my problem as we are unable to stop instance in myenv but start is working fine.

[root@ip-172-31-46-213 ~]# su - mysql Last login: Fri Sep 24 10:56:54 UTC 2021 on pts/1

Up : Master (5.7.30) Slave (5.7.30)

Down :

Master (:3306 ) : Slave (:3307 ) :

mysql@ip-172-31-46-213:~ [Master, 3306]> up

Up : Master (5.7.30) Slave (5.7.30)

Down :

Master (:3306 ) : Slave (:3307 ) :

mysql@ip-172-31-46-213:~ [Master, 3306]> stop ERROR: Cannot find nor guess PID file (rc=520). Is it possible that the database is already stopped? (rc=520) ERROR: Stopping instance Master failed (rc=546).

We currently have the situation that a customer cannot upgrade its hyper-consolidated Galera Cluster for more than a year because various different applications cannot, are not willing to or do not have the time to test against the newer version.

What what we suggest here: Create a new Galera Cluster with the new version and move over one project/application after the other to the new Cluster. This reduces complexity. You can do slightly progress and the pressure for the "slow-upgrader" increases over time.

Nearly everybody does backups. But nobody needs backups! What everybody wants and needs is a working restore not a working backup…

So how to make sure that your backup is working for the restore? There are a few things you can do already during your backup:

• Check that your backup was running fine. For example by checking the return code of your backup.
• Check the runtime of your backup. If the runtime of your backup significantly changed, it is worth to have a closer look at the backup.
• Check the size of your backup. If the size of your backup significantly changed, it is worth to have a closer look at your backup.
• And finally make your monitoring system aware if the backup was NOT running at all and if you are sure your backup is really triggered…

Backup test with FromDual Enterprise Tools

All this functionality is integrated in the newest releases of FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) and the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) in …

FromDual has the pleasure to announce the release of the new version 2.2.4 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 described 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.4

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

Changes in FromDual Backup and Recovery Manager 2.2.4

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

Foreign Key Constraints are used to model a parent/child relation in an entity relationship (ER) model:

SQL> CREATE TABLE team (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(48) NOT NULL
, PRIMARY KEY (id)
);

SQL> CREATE TABLE employee (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, first_name VARCHAR(32) NOT NULL
, last_name VARCHAR(64) NOT NULL
, team_id BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (id)
, CONSTRAINT `fk_employee_team` FOREIGN KEY (team_id)
  REFERENCES team (id) ON DELETE CASCADE ON UPDATE RESTRICT
);
ERROR 1005 (HY000): Can't create table `test`.`employee` (errno: 150 "Foreign key constraint is incorrectly formed")

The same errors looks a bit different in MySQL 8.0:

ERROR 3780 (HY000): Referencing column 'team_id' and referenced column 'id' in foreign key constraint 'fk_team' are incompatible.

To see more details about this error you can look in the InnoDB status output:

SQL> SHOW ENGINE INNODB STATUS<br>G
...
------------------------
LATEST …

Table of Contents

• What is SNMP?
• SNMP Agent (snmptrap)
• SNMP Manager (snmptrapd)
• Test the SNMP Agent
• Creating your own MIB
• Sending MariaDB SNMP traps from PHP
• Literature

What is SNMP?

A customer recently had the question if an how his MariaDB database can be easily monitored with SNMP?

SNMP means Simple Network Management Protocol. It is a widely used and standardized protocol for monitoring the health of network and other devices (including services). In principle you can monitor nearly everything with SNMP.

On Linux a common implementation of SNMP is Net-SNMP, a suite of applications used to implement SNMP v1, SNMP v2c and SNMP v3 using both IPv4 and IPv6.

SNMP is a typical client-server architecture: The client which is collecting and sending the monitoring data is called agent and the server collecting all the monitoring data is called manager.

An agent can be polled by the manager to collect the monitoring data (Request/Responses) or it can send monitoring data on its own …

FromDual has the pleasure to announce the release of the new version 2.0.3 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.x to 2.0.3

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

Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv
shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/

Upgrade of the instance directory structure

From MyEnv …

Hello Leo

Thank you for your question. What did you try so far? Or where did you stuck? Can you show us the query you have created so far?

An alternative would be to use HeidiSQL, MySQL Workbench or phpMyAdmin to design the right query.

Possibly the GROUP BY clause is what you are looking for. Please read here.

Regards, Oli

Hello,

Thank you for this great blog post.We use suitecrm and we are having issues creating a query.

We have different target lists, for example Code violations Vacants Absentee … and we wanted to know how can we show the list of targets and sort them by the target list count?

For example joe doe - 3 list mary doe - 2 list peter doe - 1 list

This way we can see which record is in the most lists? and possible see what list they are in?

thank you

This is my cheat sheet for dimensional modelling design techniques of a data warehouse (DWH) according to Kimball/Ross.

Dimensional Design Process (p. 38 ff.)

• Select the business process.
• Declare the grain (what a single fact table row represents).
• Identify the dimensions.
• Identify the facts.

Dimension Tables (p. 46 ff., p. 62 ff.)

who, what, where, when, why and how

• Dimension tables are entry point to the fact tables.
• Every dimension table has a single primary key (PK) column.
• Dimension tables are usually wide, flat denormalized tables.
• Use Surrogate Keys (synthetic Primary Keys) instead of Natural Keys (NK).
• Centipede facts should be avoided (year_dim + month_dim + day_dim vs date_dim).
• Resist the normalization urges and denormalize instead into a flattened row for simplicity and speed!
• Avoid cryptic abbreviations, use meaningful words instead.
• NULL comes from not yet populated attributes. Avoid NULL on dimensions use “Dummy strings” instead.
• A dimension can have different roles …

Storage Engines like InnoDB, Aria and MyISAM are Row Stores. They store rows one after the other in blocks or even directly in a single file (MyISAM). On the other hand a Column Store like MariaDB ColumnStore stores all the same attributes (columns) of the rows together in chunks.

This is how the table sales_fact looks like:

CREATE TABLE `sales_fact` (
  `product_id` int(11) NOT NULL,
  `time_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `promotion_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `store_sales` decimal(10,2) NOT NULL,
  `store_cost` decimal(10,4) NOT NULL,
  `unit_sales` int(11) NOT NULL
) ENGINE=ColumnStore DEFAULT CHARSET=utf8;

CREATE TABLE `sales_fact` (
  `product_id` int(11) NOT NULL,
  `time_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `promotion_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `store_sales` decimal(10,2) NOT NULL,
  `store_cost` decimal(10,4) NOT NULL,
  `unit_sales` int(11) NOT NULL,
  KEY `i_sales_customer_id` (`customer_id`), …

#
# /etc/systemd/system/glbd.service
#

[Unit]
Description=Galera Load Balancer Service
After=network.target

[Service]
#
# adjust the EnvironmentFile variable for your distribution
#
# On Redhat and derivatives it should be /etc/sysconfig/glbd.conf
#
# On Debian  and derivatives it should be /etc/default/glbd.conf
#
EnvironmentFile=/etc/sysconfig/glbd.conf
Type=simple
ExecStart=/usr/sbin/glbd --daemon --threads $THREADS --max_conn $MAX_CONN $OTHER_OPTIONS --control $CONTROL_ADDR $LISTEN_ADDR $DEFAULT_TARGETS

[Install]
WantedBy=multi-user.target

#
# This is a configuration file for glbd service script
#
# On Red Hat and derivatives it should be placed in /etc/sysconfig/glbd.conf
#
# On Debian  and derivatives it should be placed in /etc/default/glbd.conf
#
# All settings besides LISTEN_ADDR are optional.
#

# Address to listen for client connections at. Mandatory parameter.
# To bind to all interfaces only port should be specified.
#LISTEN_ADDR="0.0.0.0:3306"

# Address for controlling connection. Mandatory part is port.
# If not specified control socket will not be opened
#CONTROL_ADDR="127.0.0.1 8081"

# Control FIFO name. It is opened always. glbd will refuse to start if
# this file already exists.
#CONTROL_FIFO="/var/run/glbd.fifo"

# Number of threads (connection pools) to use. It is always a good idea
# to have a few per CPU core.
#THREADS="4"

# Maximum connections. System open files limit will be modified to accommodate
# at least that many client connections provided sufficient privileges.
# Normally you should …

Table of Contents

• Hardware requirements
• O/S (Linux) settings
• Installation
• Configuration
• Cross Engine Join User
• Start ColumnStore
• Create the Cross Engine Join User in the database
• MariaDB ColumnStore schemas
• Server System Variables and Server Status Variables
• MariaDB ColumnStore on the file system
• Creating our first table
• Querying and changing data in MariaDB ColumnStore
• MariaDB ColumnStore using sequences instead of AUTO_INCREMENT
• MariaDB ColumnStore Query Execution Plan

For a long time I wanted to investigate a bit more into the MariaDB ColumnStore architecture. Now I took the time to start with.

MariaDB ColumnStore is an interesting technology if you want to do reports (OLAP, data warehouse (DWH), BI) over only a few columns on a huge amount of rows and if you have only or mostly batch writes. These data are ideally spread (sharded) over several machines and the results where calculated on those different machines an aggregated. Because the data are stored in columns the locality of your data should be …

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

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

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

Download

The new FromDual Ops Center for MariaDB, MySQL and compatible databases (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, MySQL and compatible databases please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for …

InnoDB NUMA interleave with MariaDB also read here.

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

MySQL itself has no possibility to limit explicitly the total size nor the number of implicit temporary tables. So this can happen easily if your application runs amok or you do not have your application under control.

An sometimes there is no possibility to have an extra mount point for tmpdir because the disk is completely used by volumes etc. But you have still some space in the file system.

In this case you can, similar to a swap file, use a file in the file system as volume and mount it in a way you can use it as an separate mount point for your tmpdir directory. So in case your application runs amok it just fills up your …

MariaDB Enterprise Platform

The MariaDB Enterprise Platform is available on-premises and on the MariaDB SkySQL DBaaS (Cloud database platform). MariaDB Enterprise Platform consists of:

MariaDB Enterprise Server (MariaDB database server)

MariaDB Xpand (distributed database, scaling writes, former ClustrixDB)

MariaDB Enterprise ColumnStore (former InfiniDB used for data warehouse (DWH), BI, Reporting)

MariaDB MaxScale (Load Balancer)

MariaDB Connectors

• MariaDB Connector/C++
• MariaDB Connector/R2DBC (Reactive Relational Database Connectivity, Java?)
• MariaDB Connector/ODBC
• MariaDB Connector/Python
• MariaDB Connector/Node.js

MariaDB integration connectors

Kafka

Spark

Pentaho Kettle

Power BI

MariaDB tools

IDERA SQL Diagnostic Manager

SQLyog Ultimate

MariaDB Support

MariaDB Enterprise Server Features

MariaDB Enterprise Server comes with more features than MariaDB Community Server:

• Enterprise Cluster
• Enterprise Audit
• Enterprise Federation
• Xpand Storage Engine
• Hashicorp Vault
• InnoDB enhancements
• Replication …

Because they do NOT reflect your problems.

One of our customers upgraded last month from MariaDB 10.2 to MariaDB 10.5. In the same change he also converted all his data warehouse (DWH)/BI tables from MyISAM to Aria. An all this, naturally, without testing. And it miserably failed! And then we were under heavy time pressure to make things working again…

What has changed:

• MariaDB version: MariaDB optimizer got a lot of changes between these 4 major release series (10.2, 10.3, 10.4 and 10.5)!
• Storage Engine change from MyISAM to Aria.
• MariaDB Server System Variable aria_pagecache_buffer_size was not tested and sized properly. In combination with a MariaDB documentation bug.
• A newly introduced MariaDB bug (MDEV-25308)? caused also some confusion.

Literature research

Instead of testing and benchmarking on his own our customer relied on benchmarks done by some other people:

• Benchmarking Aria: These benchmarks, which are older than 2016, claim, that MariaDB is partly faster than MyISAM for internal …

If we do customers database configuration analysis we check on one side if the most important MariaDB server system variables (innodb_buffer_pool_size, …) are set appropriately but also if some MariaDB server system variables are configured completely wrong.

Fortunately MariaDB introduced in MariaDB 10.1 the INFORMATION_SCHEMA.SYSTEM_VARIABLES view where you can find all the relevant information. But one!

Since MariaDB 10.5 we can also see from which file the MariaDB server system variable configuration is coming from. This makes it easier to find and fix wrong configurations.

MariaDB server system variables which are NOT default

A general assumption is that the defaults set by MariaDB are in most cases OK and if you change the defaults you need a good justification for the changes. “I do not know.” is NOT a good justification!

SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE
  FROM information_schema.SYSTEM_VARIABLES
 WHERE GLOBAL_VALUE != DEFAULT_VALUE
   AND GLOBAL_VALUE NOT …