MySQL 5.6 Views and Stored Procedures Tips

Featured

MySQL LogoI recently tuned an existing application that used dozens of views and hundreds of stored procedures using MySQL 5.6.

There seems to be three attitudes towards using views and stored procedures (SPs) with MySQL:

  1. don’t use them at all to increase portability
  2. just use SPs to reduce network traffic in large reporting queries (my choice)
  3. go crazy and use them everywhere like old-school Oracle Enterprise apps.

Here are some notes on using views:

  • before creating views, review your schema to ensure keys have matching types and charsets for good performance. It’s much easier to spot schema problems in a text listing than to guess why a view is slower than expected at execution time. (This is doubly true for MySQL Cluster.)
  • MySQL currently doesn’t have CREATE VIEW FORCE, although MariaDB will later in 2014. The FORCE option will greatly simply view administration and also mysqldump output, which creates temporary tables to ensure views can be created regardless of table/view ordering issues
  • When looking at the MariaDB source code, it’s apparent that some view options were never actually implemented, like RESTRICT/CASCADE

And some notes on stored procedures (SPs):

  • if a SP makes a stateful session change, like set sql_log_bin=0, ensure that isn’t going to be a problem later if an exception condition doesn’t reset it
  • after running a SP, SHOW PROFILES will list all the queries executed with performance statistics
  • SPs that do non-essential SELECTs or INFORMATION SCHEMA queries probably need to be reviewed by a DBA for fundamental problems like non-atomic “reading before writing”

Both views and SPs are relatively new MySQL features, so budget some extra development and testing time when using them, especially with replication.

[MDEV-6365] CREATE VIEW Ignores RESTRICT/CASCADE Options
mysqlperformanceblog.com: Using MySQL triggers and views in Amazon RDS

Velocity Conference Santa Clara 2014 Tips Game Cards

Featured

The O’Reilly Velocity Web Operations & Performance Conference is June 24-26 in Santa Clara.

Next to the messages/jobs board was a Web Ops & Performance Tips board:

- use source maps to debug compressed JS and CSS
- use ::before to optimize font rendering
- use local storage to persist markup and templates to reduce requests and payload
- avoid CSS block rendering in chrome by not using screen media type until after. Then put screen back to element
- use gatling stress tool for load generation/perf testing (Apache Licence 2.0)
- learn curl
- learn POSIX before recreating another tool that already exists. Bill Joy (?)
- “if you do it more than twice a week, automate”
- it takes no skills to do NoOps! :)

AWS Pop-up Loft, San Francisco

Featured



Amazon Web Services pop-up loft (Ask an Architect area, lecture hall, kitchen/lounge)
Photo credit: Amazon.com.

I happened to be in SF today, so I went to the Amazon Web Services pop-up loft on Market St.

Amazon rented an empty storefront for 4 weeks for lecture sessions upstairs, and a computer lab and an ‘Ask an Architect’ bar downstairs.

One of the hosts said the loft was a shell in May, and they had to build out everything: the kitchen area, 2 bathrooms and various partitions.

I asked the experts about new EBS and RDS features, and they had answers as well as a $100 AWS credit.

The weather was sunny and warm in SF.

Lots of street performers and hustlers, including a very smooth male R&B singer. A young rapper named Rap2K15 was selling hand-made CDs.

Update 2014 06 23: Apparently a drawing was held, and I was one of 3 winners of a free general pass to the AWS:Reinvent Conference :)

Update 2014 06 24:

AWS Bootcamp

Full-day AWS overview, including EC2, S3, RDS, VPC and IAM, with 2 labs.

“Provisioning and Managing AWS Infrastructure with Chef” with special guest George Miranda, Chef Technical Consultant, Chef

George talked about using Chef tools like chef metal, knife and chef zero and a minimal amount of ruby to make an AMI and provision a MySQL server and 5 Nginx web servers.

Slides

@gmiranda23, chef-ami-factory

Update 2014 06 26:

Dealing With Obstacles at Scale, Bob Hagemann, Twilio

To reduce pain:

