Feed Aggregator

InnoDB plugin is enabled.

The InnoDB Log File (innodb_log_file_size) size is possibly too small.
Choosing a too small InnoDB Log File size can have significant write performance impacts.

For further details please consult MariaDB or MySQL documentation.

MySQL (InnoDB, PBXT, NDB, TokuDB) support Foreign Keys to show relatations between tables. Those relations can be enforced through Foreign Key Constraints.

Foreign Key Constraint Errors are always either a bug in your application (which should be fixed) or inconsistencies in your data (which should be fixed) or both (first fix the bug in the application, then clean-up your data).

Foreign Key Constraint Errors can be found with the following command:

SHOW ENGINE INNODB STATUS<br>G

SHOW ENGINE Syntax

Foreign Key Errors look like this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-08-19 15:09:19 7fbb6c328700 Transaction:
TRANSACTION 543875059, ACTIVE 0 sec inserting
mysql tables in use 1, locked 14 lock struct(s), heap size 1184,
2 row lock(s), undo log entries 1
MySQL thread id 124441421, OS thread handle 0x7fbb6c328700,
query id 7822461590 192.168.1.42 fronmdual update
INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130)
Foreign key constraint fails for table …

InnoDB Force Recovery (innodb_force_recovery) is enabled. This mode should be used for data recovery purposes only. It prohibits writing to the data.

Please consult MariaDB and MySQL documentation for further details or InnoDB Recovery Modes.

InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of committed transactions in case of a power failure or an unclean shutdown of your database.

You got an InnoDB Deadlock. Please talk to your development department to fix this problem. Deadlocks are an application problem!

If you are using MariaDB/MySQL 5.5 and newer you should use several InnoDB Buffer Pool Instances for performance reasons.
Some rules to size InnoDB Buffer Pool instances are:

• One InnoDB Buffer Pool Instance should be at least 1 Gibyte in size (innodb_buffer_pool_size / innodb_buffer_pool_instances
  >= 1 Gib).
• InnoDB Buffer Pool Instances you can set equal to the number of cores of your machine.

Problem

MySQL Replication Slave stuck because its disk run full. After restarting the Slave MySQL replication was broken with the following error:

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.

How can I fix this? My Master is 800 Gbyte and it will take ages to set-up a new Slave from scratch…

Solution

In this case possibly the reason of your MySQL Replication Relay Log corruption was the disk full state and not a problem on the master.

Please first free disk space, then you can …

Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.

If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.

For further information consult MariaDB or MySQL documentation.

