MySQL – How to add a foreign key on new or existing table

TL; DR

How to add a foreign key to a new TABLE:

CREATE TABLE child (ID INT PRIMARY KEY, parent_ID INT, FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID));

How to add a foreign key to an existing TABLE:

ALTER TABLE child ADD FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID);

MySQL has the ability to enforce a record that exists on a parent table when you are adding/modifying data or validate that a record doesn’t exist when you are deleting data from your child table, leaving your database inconsistent. This is called Foreign Key. You can check the complete documentation here

As seen above, you can either create your table with an FK since the beginning or modify/alter your table to add a new constrain after table creation time. Apart from syntax to refer to a field on the parent table, you can control what will be the behavior when you UPDATE or DELETE a record on the PARENT table that has a reference to in on the child table. This is controlled by the optional parameter ON UPDATE and ON DELETE, the restrictions are as follow:

We will be using as an example the below table:

mysql> SELECT * FROM parent;
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM child;
+----+-----------+
| ID | parent_ID |
+----+-----------+
|  1 |         1 |
+----+-----------+
1 row in set (0.00 sec)
  • RESTRICT or NO ACTION – Default behavior when you omit ON UPDATE or ON DELETE, this means if you try to update the filed on the parent table that is referred to at the child table, your update/delete will be blocked:
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_ID`) REFERENCES `parent` (`ID`))
    

    Although not recommended, in extreme cases you can for MySQL to disable the FK check to by-passe above error:

    SET foreign_key_checks=0;

    Have in mind that this will despite the whole reason for having FK in the first place!

  • SET DEFAULT – It’s recognized by the parse (won´t give any error), however, its interpreted as RESTRICT.
  • CASCADE – Whatever action you do on the parent table, will replicate to the child table:
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM parent;
    +----+
    | ID |
    +----+
    |  2 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM child;
    +----+-----------+
    | ID | parent_ID |
    +----+-----------+
    |  1 |         2 |
    +----+-----------+
    1 row in set (0.00 sec)
  • SET NULL – Whatever action you do on the parent table, child column will reset to NULL (make sure child filed is not set to NOT NULL):
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM parent;
    +----+
    | ID |
    +----+
    |  2 |
    +----+
    1 row in set (0.01 sec)
    
    mysql> SELECT * FROM child;
    +----+-----------+
    | ID | parent_ID |
    +----+-----------+
    |  1 |      NULL |
    +----+-----------+
    1 row in set (0.00 sec)

MySQL Load Balancing with ProxySQL – Tutorial Master and Slave

Continuing on the series of blog posts about ProxySQL. Today I will demonstrate how to put ProxySQL in front of your master and slave topology doing a load balancing by distributing reads and writes between the servers, without the need of changing your application code. To install ProxySQL and some of its basic concepts, please read this blog post.

On this tutorial, we will use 4 servers:

  1. ProxySQL (192.168.112.60)
  2. Node1 – Master (192.168.112.61)
  3. Node2 – Slave-1 (192.168.112.62)
  4. Node3 – Slave-3 (192.168.112.63)

 

Servers

With ProxySQL already installed, we will connect into the admin interface, add our 3 servers, and configure Replication Hostgroup. Basically, we will tell ProxySQL which HG will be our master and which HG will be our slaves. ProxySQL differs master and slaves based on server  read_only variable:

$ mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='proxysql> '

#proxysql> 
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.61');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.62');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.63');
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'Master / Slave App 1');
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

When we check the list of servers, we still see them all on HG 20, including our master:

proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+----------------+--------+
| hostgroup_id | hostname | status |
+--------------+----------------+--------+
| 20 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.63 | ONLINE |
| 20 | 192.168.112.62 | ONLINE |
+--------------+----------------+--------+
3 rows in set (0.00 sec)

ProxySQL has a thread responsible to connect on each server lister at mysql_servers table and check the value of  read_only variable. On table mysql_server_read_only_log we can check the logs of this thread:

proxysql> SELECT * FROM mysql_server_read_only_log LIMIT 3;
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
| 192.168.112.61 | 3306 | 1529175123875168 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
| 192.168.112.62 | 3306 | 1529175123876409 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
| 192.168.112.63 | 3306 | 1529175123877369 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

As we can see above, the proxy cannot connect on our servers. For security reasons, we will change the default username and password which ProxySQL Thread uses to connect on our servers:

UPDATE global_variables SET variable_value='p_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='M0n170Rpwd!' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Now we need to create this user on our master:

CREATE USER p_monitor@192.168.112.60 IDENTIFIED BY 'M0n170Rpwd!';

With the user configured on both, ProxySQL and MySQL, we can verify at the admin interface that now we have 4 servers on our list:

proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers ORDER BY hostgroup_id;
+--------------+----------------+--------+
| hostgroup_id | hostname | status |
+--------------+----------------+--------+
| 10 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.62 | ONLINE |
| 20 | 192.168.112.63 | ONLINE |
+--------------+----------------+--------+
4 rows in set (0.01 sec)

Our Master is listed on both HG’s. We can change this behaviour by setting mysql-monitor_writer_is_also_reader to false (it comes enabled by default).

Users

Next step is to configure our application user on ProxySQL. We can either manually create each user or import them all as described on this blog post. On this tutorial, I will demonstrate how to manually create the user. Remember that you need this user created on MySQL too and with the appropriate GRANTS in place :

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('marcelo', 'marcelo', 10);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

If you query mysql_users table you will be able to see the password in plaintext:

proxysql> SELECT username, password FROM mysql_users;
+----------+----------+
| username | password |
+----------+----------+
| marcelo | marcelo |
+----------+----------+
1 row in set (0.00 sec)

However, this is not the case when querying runtime_mysql_users table. Runtime table will have the password computed as a hash. For security reasons, every time we manually create a user on ProxySQL we need to save its runtime version back to main and also save the hashed version to disk:

proxysql> SAVE MYSQL USERS FROM RUNTIME; SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

proxysql> SELECT username, password FROM mysql_users;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| marcelo | *8E36BAA4C91256FAEF957292B1C224C102754D25 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

Query Rules

Is that all ?? Almost, now is where the magic begins.

Here is the key to make MySQL load balance work. Every time user  marcelo connets to ProxySQL, it will send all it’s queries to HG 10 (configured at  default_hostgroup field of  mysql_users table). HG 10 is mapped to our master. Now what we need to do is to tell ProxySQL to redirect our SELECT commands to HG 20, which is configured to balance the queries between all server, including our slaves. We will do this config by inserting rules on mysql_query_rules table . There is a small exception which we need to consider, SELECT . . . FOR UPDATE commands will place a lock on the records they read with the intent of update those lines. In this case, we need to make sure those queries will always reach our HG 10:

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(1,'marcelo',10,1,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(2,'marcelo',20,1,'^SELECT ',1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Done, now ProxySQL is configured to do read and write split between our servers. All we need to do is to configure our application to connect to ProxySQL instead of connecting direct to our master. Remember that by default, ProxySQL application interface is bound on 6033 port.

Below is a graph from PMM showing the difference of traffic being handled by a single master server versus the moment we change the traffic to be controlled by ProxySQL doing load balance across master and slaves:

As we can see, our node1(Master) had a high traffic compared to the other two servers, at the moment ProxySQL started to distribute the traffic we can see all 3 servers handling about the same amount of traffic. The same behaviour can be seen on load average graphic.

Failover

ProxySQL is capable to identify a failover has happened. As soon as the master is unavailable and a slave has been promoted to master, it will identify the slave is not configured with  read_only anymore and will change the slave server to HG 10.

Important: ProxySQL is a proxy, it is responsible to redirect the traffic to the desired backend server once failover has happened. It will not perform the failover for your, such as execute  CHANGE MASTER TO on remaining slaves.

 

Delayed Slaves

ProxySQL can stop send traffic to a slave if it is behind master for more than x seconds. In order to be able to check the status of the slave, ProxySQL monitor user will require REPLICATION CLIENT. We will add this grant to our user by executing below command on our master:

GRANT REPLICATION CLIENT ON *.* TO p_monitor@192.168.112.60

Now, we will configure ProxySQL to shun traffic on slaves with more than 10 seconds:

UPDATE mysql_servers SET max_replication_lag = 10 WHERE hostgroup_id=20;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Done, now every time a slave gets behind, it will have its status changed to SHUNNED at runtime_mysql_servers tables:

proxysql> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 20 | 192.168.112.63 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 20 | 192.168.112.62 | 3306 | SHUNNED | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

We can check the lag ProxySQL got when checking the server by querying mysql_server_replication_lag_log table:

proxysql> SELECT * FROM mysql_server_replication_lag_log WHERE hostname = '192.168.112.62' ORDER BY time_start_us DESC LIMIT 1;
+----------------+------+------------------+-----------------+----------+-------+
| hostname | port | time_start_us | success_time_us | repl_lag | error |
+----------------+------+------------------+-----------------+----------+-------+
| 192.168.112.62 | 3306 | 1529190811923215 | 953 | 604 | NULL |
+----------------+------+------------------+-----------------+----------+-------+
1 row in set (0.00 sec)

Slave lag is monitored every mysql-monitor_replication_lag_interval millisecond (10 seconds by default).

ProxySQL – Sync / Import users

On my last post I gave a gasp about some key concepts of ProxySQL. Today I will demonstrate how we can import all users from MySQL to ProxySQL

To accomplish this, we will make use of,proxysql-admin a tool developed by  Percona to help you manage and install ProxySQL. If you are using ProxySQL provided by Percona, this tool is already installed, otherwise, you can download it directly from Github.

We will use the  --syncusers option, which will connect to the desired MySQL server, read all users from  mysql.user table and import or sync them to ProxySQL:

proxysql-admin --cluster-username=[mysql-user] --cluster-password=[mysql-password] --cluster-port=[mysql-port] --cluster-hostname=[mysql-host] --syncusers

This tool was originally designed to enhance the compatibility between ProxySQL and  Percona XtraDB Cluster, so, many terminology mentions cluster, however, the functionality demonstrate on this post is fully compatible with Master-Slave.

Here is one example of the command and it’s output:

$ proxysql-admin --cluster-username=root --cluster-password=sekret --cluster-port=3306 --cluster-hostname=192.168.112.61 --syncusers

Syncing user accounts from Percona XtraDB Cluster to ProxySQL

Synced Percona XtraDB Cluster users to the ProxySQL database!

It worth mention that every time a new user is added or an existing user has its password changed, we also need to update the user on ProxySQL. With proxysql-admin, all we need to do on those cases is re-run the tool with  --syncusers  to get everything back in sync.

How to drop a column in mysql table

In this 101 article, I will show how to drop/remove a column from a table in MySQL.

In this article I will use an example table:

CREATE TABLE tb(
c1 INT PRIMARY KEY,
c2 char(1),
c3 varchar(2)
) ENGINE=InnoDB;

To remove a column we will make use of ALTER TABLE command.

How to remove a colum in a MySQL table:

ALTER TABLE tb DROP COLUMN c2;

How to remove multiple columns in a MySQL table:

The command allows you to remove multiple columns at once:

ALTER TABLE tb DROP COLUMN c2, DROP COLUMN c3;

If you are running MySQL 5.6 onwards, you can make this operation online, allowing other sessions to read and write to your table while the operation is been performed:

ALTER TABLE tb DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

Reference:

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Understanding ProxySQL

In this post, I will cover a bit of ProxySQL. ProxySQL is a proxy which implements MySQL protocol, allowing it to do things that other proxies cannot do. ProxySQL is gaining a lot of traction nowadays and it’s capable to integrate with a variety of products from MySQL ecosystems, such as replication (master – slave / master – master), Percona XtraDB Cluster and Group Replication.
One of its many functionalities (which IMHO makes it awesome) is the ability to do read/write split seamless to the application. You can start sending reads to your slave without doing a single line of code.

In this article, I will cover a few important points to understand how it works.

Instalation

The instalation is easy, you can do it by:

  1. Downloading the corresponding package for your OS from its official github repo https://github.com/sysown/proxysql/releases
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
    
  2. Downloading the corresponding package for your OS from Percona website https://www.percona.com/downloads/proxysql/
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
  3. Installing Percona repository for your OS https://www.percona.com/doc/percona-server/LATEST/installation/apt_repo.html or https://www.percona.com/doc/percona-server/LATEST/installation/yum_repo.html (preferable):
    sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
    sudo yum install proxysql

Now we just need to start the service

sudo service proxysql start 

Interfaces

ProxySQL splits application interface from the admin interface. It will listen on 2 network ports. Admin will be on 6032 and application will listen on 6033 (reverse of 3306 ).

Layers

Other important part to understand how the proxy works is to understand its layers. I am gonna show you a diagram that can be found on its official documentation:

+-------------------------+
|      1. RUNTIME         |   
+-------------------------+
       /|\          |
        |           |
        |           |
        |          \|/
+-------------------------+
|     2.  MEMORY          |   
+-------------------------+ 
       /|\          |      
        |           |      
        |           |       
        |          \|/      
+-------------------------+  
|     3.   DISK           |  
+-------------------------+  

ProxySQL will always read information from 1.Runtime layer, which is stored in memory.
Every time we connect to the admin port (6032) we are manipulating information from layer 2.Memory. As the name infers, its also stored in memory.
We then have the layer 3.Disk. As the other two layers are stored in memory, we need a layer to persist information across service/server restarts.

What is the benefic of this layout?
It allows us to manipulate different areas and apply the changes at once. We can think about how a transaction works, where we run multiple queries and commit them at once. When we alter something, we will be manipulating the Memory layer, then we will run a command LOAD MYSQL [SERVERS | USERS | QUERY RULES] TO RUNTIME to load this information to runtime and we will save the information to disk layer by issuing SAVE MYSQL [SERVERS | USERS | QUERY RULES] TO DISK.

Hostgroups

ProxySQL group servers in something named hostgroup. In a topology which we have a master and two slaves, we will create a hostgroup(HG) 1 and specify that our master is part of that HG and we will create a HG 2 and specify that both slaves belong to that HG. Hostgroup creation is done at the time we specify servers on mysql_servers table. There is not fixed enumeration, you can create your HG with any ID you want.

Later we will configure user and queries to identify if the query coming in is a read it should be answered by one of the servers from HG 2 (where we configured our slaves). If the query is not a read, then our server on HG 1 will receive it.

Authentication

ProxySQL has functionalities like firewall, in which it has the capability of blocking a query even before it reaches our backend server. To do it it’s required to have the user authentication module also present on the proxy side. So we will be required to create all users that we wish to connect via ProxySQL also create on the proxy side.

 

Now that you understand a bit of the basic of how ProxySQL works, you can start playing with it.

MySQL Online Backup with xtrabackup

My last post about backups was a while ago. Although it is still valid, it’s not advisable to use it as daily backup. For that we can use XtraBackup.

With XtraBackup, you can take online backups without interrupt the server workload(it will require a small lock to take binlog coordinates).
Today I’ll show how to take a complete backup.

Install:

To install it, I advise you to use Yum / Apt-get repos:

Centos / Redhat:

sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
sudo yum install percona-xtrabackup-24

Debian / Ubuntu

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24

Backup:

To take the backup, we will use innobackupex script:

innobackupex --user=root --password='Passw0rd!' /backups/

The script will produce some log messages, we need to verify if it ends with :

...
170429 21:07:12 completed OK!

Parallel:

Xtrabackup allows the copy of the tables to be done in parallel, you can set the number of threads by using the –parallel option:

innobackupex --user=root --password='Passw0rd!' --parallel=8 /backups/

We can observe that each threads receives a number which is displayed within []:

170429 21:12:27 [05] Copying ./ibdata1 to /backups/2017-04-29_21-12-27/ibdata1
170429 21:12:27 [06] Copying ./mysql/plugin.ibd to /backups/2017-04-29_21-12-27/mysql/plugin.ibd
170429 21:12:27 [06]        ...done
170429 21:12:27 [02] Copying ./mysql/servers.ibd to /backups/2017-04-29_21-12-27/mysql/servers.ibd
170429 21:12:27 [02]        ...done
170429 21:12:27 [03] Copying ./mysql/help_topic.ibd to /backups/2017-04-29_21-12-27/mysql/help_topic.ibd
170429 21:12:27 [07] Copying ./mysql/help_category.ibd to /backups/2017-04-29_21-12-27/mysql/help_category.ibd
170429 21:12:27 [07]        ...done

Compress:

Xtrabackup also allows you to compress your backup using –compress and –compress-threads (normally set to the same number of –parallel threads):

innobackupex --user=root --password='Passw0rd!' --parallel=8 --compress --compress-threads=8 /backups/

For example, a backup that takes 702M of disk space, now takes only 387M:

702M	/backups/2017-04-29_21-12-27
387M	/backups/2017-04-29_21-15-53

Restoring:

To restore the backup, we need to (1) decompress it with –decompress option in case we have compressed it before (we will require qpress for that) and (2) apply the log of transactions that happened while the backup was been taken (Basically a crash recovery that InnoDB does when MySQL starts):

innobackupex --decompress /backups/2017-04-29_21-18-04/
innobackupex --apply-log --use-memory=4G /backups/2017-04-29_21-18-04

To speed up the –apply-log process, we can configure –use-memory, which will behave like InnoDB Buffer Pool.

All .qp files will remain on the directory, we need to manually remove then. We can do it using below command:

find /backups/2017-04-29_21-18-04  -name "*.qp" -exec rm -f {} \;

Now we only need to copy the restored backup to the MySQL datadir and configure mysql as owner and group owner of all files in the folder.

That is it for today. Remember, if you don’t test your backups, YOU DON’T HAVE A BACKUP!

MySQL 8.0 – SET PERSIST command

Hi guys. MySQL 8 DMR was released today and it has some cool new features. One of those is the ability to persist dynamic changed variables/configurations across restarts. It’s very usefull if you change variables dynamically. It’s saves you the trouble of edit a cnf file every time you run a SET on mysql (or even when you don’t have access to those files).

The new syntax will be as follow:

SET PERSIST option=value;
SET @@persist.option=value;

MySQL will create a new file named mysqld-auto.cnf located on it’s DATADIR folder. This file will contain all PERSISTENT variables and will be loaded after all other files (my.cnf / –defaults-file / ~/.my.cnf / …) Which means that values from mysqld-auto.cnf will take place in case of the same option be present on multiple files.

Those variables can be UNSET by setting it to it’s default value, or manually edit the file on disk(not advised).

This feature can be controlled by the persisted-globals-load config. It is set to ON by default. If you set it to OFF (persisted-globals-load=OFF), MySQL will ignore all variables present on mysqld-auto.cnf file.

Variables can be listed from performance_schema.variables_info table. On column variable_source we can filter the ones that are persistent or from other source:

  • COMPILED – Compiled on mysql source (default values)
  • GLOBAL – Part of global file
  • SERVER – Part of global $MYSQL_HOME/my.cnf file
  • EXPLICIT – Part of –defaults-file option file
  • EXTRA – Part of defaults-extra-file option file
  • USER – Part of ~/.my.cnf
  • LOGIN – Part of login path option file
  • COMMAND_LINE – Command line options
  • PERSISTED – part of persistent mysqld-auto.cnf file
  • DYNAMIC – variables set dynamically after server start

So we can get useful information from this table. For example, we can list all variables that were dynamically changed after the server started:

select * from performance_schema.variables_info where variable_source like 'DYNAMIC';

MySQL 8.0 is available for Download at http://dev.mysql.com/downloads/mysql/8.0.html. Be aware that it’s not a production release and should only be used for testing purposes. YET!

Give MySQL 8.0 a try !

MySQL 5.7 using vagrant

Hi there.
Today I will write a quick post to show how to easily configure a brand new mysql 5.7 machine using vagrant.
Vagrant is a command line utility that provides tools to manage virtual machines for different virtualization technologies, for example virtualbox. You can see more details about it here. Navigate to installation if you want to know how to install it.

I’ve created a vagrant script to boot a centos 7 + mysql 5.7 machine. You can find it on github. See how you can get it:

git clone https://github.com/altmannmarcelo/mysql-vagrant.git
cd mysql-vagrant
vagrant up
vagrant ssh mysql57
mysql

If you look the files you have cloned from git, there is a file called bootstrap.sh. This file will configure mysql yum repository, install mysql 5.7 and reset the password for the root user.

If you have any questions, please comment.

Invalid datetime when converting to timestamp

Hi there.
Today I faced an issue that from first looking at it I was almost sure I`ve found a bug, but putting a bit more research on it, it makes totally sense.

