Patch to Add Shutdown Statement to MySQL MariaDB

Featured

MySQL LogoAt the OSCON 2011 MariaDB Birds-of-a-Feather (BoF) session, I suggested adding a MySQL SHUTDOWN statement to Monty, which was written up as WL#232. Other databases have this feature, and it’s very handy when automating management of a cluster of MySQL servers.

And at the Percona Live MySQL Conference 2013, Monty suggested to MariaDB BOF attendees that a good way to get a new feature added is to to write a patch to pave the way for a committer to start with.

Phase 1

So … I sat down last nite and wrote the patch against MariaDB 5.5.30.

Basically it meant telling mysql’s lex/yacc files to parse “shutdown”, then calling the existing MySQL API shutdown kill_mysql() function.

This code is released under the Open Source BSD-new License, according to the MariaDB Contributor Agreement.

shutdown_0.1.patch.txt – MariaDB 5.5.30:

--- sql_parse.cc	2013-03-11 03:29:13.000000000 -0700
+++ /home/james/mariadb-5.5.30-new/sql/sql_parse.cc	2013-05-15 13:17:05.000000000 -0700
@@ -1305,7 +1305,6 @@
     my_ok(thd);
     break;
   }
-#ifndef EMBEDDED_LIBRARY
   case COM_SHUTDOWN:
   {
     status_var_increment(thd->status_var.com_other);
@@ -1333,7 +1332,6 @@
     error=TRUE;
     break;
   }
-#endif
   case COM_STATISTICS:
   {
     STATUS_VAR *current_global_status_var;      // Big; Don't allocate on stack
@@ -3736,6 +3734,31 @@
                     lex->kill_signal);
     break;
   }