- UTC timezone
- UTF8
- use thin AMI and chef/puppet instead of thick AMI
- wrote boxconfig a few years ago (like netflix asgard)
- remote admin mainly
- small teams 3-8
- services should run in 3 AZs
- monitoring with nagios, cron, pingdom
- haproxy on each host as proxy
- MySQL, MHA, LVM. Manual failover.
- SQS DLQ
- global low latency with route53
- http://github.com/twilio
- @bobzilla42
- Uses freeswitch plus own telcom sw
- billing system 100s QPS
- Ops team is about 8 people
- VPNs to HQ and carrier-approved colo
- three founders, one came from Amazon.

925 Market Street, SF
June 4 – 27, 2014 (likely closed on the 27th for dismantling)
Free registration, tshirts and lunch. Closes 5:30 pm, 6:00 pm or 8:00 pm daily.
Muni 30 and 45 return from Market St. and 5th to Caltrain.

@AWSstartups #AWSloft

Advanced Liquibase Techniques

Featured

Liquibase LogoI recently did some work with liquibase. Here’s some techniques for advanced users to workaround limitations to calculate query cost.

Liquibase Introduction

Liquibase is an Open Source (Apache 2.0 License) Java utility and API for specifying and versioning schema changes (DDL) for several popular databases. It is commonly introduced to projects by programmers, rather than DBAs.

What liquibase can do:

  • allow “refactoring” of SQL schema changes to target multiple databases using XML by using a database-independent syntax, or raw SQL, depending on your preference
  • allow conditional execution and rollback of SQL based on database type or environment.

What liquibase can’t do:

  • has no built-in provisions for operational concerns, like conditionally executing SQL based on time/cost. There’s an assumption that schema changes are online, often true on Oracle and SQL Server, less so on MySQL, especially prior to 5.6
  • does not do intelligent merges to the same object across changesets, like adding multiple columns to the same table in one statement.

How liquibase works:

  • the programmer specifies schema changes in Java, XML or JSON and runs the liquibase command
  • liquibase creates 2 tables in your database to store version, user and patch name information and to lock out other simultaneous liquibase runs.

How to Make Liquibase Consider Cost for MySQL

After some experimentation, there’s a couple liquibase features you can use to do more advanced things:

  1. create a savepoint using the tag and rollback options:
    • liquibase tag rel0; liquibase update …; liquibase rollback rel0
  2. prepend and append logic to each changeset to use information_schema on the SQL DDL statement. on failure, exit with 1 (See XML example below)

changeset.xml:

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
       http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet id="1" author="james">
     <sql>
       create table if not exists `profiling` ( `connection_id` int(11) not null default 0, `query_id` int(11) not null default '0', `state` varchar(40) default '', KEY (query_id));
       truncate table profiling;
       set profiling=1;

       alter table department add column test2 int default null;
       insert into profiling (connection_id, query_id, state) select connection_id(), query_id, state from information_schema.profiling where query_id=2;
     </sql>
     <rollback>
        <sql>alter table department drop column test2</sql>
    </rollback>
    </changeSet>

    <changeSet id="1-post" author="james">
      <preConditions onFail="HALT">
        <sqlCheck expectedResult="0">SELECT count(*) from profiling where state='copy to tmp table'</sqlCheck>
      </preConditions>
    </changeSet>
</databaseChangeLog>

Notes:

  1. the changeset DDL statement will still have run, even if the precondition HALTs – they’re separate changesets, after all
  2. the rollback in “1″ will not be executed, even if “1-post” HALTs.

The workaround for those 2 issues is to combine the two techniques in a shell script:

#!/bin/bash

liquibase tag rel0

liquibase update changeset.xml || {
    # fail the build pipeline to not propagate changeset to next stage
    # (ie. don't run in production)
    liquibase rollback rel0
    mysql -e 'alter table test.department drop column test2' 
    exit 1
}

The above looks a little kludgy, but provides a stepping stone for the reader to customize in their particular environment. (The preConditions and bash script can be easily autogenerated with a Perl or Python script.)

An alternative to XML is using the Java API to set everything up.

Please leave a comment if you have any suggestions or a Java API program.

Percona Live MySQL Conference Santa Clara 2014

Featured

The Percona Live MySQL Conference was held once again in Santa Clara from April 1-4, 2014.