I had a table, that I`ll call here as t1. This table has a field that is datetime. Due to a normalization project that we are working on, we are changing some datetime fields to timestamp. When I tried to convert t1, I got the bellow error:

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from t1;
+---------------------+
| date                |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
ERROR 1292 (22007): Incorrect datetime value: '2010-03-28 01:28:20' for column 'date' at row 1

This date looked perfectly fine. Then I realized that we use Europe/Dublin as timezone, I went to http://www.timeanddate.com/time/change/ireland?year=2010 to checkout when Daylight Savings started on 2010 in Ireland, and guess what ?!?!?!
Bingo, that is exactly the day DST started on my server timezone. From the above website:

When local standard time was about to reach
Sunday, 28 March 2010, 01:00:00 clocks were turned forward 1 hour to
Sunday, 28 March 2010, 02:00:00 local daylight time instead

Which means 01:28:20 never existed on Europe/Dublin for that specific day. Why the data was there in the first place is another discussion. But why it did not complained before ?
FROM: http://dev.mysql.com/doc/refman/5.6/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

So, how to fix it ?
Identify the records that have an invalid date and fix it(In my case, fix it means add an hour to it`s time). One way to do it is using the bellow approach:

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
+---------------------+
| date |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> UPDATE t1 SET date=DATE_ADD(date, INTERVAL 1 hour) WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Query OK, 1 row affected (0,10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Empty set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
Query OK, 1 row affected (0,05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1;
+---------------------+
| date |
+---------------------+
| 2010-03-28 02:28:20 |
+---------------------+
1 row in set (0,00 sec)

We have identified all records, fixed it and then mysql allowed us to convert it to Timestamp.

That is it for today.

Forcing deadlock rollback victim transaction

If you use a storage engine that supports transactions, you probably have faced or heard of deadlock's.

From MySQL Documentation:
“Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.”

At work, we had an important job that sometimes were failing due to dead lock. I wanted to enhance it, so it will do what the documentation says ( Retry the transaction ). In order to do that, I wanted to have a scenario where I was able to reproduce the deadlock and the victim transaction was the one from the job I was fixing.

Create a deadlock is simple, you just need to have 2 sessions that each one holds a lock that the other is waiting for. For example:
We have a table that has 4 entries on it (entry 1, entry 2, entry 3, entry 4) and we have 2 transactions that do the follow in time order:

T1: locks entry 1;
T2: locks entry 4;
T1: require a lock on entry 4 (it will wait until T2 release the lock);
T2: require a lock on entry 1 (it will wait until T1 release the lock);

At this point each transaction will wait for each other and MySQL detects the deadlock. Let’s see it in practice:

T1 > CREATE TABLE t (i INT, PRIMARY KEY(i)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

T1 > INSERT INTO t VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

T1 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T1 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2 > UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T1 >  UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (9.54 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > UPDATE t SET i = 1 WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

How MySQL choose the victim transaction to be rolled back ?

MySQL does an internal calculation based on transaction weight. It calculates it based on the amount of rows altered and the number of rows locked on the transaction.
This value can be retrieved on trx_weight column from information_schema.innodb_trx table:

T1 > SELECT * FROM  information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 23326
                 trx_state: RUNNING
               trx_started: 2016-02-19 10:10:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 5
                 trx_query: SELECT * FROM  information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

The tip:
One important factor on the above calculation, is if any transaction have made changes to non-transactional tables, it is considered heavier than the ones that haven’t.
So for my test, I’ve create an MyISAM table and I’ve inserted a row on this table to make it “heavier”:

T2 > CREATE TABLE t2 (i INT, PRIMARY KEY(i)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)

T1 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T1 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2 > UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

T1 > UPDATE t SET i = 4 WHERE i = 4;  -- it will wait for T2 lock be released ...

T2 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T1 >
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

As you can see, the victim from rollback was T1.

That is it for today guys. I hope you enjoy it.