OpenWorld 2017: What’s New in MySQL NDB Cluster?

This presentation was given by Bernd Ocklin, Senior Director MySQL Cluster Development at Oracle.  It was a good overview of what NDB Cluster is, where it came from and some recent changes.  My notes are:

  • Trade-Offs: Cellular Network
  • Trade-Offs: Online Games – Massive Parallel
  • 99.999% uptime
  • Distributed real-time in-memory network database
  • Runs standalone or as a storage engine for MySQL
  • Auto-sharding based on key
    • transparent between node groups
    • each fragment active in one Data Node with synchronous replication to 2nd data node
  • DBA chooses what part of Primary key to shared on
  • parallelization works even on single queries
  • batches automatically
  • event-driven and asynchronous
  • Active-Active with Geo-Replication in NDB Cluster
    • Update anywhere
    • conflict detection
    • auto-conflict-resolution
  • Async-Replication is for read scale out
  • MySQL Group Replication – innoDB Cluster
    • scale out writes as well as reads (limited write scale out)
  • Moving towards mixing NDB cluster as a node in InnoDB cluster (not there yet)
  • Synchronous locally and use replication for geo-replication (asynchronous)
  • Updates scale pretty much linearly
  • version 7.6 new features
    • makes joins much faster
    • added ndb import to read in csv files
    • MEM integration
    • MySQL 5.7
    • dynamic resource allocation
    • Partial Local Checkpoint – for performance improvements and scalability (now support much larger databases)
    • Multi-treaded backup
  • in MySQL 8.0 they are moving NDB to base MySQL to avoid forking and placing it as a first class citizen
  • MySQL 8.0 new feature
    • CTEs
    • Window Functions
    • Data Dictionary
    • utf8mb4
    • Security Roles
    • Improved Information Schema Performance

OpenWorld 2017: MySQL Document Store

This was a great session that was centered on the updates to JSON support in MySQL 8.  But to paint the full picture of how MySQL 8 is making JSON a first class data citizen in the platform, it also covered some updates to InnoDB Cluster and the new MySQL Shell.  The session was present by Alfredo Kojima, Miguel Araujo, and Mike Zinner all from Oracle.  Mike is responsible for client tools – shell, workbench, router, connectors, etc.  Alfredo was there to talk about the changes in InnoDB Cluster, and Miguel for the Document Store itself.

These changes make it more possible for you to use MySQL as a NoSQL document store.  With MySQL however you can retain ACID compliance while doing most (if not all) that NoSQL data platforms like MongoDB provide.  It will be interesting to get my hands on this to see what tradeoffs they make to retain the ACID model.  Especially compared to other NoSQL data engines that take the BASE model.