Executive Summary:

  1. Percona hosted another excellent conference, with 1,150 attendees from 43 countries plus a vibrant exhibit hall.
  2. The overall themes that emerged this year were “What’s new in MySQL 5.6?” and “The rise of Galera Cluster.” Unfortunately, Oracle delivered the 5.6 features they promised, but didn’t bother to ask production DBAs what they really needed (ie. GTIDs require downtime to configure, and ALTER ONLINE doesn’t support throttling or background operation on slaves (SR 3-8856341908).)
  3. MySQL 5.7 is promising about double the performance of 5.6, but note that the 5.7 feature micro-benchmark effort hasn’t translated into a complete understanding of whole database performance yet.
  4. the current active branches are now: Oracle 5.6/5.7, MariaDB 10.0/10.1, Webscale SQL (Facebook, Google, LinkedIn, and Twitter), Facebook 5.6 with Deployable GTIDs, and Percona Server 5.6. (The version you want to migrate to is one based on MySQL 5.6.17 or later.)


Severalnines Booth
Severalnines.com booth. They create and support cluster and cloud database solutions. Photo credit: Steve Barker, SphinxSearch.com

Wednesday

Wed. Keynotes

Percona Live 2014 opening keynote with Percona CEO Peter Zaitsev
Robert Hodges – Getting Serious about MySQL and Hadoop at Continuent
(Continuent needs to pivot into another market as MySQL’s new built-in features displace their replication products.)
‘Raising the MySQL Bar’ with Oracle’s Tomas Ulin, VP of Engineering for MySQL, Oracle
Adventures in MySQL at Dropbox, Renjish Abraham

Wed. Talks

Online schema changes for maximizing uptime, David Turner, Dropbox, Ben Black, Tango

- MySQL 5.6 has online schema change capability, however there’s no way to throttle IO consumed during the operation and the single-threaded slave will lag
- David has tested the ALTER ONLINE in MySQL 5.6.17 and will use it when ported to Percona Server
- for now uses Percona Online Schema Change utility for its throttling feature.

Be the hero of the day with the InnoDB Data recovery tool, Marco “The Grinch” Tusa and Aleksandr Kuzminsky, Percona Services

- tools have been created by Percona to recover Innodb data if you don’t have backups and you’re out of business otherwise. Call them! :)

Galera Cluster New Features, Seppo Jaakola, Codership

- reviewed features in Galera Cluster versions 3 and 4
- looking good.

MySQL Cluster Performance Tuning, Johan Andersson, severalnines.com

- Disable NUMA
- echo 0 > /proc/sys/vm/swappiness
- bind data node threads to CPUs
- cat /proc/interrupts

ThreadConfig

LDM = cores/2

TC = LDM/4

RealTimeScheduler=1

Numoffraglogparts=LDM

Tune redo log

Fragmentlogsize=256M

Nooffragmentlogfiles=redobuffer=64M

Practical sysbench, Peter Boros, Percona

- prefers “latency” graph style with transparent dots vs. line charts
- uses R and ggplot2 for graphing
- attendees tried to guess SSD performance on Peter’s notebook for different block sizes, most were proven totally wrong by sysbench

Birds of a Feather (BoF) Sessions

“Meet MySQL Team (at Oracle)” BoF

- discussion again this year about parallel query execution (same as at MariaDB BoF last year), with Peter Zaitsev also bringing it up again
- discussion about raw partitions (belief is that they will be 20% more space-efficient and 30% faster, and avoid Linux endless limitations and bugs)
- internal “development roadmap” only extends about 12 months at a time, subject to customer demands
- I griped about FK panic/data loss issues in MySQL Cluster 7.3.3. Tomas Ulin, Vice President, MySQL Engineering, said that was news to him. (See SR 3-8717994851 and SR 3-87646727311)
- Mark Callaghan, Facebook, said he was working on MongoDB now, but requested named keys in flexible schema in MySQL.
- Peter Zaitsev, Percona, said several clients are using GTIDs and they seem to work.
- Oracle pleaded with users to drop MyISAM. I mentioned the main reason was that legacy systems used older compression methods, but InnoDB could be used since it has compression too
- The Oracle MySQL Fabric project is an attempt to counter MongoDB’s automatic slave promotion.

Thursday

Thursday Keynotes

’9 Things You Need to Know…’, Peter Zaitsev, Percona
The Evolution of MySQL in the All-Flash Datacenter, Nisha Talagala, Fusion-IO
MySQL, Private Cloud Infrastructure and OpenStack, Sean Chighizola, Big Fish Games
Keynote Panel: The Future of Operating MySQL at Scale

Thu. Talks