The number of table definitions (SHOW CREATE TABLE<br>G) that can be stored in the table definition cache (table_definition_cache). If you have a large number of tables (
> 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency.
The command SELECT COUNT(*) FROM information_schema.tables; shows you how many tables and thus table definitions you have. The global status Open_table_definitions is the current amount of open table definitions.

For further information consult the documentation of MariaDB and MySQL.

Good values for the table_definition_cache are between 400 and 2000. A bigger table definition cache also means that your database uses more memory (RAM)!

The Table Open Cache (table_open_cache or old name table_cache) is a cache to store file handles for all threads. The actual value of cache entries can be seen with the global status of open tables (Open_tables).
Increasing table_open_cache increases the number of file descriptors (open_files_limit) that MySQL requires.
You can check whether you need to increase the Table Open Cache by checking Open_tables and Opened_tables. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.

For further information consult MariaDB and MySQL documentation or the following article.

If and how to use Galera Cluster for MySQL has been described already by many others…

In this article we focus on the question: When is Galera Cluster NOT the right solution for you?

These thoughts also apply to Percona XtraDB Cluster (PXC) and MariaDB Galera Cluster.

• Galera Cluster currently supports only InnoDB (and TokuDB?) tables properly. You should avoid to use MyISAM or MEMORY tables. Otherwise data consistency is not guaranteed.
• It is wise to use Galera Cluster only if your software vendor supports this high-availability solution.
• Galera Cluster is a no-go if you rely on missing InnoDB features (like GIS indices).
• Synchronous replication cluster solutions are sensitive to hot-spots (like queues, messaging systems or sequences). If you have high concurrency on such hot-spots your cluster could become slow.
• Galera Cluster is primarily a high-availability solution and not a high-performance or scalability solution.
• Galera Cluster will NOT be fun if you have an unstable network.
• Galera Cluster is …

Database migration tools

A generic database migration tool to MySQL and MariaDB is the MySQL Workbench.

An alternative is the old MySQL Migration Tool Kit.

Recommended by MariaDB Foundation: Sqlines.

Adabas D to MariaDB migration

Project migrating an Adabas D database to MariaDB is in progress. More information are expected before end of 2015.

Oracle to MySQL or MariaDB migration

Data Migration Tools

• MyOraDump is a tool to export data from an Oracle database to MySQL or MariaDB.
• Oracle-to-MySQL-DataMigrator
• SQLWays by Inspire
• Successful migration of Oracle to MySQL with utf8mb4 character set with SQL Workbench/J on command line

Exasol to Galera Cluster for MySQL migration

Exasol is a shared in-memory column store for data analysis.

• Data was extracted by the Exasol GUI as .CSV file.

• Structure was extracted by the Exasol GUI as DDL commands and adapted manually because we failed to use the MySQL Workbench.

• Data was loaded with the LOAD DATA INFILE command (20 Gbyte in about 70 minutes).

  LOAD DATA INFILE …

The InnoDB Flush Method has changed. This can have an impact on InnoDB write Performance.

The press, be it the general daily newspaper or the computer magazines, is currently informing the public about an upcoming leap second, which will be taken in the night from June 30 to July 1 at 00:00:00 UTC. While we Europeans will enjoy our well-deserved sleep then, this will be at 5 PM (17:00) local time on June 30 for Califormia people, and during the morning of July 1 for people in China, Japan, Korea, or Australia. (Other countries not mentioned for the sake of brevity.) This is different from last time, when the leap second was taken in the night from Saturday to Sunday (2012-July-1 00:00:00 UTC), so it was a weekend everywhere on the globe.

We have got several requests from our customers about this upcoming leap second, whether they need to take any special precautions or whether they “are safe”. Well, obviously nobody is “safe” from the leap second in the sense that it would circumvent them, everybody will encounter it on their systems. The concern is whether they have to …

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

You can download the FromDual Backup and Recovery Manager from here.

In the inconceivable case that you find a bug in the Backup and Recovery Manager please report it to our Bugtracker.

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

Upgrade from 1.2.x to 1.2.2

# cd ${HOME}/product
# tar xf /download/fromdual_brman-1.2.2.tar.gz
# rm -f fromdual_brman
# ln -s fromdual_brman-1.2.2 fromdual_brman

Changes in FromDual Backup Manager 1.2.2

FromDual Backup Manager

It contains mainly fixes with brman catalog and physical backups.

You can verify your current FromDual Backup Manager version with the following command:

fromdual_bman --version

• Archiving with physical backup bug fixed.
• Connect replaced by OO style and error exit fixed.
• Create catalog fixed.
• Archivedir without archive option does not make …

How can I search the following string in a text field: ‘%newline,tabluator,b)%’?

The following example should work for you:

CREATE TABLE spec(txt VARCHAR(255));

INSERT INTO spec values (‘bla\tbla\nbla’); INSERT INTO spec values (’\n\tb)’); INSERT INTO spec values (‘abc\n\tb)xyz’);

SELECT * FROM spec;

SELECT * FROM spec WHERE txt LIKE ‘\n\tb)’; SELECT * FROM spec WHERE txt LIKE ‘%\n\tb)%’; SELECT * FROM spec WHERE txt REGEXP ‘^\n\tb)$’; SELECT * FROM spec WHERE txt REGEXP ‘\n\tb)’;

You find many different possibilities how to do a MariaDB/MySQL backup with mariadb-dump/mysqldump. But which one is the correct one?

Correct mariadb-dump/mysqldump backup

Correct is a question of definition… I suggest starting with the following command for a full backup:

For my.cnf

$ BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
$ BACKUP_DIR='/mybackupdir'
$ cp /etc/my.cnf $BACKUP_DIR/my_$BACKUP_TIMESTAMP.cnf

For MariaDB/MySQL databases with MyISAM or Aria tables

$ BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
$ BACKUP_DIR='/mybackupdir'
$ mariadb-dump --user=root --all-databases --lock-all-tables --master-data=1 \
  --flush-privileges --flush-logs --triggers --routines --events \
  --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

For MariaDB/MySQL databases with InnoDB tables only

$ BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
$ BACKUP_DIR='/mybackupdir'
$ mysqldump --user=root --all-databases --single-transaction --master-data=1 \ …