Infrastructure at your Service

MySQL Archives - Blog dbi services

Elisa Usai

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

By | Database Administration & Monitoring | No Comments

As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys. In our example, we have the following parent table in a MySQL 5.7.21 server: mysqld3-(root@localhost) [sakila]> show create table actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON…

 
Read More
Daniel Westermann

2016 is over and it was great, 2017 will be even more exciting in the open source area

By | Database Administration & Monitoring | No Comments

Disclaimer: This will not be a technical post at all. The goal is to provide a short overview of what we did in 2016 and what we plan to do in 2017 when it comes to our Open Infrastructure division. We’ll start with a few figures then look at the events we did and some of the projects we completed in 2016. Here we go …  

 
Read More
Saïd Mendi

MySQL 8.0.0 DMR – First impression

By | Database management | One Comment

Last week (09/12/2016) was announced the new MySQL 8.0.0 DMR (Development Milestone Release) It is now available for download at dev.mysql.com I downloaded and installed it Following are my first impression on some new features First of all, when you remember the size of earlier versions, it is more & more gluttonous, see below mysql-5.0.96-linux-x86_64-glibc23.tar.gz    121.2M mysql-5.1.72-linux-x86_64-glibc23.tar.gz    127.8M mysql-5.5.52-linux2.6-x86_64.tar.gz            177.2M mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz    299.7M mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz   611.8M mysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz is around 1Gb and uncompressed 3.6Gb (Oups…) At…

 
Read More
Daniel Westermann

The (almost) same sample schema for all major relational databases (3) – MySQL/MariaDB

By | Database Administration & Monitoring | No Comments

Some time ago I blogged on how to get the “Dell DVD Store Database Test Suite” installed in PostgreSQL and Oracle. This time we’ll do the same on a MariaDB instance. I have to admit here that I am not very familiar with MariaDB/MySQL so the stuff presented here for sure breaks any security best practices  

 
Read More
Grégory Steulet

MySQL versions performance comparison

By | Application integration & Middleware | 6 Comments

This blog aims to make a performance comparison between the different MySQL versions/editions and also comparing the differents MySQL forks such as Percona Server and MariaDB.  Indeed number of improvements as been done to innodb storage engine in the last MySQL versions. You can find below some of the performance improvements applied to InnoDB these last years (non exhaustive list): MySQL 5.0 1. New compact storage format which can save up to 20% of the…

 
Read More
Grégory Steulet

Security improvements in MySQL 5.7

By | Database management | One Comment

If you have a look on the last mysql 5.7.4 version or later you will probably see that there are several security improvements. The list of added security features and improvements can be seen on the following page: http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html There are three main improvements that are shortly described in this blog: 1. Nonempty plugin column 2. Password lifetime policy 3. mysql_install_db secured Nonempty plugin column As of MySQL 5.7.2, the server requires account rows in…

 
Read More
Grégory Steulet

MySQL high availability management with ClusterControl

By | Database management | No Comments

Installing and managing a highly available MySQL infrastructure can be really tedious. Solutions to facilitate database and system administrator’s task exist, but few of these cover the complete database lifecycle and address all the database infrastructure management requirements. Severalnines’ product ClusterControl is probably the only solution that covers the full infrastructure lifecycle and is also able to provide a full set of functionalities required by database cluster architectures. In this article, I will show how…

 
Read More
Grégory Steulet

Mysqldump issue – SELECT command denied to user

By | Database management | No Comments

During one of my last MySQL mission, I encountered a “security error” with mysqldump. The mission was about migrating a MySQL database from one box to another MySQL version and from Windows to Linux with different users. In order to do that, I used mysqldump. During the import of the data, I got the following warning: ERROR 1449 (HY000) at line 1860: The user specified as a definer (‘cdadmin’@’%’) does not exist Following the migration,…

 
Read More
Grégory Steulet

MySQL Data High Availability with semi-synchronous replication

By | Hardware & Storage | No Comments

Starting with MySQL 5.5 it is possible to configure semi-synchronous replication. As you probably know, by default, MySQL replication is asynchronous.  Asynchronous replication means that events/transactions applied on one server are not immediately applied on the other one. In the specific context of MySQL such behavior means that the MySQL Master writes transactions/events to the local binary logs, however it has no idea when the slave(s) will get and process them. Of course such configuration can lead, in case of failover, to some transaction loss since there is no guaranty that the transactions have been transmitted on the standby server(s).

 
Read More