Benchmarking Databases for Scale, Peter Boros and Kenny Gryp, Percona

Question: “What is Percona’s secret to professional benchmarks?”
Answer: “Benchmark absolutely everything multiple times, time permitting.”

MySQL 5.7: Performance & Scalability Benchmarks, Dimitri KRAVTCHUK

- comprehensive micro-benchmarking graphs of 5.7 to gain a deeper understanding of parts
- the challenge remains: how to tune the whole database to perform well?

Use Your MySQL Knowledge to Become an Instant Cassandra Guru, Robert Hodges, Continuent and Tim Callaghan, Tokutek

- good comparison of relational data modelling and C* data modelling, lots of similarities
- note that MariaDB has a Cassandra plugin

RDS for MYSQL, Tips, Patterns and Common Pitfalls, Laine Campbell, Blackbird (formerly PalominoDB)

Write Conflicts in Multi-Master Replication Topologies, Seppo Jaakola, Codership

- it’s good to see that Codership is paying attention to the details of replication

MySQL Community Awards

Shlomi has a comprehensive post on this years winners.

MySQL Lightning Talks (5 minutes each)

Truncating Sub Optimal DBA Verbal Responses Vectors, David Stokes (Oracle)

MySQL 5.6 Global Transaction IDs: Benefits and Limitations, Stephane Combaudon (Percona)

mysqlfailover
mysqlrpladmin

Zero database downtime using the Federated storage engine and Replication, prasad mani (BBC)

Scaling via adding a Table, Rick James (self)

Rick knows some clever ways to optimize solutions with MySQL. He’s doing consulting now, so contact him.

IPs
Lat/Long
Mysql.rjweb.org
Extra Table Saves the Day: Slides

No es ‘ano’, es ‘año’! A take on encoding in your DB, Ignacio Nin (Vivid Cortex)

What Not to Say to the MySQL DBA, Gillian Gunson (Blackbird (formerly PalominoDB))
“I’ll code around it. ”
“Stop micro-optimizing. ”
“Use passive master for QA”
“MySQL is a toy database. ”
This conference is a support group. ”

Hall of Shame, Shlomi Noach
Triple active-replication in gaming anecdote: don’t do that.

The bash slave-prefetch oneliner, Art van Scheppingen (Spil Games)

Unsung Relay Log, Vishnu Rao, FlipKart
Com_relaylog_dump for tungsten and mysql 5.5

Unique User Count — Rollup, Rick James (self)

Formula for user visit estimation by counting bits.

Logical Backups in the Cloud, Bill Karwin, Percona
Backups for PHP designers
PHP class Mysql/Dump

How to Squat, Kyle Redinger (VividCortex, Inc)

Iron DBA Replication Challenge, Attunity
Humor

Friday

Friday Keynotes

Percona CMO Terry Erisman opens the 3rd and final day of Percona Live 201

Keynote: OpenStack Co­Opetition, A View from Within, Boris Renski, Mirantis and OpenStack Boardmember

- one of the best conference keynotes ever, and a great primer on Open Source marketing … up there with the O’Reilly Open Source Conference keynote on the importance of Android – before it shipped.

Friday Talks

Global Transaction ID at Facebook, Evan Elias, Santosh Banda and Yoshinori Matsunobu, Facebook

- just write your own MySQL branch if a feature is too hard to deploy :)

R for MySQL DBAs, Ryan Lowe and Randy Wigginton, Percona

- R has about 1,000 interesting sample databases (demos included diamonds and cars)
- good interface for quick graphing, not so great for complex programs
- Percona usess R and ggplot graph module for most of the graphs you see now.

MariaDB for Developers, Colin Charles, Chief Evangelist, MariaDB

Closing Prize Drawing

About 30 high-end gifts were handed out.

Some nice prizes contributed by exhibitors, including Nexus 7 tablets, $250 AWS gift certificates, SQLyog and Monyog licenses, and a quad drone!

Exhibits

The exhibits are one of my favorite things at the conference each year because of how strong the MySQL third-party community is.

Some notable absences were Clustrix and Violin memory, but those were offset by new exhibitors. Webyog was a sponsor but I didn’t see a booth. PalominoDB changed their name to Blackbird, and appear to be offering DevOps as well as DBA services.

And of course, as the organizers, Percona had a large, central spread. :)

Thanks to the sponsors and exhibitors for making a conference like this financially possible.