+  case SQLCOM_SHUTDOWN:
+  {
+    // jeb - This code block is copied from COM_SHUTDOWN above. Since kill_mysql(void) {} doesn't take a level argument, the level code is pointless.
+    // jeb - In fact, the level code should be removed and Oracle Database statements implemented: SHUTDOWN, SHUTDOWN IMMEDIATE and SHUTDOWN ABORT. See WL#232.
+
+    status_var_increment(thd->status_var.com_other);
+    if (check_global_access(thd,SHUTDOWN_ACL))
+      break; /* purecov: inspected */
+
+    enum mysql_enum_shutdown_level level;
+    level= SHUTDOWN_DEFAULT;
+    if (level == SHUTDOWN_DEFAULT)
+      level= SHUTDOWN_WAIT_ALL_BUFFERS; // soon default will be configurable
+    else if (level != SHUTDOWN_WAIT_ALL_BUFFERS)
+    {
+      my_error(ER_NOT_SUPPORTED_YET, MYF(0), "this shutdown level");
+      break;
+    }
+    DBUG_PRINT("SQLCOM_SHUTDOWN",("Got shutdown command for level %u", level));
+    my_eof(thd);
+    kill_mysql();
+    res=TRUE;
+    break;
+  }
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   case SQLCOM_SHOW_GRANTS:
   {
--- sql_yacc.yy	2013-03-11 03:29:19.000000000 -0700
+++ /home/james/mariadb-5.5.30-new/sql/sql_yacc.yy	2013-05-15 11:12:03.000000000 -0700
@@ -791,7 +791,7 @@
   Currently there are 174 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 174
+%expect 196
 
 /*
    Comments for TOKENS.
@@ -1645,6 +1645,7 @@
         definer_opt no_definer definer
         parse_vcol_expr vcol_opt_specifier vcol_opt_attribute
         vcol_opt_attribute_list vcol_attribute
+        shutdown
 END_OF_INPUT
 
 %type  call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt
@@ -1796,6 +1797,7 @@
         | savepoint
         | select
         | set
+        | shutdown
         | signal_stmt
         | show
         | slave
@@ -13715,6 +13717,17 @@
         ;
 
 
+shutdown:
+          SHUTDOWN
+          {
+            LEX *lex=Lex;
+            lex->value_list.empty();
+            lex->users_list.empty();
+            lex->sql_command= SQLCOM_SHUTDOWN;
+          }
+        ;
+
+
 set_expr_or_default:
           expr { $$=$1; }
         | DEFAULT { $$=0; }
--- sql_prepare.cc	2013-03-11 03:29:11.000000000 -0700
+++ /home/james/mariadb-5.5.30-new/sql/sql_prepare.cc	2013-05-15 03:07:00.000000000 -0700
@@ -2173,6 +2173,7 @@
   case SQLCOM_GRANT:
   case SQLCOM_REVOKE:
   case SQLCOM_KILL:
+  case SQLCOM_SHUTDOWN:
     break;
 
   case SQLCOM_PREPARE:
--- mysqld.cc	2013-03-11 03:29:14.000000000 -0700
+++ /home/james/mariadb-5.5.30-new/sql/mysqld.cc	2013-05-15 01:20:11.000000000 -0700
@@ -3333,6 +3333,7 @@
   {"savepoint",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SAVEPOINT]), SHOW_LONG_STATUS},
   {"select",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SELECT]), SHOW_LONG_STATUS},
   {"set_option",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SET_OPTION]), SHOW_LONG_STATUS},
+  {"shutdown",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHUTDOWN]), SHOW_LONG_STATUS},
   {"signal",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SIGNAL]), SHOW_LONG_STATUS},
   {"show_authors",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_AUTHORS]), SHOW_LONG_STATUS},
   {"show_binlog_events",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_BINLOG_EVENTS]), SHOW_LONG_STATUS},
--- sql_lex.h	2013-03-11 03:29:13.000000000 -0700
+++ /home/james/mariadb-5.5.30-new/sql/sql_lex.h	2013-05-15 01:19:17.000000000 -0700
@@ -193,6 +193,7 @@
   SQLCOM_SHOW_RELAYLOG_EVENTS, 
   SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS,
   SQLCOM_SHOW_CLIENT_STATS,
+  SQLCOM_SHUTDOWN,
 
   /*
     When a command is added here, be sure it's also added in mysqld.cc

To apply:

tar zxvf - < mariadb-5.5.30.tar.gz
cd mariadb-5.5.30/sql
wget http://jebriggs.com/php/shutdown_0.1.patch.txt
patch -b < shutdown_0.1.patch.txt

make.sh:

#!/bin/bash

cd mariadb-5.5.30
cmake . -DCMAKE_INSTALL_PREFIX:PATH=/usr/local/mariadb-5.5.30
make --with-debug
sudo make install

start.sh:

#!/bin/bash

killall mysqld
/usr/local/mariadb-5.5.30/bin/mysqld_safe --user=mysql --debug &
tail -f  /tmp/mysqld.trace | grep Got &
mysql -u root -p

mysql client (with mysqld.log and mysql.trace entries overlaid):

mysql> shutdown;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 130515 13:20:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

/tmp/mysql.trace:


T@4    : | | | >parse_sql
T@4    : | | | <parse_sql
T@4    : | | | >LEX::set_trg_event_type_for_tables
T@4    : | | | <LEX::set_trg_event_type_for_tables
T@4    : | | | >mysql_execute_command
T@4    : | | | | >deny_updates_if_read_only_option
T@4    : | | | | <deny_updates_if_read_only_option
T@4    : | | | | >stmt_causes_implicit_commit
T@4    : | | | | <stmt_causes_implicit_commit
T@4    : | | | | SQLCOM_SHUTDOWN: Got shutdown command for level 16
T@4    : | | | | >set_eof_status
T@4    : | | | | <set_eof_status
T@4    : | | | | >kill_mysql
T@4    : | | | | | quit: After pthread_kill
T@4    : | | | | <kill_mysql
T@4    : | | | | proc_info: /home/james/mariadb-5.5.30/sql/sql_parse.cc:4507  query end

/var/log/mysqld.log:

130515 13:20:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130515 13:20:08 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
130515 13:20:08 InnoDB: The InnoDB memory heap is disabled
130515 13:20:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130515 13:20:08 InnoDB: Compressed tables use zlib 1.2.3
130515 13:20:08 InnoDB: Initializing buffer pool, size = 128.0M
130515 13:20:08 InnoDB: Completed initialization of buffer pool
130515 13:20:08 InnoDB: highest supported file format is Barracuda.
130515 13:20:09  InnoDB: Waiting for the background threads to start
130515 13:20:10 Percona XtraDB (http://www.percona.com) 5.5.30-MariaDB-30.1 started; log sequence number 1597945
130515 13:20:10 [Note] Plugin 'FEEDBACK' is disabled.
130515 13:20:10 [Note] Event Scheduler: Loaded 0 events
130515 13:20:10 [Note] /usr/local/mariadb-5.5.30/bin/mysqld: ready for connections.
Version: '5.5.30-MariaDB-debug'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130515 13:20:37 [Note] Got signal 15 to shutdown mysqld
130515 13:20:37 [Note] /usr/local/mariadb-5.5.30/bin/mysqld: Normal shutdown

130515 13:20:37 [Note] Event Scheduler: Purging the queue. 0 events
130515 13:20:37  InnoDB: Starting shutdown...
130515 13:20:38  InnoDB: Shutdown completed; log sequence number 1597945
130515 13:20:38 [Note] /usr/local/mariadb-5.5.30/bin/mysqld: Shutdown complete

130515 13:20:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

A possible test would be like this, but it would interfere with operation of the test mysqld instance:

mysql-test/t/shutdown.test:

shutdown;

Phase 2

My above patch applies cleanly within the existing MySQL shutdown framework, which implements a feature like Oracle Database's SHUTDOWN IMMEDIATE command.

However, my patch is a Pyrrhic victory, since there's so much wrong with MySQL's existing shutdown framework that it will take an internals committer to sort it out.

The shutdown framework is badly designed, if it was designed at all, since it fails the "does this feel programmed on purpose?" test, and in fact doesn't work reliably:

  1. Conceptually, there should be 3 Oracle Database-style SHUTDOWN options: WAIT, IMMEDIATE and ABORT. Implementing SHUTDOWN WAIT would mean intrusive changes to the MySQL source code, while SHUTDOWN ABORT would be easier to program, but at the risk of data integrity.
  2. the following bug reports describe a race condition between mysqld threads and the shutdown thread:

This is actually my second MySQL patch contribution. In 1997 or 1998 I submitted a patch for the installer, which was one of the most troublesome components at that time. Monty rewrote it, but I liked my version better.

I guess I'll have to pay myself the worklog bounty of $100. :)

shutdown_0.1.patch.txt
MySQL's Missing Shutdown Statement
WL#232

Database Architecture: System of Record and MySQL Multi-Master Products

MySQL LogoAs MySQL multi-master products (Galera, Percona Xtradb Cluster/Galera, Tungsten Replication, etc.) proliferate, a question arises, “How do they support the notion of “The system of record”?

There’s 3 aspects of implementing the system of record (non-trivial in of multi-master systems):

  1. policy – the DBA must specify which database is the master for each dataset, what the failover steps are, and what to do in cases of data drift
  2. technology – the distributed system must enforce that the master receives all updates (aside from bugs, corruption and cosmic rays)
  3. operations – monitoring the distributed system and ensuring the policy is enforced. Early detection of network partitions and data drift is critical for data integrity in multi-master systems.

One of the biggest limitations of vanilla MySQL is that it is inherently single-master, as there is no in-flight conflict resolution logic to support two or more masters doing UPDATE/INSERT on the same rows.

But that is also ironically one of its strengths – single-master enforces one aspect of data integrity, the system of record, at the architectural level. Moving to a master-master product requires both conflict resolution and additional operational processes to handle data drift in both masters and slaves.

Product Replication Technology In-flight Conflict Resolution Consistency Checking Supported Storage Engines CAP One System of Record
MySQL 5.5 Async & SemiSync, Statement, Row, Hybrid No pt-table-checksum MyISAM, InnoDB AP Yes, Active Master
MySQL 5.6 with GTID Async & SemiSync, Statement, Row, Hybrid No pt-table-checksum MyISAM, InnoDB AP Yes, Active Master
Galera Sync, Certification based Row with Statement DDL Yes, Deadlock pt-table-checksum InnoDB CA No, but all nodes have same data?
Percona Galera Sync, Certification based Row with Statement DDL Yes, Deadlock pt-table-checksum InnoDB CA No, but all nodes have same data?
Tungsten Replication Async, Custom Yes, filters and tags trepctl check MyISAM*, InnoDB AP Yes, one master is defined by DBA in filters and tags

* Tungsten supports the MyISAM storage engine, but does not recommend it because it is not crash-safe in several respects.
* Row-based replication is expected to inherently drift less than statement-based replication by most people.
* CAP is not a checkbox item. Please read “Problems with CAP, and Yahoo’s little known NoSQL system” for a deeper understanding of applying the CAP Theorem.

The System of Record in the Global Data Warehouse
wikipedia: System of Record
The Scale-Out Blog: The System of Record Approach to Multi-Master Database Applications
The Scale-Out Blog: If You *Must* Deploy Multi-Master Replication, Read This First
The Scale-Out Blog: Slouching towards Multi-Master Conflict Resolution
Multi-Master Replication Manager for MySQL
What’s wrong with MMM?
pt-table-checksum
More Galera lessons: parallel slave, out of order commits and deadlocks

IMUG: Building Scalable i18n and L10n tools for 300 Languages at Wikipedia

Wikipedia Logo
Alolita Sharma, Director of Engineering at Wikipedia, did a talk tonite at IMUG on “Building Scalable i18n and L10n tools for 300 Languages at Wikipedia”.


Alolita Sharma

She is driving the initiative for Wikipedia to build open source tools and technologies to support hundreds of languages for Wikipedia. An engineering manager and software engineer, she has been working with open source software and has promoted open source adoption for more than a decade.

Some Wikipedia statistics:

- 24.6 million articles
- 286 languages
- 500 million unique users monthly
- 22 billion page views per month
- 310 incubator languages (596 total languages)
- 792 wikis for projects plus Mediawiki

Some notes on her talk:

- WMF Language Engineering Team (10 staff)
- equality for all languages
- great user experience for all languages
- nobody wants tofu blocks (empty rectangular glyphs for missing font) as their language user experience – jQuery.webfonts
- 50+ high-quality fonts for 20+ non-latin scripts in font formats ttf, woff, eot, svg
- Language Coverage Matrix for Wikimedia websites
- interesting translatewiki.net demo in lohit devenagari, other Indian languages, phonetic keymaps
- UIs are easy compared to computationally-hard problems like content translation and selection
- crowdsourced translations
- audience question about showing context for string translations
- you might get 70% accuracy in machine translation of English, but 10% in other languages.
- stats.wikipedia.org
- big difference between rich web and new mobile platform support
- crowdsourced translation works when there is a common interest (twitter, facebook, WMF)
- crowdsourced translation quality is not an issue for WMF because they guinea-pig their users and feedback is provided rapidly
- use CLDR, but there needs to be bulk import tools to really give back
- listen-in on Unicode Consortium and provide comments, too small to drive it


Wikipedia talk audience members

Alolita is on the board of the Open Source Initiative, adviser to Software Freedom Law Center and a passionate advocate of open source and the open Web. She holds Bachelors and Masters degrees in Computer Science and speaks internationally on language technologies, i18n, L10n, open web standards, open source trends, technologies and building successful developer communities.

Joe Katz did quite an IMUG introduction, strolling down memory lane back to 1987.

Thanks again to Adobe for hosting the event tonite.

IMUG Meetup: Home Page, This Event’s Comments

Lion Air PK-LKS Accident Investigation Report April 2013


PK-LKS 747-800

Accident investigators examine PK-LKS. Note the Lion Air logo has been painted over.

From a recent article on the April PK-LKS 737-800 April accident in Bali:

“The committee’s report issued safety recommendations to the airline in order to ensure pilots were trained to follow correct procedure in handing over flight controls during critical moments and times. It also called on the airline to “review the policy and procedures regarding the risk associated with changeover of control at critical altitudes or critical times.”

The altitude at which the co-pilot handed control of the plane to his colleague was below the minimum altitude considered safe to continue final descent.”


DPS VOR/DME Runway 09

Looking at the approach plate above in the investigation report (above) … minimum descent altitude (MDA) for a straight-in approach (aligned within 30° of the runway) is 465′ (see red arrow) for the missed approach, yet they descended to 150′ in rain, the copilot handed flying control over to the pilot, who started their missed approach at 20′.

For non-pilots, let me spell this out: the pilot just had to put E on his compass, descend to 465′, and land if the airport was visible, otherwise climb.

A new 737-800 was destroyed, 4 people were hurt, and everybody on board risked their lives because these “pilots” couldn’t follow one of the simplest approach plates.

The training and recency of the SIC is also questionable (Indian, 1200 hours total time, but only 14 hours 36 minutes logged in the past 60 days.)

The following questions come to mind:

  • Why so few recent hours?
  • Was this flight really a training exercise for the copilot?
  • Did he have an earned pilot license, or was it an “Indian expedited license”?
  • Did both pilots have approach plates?
  • If so, why couldn’t they read and follow the approach plates?
  • Does the copilot understand the difference between a non-precision and a precision approach?
  • If so, why would the copilot descend below MDA 465′ to 150′, which sounds like a precision (ILS) altitude?
  • Does Lion Air management pressure pilots to not execute missed approaches to save money, fuel and embarrassment (as in India)?

I advise people flying in Indonesia to completely avoid Surabaya because of the frequent torrential rains, and to check for rain at other airports before embarking on a flight, if possible.

Pilot training blamed for Bali airliner crash
Indonesia NTSC 2013
wikipedia: Instrument Flying

Howto Add a New Command to the MySQL Server

MySQL LogoAdding a new statement or command to the MySQL server is not difficult.

Since I just added the SHUTDOWN command, I thought I would be helpful to outline the steps needed to add a new command.

Prerequisites:

  1. some familiarity with C/C++ syntax and programming (like “The C Programming Language”, by Kernighan and Ritchie.)
  2. some familiarity with lex and yacc. (I read the Dragon Book a long time ago.)
  3. access to a linux account with cmake, gcc, make and bison packages.
# CentOS
yum install cmake gcc make bison

# Ubuntu
apt-get update
apt-get install cmake gcc make bison

# unpack the MySQL source code:

tar zxvf - < mariadb-5.5.30.tar.gz

# most of the files you need to modify are in this directory:

cd mariadb-5.5.30/sql
  • sql_parse.cc
  • sql_yacc.yy
  • sql_prepare.cc
  • mysqld.cc
  • sql_lex.h

# add the token(s) (commands and arguments you think you will need) and verify the syntax:

bison -v sql_yacc.yy

# if you get warnings, fix %expect in sql_yacc.cc

# cut-and-paste a code block from a command with similar syntax in sql_yacc.cc to implement your new command, and build a test version of MySQL

# build your new server in a sandbox:

make.sh:

#!/bin/bash

cd mariadb-5.5.30
cmake . -DCMAKE_INSTALL_PREFIX:PATH=/usr/local/mariadb-5.5.30
make --with-debug
sudo make install

# test your new server with 3 terminal windows:

start.sh:

#!/bin/bash

killall mysqld
/usr/local/mariadb-5.5.30/bin/mysqld_safe --user=mysql --debug &
tail -f  /tmp/mysqld.trace | grep Got &
tail -f /var/log/mysqld.log &
mysql -u root -p
# login, then test your new command while watching the log and trace

# read /var/log/mysqld.log and /tmp/mysqld.trace for errors and panics like this:

Version: '5.5.30-MariaDB-debug'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
mysqld: /home/james/mariadb-5.5.30/sql/sql_parse.cc:4477: int mysql_execute_command(THD*): Assertion `0' failed.
130515 11:25:19 [ERROR] mysqld got signal 6 ;

This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

The above panic was caused by the SQLCOM_ switch falling through, because the new command was not defined yet.

# When you’re done, make a test

vi mysql-test/t/my_new_command.test

# Create a patch file:

mv mariadb-5.5.30 mariadb-5.5.30-new
tar zxvf - < mariadb-5.5.30.tar.gz

cd mariadb-5.5.30/src
>patch.txt
for i in sql_parse.cc sql_yacc.yy sql_prepare.cc mysqld.cc sql_lex.h; do
   echo $i
   diff -u $i ../../mariadb-5.5.30-new/sql/ >>patch.txt
done
# don't forget mysql-test/t/my_new_command.test

# apply your patch file:

patch -b < patch.txt

# do a build and test your patch before distributing it.

How can I get the output of the DBUG_PRINT
How to find shift/reduce conflict in this yacc file?
MariaDB Contributor Agreement (MCA) Frequently Asked Questions
wikipedia: diff

Keywords: MySQL server programming, tutorial, patch.