My notes from the session where.

  • MySQL Document Store Intro
    • What is a document?
      • structured data
      • They are using JSON (=JavaScript Object Notation)
      • Others are XML, YAML, etc.
    • Relational Model vs. Document Model
      • Higher Upfront effort during design vs. lower effort upfront
      • Database ensures integrity vs. Application responsible for integrity
      • Schema updates can be problematic vs. Simple schema change
    • Now MySQL supports both relational and document models
    • Exposes new CRUD interface, while keeping SQL
    • Standard plugin for MySQL Server (since MysqL 5.7.12)
    • Store JSON documents in MySQL inside tables (JSON type column)
    • Store JSON outside of relational tables (collection not table)
      • Collections belong to a database/schema just like a table, so you can have collections of the same name in different schemas on the same instance
      • Collections are implemented as tables(with an id and a doc column of type json)
    • Key takeaways
      • INNODB
      • transactions and locking
      • ect…
  • Components of the MySQL Document Store
    • MySQL Shell – new command line interface
    • Works with InnoDB Cluster
    • X Protocol – new protocol to deal with documents (more efficient)
    • Connectors for
      • .NEt
      • Node.JS
      • Python
      • Java
      • etc..
    • Document Store is tightly connected to InnoDB Cluster
      • for scale out
  • InnoDB Cluster
    • basically turning MySQL into a scale out architecture (looks like MongoDB or other NoSQL system architectures to me)
  • The X DevAPI – A modern CRUD App Programming Interface
    • To create a collection – “var products = db.createCollection(‘products’);”
    • Add document – “products.add({“_id”: 123, “name”: “Test1″}).execute();”
    • retrieve/select – “products.find(“price > 10 and price < 100”).fields([“_id”, “name”]).sort([“name”]).execute();
    • change – “products.modify(“_id”: 123  <missed it>
    • delete – products.remove(” <missed it>
  • Node Js example – Async call to MySQL and function (doc) as loopback

var schema = session.getSchema(‘mySchema’);

var coll = schema.getCollection(‘myColl’);

var query = “$.name == :name”;

coll.find(query).bind(‘name’,  ‘Alfredo’).execute(function (doc) {

console.log(doc);

})

.catch(function (err) {

console.log(err.message);

console.log(err.stack);

});

  • Demo of shell interface
    • port 33060 for XDev API port
    • new shell defaults to java script mode
    • can switch to sql with \sql
    • \js to switch back to javascript mode
    • dba.  used to manage the instance with javascript
    • session.sql(“SELECT * FROM TEST1;”).execute(); – for sql in javascript shell
    • session.sql(“select ?”).bind(“‘Hello World'”).execute(); – by using bind it escapes everything for you
    • much more powerful interface even for DBAs
    • scripts require the .execute(), but not needed while using the shell
    • new shell documentation – https://dev.mysql.com/doc/x-devapi-userguide/en/
    • new uuid with timestamp at the end to be more sequential for performance (within a session)
  • Documents via SQL
  • Behind the Scenes

OpenWorld 2017: MySQL Automatic Diagnostics: System, Mechanism, and Usage

Shangshun Lei and Lixun Peng from Alibaba Cloud discussed in this session a system they have built called CloudDBA to automate a lot of traditional DBA roles.  The system sounds pretty cool and is what my team should be aspiring to do.  But there was not a lot of information on the how, and a lot of the what for CloudDBA.  My notes from the session are:

  • Why CloudDBA
    • Reduce Costs –
      • 80% of time spent on finding root cause, optimizing performance, scaling hardware and resources
      • 20% on database platform
    • Focus your resources on business
    • Provide best technology
  • Architecture
    • Kafka/JStorm for log collection
    • Offline Data repository
      • Error log
      • slow log
      • audit log
      • cpu/ios/status
    • Offline diagnostics
      • Top SQL Analyss
      • Trx Analysis
      • SQL Review
      • Deadlock Analysis
    • Online diagnostics
      • Knowledge base – rule engine
      • Inference Engine – matches for conditions and runs execution to resolve or provide advise to users
    • Realtime Event and advise
      • Slave delay
      • Config Tuning
      • Active Session
      • Lock and Transaction
      • Resource
  • Rule Engine
    • Immediate detection of useful changes with low cost
    • Choose correct inference model
      • Database global status is mature and easy to get
      • High frequency monitoring to make sure no useful info is missed
      • Real time state change detection algorithms
      • Importance of database experience
  • Knowledge Base and inference engine
    • Ability to accumulate DBA experts’ experience in short time
    • Accurate issue detection & corresponding advice
  • Offline diagnosis
    • Audit log does matter
    • Record full SQLs for database
    • A feature of AliSQL, no performance impact
  • Transaction analyiss
    • uncommitted transactions
    • long transactions
    • long interval between transactions statements
    • big transactions
  • SQL review
    • how many types of sql
    • how many types of transactions
    • sqls or sequence in transaction is expected or not
    • scan rows, return rows, elapsed time and sql advise
  • Top SQL
    • top sql before optimize
    • help explain questions such as why my cpu is 100%
    • different statistics dimensions and performance metrics
  • SQL Advisor
    • Not a database optimizer
    • built outside of MySQL kernel
    • query rewriter
    • follow rules to create indexes that works for the lowest cost

Percona Live Data Performance Conference 2016

I’m sitting in the San Fransisco airport trying to get enough caffein in me to feel alive again.  A sure sign of a great conference.  Last year I live blogged the conference but this year there was so much stuff that I wanted to absorb that I failed to make time for that.  There were a ton of great sessions.  It did feel like there were fewer folks in attendance, but that only made the learning feel more personal.  This year the folks at Percona opened up the conference to encourage content across all platforms.  I wonder if that made some of the MySQL folks worry that the conference didn’t have anything to offer them.  I can tell you that there was plenty of great MySQL content.

Working in an enterprise database administration shop, the expanded content was perfect for me.  While we have historically only supported 2 platforms, we currently support 4 (about to be 5) and are constantly being pushed to support more.  This is a natural extension of modern application design and have a conference where we can hit on several platforms really helps.  We all have tight budgets these days and its almost like having 3-4 conferences in one.  But the soul of the conference is still very much MySQL.  With content geared towards first time DBAs through sessions on the InnoDB and the new MySQL Document Store, you can’t go wrong with this conference to expand your MySQL knowledge.

I am coming home with a bunch of great ideas.  The hard part now is to pick which things to work on implementing first.  Some will be changes my team can make ourselves and others will need to involve change in other teams as well.  I was very intrigued by the “chatops” idea.  I will try to see how we can integrate the concept into our operations.  I similarly was interested in the rise of integrating chat bots into database automation.  We are hitting database automation real hard right now.  Thus far our focus has been on self service APIs, but I think we will add a natural text interface into these APIs to further improve the experience for our customers.

Well, I am out of coffee so I need to run and refill the cup.  More to come on Percona Live and all the projects that come out of it.

Verifying our XtraDB Cluster

In my last blog post, I showed you how easy it is to set up a three node Percona XtraDB Cluster.  But how do we know if the cluster is working or not?  In this post I will go over the built-in variables that ship with Galera you can use to check on the status of your cluster.  Before we dive in, it is important to realize that Galera is a multi-master cluster.  This means that you can read and write to all the nodes.  One of the implications of the manic that makes Galera work is that each node can be independently configured. As such, it is important that you check all the nodes, not just one, to determine the overall health of your cluster.  For the rest of this post I will be only running the checks on one of the nodes for brevity.  But you should really run these checks on all the nodes to ensure a fully functioning cluster.

Probably the most important variable is wsrep_cluster_status. This variable will tell you if the node is up and fully part of the cluster.  If the node is fully part of the cluster this variable will return the status of Primary.  Any other response and the node is not fully part of the cluster.  You can check the status by running:

node1 mysql> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+
1 row in set (0.00 sec)

But checking the variable alone is not enough, because you can bootstrap all three nodes and have three seperate clusters. In this case, all three would return Primary but you would still not have a three node cluster. In order to tell if you if the node is part of the cluster you can use wsrep_cluster_size. This will tell you the number of nodes in the cluster.

node1 mysql> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

For our simple three node example, this is probably good enough. But what if you work in an environment with multiple clusters. How can you tell if the node is part of the right cluster? To be sure we can use wsrep_cluster_state_uuid. All the nodes fully participating in the cluster should return the same value.

node1 mysql> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_state_uuid | c0803766-24db-11e5-ac7a-abcded9c6987 |
+--------------------------+--------------------------------------+
1 row in set (0.00 sec)

For more information on how to use wsrep variables to check on the health of your cluster you can use the Galera documentation.  In future posts we will dive more into Galera.

Installing Percona XtraDB Cluster on CentOS 6 using VirtualBox

In this post I will be walking you through installing a 3 node test XtraDB Cluster (PXC) environment using VirtualBox.  The version of VirtualBox I will be using is 4.3.26. And I will be using CentOS 6.6 for the operating system on the three nodes. To get started you will want to perform a minimal install of 6.6 on 3 virtual machines. I named mine pxc-node1, pxc-node2, and pxc-node3. I also set up port forwarding in VirtualBox to allow me to ssh to the machines via a local port on the host. I set up 22201 to point to pxc-node1, 22202 to point to pxc-node2, and 22203 to point to pxc-node3. The last thing that I did was to add a second host only network interface to each of the nodes and ensure the same name was set for all the nodes.  You can do all of this work yourselves, or simply download an already configured appliance from here.

Now that we have the images created, it is time to discuss the plan for installing our Percona XtraDB Cluster. The actual installation process is very similar to the process that I used for installing Percona Server. But once we have the binaries laid down the configuration of the instances will be different and we will need to start the first node of the cluster a special way. While this quick overview does not give a lot of detail, I think it best to learn by doing. With that in mind, here we go.

First ensure that all of the nodes are running. To keep things simple we will be installing as root. But I have done installs at work on RHEL 6 using sudo and the process is pretty much identical. First thing we will need to do is to enable the network interfaces on each machine and disable SELinux. Galera does not work with SELinux and since XtraDB Cluster is just Percona’s Galera implementation we will need to disable it. In the console for each of the nodes you will need to run the following commands.

[root@localhost ~]$ vi /etc/sysconfig/network-scripts/ifcfg-eth0

You will want to modify the ONBOOT to be yes. The file should look like this when finished.

DEVICE=eth0
HWADDR=08:00:27:24:FD:19
TYPE=Ethernet
UUID=b3c128fe-335e-437d-bf3b-32f3706d9273
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=dhcp

Now save the file.  We also need to add a config for the secondary NIC which we want to not set up for DHCP, but use static ip addresses.  We will use 192.168.70.11 for pxc-node1, 192.168.70.12 for pxc-node2, and 192.168.70.13 for pxc-node3.  The contents of /etc/sysconfig/network-scripts/ifcfg-eth1 for pxc-node1 would look like.

NM_CONTROLLED=no
BOOTPROTO=none
ONBOOT=yes
IPADDR=192.168.70.11
NETMASK=255.255.255.0
DEVICE=eth1

PEERDNS=no

To disable SELInux we need to run.

[root@localhost ~] vi /etc/selinux/config

You will want to set it to disabled. The file should look like this when finished.

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

Now save the file. We also will be stopping the firewall and setting it not to restart.  In production we would want to add rules to allow traffic, but this is just a test environment so we’ll take the short cut.

[root@localhost ~]$ service iptables stop
[root@localhost ~]$ chkconfig iptables off

To make it easier to know what system you are on I would modify the shell to show the node name. To do that edit the bash configuration file using.

vi /etc/bashrc

Find the line that says

[ "$PS1" = "\\s-\\v\\\$ " ] && PS1="[\u@\h \W]\\$ "

Comment that line out and add the following

PS1='\u@pxc-node1:\w\$ '

The section should look like this when finished then save the file.

# Turn on checkwinsize
  shopt -s checkwinsize
#  [ "$PS1" = "\\s-\\v\\\$ " ] && PS1="[\u@\h \W]\\$ "
  PS1='\u@pxc-node1:\w\$ '

After making these changes you will want to restart the node using the following command and then perform the same steps on the other two nodes.

[root@localhost ~] shutdown -r 0

Once we are finished with the prep of the three nodes we are ready to start the installation of the PXC cluster. First we want to make sure the the MySQL libraries are not already on the box.

root@pxc-node1:~# yum -y remove mysql-libs

Then we want to import the EPEL and the Percona Repositories.

root@pxc-node1:~# rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
root@pxc-node1:~# rpm -Uvh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Now that we have the repository registered it is time to lay down the binaries.

root@pxc-node1:~# yum install -y socat
root@pxc-node1:~# yum install -y Percona-XtraDB-Cluster-server-56 Percona-XtraDB-Cluster-client-56 Percona-XtraDB-Cluster-shared-56 percona-toolkit percona-xtrabackup
root@pxc-node1:~# touch /etc/my.cnf
root@pxc-node1:~# /usr/bin/mysql_install_db --defaults-file=/etc/my.cnf --force --datadir=/var/lib/mysql --basedir=/usr/ --user=mysql

You will need to run these same steps on all three nodes to install the binaries. This will also create the data directory and populate it with the system tables.
Now that we have the binaries on each machine it is time to set up the cluster. This involves editing the /etc/my.cnf file. To start with, let’s configure the first node in our cluster. On pxc-node1 edit the /etc/my.cnf file so that it looks like this.

[mysqld]
datadir                         = /var/lib/mysql
log_error                       = error.log

log-bin
server-id = 1

query_cache_size=0
query_cache_type=0

innodb_buffer_pool_size                 = 48M
innodb_log_file_size            = 24M
innodb_flush_method                             = O_DIRECT
innodb_file_per_table
innodb_flush_log_at_trx_commit  = 0

performance_schema=OFF

binlog_format = ROW

# galera settings
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://192.168.70.11,192.168.70.12,192.168.11.13
wsrep_node_name = node1
wsrep_node_address = 192.168.70.11
wsrep_sst_auth = sst:secret
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = ON


[mysql]
prompt                          = "node1 mysql> "

[client]
user                            = root​

If we try to start the cluster now using the same method for a normal MySQL instance it will fail.

root@pxc-node1:~# service mysql start
Starting MySQL (Percona XtraDB Cluster)................................... ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).
 ERROR! MySQL (Percona XtraDB Cluster) server startup failed!

This is because the first node in a cluster has to be forced online as a safety feature since you cannot have quorum with only one node. To force it online run the following.

root@pxc-node1:~# service mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster)...... SUCCESS!

You may see a message similar to the following since we just tried to start the cluster without bootstrapping first.

root@pxc-node1:~# service mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster) ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists
Starting MySQL (Percona XtraDB Cluster)......... SUCCESS!

Either way you now have a cluster up and running with one node. Before we add any more nodes, let’s put some things in place to verify the cluster once it is up. We will be installing sysbench to allow us to drive a load at the machine, and we will be installing myq_tools to check the flow of transactions in the cluster. Run this on all three nodes to set up sysbench and myq_tools and get them ready for our use.

root@pxc-node1:~# yum install -y sysbench
root@pxc-node1:~# mysql
node1 mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
node1 mysql> GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost';
node1 mysql> \q
root@pxc-node1:~# yum install -y wget
root@pxc-node1:~# cd /usr/local/bin​ ​
root@pxc-node1:~# ​wget https://github.com/jayjanssen/myq-tools/releases/download/v0.5/myq_tools.tgz ​
​root@pxc-node1:~# tar -xzvf myq_tools.tgz ​
root@pxc-node1:~# cd bin ​
root@pxc-node1:~# mv * ../ ​
root@pxc-node1:~# cd .. ​
root@pxc-node1:~# rm -rf​ bin​ ​
​root@pxc-node1:~# ln -s /usr/local/bin/myq_status.linux-amd64 myq_status​​
​root@pxc-node1:~# vi /usr/local/bin/run_sysbench_oltp.sh

sysbench --db-driver=mysql --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=test --mysql-password=test --mysql-db=test --mysql-host=localhost --mysql-ignore-errors=all --oltp-tables-count=1 --oltp-table-size=250000 --oltp-auto-inc=off --num-threads=1 --report-interval=1 --max-requests=0 --tx-rate=10 run | grep tps

root@pxc-node1:~# chmod +x run_sysbench_oltp.sh
root@pxc-node1:~# sysbench --db-driver=mysql --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=test --mysql-password=test --mysql-db=test --mysql-host=localhost --mysql-ignore-errors=all --oltp-table-size=250000 --num-threads=1 prepare

You can verify that you have sysbench set up correctly but running the following.  You should not see any errors.  There should also be numbers in the reads and writes columns.

root@pxc-node1:/usr/local/bin# run_sysbench_oltp.sh
[   1s] threads: 1, tps: 10.99, reads: 153.85, writes: 43.96, response time: 12.64ms (95%), errors: 0.00, reconnects:  0.00
[   2s] threads: 1, tps: 17.02, reads: 238.28, writes: 68.08, response time: 12.84ms (95%), errors: 0.00, reconnects:  0.00
[   3s] threads: 1, tps: 10.00, reads: 153.02, writes: 40.01, response time: 10.10ms (95%), errors: 0.00, reconnects:  0.00
[   4s] threads: 1, tps: 19.00, reads: 253.00, writes: 76.00, response time: 10.26ms (95%), errors: 0.00, reconnects:  0.00
[   5s] threads: 1, tps: 9.00, reads: 125.99, writes: 36.00, response time: 11.42ms (95%), errors: 0.00, reconnects:  0.00
[   6s] threads: 1, tps: 6.00, reads: 83.98, writes: 23.99, response time: 12.19ms (95%), errors: 0.00, reconnects:  0.00
[   7s] threads: 1, tps: 6.00, reads: 84.01, writes: 24.00, response time: 63.33ms (95%), errors: 0.00, reconnects:  0.00

Now that we have the cluster running we need to add the other two nodes. But before we do that, we need to create a user with permissions for the SST process. We’ll talk more about SST in the future, but for now its important to know this process allows the other nodes to get a copy of the database when they join the cluster. To do that we need to run the following on node 1.

root@pxc-node1:/usr/local/bin# mysql
node1 mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';​​
node1 mysql> \q

Since we already have the binaries installed we just need to configure the other two instances and start mysql on the nodes. The instructions for node 2 are below. Notice that we don’t need to bootstrap these instances. In fact, if we do bootstrap them we would end up with 3 clusters instead of just one.

root@pxc-node2:~#vi /etc/my.cnf

[mysqld]
datadir                         = /var/lib/mysql
log_error                       = error.log

log-bin
server-id = 2

query_cache_size=0
query_cache_type=0

innodb_buffer_pool_size                 = 48M
innodb_log_file_size            = 24M
innodb_flush_method                             = O_DIRECT
innodb_file_per_table
innodb_flush_log_at_trx_commit  = 0

performance_schema=OFF

binlog_format = ROW

# galera settings
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://192.168.70.11,192.168.70.12,192.168.70.13
wsrep_node_name = node2
wsrep_node_address = 192.168.70.12
wsrep_sst_auth = sst:secret
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = ON


[mysql]
prompt                          = "node2 mysql> "

[client]
user                            = root​

root@pxc-node2:~# service mysql restart
Shutting down MySQL (Percona XtraDB Cluster).. SUCCESS!
Starting MySQL (Percona XtraDB Cluster).....State transfer in progress, setting sleep higher
... SUCCESS!

At this point we now have a two node cluster. To add the last node you need to modify /etc/my.cnf on that node and then start the mysql service. You can use the configuration file for node 2 as an example. You will need to modify the server-id, wsrep_node_name, wsrep_node_address, and prompt to the values for pxc-node3. If you have any issues let me know.

In a future post, I will show you how to set up HAProxy to provide a single connection (actually we will use 2 connections but more on that later) for your applications. We will also play around with some of the more interesting features of Galera and Percona XtraDB Cluster.

MySQL Auto Increment Fun

Originally Posted 10/21/2014

Coming from a heavy Microsoft SQL Server background the concept of the AUTO_INCREMENT attribute in MySQL is very familiar.  In SQL Server we call it the IDENTITY attribute, but they are conceptually very similar.  When a row is inserted into the table the column specified with the AUTO_INCREMENT attribute automatically gets the next value in a sequence.  But while they are conceptually very similar, there are a number of differences that a SQL Server Database Administrator should be aware of.  To start with lets create a database and a table we will use as we explore the AUTO_INCREMENT attribute.  You can copy the following script to a file on your system named “auto_increment.sql”.  Or you can simply run the commands from the mysql prompt to generate the objects and seed the table with data.

CREATE DATABASE IF NOT EXISTS moderndba;

 USE moderndba;

 CREATE TABLE IF NOT EXISTS autofun
 (
       employee_id        INT UNSIGNED NOT NULL AUTO_INCREMENT
     , employee_name      VARCHAR(150) NOT NULL
     , employee_level     TINYINT UNSIGNED NULL
     , PRIMARY KEY (employee_id)
 );

 INSERT INTO autofun (employee_name, employee_level)
 VALUES
  ('Joe Smith', 0)
, ('Tim Bradford', 1)
, ('Betty Stafford', 1);

If you chose to copy the script and run it you can run the script as follows.

[student@percona56 ~]# mysql -u root -p < auto_increment.sql

In SQL Server you can insert directly into an IDENTITY column, but you have to turn on IDENTITY_INSERT first. You can only have it turned on for one table at a time and you need elevated permissions to do so. Let’s see what happens when we try to insert directly into a AUTO_INCREMENT column in MySQL. Run the following statements from the MySQL prompt.

[student@percona56 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-72.2-log Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE moderndba;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A

 Database changed
 mysql> INSERT INTO autofun
     -> (employee_id, empoyee_name)
     -> VALUES
     -> (10, 'John Wiley');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |          10 | John Wiley     |           NULL |
 +-------------+----------------+----------------+
 4 rows in set (0.00 sec)

 mysql>

As you can see, in MySQL you can insert directly into the identity table as the default behavior. But what happens when we go to add the next row. Will the employee_id be a 4, an 11, or some other number? Let’s see.

mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Amanda King');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 +-------------+----------------+----------------+
 5 rows in set (0.00 sec)

 mysql>

Ok. That makes sense. The last row inserted was a 10, so the next row would be safest obviously to use the last value plus one. But if this is the case, surely when you insert a value into the column explicitly it must be greater than the largest value in the column, correct? Let’s find out. We can run the following commands and test out this theory.

mysql> INSERT INTO autofun
     -> (employee_id, empoyee_name)
     -> VALUES
     -> (5, 'Sarah Wagoner');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 +-------------+----------------+----------------+
 6 rows in set (0.00 sec)

 mysql>

Alright, you can insert into the middle of the series. Well, for safety it will increment the column from the largest value and not the last then, right?

mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Mia Brown');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 +-------------+----------------+----------------+
 7 rows in set (0.00 sec)

 mysql>

Yes it does. That seams pretty safe, since you could easily run into problems using the number after the last number used if you allowed inserts into the middle of the series. But it brings up a specific risk that DBAs and Developers need to be aware of. Remember from the script above we made the employee_id field an unsigned integer. The maximum value we can store in an unsigned integer in MySQL is 4,294,967,295. The risk is probably a lot lower on an unsigned integer than on a signed tiny integer (1 byte with a maximum value of 127), but what will happen if someone accidentally inserts a row with a value for employee_id towards the end of the range. Let’s take a look and see what happens.

mysql> INSERT INTO autofun
     -> (employee_id, empoyee_name)
     -> VALUES
     -> (4294967294, 'Brooke Allen');
 Query OK, 1 row affected (0.03 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 +-------------+----------------+----------------+
 8 rows in set (0.00 sec)

 mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Chris Walters');
 Query OK, 1 row affected (0.03 sec)


 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 |  4294967295 | Chris Walters  |           NULL |
 +-------------+----------------+----------------+
 9 rows in set (0.00 sec)

 mysql>

So everything is cool so far, but we are now up against the limit of the range for an unsigned integer, and we got there with only two inserts. (We could have gotten there with one insert if we really wanted.) So what happens when we go to add the next auto incrementing row to the table. Lets find out.

mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Ben Forest');
 ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
 mysql>

So we have reached the end of the range and when MySQL tries to grab the next value larger than the largest value in the column it can’t and therefore grabs the same maximum value for the data type. This leads to the error of duplicate primary keys. But what if we remove the rows from the table, then what? Is it really looking at the maximum value in the table, or the maximum value ever inserted. Lets see.

mysql> DELETE FROM autofun WHERE employee_name = 'Chris Walters';
 Query OK, 1 row affected (0.05 sec)

 mysql> SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 +-------------+----------------+----------------+
 8 rows in set (0.00 sec)

 mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Ben Forest');
 Query OK, 1 row affected (0.03 sec)

 mysql>  SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967294 | Brooke Allen   |           NULL |
 |  4294967295 | Ben Forest     |           NULL |
 +-------------+----------------+----------------+
 9 rows in set (0.00 sec)

 mysql> DELETE FROM autofun WHERE employee_name IN ('Ben Forest', 'Brooke Allen');
 Query OK, 2 row affected (0.02 sec)

 mysql> INSERT INTO autofun (empoyee_name)
     -> VALUES ('Nina Gardner');
 Query OK, 1 row affected (0.03 sec)

 mysql>  SELECT *
     -> FROM autofun;
 +-------------+----------------+----------------+
 | employee_id | employee_name  | employee_level |
 +-------------+----------------+----------------+
 |           1 | Joe Smith      |              0 |
 |           2 | Tim Bradford   |              1 |
 |           3 | Betty Stafford |              1 |
 |           5 | Sarah Wagoner  |           NULL |
 |          10 | John Wiley     |           NULL |
 |          11 | Amanda King    |           NULL |
 |          11 | Mia Brown      |           NULL |
 |  4294967295 | Nina Gardner   |           NULL |
 +-------------+----------------+----------------+
 8 rows in set (0.00 sec)

 mysql>

So it is not using the largest value in the column plus one. Instead it really is using the next value after the largest value ever inserted. The only issue is that when you get to the end of the range, instead of raising an error that the next value is out of range it takes the closest value in the range and tries to use it. This can be an issue since we are dealing with the primary key on the table, what does this behavior mean to other tables with relationships back to this key.

I understand that this is an edge case, and most of the time you will not need to worry about this. But it is an interesting behavior and one that I have not seen in other relational database management systems. I hope you found this as interesting as I have.