Facebook Debuts Web-Scale Variant Of MySQL

Facebook’s Yoshinori Matsunobu on MySQL, WebScaleSQL & Percona Live
Twitter’s Calvin Sun on WebScaleSQL, Percona Live
Slides
Tweets about PerconaLive
Percona Live MySQL Conference Highlights

Cassandra Operations Checklist

Featured

Most of the Cassandra rollouts I’ve heard about at conferences have been “Devopsed” – written by Dev and productionized by Dev, with hand-off to Operations long afterwards.

That’s the opposite to how RDBMS projects are usually deployed in large companies.

As Cassandra becomes more mature, this hand-off will occur earlier after development ends.

Here is a checklist for handing off a Cassandra database to Operations (I only consider non-trivial rings of 3 or more nodes in production with a full data set):

  Node Impact
  Item Comments Performance/ Space/ Time/IOPs/BW
Cassandra Server Version
Cassandra Client/Connector Version
Replication Factor (RF)? Why? usually RF=3 for SoT* data
Read Consistency Level? Why? Netflix recommends CL=ONE. ALL seldom makes sense.
Write Consistency Level? Why? ALL seldom makes sense.
Expected Average Query Latency 10 ms is reasonable, 1 ms is tough.
nodetool repair/scrub needed weekly yes more space more
Bootstrapping a new node yes yes
Java gcpause stop the world yes yes
Are there any wide columns? do they get wider over time? pathological case for Cassandra yes more space more
Backup in case of application bug or a disaster yes slightly more for incremental backups, double for local cold copy more
Restore requires Cassandra node shutdown yes
If a storage volume fills, howto fix it? yes less space less
If a storage volume fails, howto fix it? yes less space less
What is the total data size now? Projected in 12 months? affects most operations yes yes yes
What is the acceptable query latency? affects network and hardware choices
What is the best maintenance window time each week?
What are the business and practical SLAs?
What training is needed for your Operations team? Datastax Admin and Data Modelling Classes (recommend most recent Cassandra version)
What procedures need to be written for your Operations team?
What monitoring tools?
  1. DSE or DCE/OpsCenter
  2. Boundary
  3. nagios/zabbix
What lessons can Devops share with the Operations team?

SoT = Source of Truth

About Data Consistency in Cassandra
ConstantContact techblog: Cassandra and Backups
stackoverflow.com: Do I absolutely need a minimum of 3 nodes/servers for a Cassandra cluster or will 2 suffice?

Howto Add a New Command to the MySQL Server

Featured

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

First, decide if you want to modify the server source code, or if a User-Defined Function (UDF) will meet your needs.

Since I just added the SHUTDOWN server 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.

Easy peasy, right! :)

Sergei Golubchik wrote on the MariaDB developers list: "Reserved words are keywords (listed in the sql/lex.h) that are
not listed in the 'keyword' rule of sql_yacc.yy (and 'keyword_sp' rule, that 'keyword' rule includes)."

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

MySQL Internals Manual
mysqlperformanceblog.com: XtraDB / InnoDB internals in drawing
Overloading Procedures
innodb_diagrams project
Understanding MySQL Internals By Sasha Pachev (O'Reilly)
DTrace can tell you what MySQL is doing
MySQL C Client API programming tutorial
MySQL 5.1 Class Index

  • https://launchpad.net/~maria-developers
  • IRC, #maria channel on Freenode
  • https://kb.askmonty.org/en/community-contributing-to-the-mariadb-project/
  • https://kb.askmonty.org/en/contributing-code/
  • https://kb.askmonty.org/en/google-summer-of-code-2013/ (ideas)
  • http://mariadb.org/jira/ (search for unassigned tasks)

Keywords: MariaDB, MySQL server programming, tutorial, patch.

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:

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

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.

Update: Sergei Golubchik committed this patch to MariaDB 10.0.4 on 2013-06-25. Thanks, Sergei!

shutdown_0.1.patch.txt
MySQL's Missing Shutdown Statement
WL#232
Bug #63276: skip sleep in srv_master_thread when shutdown is in progress

Jeppesen Tech Star Flight Computer Manual

I haven’t seen any Jeppesen Tech Star Flight Computer manuals online, so here’s a feature summary.

Overview

  • uses 4xAAA batteries, thus prone to corrosion
  • comes with plastic film to protect keyboard (leave it on)
  • math calculations are instant, but menu takes 2 seconds
  • arrow keys not used
  • no constant memory
  • no Mach calculations

Menus

Menus are simple … what you see is what you get. That’s all.
right function button – input value from current display
CMP right function button – calculate output

Memories

RCL RCL- view all 6 memory locations
STO 1-6 – save current display value to a memory location
RCL 1-6 – restore memory location to current display value

Missing Features

Use the following formula to calculate TAS from Mach number:

TAS = approximate square root to one decimal place(Celsius + 273.15) * 38.94 * Mach number

Sheppard Air ATP 121 Test Installation on Mac OS X

On August 1, 2014, the FAA Airline Transport Pilot (ATP) certificate requirements become significantly more difficult and expensive to qualify for.

To be “grandfathered” under the old requirements, applicants must pass the ATP Part 135 or Part 121 written test before August 1. (The passing score is 70%, but many airlines require 90%. You can always write the test again, every 30 days.)

After that time, ground school and simulator time will be required, costing $5,300 for ERAU students and up to $20,000+ at FlightSafety, if and when it becomes available to the public.

There are several ways to prepare for the ATP written test, among them:

  1. self-learning using the usual reference manuals (good to know, but not recommended for achieving a high test score)
  2. Gleim test prep book and/or software ($99.95)
  3. ATP Flight School ($295 including class, test prep and test)
  4. Dauntless Software
  5. King Schools ($149 til Oct. 1)
  6. Sheppard Air test prep software ($75, but call before their July 28 deadline).

Both Gleim and Sheppard Air offer a free trial download to test your computer compatibility, and a money-back guarantee (refund below 70% for Gleim and below 90% for Sheppard Air.)

I chose the Sheppard Air ATP Part 121 test prep software.

I didn’t see any README or installation instructions for the Sheppard Air ATP test prep software, so here’s what I had to do to get running on my Macbook Pro with OS X 10.5:

  1. if you installed the Sheppard Air trial version, remove it first: Mac Finder … Applications … “Uninstall Flight Test 5″
  2. call Sheppard Air at (940) 642-4945 (before their July 28 deadline), leave message for ATP test prep signup, wait for return phone call, pay $75 for 121 ATP test, wait for Receipt email, login to their web site, view computer system requirements, click on software download link, double-click on zip file after it’s finished downloading
  3. type these 2 commands:

    cd /Users/james/Downloads/Install FlightTest5 ATP.app/Contents/MacOS
    bash installbuilder.sh

  4. Open Finder, click on Applications … “Study with Flight Test 5″ and type in the username and password from your Receipt email into the Sheppard Air software login dialog.

Some suggestions to Sheppard Air would be to:

  1. automate purchase via the web
  2. provide installation instructions online and in a README.txt file in the zip file. Requiring phone in for purchasing and then expecting people to phone in again for mandatory installation help is an archaic and expensive way to sell low-dollar software products today.

If you use a Jeppesen Tech Star electronic flight computer or basic calculator without mach calculations, use the following formula:

TAS = approximate square root to one decimal place(Celsius + 273.15) * 38.94 * Mach number

avweb.com: ATP Written Prerequisites About to Change Drastically
dauntless-soft.com: Part 121 or Part 135 ATP Written Test?
Sheppard Air References

MySQL Blackhole Storage Engine for Fun and Profit

MySQL LogoRecently I did some work with MySQL and MySQL Cluster, where the dev and test environments ran on CloudStack VMs, and a reporting server ran on AWS EC2.

The MySQL Blackhole storage engine could have helped in two cases:

  1. disk IO is a limiting factor in virtualized environments, so writing less data when persistence is not needed increases performance for all VMs
  2. AWS charges for Internet bandwidth, so using a proxy server running Blackhole or replication filters can reduce the amount of data replicated to AWS.

I was fortunate that I attended the “Blackhole and Federated Storage Engines” talk by Giuseppe Maxia et al. at the MySQL Conference 2007. Jay Pipes, the conference organizer, recommended it as the most devious and twisted talk he had ever previewed, and he was right. :)

paulferrett.com: Why is the MySQL Blackhole storage engine useful? [2009]
jroller.com: MySQL replication using blackhole engine [2006]
danielschneller.com: “Streaming” MySQL slave deployment [2007]