You are here: Home / Archives for ronald

For MySQL DBA fame and glory. Prize included.

by

I came across the following configuration today on a Production MySQL system (5.0.67) running 30+ blogs using WordPress MU.

$ cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database

No I did not truncate the output. I could say I’ve seen worse, but that’s a stretch.


So the quiz and a prize for the best response, for the next 48 hours I’ll accept your comments as responses to this post for the top 5 settings you would add, and additionally what information you may need to add these settings. Bonus points for giving a reason why you would add the settings as well.

For example, I’ll give you the most obvious.

key_buffer_size = ????

To determine a key_buffer_size to start with I would look at the size of all Indexes via I_S, and combine with some estimate of growth, say 2x-5x.

For the best answer with the top 5 settings, I’ll send you a copy of Sheeri Cabral’s book MySQL Administrators Bible which even I as a resident MySQL Expert has enjoyed scanning and learning something new.

Basic OS/MySQL Security

by

If you can do either of these on your MySQL production server, you need to correct immediately.

1. Login directly to your MySQL server as the ‘root’ Linux Operating System user. For example:

$ ssh root@server-name
Password:  ************

2. Connect to MySQL database as the ‘root’ MySQL user without a password.

$ mysql -uroot

Here are the 60 second fixes to address these major security flaws.
To disable direct root access to your server, first ensure you can login as a normal user, then su – or sudo su – appropriately. Then, disable ssh root access with the following configuration change.

$   vi /etc/ssh/sshd_config
    # ensure this is commented out and set to no
    PermitRootLogin no

$   /etc/init.d/sshd restart

This will stop any brute force attack on your server by automated bots and password generators.

Second, the default installation of MySQL *DOES NOT SET A PASSWORD*. Apart from being crazy, I’ve seen production systems without a MySQL ‘root’ user password. To set a password run:

$ mysqladmin -uroot password SOMEPASSWORD
$ mysqladmin -uroot -pSOMEPASSWORD -hlocalhost password SOMEPASSWORD

Transcending Technology Specific Boundaries

by

I had the pleasure to sit on the Performance Panel at the recent Percona Performance Conference. While the panel contained a number of usual MySQL suspects, one person was not familiar, that being Cary Millsap from Method R.

An expert in optimizing Oracle performance, Cary also gave an session on Day 2 that I attended. While he opened professing not to be an expert in MySQL, his talk provided valuable foundation knowledge irrespective of whether you use MySQL or another database product.

Having come myself from 7 straight years in system architecture and performance tuning in Ingres, then a further 6 years in Oracle again heavily involved in system architecture and performance tuning, a lot of my experience in the 10 years of providing my own MySQL consulting is drawn from my past RDBMS experiences. In addition much of what I actually provide to clients today is common sense that I don’t see applied.

A summary of the excellent content provided by Cary.

The common technology agnostic problem we need to address is:

  • Users say that everything is slow, but I don’t know where to begin
  • Users are complaining but all the monitoring dials are green

From a user’s perspective, their experience consist simply of two elements.

  1. Task
  2. Time

In general, business people simply don’t care about the “system” except thought the specific tasks that make up their pressing business needs. And for these users, performance is all about the time to complete this task.

Throughput can be stated as tasks per time.
Response time is the time taken per task.

Cary also quoted Donald Knuth — “The universal experience of programmers who have been using measurements tools has been that there intuitive guesses fail.”

Performance is easy if you stop guessing where your code is slow. A few best practice tips are:

  • You have to insist on seeing where time goes for any task you think is important
  • You need to look at the sequence diagram of the task
  • What individual part takes the most time, then look at the task before that. The fastest way to do something is don’t.
  • To drill down, you need to attack the skew of each part, not the average.

In Summary the closing points were:

  • Performance is about time and tasks
  • Not all tasks are created equal
  • Read “The Goal”
  • Don’t guess, your probably wrong
  • Measure response time before you optimize anything – Insist on it

Performance is easy when code measures it’s own time and tasks. This closing statement on instrumentation I completely concur with.

SHOW WARNINGS woes

by

Recently on a client site I had to fight the pain of having no way to confirm loss of data integrity when optimizing data types. Due to MySQL’s ability to perform silent conversion of data, when converting a number of columns we enabled sql_mode to catch any truncations as errors.

sql_mode=STRICT_ALL_TABLES

This ensured that should any data truncations occur, an error is thrown not a warning. The following shows an example case study for converting an INT to TINYINT UNSIGNED and shows that without sql_mode silent conversions occur.

mysql> drop schema if exists tmp;
Query OK, 25 rows affected (0.40 sec)

mysql> create schema tmp;
Query OK, 1 row affected (0.01 sec)

mysql> use tmp
Database changed
mysql> create table t1(i1 INT NULL);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(1),(2),(3),(256),(65536),(NULL);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+-------+
| i1    |
+-------+
|     1 |
|     2 |
|     3 |
|   256 |
| 65536 |
|  NULL |
+-------+
6 rows in set (0.03 sec)

mysql> set session sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL;
ERROR 1264 (22003): Out of range value for column 'i1' at row 4
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL;
Query OK, 6 rows affected, 2 warnings (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 4 |
| Warning | 1264 | Out of range value for column 'i1' at row 5 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| i1   |
+------+
|    1 |
|    2 |
|    3 |
|  255 |
|  255 |
| NULL |
+------+
6 rows in set (0.00 sec)

The process however for the client included converting a number of DATETIME columns to DATE columns. The input data specification called for the input values to be dates only so storing and using DATETIME was simply a waste of space.

Unfortunately the use of sql_mode didn’t help here, so the combination of changing multiple columns in one time simply meant it was impossible to determine if any truncations occurred.

This combined with the sheer data volume, tables between 10GB – 500GB, with 10 – 100 million rows.
When you see 14+ millions rows modified, 35+million rows and of course SHOW WARNINGS by default only shows a very small number of actual warnings

It is of course impossible to run individual ALTER statements on columns. Of the top 10 tables, excluding the largest, alter times were between 3 and 15 hrs.

The only solution is to run pre SQL checks on the data to search for the boundary conditions manually. This is not the ideal solution for a customer.

The MySQL crystal ball says …

by

As the recipient of the 2009 MySQL Community Member of the Year award I received a MySQL crystal ball. While it looks good in my bookcase, unfortunately the best advice I can offer during this time of uncertainty is “watch this space”.

A number of topics where information is still very much unknown and I’m either asked about, or am following includes:

  • The Oracle acquisition of Sun, owner of MySQL.
  • MySQL 5.4 Alpha release and schedule for production release
  • The end of MySQL 5.0 Community/Enterprise split
  • The future of Falcon in MySQL 6.0?

Thanks to Julian Cash of the Human Creativity Project of his photograph at the 2009 MySQL Conference.

Ronald Bradford - MySQL Community Member of the Year

View Photo. View all 2009 MySQL Conference photos.

MySQL for the Oracle DBA Resources

by

The announcement last month of Oracle to acquire Sun continues to warrant a lot of discussion over exactly what Oracle will do with MySQL. Only time will tell what will happen with the official product, however it is important to remember that MySQL is GPL, there will always be a free version of MySQL available for popular LAMP stack products such as WordPress and Drupal and new and existing startup’s will continue to use MySQL.

This announcement will see Oracle resources begin to better understand and evaluate MySQL. As a resident MySQL Expert, I also have a strong background in Oracle having also worked for Oracle Corporation. I have also delivered several successful one day and half day workshops on MySQL/Oracle related content including:

  • 2009 – Best Practices for Migrating to MySQL from Oracle and SQL Server environments. 4 hour workshop for the Federal Government – Washington DC
  • 2008 – MySQL for the Oracle DBA Bootcamp. 1 day workshop for the Federal Government – Washington DC
  • 2007 – MySQL for the Oracle DBA Bootcamp. 1 day workshop New York, NY & San Francisco, CA
  • 2007 – MySQL for Oracle DBA’s and Developers. 90 minute presentation at MySQL Conference and Expo, Santa Clara, CA
  • 2006 – MySQL for Oracle Developers. 45 minute presentation at MySQL Users Conference, Santa Clara, CA
  • 2006 – Know your Competitor. A MySQL developers guide to Oracle 10g express edition. 60 minutes presentation at MySQL Users Group, Brisbane, Australia

In addition I have proposed 2 MySQL related talks at Oracle Open World 2009

  • Integrating MySQL into your Oracle DBA management processes
  • An overview for evaluating migrating from Oracle to MySQL

For more information check out my MySQL for the Oracle DBA Resources.

HiTCHO Top tech tips

by

I recent visit with old Brisbane friend HiTCHO which I met at the Brisbane MySQL Users Group in 2005, has lead to this cool list of some hardware and software technologies he used that I am now considering or have already implemented or purchased.

Software

  1. xmarks.com – Bookmark-Powered Web Discovery
  2. Pulse – Smart Pen
  3. Quicksilver Mac windows manager
  4. MailPlane – Brings Gmail to your Mac desktop
  5. Evernote – Remember Everything, with Firefox plugin and iPhone App
  7. Textmate – The missing editor for Mac OS/X
  8. Screen flow Professional screencasting Studio
  9. Snoop – A GNU/Linux file descriptor monitoring tool inspired by FreeBSD’s ‘watch’.

Hardware

  1. Drobo – Storage that manages itself
  2. Canon PowerShot SX1. True HD in a Canon compact digital camera.
  3. LiveScribe – Never miss a word

Drizzle now available on Mosso

by

Mosso the Rackspace Cloud now has a Drizzle developer image much like the first Drizzle AMI on EC2.

The Mosso interface is definitely different, it’s a GUI, and I definitely prefer CLI, but it’s a simpler navigation for a new user. I suspect an API may be available.

I had an issue with the backup process, more the lack of feedback. The Knowledge Base didn’t help, so both calling and Live Chat directed me ultimately to the same person. I also found a bug in the backup process, that is being able to select an incomplete backup to try and launch a new server. I talked to Support about and apparently already known.

And in true open source form, the Drizzle version is actually one point higher then yesterday’s AWS image.

I don’t know how to *publish* this backup so others can try it. Something on the list of things to do, however I was able to verify my backup with a new instance.

$ drizzle
Welcome to the Drizzle client..  Commands end with ; or g.
Your Drizzle connection id is 2
Server version: 2009.04.998 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

drizzle>
drizzle> select version();
+-------------+
| version()   |
+-------------+
| 2009.04.998 |
+-------------+
1 row in set (0 sec)

drizzle> select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.18 sec)

One advantage of Oracle/Sun/MySQL

by

This weeks’ announcement Oracle to by Sun was a major talking point at the 2009 MySQL Conference & Expo. While it is too early to even speculate what the future holds with the official MySQL product, for myself a speaker on MySQL topics, Oracle Open World is now a target market.

In addition to many years of providing MySQL for the Oracle DBA Resources I have with the recent closure of call for papers submitted two sessions for consideration.

Integrating MySQL into your Oracle DBA management processes

Most large enterprise organizations use more then one RDBMS product to service business requirements. With the increase in MySQL usage for web based applications such as self-service content, Oracle DBA’s need to understand and appreciate the minimum to ensure performance and availability meets client expectations.

Just how to you integrate MySQL into existing and existing Oracle database infrastructure and management monitoring process?
What are the critical monitoring components? How do these compare to current Oracle Best Practices.
Understand the various end user tools support multiple RDBMS products including Oracle and MySQL.

In this session, DBA’s will leave with the essential knowledge and appreciation of MySQL management.

An overview for evaluating migrating from Oracle to MySQL

MySQL is becoming increasing popular RDBMS for web based applications due to it’s ease of use, availability within the the LAMP stack and large number of open source applications. While implementing MySQL for a new development project may be easy, migrating existing databases, data and applications to use MySQL is not. In this presentation, we will answer questions including:

What are the major challenges to overcome to consider MySQL for some portion of your business?
What are the issues in application portability?
What are ideal applications to consider for migration?
Tools, Products and options for easy migration?
What Oracle features are not supported?

Learn how to read and write to MySQL directly via Oracle Heterogeneous services.

Announcing Drizzle on EC2

by

I have published the very first sharable Drizzle Amazon Machine Image (AMI) for AWS EC2, based on the good feedback from my discussion at the Drizzle Developer Day on what options we should try.

This first version is a 32bit Developer instance, showcasing Drizzle and all necessary developer tools to build Drizzle from source.

What you will find on drizzle-ami/intrepid-dev32 – ami-b858bfd1

Ubuntu 8.10 Intrepid 32 bit base server installation:

  • build tools
  • drizzle dependencies
  • bzr 1.31.1

From the respective source trees the following software is available:

  • drizzle 2009.04.997
  • libdrizzle 0.0.2
  • gearman 0.0.4
  • memcached 1.2.8
  • libmemcached 0.28

Drizzle has been configured with necessary dependencies for PAM authentication, http_auth, libgearman and MD5 but these don’t seem to be available in the binary distribution.

I will be creating additional AMI’s including 64bit and LAMP ready binary only images.

The following example shows using drizzle on this AMI. Some further work is necessary for full automation, parameters and logging. I’ve raised a number of issues the Drizzle Developers are now hard at work on.

1. Starting Drizzle

ssh [email protected]
sudo /etc/init.d/drizzle-server.init start &

2. Testing Drizzle (the sakila database has been installed)

$ drizzle
Welcome to the Drizzle client..  Commands end with ; or g.
Your Drizzle connection id is 4
Server version: 2009.04.997 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

drizzle> select version();
+-------------+
| version()   |
+-------------+
| 2009.04.997 |
+-------------+
1 row in set (0 sec)

drizzle> select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0 sec)

3. Compiling Drizzle

sudo su - drizzle
ls
deploy  drizzle  libdrizzle  sakila-drizzle
cd drizzle
./configure --help
Description of plugins:

   === HTTP Authentication Plugin ===
  Plugin Name:      auth_http
  Description:      HTTP based authentications
  Supports build:   static and dynamic

   === PAM Authenication Plugin ===
  Plugin Name:      auth_pam
  Description:      PAM based authenication.
  Supports build:   dynamic

   === compression UDFs ===
  Plugin Name:      compression
  Description:      UDF Plugin for compression
  Supports build:   static and dynamic
  Status:           mandatory

   === crc32 UDF ===
  Plugin Name:      crc32
  Description:      UDF Plugin for crc32
  Supports build:   static and dynamic
  Status:           mandatory

   === Error Message Plugin ===
  Plugin Name:      errmsg_stderr
  Description:      Errmsg Plugin that sends messages to stderr.
  Supports build:   dynamic

   === Daemon Example Plugin ===
  Plugin Name:      hello_world
  Description:      UDF Plugin for Hello World.
  Supports build:   dynamic

   === Gearman Logging Plugin ===
  Plugin Name:      logging_gearman
  Description:      Logging Plugin that logs to Gearman.
  Supports build:   dynamic

   === Query Logging Plugin ===
  Plugin Name:      logging_query
  Description:      Logging Plugin that logs all queries.
  Supports build:   static and dynamic
  Status:           mandatory

   === Syslog Logging Plugin ===
  Plugin Name:      logging_syslog
  Description:      Logging Plugin that writes to syslog.
  Supports build:   static and dynamic
  Status:           mandatory

   === MD5 UDF ===
  Plugin Name:      md5
  Description:      UDF Plugin for MD5
  Supports build:   static and dynamic

   === One Thread Per Connection Scheduler ===
  Plugin Name:      multi_thread
  Description:      plugin for multi_thread
  Supports build:   static
  Status:           mandatory

   === Old libdrizzle Protocol ===
  Plugin Name:      oldlibdrizzle
  Description:      plugin for oldlibdrizzle
  Supports build:   static
  Status:           mandatory

   === Pool of Threads Scheduler ===
  Plugin Name:      pool_of_threads
  Description:      plugin for pool_of_threads
  Supports build:   static
  Status:           mandatory

   === Default Signal Handler ===
  Plugin Name:      signal_handler
  Description:      plugin for signal_handler
  Supports build:   static
  Status:           mandatory

   === Single Thread Scheduler ===
  Plugin Name:      single_thread
  Description:      plugin for single_thread
  Supports build:   static
  Status:           mandatory

   === Archive Storage Engine ===
  Plugin Name:      archive
  Description:      Archive Storage Engine
  Supports build:   static
  Status:           mandatory

   === Blackhole Storage Engine ===
  Plugin Name:      blackhole
  Description:      Basic Write-only Read-never tables
  Supports build:   static and dynamic
  Configurations:   max, max-no-ndb

   === CSV Storage Engine ===
  Plugin Name:      csv
  Description:      Stores tables in text CSV format
  Supports build:   static
  Status:           mandatory

   === Memory Storage Engine ===
  Plugin Name:      heap
  Description:      Volatile memory based tables
  Supports build:   static
  Status:           mandatory

   === InnoDB Storage Engine ===
  Plugin Name:      innobase
  Description:      Transactional Tables using InnoDB
  Supports build:   static and dynamic
  Configurations:   max, max-no-ndb
  Status:           mandatory

   === MyISAM Storage Engine ===
  Plugin Name:      myisam
  Description:      Traditional non-transactional MySQL tables
  Supports build:   static
  Status:           mandatory


Report bugs to <http://bugs.launchpad.net/drizzle>.

Compiling libdrizzle

by

Compiling libdrizzle is a rather trivial task. The following are the steps I undertook on Ubuntu 8.10 Intrepid 32 bit.

There was one pre-requisite from the most basic installed developer tools.

sudo apt-get install -y  automake

bzr clone lp:libdrizzle
cd libdrizzle
./config/autorun.sh
./configure
make
make install

And there they are:

$ ls -l /usr/local/lib/libdrizzle*
-rw-r--r-- 1 root root 1122710 2009-04-26 18:10 /usr/local/lib/libdrizzle.a
-rwxr-xr-x 1 root root     940 2009-04-26 18:10 /usr/local/lib/libdrizzle.la
lrwxrwxrwx 1 root root      19 2009-04-26 18:10 /usr/local/lib/libdrizzle.so -> libdrizzle.so.0.0.2
lrwxrwxrwx 1 root root      19 2009-04-26 18:10 /usr/local/lib/libdrizzle.so.0 -> libdrizzle.so.0.0.2
-rwxr-xr-x 1 root root 1003734 2009-04-26 18:10 /usr/local/lib/libdrizzle.so.0.0.2

I added the following to enable other programs using libdrizzle to find the libraries in the system path.

echo "/usr/local/lib" > /etc/ld.so.conf.d/drizzle.conf
ldconfig

Drizzle/bzr dependency

by

A number of developers had problems on Friday at the Drizzle Developer Day with compiling bzr. The distro in question I was helping with was CentOS 5 32-bit. I had no issues on CentOS 5 64bit.

Today while creating the first deployed Drizzle AWS AMI I discovered the same problem using Ubuntu 8.10 Intrepid 32 bit.

The solution was actually rather trivial. Installing the python-dev package solved the problem.

apt-get install python-dev

Bzr 1.13.1 Compiling error

building 'bzrlib._btree_serializer_c' extension
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -I/usr/include/python2.5 -c bzrlib/_btree_serializer_c.c -o build/temp.linux-i686-2.5/bzrlib/_btree_serializer_c.o
bzrlib/_btree_serializer_c.c:4:20: error: Python.h: No such file or directory
bzrlib/_btree_serializer_c.c:5:26: error: structmember.h: No such file or directory
bzrlib/_btree_serializer_c.c:35: error: expected specifier-qualifier-list before ‘PyObject’
....
bzrlib/_btree_serializer_c.c:1651: error: request for member ‘f_lineno’ in something not a structure or union
bzrlib/_btree_serializer_c.c:1651: warning: statement with no effect
bzrlib/_btree_serializer_c.c:1652: warning: implicit declaration of function ‘PyTraceBack_Here’

  Cannot build extension "bzrlib._btree_serializer_c".
  Use "build_ext --allow-python-fallback" to use slower python implementations instead.

error: command 'gcc' failed with exit status 1

Adding a Drizzle Plugin

by

I joined about 50 others including a number of core MySQL developers and MySQL community members today for the 2009 Drizzle developers day at Sun Microsystems Santa Clara campus.

In addition to a number of presentations and various group discussions most of my individual hacking time was under the guidance of Drizzle team developer Stewart Smith were Patrick Galbraith and myself started the porting of Patrick’s memcached UDF functions for MySQL.

Leveraging some existing Drizzle plugin’s such as CRC32() and UNCOMPRESS() we were easily able to navigate the src/plugin/memcached directory plug.in, Makefile.am and drizzle_declare_plugin definition in the new get.cc to get a working stub ‘Hello World Example';

plug.in

$ more plug.in
DRIZZLE_PLUGIN(memcached,[memcached UDF],
        [UDF Plugin for memcached])
DRIZZLE_PLUGIN_STATIC(memcached,   [libmemcachedudf.a])
DRIZZLE_PLUGIN_MANDATORY(memcached)  dnl Default
DRIZZLE_PLUGIN_DYNAMIC(memcached,   [libmemcachedudf.la])


Makefile.am

$ more Makefile.am
# Copyright (C) 2006 MySQL AB
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

EXTRA_LTLIBRARIES =	libmemcachedudf.la
pkgplugin_LTLIBRARIES =	@plugin_memcached_shared_target@
libmemcachedudf_la_LDFLAGS =	-module -avoid-version -rpath $(pkgplugindir)
libmemcachedudf_la_LIBADD =		$(LIBZ)
libmemcachedudf_la_CPPFLAGS=	$(AM_CPPFLAGS) -DDRIZZLE_DYNAMIC_PLUGIN
libmemcachedudf_la_SOURCES =	get.cc


EXTRA_LIBRARIES =	libmemcachedudf.a
noinst_LIBRARIES =	@plugin_memcached_static_target@
libmemcachedudf_a_SOURCES=	$(libmemcachedudf_la_SOURCES)

$ more get.cc
/* Copyright (C) 2009 Patrick Galbraith, Ronald Bradford
...
*/
#include <drizzled/server_includes.h>
#include <drizzled/sql_udf.h>
#include <drizzled/item/func.h>
#include <drizzled/function/str/strfunc.h>

#include <stdio.h>
#include <libmemcached/memcached.h>

using namespace std;

/* memc_get */
class Item_funcmemc_get : public Item_str_func
{
public:
  Item_funcmemc_get() : Item_str_func() {}
  const char *func_name() const { return "memc_get"; }
  bool check_argument_count(int n) { return (n==1); }
  String *val_str(String*);
  void fix_length_and_dec() {
    max_length=32;
    args[0]->collation.set(
      get_charset_by_csname(args[0]->collation.collation->csname,
                            MY_CS_BINSORT), DERIVATION_COERCIBLE);
  }

};


String *Item_funcmemc_get::val_str(String *str)
{
  assert(fixed == 1);
  String * sptr= args[0]->val_str(str);
  str->set_charset(&my_charset_bin);
  if (sptr)
  {
    null_value=0;
    str->set("hello memcached test", 20,system_charset_info);
    return str;
  }
  null_value=1;
  return 0;
}


Create_function memc_get_factory(string("memc_get"));

static int memcached_plugin_init(PluginRegistry &registry)
{
  registry.add(&memc_get_factory);
  return 0;
}

drizzle_declare_plugin(memcached)
{
  "memcached",
  "0.1",
  "Patrick Galbraith, Ronald Bradford",
  "memcached plugin",
  PLUGIN_LICENSE_GPL,
  memcached_plugin_init, /* Plugin Init */
  NULL,   /* Plugin Deinit */
  NULL,   /* status variables */
  NULL,   /* system variables */
  NULL    /* config options */
}
drizzle_declare_plugin_end;

Percona Performance Conference Talk

by

My final presentation during the 2009 MySQL Conference and Expo week was with the Percona Performance Conference on the topic of The Ideal Performance Architecture. My talk included discussions on Technology, Disk, Memory, Indexes, SQL and Data.

The Ideal Performance Architecture

Updated 09/18/09
you can now see video of the event at Percona TV.

MySQL Monitoring 101

by

At the 2009 MySQL Conference and Expo I presented to a full room on MySQL Monitoring 101.

This presentation focused on the following four goals.

  • Know what to monitor
  • Know how you can monitor
  • Learn practices to diagnose problems
  • Have a foundation of historical information
MySQL Monitoring 101

Updated 09/18/09
You can also find additional materials at:

A change in the MySQL Binary distributions

by

Yesterday was the surprise announcement of MySQL 5.4 at the 2009 MySQL Conference and Expo. It was unfortunate that the supporting information was not that forthcoming on the MySQL website. I tried for several hours to try and download, but no mirrors were initially available. Today I see some information on the mysql.com home page and finally able to get the binary.

What I found most significant with this new major version release is a change in the binary distribution, as seen on the Download page.

MySQL 5.4 is only available on 3 platforms:

  • Linux (AMD64 / Intel EM64T)
  • Solaris 10 (SPARC, 64-bit)
  • Solaris 10 (AMD64 / Intel EM64T, 64-bit)

I was also surprised that this beta release highlights the emphasis of community contributions (long overdue), yet the community and indeed many employees of Sun/MySQL were simply unaware of this work. This is clearly a change in involving the community. While I applaud the beta status, hopefully a more stable product to start with, it’s development was done in a very closed company model.

Setting up MySQL on Amazon Web Services (AWS) Presentation

by

On Tuesday at the MySQL Camp 2009 in Santa Clara I presented Setting up MySQL on Amazon Web Services (AWS).

This presentation assumed you know nothing about AWS, and have no account. With Internet access via a Browser and a valid Credit Card, you can have your own running Web Server on the Internet in under 10 minutes, just point and click.

We also step into some more detail online click and point and supplied command line tools to demonstrate some more advanced usage.

Getting started with MySQL in Amazon Web Services

What's happening with InnoDB

by

I have moved on to InnoDB: Innovative Technologies for Performance and Data Protection by Ken Jacobs at MySQL Conference and Expo.

With a brief history lesson of inception from 1994, inclusion in MySQL in 2000 and acquired by Oracle in 2005. Most of the work was done by one person. InnoDB is based on sound database computer science using Gray & Reuters definitive text on database design.

Some key points in Ken’s discussion.

  • Adaptive Hash indexing for frequent queries on keys.
  • In plugin Adaptive Hash is configurable
  • Insert Buffering – Deferring secondary index writes
  • Fast Index Create – doesn’t requires all indexes to be rebuilt
  • Table Compression – Changing the page size

The InnoDB plugin available in 5.1 has a number of new benefits.

  • fast index creation
  • table compression
  • info schema tables
  • new row storage format
  • file format management

All InnoDB 1.0.3 plugin features will be available in MySQL 5.4

The big announcement is a new product – Embedded InnoDB. This has the high performance, reliability and rich functionality of InnoDB, has a flexible programmatic API. No SQL, No security.

Search at Craigslist

by

I am now sitting in on MySQL and Search at Craigslist by Jeremy Zawodny at MySQL Users Conference

Some of the technical difficulties that required addressing.

  • High churn rate
  • half life can be very short
  • Growth
  • Traffic
  • Need to archive postings, e.g. 100M but be searchable
  • Internationalization and UTF-8

Some of the Craigslist Goals

  • Open Source
  • Easy and approachable
  • be green with energy use

A review of the Internals server configuration

  • Load Balancer (perlbal like)
  • Read Proxy Array (perl+memcached)
  • Web Read Array (apache 1.3 + mod_perl)
  • Object Cache (Perl + memcached)
  • Read DB Cluster (MySQL 5.0.x)
  • Search Cluster (Sphinx)

Clusters of DB servers have good vertical partitioning by Roles. These being

  • Users
  • Classified
  • Forums
  • Stats
  • Archive

Sphinx is a full standalone full text search that is used. Did compare with Apache Solr, but it seemed more complex and complicated. The Sphinx configuration:

  • Partitioned based on cities (people search locally)
  • Attributes v Keywords
  • Persistent Connections
  • Minimal stopword list
  • Partition in 2 clusters (1 master, 4 slaves)

The results of implementing Sphinx were:

  • decrease in 25 MySQL boxes to 10 sphinx boxes
  • no locking
  • 1,000+ qps
  • 50M queries per day
  • Better separation of code

MySQL Users Conference Opening Lines

by

Opening introduction from Colin Charles got us started. Karen Tegan Padir VP MySQL & Software Infrastructure was the opening keynote.

She comes from a strong tech background and is passionate about open source, the communities and how to make a successful product.

There isn’t a person that doesn’t go a day without interacting with a website or hardware system that uses a MySQL database.

The big news was the announcement of MySQL 5.4 – Performance & Scalability. Key features include.

  • InnoDb scalability 16way x86 and 64 way CMT servers
  • subquery optimization
  • new query algorithms
  • improved stored procedures, and prepared statements
  • enhanced Information Schema
  • improved DTrace Support

More information at MySQL 5.4 Announcement Details….

Other key points includes:

1. Ken Jacobs announces today an Embedded Innodb with a powerful API (not SQL based). Read more at Innobase Introduces Embedded InnoDB
2. MySQLCluster 7.0 is also released today. Some benchmarks 4.3x improvements. New features also include LDAP support.
3. The next release of MySQL Query Analyzer, 2.1 announced.
4. Sun announces a commitment to accept contributions from the community.
5. Community also gets the Monthly Rapid Updates.
6. MySQL Drizzle Project is discussed as a technology incubator.

Partners of the year: Intel, Infobright and Lifeboard.
Appliation of the year: Zappos, Alcatel-lucent and Symantec.
Community members of the year: Marc Delisle, Ronald Bradford, Shlomi Noach.

Where is the MySQL in Sun's announcement

by

I find it surprising that in the official Sun Announcement there is no mention of MySQL for two reasons. Firstly, this was Sun largest single purchase of $1 billion only 12 months ago. Second, MySQL’s largest competitor is Oracle.

While the Sun website shows the news in grandeur, the MySQL website is noticeably absent in any information of it’s owners’ acquisition.

On my professional side, as an independent speaker for Sun Microsystems with plans for upcoming webinars and future speaking on “Best Practices in Migrating to MySQL from Oracle”, this news does not benefit my bottom line.

Drizzle + PHP = Sweet

by

I’ve just successfully configured Drizzle with the PHP Extension and successfully retrieve data to present on a web page.

Qudos to Eric Day for his work. I was able to identify a problem with the current tar release, and a quick confirmation on #drizzle at IRC confirmed a fix had already been commited.

I’m looking forward to evaluating WordPress and Drupal, two popular and common LAMP stack applications that run on MySQL, and to provide any feedback to the community for future support of Drizzle.

What questions do you ask?

by

When you have to evaluate a MySQL System & Environment, what questions do you ask in order to determine critical information about the environment and evaluate the business success and viability. You don’t have to be a consultant to ask these questions, ask them of your own environment. Do any of the answers shock or concern you?

I was prompted to write about this from a conversation with a colleague about “accepting risk”. His comment was, “every IT server on the planet is vulnerable regardless of best practices.”

Here is my list of questions for you based on an immediate response from this discussion?

Technology

  • What is your full technology stack, i.e. Operating System, Database, Application Server, Development Language(s) and other essential components?
  • What are the versions of these technologies?
  • What new technologies or versions of existing technologies are you presently evaluating?

Disaster Recovery

  • What is your Backup and Recovery strategy including your Database, Application and Administration?
  • Have you tested your Backup and Recovery strategy?
  • Have you really tested your Backup and Recovery strategy from end to end? How long ago? How long did it take?
  • What RAID do you run? Have you physically verified that? When did you confirm you are not running in a degraded RAID situation?
  • What does your website look like when it’s unavailable? What is the physical content on the website. Let’s pretend your entire data center is unavailable for 40 hours.
  • Have you ever had a major disaster? What did you learn from this experience?

Development Processes

  • Do you use a version control system? Which one? Is everything under version control?
  • Do you have a controlled and reproducible build and release process? Is it automated in any way?
  • What are your levels of testing. Unit test for coding? Regression testing for new features? Volume Testing? End User Testing?
  • Do you have a proper test environment (which is not production) where you can accurately evaluate production software and production problems?

Infrastructure

  • How do you know when there is a problem with your site? Do you have monitoring and alert notification in place?
  • When you have a performance problem, can you evaluate if it is new, re-occuring or a gradually worsening problem?
  • What are you two biggest performance problems right now? What are the specific details of the problem? “My website is slow” is not an answer
  • Can you roll out new features without taking your website down for general use?

Business Viability

  • How long would a customer stay with your site if it was unavailable?
  • Can your clients be satisfied with the Twitter “failed whale” approach or will they leave?
  • When will your system crash under load? Do you know this figure? What is the load today and the projections to this failure point?

Given more time I’d probably revise the list, but this was just an initial response.

This post is part of 31 Days to Build a Better Blog – Write a List Post.

mysql.com search is so broken

by

Today, while on the MySQL manual page, I typed in ‘select’ in the search manual box to confirm the SELECT syntax.

The result was not what I expected, the “SELECT” command. Instead I only got two options “Speed of SELECT …” and “Optimizing SELECT and …”.

Ok, well that’s not what I want, there is a suggestion box to the right so I pick the top option “mysql select”. Not only is this worse with “Type Conversion in …”, “Searching on Two Keys” I also get 3 totally useless “Keymatch” records

Download MySQL -http://dev.mysql.com/downloads/
MySQL Training – http://www.mysql.com/training/ KeyMatch
Buy MySQL Enterprise -http://shop.mysql.com/enterprise/

I know in the past just entering ‘SELECT’ worked, because I’ve been presently writing tests on JOIN syntax and I wanted to link in my blog reference.

Images of my searches.


Developing Code Coverage for MySQL tests

by

I have always been a strong advocate of good testing of any system. I started on a project last year with Drizzle to produce coverage tests to facilitate verifying syntax and helping in comparison with MySQL.

From my extensive experience in code generation from the past 20 years, I produced about 3 years ago when at MySQL Professional Services a Java based solution with a small meta language to automated the creation of a large number of tests. At the time is was some 475 tests and 200k lines of mysql-test syntax when I first looked at validating the Nitro Storage Engine.

A number of issues with mysql-tests including the support of multiple storage engines had me last year write a Proposed Testing Protocol for Drizzle for further discussion. While the desire for improvements still exists, we can not deny the benefit of leveraging the large amount of scope the current MySQL Test Suite, and so I have started in the creation of tests in this format.

My first output is to validate all possible variants of creating various data types in MySQL. This includes the obvious datatype, but also NULL, NOT NULL syntax, sizing with (n) and (m,n), UNSIGNED and ZEROFILL. What my tests also cover is not that the syntax is valid, but also the cases where the syntax is invalid (e.g. you can use UNSIGNED for Character fields). This POC is a small example of what can be produced.

The below first test enabled me to easily confirm valid and invalid syntax within Drizzle. This for example helped in clarifying Drizzle Data Types. I hope to be able to use the same framework to confirm MariaDB syntax and MySQL compatibility.

ct_syntax_1col.test

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (c1 tinyint NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 date NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 time NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 datetime NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinytext NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumtext NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longtext NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinyblob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumblob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longblob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 varchar NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary NULL);

CREATE TABLE t1 (c1 tinyint NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 date NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 time NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 datetime NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinytext NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumtext NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longtext NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinyblob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumblob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longblob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 varchar NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary NOT NULL);

CREATE TABLE t1 (c1 tinyint(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varbinary(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 double(1) NULL);
--error 1064
CREATE TABLE t1 (c1 date(1) NULL);
--error 1064
CREATE TABLE t1 (c1 time(1) NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(1) NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(1) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(1) NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(1) NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(1) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(1) NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(1) NULL);

CREATE TABLE t1 (c1 tinyint(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varbinary(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 double(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(1) NOT NULL);

CREATE TABLE t1 (c1 decimal(5,2) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(5,2) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double(5,2) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 tinyint(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 smallint(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 int(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 bigint(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 bit(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 date(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 time(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 year(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 char(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 varchar(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 binary(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 text(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 blob(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(5,2) NULL);

CREATE TABLE t1 (c1 decimal(5,2) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(5,2) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double(5,2) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 tinyint(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 smallint(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 int(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 bigint(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 bit(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 year(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(5,2) NOT NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED NOT NULL);

CREATE TABLE t1 (c1 tinyint ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 date ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 time ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 datetime ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 char ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varchar ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 binary ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 text ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longtext ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 blob ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longblob ZEROFILL NULL);

CREATE TABLE t1 (c1 tinyint ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob ZEROFILL NOT NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED ZEROFILL NOT NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED ZEROFILL NULL);

A beginners look at Drizzle – SQL_MODE

by

A new feature to MySQL Version 5 was the introduction of SQL_MODE to support STRICT… or TRADITIONAL values.

This feature enabled a closer compatibility to other RDBMS products. MySQL by default performs a number of silent data changes which do not help in providing a level of data integrity if you come from a more traditional background. MySQL by default represents these as warnings, while with an appropriate SQL_MODE, these are in turn treated as errors.

How does Drizzle handle this? Very simple. There is no SQL_MODE. By default Drizzle handling a strict mode of producing errors for any invalid data. The following are some test case examples showing the varying conditions.

Test Case

select version();
create database if not exists test;
use test;
drop table if exists t1;
create table t1(i1 int, c1 char(10), d1 timestamp);
#Pass Tests
insert into t1(i1) values (500000000);
insert into t1(c1) values('1234567890');
insert into t1(i1) values (5000000000);
#Fail Tests
insert into t1(c1) values('12345678901');
insert into t1(d1) values(now());
insert into t1(d1) values(0);

Drizzle Output

drizzle> select version();
+-------------------------+
| version()               |
+-------------------------+
| 2009.03.970-development |
+-------------------------+
1 row in set (0.00 sec)

drizzle> create database if not exists test;
Query OK, 1 row affected (0.01 sec)

drizzle> use test;
Database changed
drizzle> create table t1(i1 int, c1 char(10), d1 timestamp);
Query OK, 0 rows affected (0.17 sec)
#Pass Tests
drizzle> insert into t1(i1) values (500000000);
Query OK, 1 row affected (0.08 sec)
drizzle> insert into t1(c1) values('1234567890');
Query OK, 1 row affected (0.05 sec)
drizzle> insert into t1(d1) values(now());
Query OK, 1 row affected (0.02 sec)
#Fail Tests
drizzle> insert into t1(i1) values (5000000000);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
drizzle> insert into t1(c1) values('12345678901');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
drizzle> insert into t1(d1) values(0);
ERROR 1685 (HY000): Received an invalid value '0' for a UNIX timestamp.

MySQL Output

mysql> create database if not exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use test;
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> drop table if exists t1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t1(i1 int, c1 char(10), d1 timestamp);
Query OK, 0 rows affected (0.16 sec)

mysql> #Pass Tests
mysql> insert into t1(i1) values (500000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(c1) values('1234567890');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(i1) values (5000000000);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> #Fail Tests
mysql> insert into t1(c1) values('12345678901');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into t1(d1) values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(d1) values(0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+------------+---------------------+
| i1         | c1         | d1                  |
+------------+------------+---------------------+
|  500000000 | NULL       | 2009-04-06 12:14:21 |
|       NULL | 1234567890 | 2009-04-06 12:14:21 |
| 2147483647 | NULL       | 2009-04-06 12:14:21 |
|       NULL | 1234567890 | 2009-04-06 12:14:21 |
|       NULL | NULL       | 2009-04-06 12:14:21 |
|       NULL | NULL       | 0000-00-00 00:00:00 |
+------------+------------+---------------------+
6 rows in set (0.00 sec)

MySQL SQL_MODE=STRICT_ALL_TABLES Output

mysql> set sql_mode = STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.32    |
+-----------+
1 row in set (0.00 sec)

mysql> create database if not exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use test;
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> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(i1 int, c1 char(10), d1 timestamp);
Query OK, 0 rows affected (0.12 sec)

mysql> #Pass Tests
mysql> insert into t1(i1) values (500000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(c1) values('1234567890');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(i1) values (5000000000);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
mysql> #Fail Tests
mysql> insert into t1(c1) values('12345678901');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
mysql> insert into t1(d1) values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(d1) values(0);
Query OK, 1 row affected (0.00 sec)

Update
Thanks to Robert Wultsch who highlighted to me that SQL_MODE has been around since 4.1.

Identifying resource bottlenecks – Memory

by

Continuing on from CPU, we turn our attention to Memory. One of the first steps when addressing a MySQL performance tuning problem is to perform a system audit of the physical hardware resources, then identify any obvious bottlenecks in these resources.

In auditing, I start with the ‘free’ command, the already used ‘vmstat’ command, the /proc/meminfo and /proc/sys/vm/swappiness files to get an indication of memory and swap resources. While we are looking at the Memory, the configuration of Swap is also very important. I will discuss this in more detail later.

$ free -m
             total       used       free     shared    buffers     cached
Mem:          3955       3838        117          0        402       2366
-/+ buffers/cache:       1069       2886
Swap:         1027          0       1027

$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0    128 234088 409632 2474372    0    0     0     0 1081  198  0  0 100  0  0
 0  0    128 234088 409632 2474396    0    0     0     0 1003   59  0  0 100  0  0
 0  0    128 234088 409636 2474392    0    0     0   100 1085  209  0  0 100  0  0
 0  0    128 233836 409636 2474396    0    0     0     0 1014  184  3  0 97  0  0
 0  0    128 233284 409636 2474396    0    0     0     0 1182  435  2  0 98  0  0
 0  0    128 233176 409636 2474396    0    0     0     0 1024  104  1  0 99  0  0
 0  0    128 233176 409636 2474396    0    0     0     0 1079  195  0  0 100  0  0
 1  0    128 233168 409644 2474396    0    0     0   232 1021  188  3  0 97  0  0
 0  0    128 233176 409644 2474396    0    0     0     0 1111  213  2  0 98  0  0
 0  0    128 233176 409644 2474396    0    0     0     0 1005   60  0  0 100  0  0

Memory

  • swpd: the amount of virtual memory used.
  • free: the amount of idle memory.
  • buff: the amount of memory used as buffers.
  • cache: the amount of memory used as cache.
  • inact: the amount of inactive memory. (-a option)
  • active: the amount of active memory. (-a option)

Swap

  • si: Amount of memory swapped in from disk (/s).
  • so: Amount of memory swapped to disk (/s). 
$ cat /proc/meminfo
MemTotal:      4050776 kB
MemFree:        120984 kB
Buffers:        411928 kB
Cached:        2423468 kB
SwapCached:          0 kB
Active:        1861536 kB
Inactive:      1492152 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      4050776 kB
LowFree:        120984 kB
SwapTotal:     1052248 kB
SwapFree:      1052120 kB
Dirty:             172 kB
Writeback:           0 kB
AnonPages:      518112 kB
Mapped:          23140 kB
Slab:           544448 kB
PageTables:       9528 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   3077636 kB
Committed_AS:   859208 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    263132 kB
VmallocChunk: 34359474803 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

$ cat /proc/sys/vm/swappiness
60

What’s important is not to believe that ‘free’ or ‘MemFree’ values are the total free memory. Linux/Unix distributions use available memory as a File System Cache, dumping this data if additional memory is needed. It’s important to add Cached to get a better indication of the true memory available.

Your initial audit should also look at the processes that are using the memory. Options include ‘top’ and ‘ps’.

$ top
M

op - 15:33:56 up 36 days, 17:08,  2 users,  load average: 0.01, 0.11, 0.08
Tasks: 133 total,   1 running, 132 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4050776k total,  3792952k used,   257824k free,   368140k buffers
Swap:  1052248k total,      128k used,  1052120k free,  2329212k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2159 mysql     15   0  411m 204m 5624 S    0  5.2  33:26.43 mysqld
 2965 buildbot  15   0  280m 105m 1932 S    0  2.7 393:15.54 buildbot
 8533 nobody    15   0  168m  25m 4092 S    0  0.7   0:03.49 httpd
 9121 nobody    15   0  166m  24m 3432 S    0  0.6   0:01.61 httpd
...

$ ps -eopid,fname,rss,vsz,user,command | more
  PID COMMAND    RSS    VSZ USER     COMMAND
    1 init       700  10324 root     init [3]
   346 httpd    25252 170484 nobody   /opt/httpd-2.2.9/bin/httpd -k start
  364 httpd    25184 170344 nobody   /opt/httpd-2.2.9/bin/httpd -k start
  425 kpsmouse     0      0 root     [kpsmoused]
  452 httpd    21000 165684 nobody   /opt/httpd-2.2.9/bin/httpd -k start
...
 2095 mysqld_s  1204  63800 root     /bin/sh bin/mysqld_safe
 2159 mysqld   209448 421248 mysql   /opt/mysql51/bin/mysqld --basedir=/opt/mysql51 --datadir=/opt/mysql51/data --user=mysql --log-error=/opt/mysql51/data/dc1.onegreendog.com.err --pid-file=/opt/mysql51/data/dc
1.onegreendog.com.pid
 ...
  • rss RSS resident set size, the non-swapped physical memory that a task has used (in kiloBytes). (alias rssize, rsz).
  • vsz VSZ virtual memory size of the process in KiB (1024-byte units). Device mappings are currently excluded; this is subject to change.
    (alias vsize).

As I have written about previously, Are you monitoring RSS & VSZ?, these columns are important and should be monitored appropriately.

Regarding Swap. This is pre assigned disk space that is used to swap out (dump) memory processes when you have run out of memory for all the running processes. You never want to run out of memory on your database server. Swapping is both extremely slow, and if your database server swaps out the mysqld process, this will effectively kill your database. If you have insufficient swap space for the process, again in the case of database server this can cause your system to crash.

Historically in Unix world, swap was always defined as 2x Memory. This also doubled as a place to dump all memory in a kernel panic. I spent time in a past life doing core dump analysis.
Today, most Linux systems are ill-configured for swap. If you use a dedicated server for example, you may be limited to what is configured by a third party. The above example shows a configuration I would not recommend where swap is less then 1x the memory.
There is also a consideration to have no Swap. By setting swapiness to 0, you are effectively saying never swap. I would also not recommend this.

Monitoring memory usage closely is important. Taking appropriate action regarding the mysql process because you can’t control how much total memory it uses is critical. Correctly configuring mysql to use memory optimally is key to a well and long running database server.

Next, we will be looking at Disk and Network resource bottlenecks.

A beginners look at Drizzle – Datatypes and Tables

by

The Drizzle database, while similar to MySQL includes a number of significant differences. In this post we will look at data types and table syntax that is valid in Drizzle. For more background information you can also review A beginners look at Drizzle – Getting around with SHOW.

Data Types

This comparison is with Drizzle 2009.03.970 and MySQL 5.1.32 GA. More information at MySQL 5.1 Data Types.

The following data types are not valid in Drizzle.

  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • BIT
  • TIME
  • YEAR
  • BINARY
  • SET

Tests used the following data-types in comparison with MySQL 5.1.
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT, FLOAT,DOUBLE,DECIMAL,BIT, ENUM, SET, DATE,TIME,DATETIME,TIMESTAMP,YEAR, CHAR,VARCHAR,BINARY,VARBINARY, TEXT,TINYTEXT,MEDIUMTEXT,LONGTEXT, BLOB,TINYBLOB,MEDIUMBLOB,LONGBLOB

I’m surprised that a number of data types are still valid in Drizzle however the product is still under heavy development. My kill-list for further reducing the syntax scope would include TINYTEXT,MEDIUMTEXT,LONGTEXT, TINYBLOB,MEDIUMBLOB,LONGBLOB

Numeric sizing for INT,BIGINT,FLOAT datatypes is not supported. i.e. INT(1). Very glad about that.

The UNSIGNED and ZEROFILL syntax for Numeric datatypes are not supported.

Character Sets

Another difference is the lack of the CHARACTER SET syntax for character fields. Drizzle only supports the UTF8 character set for all text fields. This also differs from MySQL, as it is a 4 byte field, not 3 byte in MySQL.

drizzle> select * from information_schema.character_sets;
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8               | utf8_general_ci      | UTF-8 Unicode |      4 |
| binary             | binary               |               |      1 |
+--------------------+----------------------+---------------+--------+
2 rows in set (0.09 sec)

mysql> select * from information_schema.character_sets where character_set_name in ('utf8','binary');
+--------------------+----------------------+-----------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION           | MAXLEN |
+--------------------+----------------------+-----------------------+--------+
| utf8               | utf8_general_ci      | UTF-8 Unicode         |      3 |
| binary             | binary               | Binary pseudo charset |      1 |
+--------------------+----------------------+-----------------------+--------+
2 rows in set (0.00 sec)

Collations (the COLLATE syntax) is still supported.

drizzle> select * from information_schema.collations order by 1;
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| binary             | binary             |  63 | Yes        | Yes         |       1 |
| utf8_bin           | utf8               |  46 |            | Yes         |       1 |
| utf8_czech_ci      | utf8               | 234 |            | Yes         |       8 |
| utf8_danish_ci     | utf8               | 235 |            | Yes         |       8 |
| utf8_esperanto_ci  | utf8               | 241 |            | Yes         |       8 |
| utf8_estonian_ci   | utf8               | 230 |            | Yes         |       8 |
| utf8_general_ci    | utf8               |  45 | Yes        | Yes         |       1 |
| utf8_hungarian_ci  | utf8               | 242 |            | Yes         |       8 |
| utf8_icelandic_ci  | utf8               | 225 |            | Yes         |       8 |
| utf8_latvian_ci    | utf8               | 226 |            | Yes         |       8 |
| utf8_lithuanian_ci | utf8               | 236 |            | Yes         |       8 |
| utf8_persian_ci    | utf8               | 240 |            | Yes         |       8 |
| utf8_polish_ci     | utf8               | 229 |            | Yes         |       8 |
| utf8_romanian_ci   | utf8               | 227 |            | Yes         |       8 |
| utf8_roman_ci      | utf8               | 239 |            | Yes         |       8 |
| utf8_sinhala_ci    | utf8               | 243 |            | Yes         |       8 |
| utf8_slovak_ci     | utf8               | 237 |            | Yes         |       8 |
| utf8_slovenian_ci  | utf8               | 228 |            | Yes         |       8 |
| utf8_spanish2_ci   | utf8               | 238 |            | Yes         |       8 |
| utf8_spanish_ci    | utf8               | 231 |            | Yes         |       8 |
| utf8_swedish_ci    | utf8               | 232 |            | Yes         |       8 |
| utf8_turkish_ci    | utf8               | 233 |            | Yes         |       8 |
| utf8_unicode_ci    | utf8               | 224 |            | Yes         |       8 |
+--------------------+--------------------+-----+------------+-------------+---------+
23 rows in set (0.09 sec)

mysql> select * from information_schema.collations where character_set_name in ('utf8','binary') order by 1;
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| binary             | binary             |  63 | Yes        | Yes         |       1 |
| utf8_bin           | utf8               |  83 |            | Yes         |       1 |
| utf8_czech_ci      | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci     | utf8               | 203 |            | Yes         |       8 |
| utf8_esperanto_ci  | utf8               | 209 |            | Yes         |       8 |
| utf8_estonian_ci   | utf8               | 198 |            | Yes         |       8 |
| utf8_general_ci    | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_hungarian_ci  | utf8               | 210 |            | Yes         |       8 |
| utf8_icelandic_ci  | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci    | utf8               | 194 |            | Yes         |       8 |
| utf8_lithuanian_ci | utf8               | 204 |            | Yes         |       8 |
| utf8_persian_ci    | utf8               | 208 |            | Yes         |       8 |
| utf8_polish_ci     | utf8               | 197 |            | Yes         |       8 |
| utf8_romanian_ci   | utf8               | 195 |            | Yes         |       8 |
| utf8_roman_ci      | utf8               | 207 |            | Yes         |       8 |
| utf8_slovak_ci     | utf8               | 205 |            | Yes         |       8 |
| utf8_slovenian_ci  | utf8               | 196 |            | Yes         |       8 |
| utf8_spanish2_ci   | utf8               | 206 |            | Yes         |       8 |
| utf8_spanish_ci    | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci    | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci    | utf8               | 201 |            | Yes         |       8 |
| utf8_unicode_ci    | utf8               | 192 |            | Yes         |       8 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.00 sec)

Perhaps a new MySQL 6.0 collation that is in Drizzle is ‘utf8_sinhala_ci’.

Storage Engines

A key difference in Drizzle is the default storage engine. This defaults to InnoDB, rather then MyISAM. MyISAM is still currently packaged with Drizzle, however I hope that Maria becomes it’s replacement.

The MERGE and FEDERATED storage engines are not supported.
BLACKHOLE by default is not supported, however documentation indicates this can be compiled.

Table Syntax

FULLTEXT indexes which is valid for MyISAM only in MySQL is not supported.
PARTITIONS is not supported.

The following table options are still valid in Drizzle. These were only tested as valid syntax, not any usage of this functionality.
ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, COLLATE, COMMENT, CONNECTION, DELAY_KEY_WRITE, KEY_BLOCK_SIZE, PACK_KEYS

INSERT_METHOD, CHARACTER SET is not support.

I suspect a number of these could be eliminated, definitely CONNECTION as this is related to the FEDERATED storage engine.

VIEWS are not supported in Drizzle.

I will need to invest more time to confirm INDEX and CONSTRAINT syntax.

A beginners look at Drizzle – Getting around with SHOW

by

Assuming you have successfully compiled Drizzle, and you are ready to start for the first time, here are some beginner differences with those familiar with the current MySQL 5.1 GA version. Note: drizzle is actually an original fork of the 6.0.x codebase however the code looks very little like mysql anymore.

Getting started differences

The first default MySQL installation step scripts/install_mysql_db is gone, and there is no replacement. The ‘mysql’ schema no longer exists. Talking about schemas, ‘test’ is also gone by default.

While a bin/drizzled_safe exists to start drizzle, this presently doesn’t operate as expected. You should start drizzle with sbin/drizzled &

They still have an empty password for ‘root’. Would be nice if it was at least a default like an Oracle installation, and a password became a mandatory requirement. Speaking of passwords, trying ‘drizzle -uroot -pxxxx’ will result in an unexpected error.

$ bin/drizzle -uroot -pxxx

ERROR:
Non-integer value supplied for port.  If you are trying to enter a password please use --password instead.

Taking a look around at some common mysql commands you may be familiar with.

Information Schema

drizzle> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
drizzle> use information_schema;
drizzle> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PLUGINS                               |
| PROCESSLIST                           |
| REFERENTIAL_CONSTRAINTS               |
| SCHEMATA                              |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| INNODB_CMP                            |
| INNODB_CMP_RESET                      |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_LOCK_WAITS                     |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
+---------------------------------------+
23 rows in set (0.00 sec)

Gone are COLUMN_PRIVILEGES ENGINES EVENTS FILES KEY_COLUMN_USAGE PARTITIONS PROFILING ROUTINES SCHEMA_PRIVILEGES TABLE_PRIVILEGES TRIGGERS USER_PRIVILEGES VIEWS. With MySQL privileges and a number of 5.x features removed, that’s expected. I was surprised to see ENGINES gone. More on that later.

SIDE NOTE: Disappointed to see (0.00 sec) still around. I would have expected the client interface could have had an overhaul for timing output, and report say 0.00 milliseconds.

Variables

drizzle> show variables
+----------------------------------------+---------------------------------------------+
| Variable_name                          | Value                                       |
+----------------------------------------+---------------------------------------------+
| archive_aio                            | OFF                                         |
..
| innodb_change_buffering                | inserts                                     |
| innodb_file_format                     | Antelope                                    |
| innodb_file_format_check               | Antelope                                    |
| innodb_replication_delay               | 0                                           |
| innodb_stats_on_metadata               | ON                                          |
| innodb_stats_sample_pages              | 8                                           |
| innodb_strict_mode                     | OFF                                         |
| innodb_use_sys_malloc                  | ON                                          |
| innodb_version                         | 1.0.3                                       |
...
| logging_query_enable                   | OFF                                         |
| logging_query_filename                 |                                             |
| logging_query_threshold_big_examined   | 0                                           |
| logging_query_threshold_big_resultset  | 0                                           |
| logging_query_threshold_slow           | 0                                           |
| logging_syslog_enable                  | OFF                                         |
| logging_syslog_facility                | local0                                      |
| logging_syslog_ident                   | drizzled                                    |
| logging_syslog_priority                | info                                        |
| logging_syslog_threshold_big_examined  | 0                                           |
| logging_syslog_threshold_big_resultset | 0                                           |
| logging_syslog_threshold_slow          | 0                                           |
...
| multi_thread_max_threads               | 2048                                        |
| myisam_block_size                      | 1024                                        |
| optimizer_switch                       |                                             |
| optimizer_use_mrr                      | force                                       |
| pool_of_threads_size                   | 8                                           |
| replicator_directory                   |                                             |
| replicator_enabled                     | OFF                                         |

161 rows in set (0.01 sec)

MySQL 5.1 has some 265 variables values, so huge changes here. The big sections gone are around CHARACTER SETS, SSL, REPLICATION, QUERY_CACHE, LOG, FULL TEXT. The above list shows new variables, some of these are Innodb plugin related.

Status

drizzle> show status
...
112 rows in set (0.01 sec)

MySQL 5.1 has some 290 status and Drizzle currently 112. Same suspects, REPLICATION, SSL, QUERY_CACHE, a lot of COM_ commands, all INNODB related status and THREADS_CREATED and THREADS_RUNNING. Nothing new from Drizzle.

Engines

drizzle> show engines;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'engines' at line 1
drizzle> show plugins;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'plugins' at line 1

An initial surprise here, but knowing that all work is converting to plugins, you found this all in INFORMATION_SCHEMA.PLUGINS

drizzle> select * from plugins;
+---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_LIBRARY | PLUGIN_AUTHOR                        | PLUGIN_DESCRIPTION                                                       | PLUGIN_LICENSE |
+---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+
| binlog              | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | MySQL AB                             | This is a pseudo storage engine to represent the binlog in a transaction | GPL            |
| compress            | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for compress()                                                       | GPL            |
| crc32               | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for computing CRC32                                                  | GPL            |
| logging_query       | 0.2            | ACTIVE        | LOGGER             | NULL           | Mark Atwood  mark @fallenpegasus.com  | Log queries to a CSV file                                                | GPL            |
| logging_syslog      | 0.2            | ACTIVE        | LOGGER             | NULL           | Mark Atwood  mark @fallenpegasus.com  | Log to syslog                                                            | GPL            |
| multi_thread        | 0.1            | ACTIVE        | SCHEDULING         | NULL           | Brian Aker                           | One Thread Per Session Scheduler                                         | GPL            |
| pool_of_threads     | 0.1            | ACTIVE        | SCHEDULING         | NULL           | Brian Aker                           | Pool of Threads Scheduler                                                | GPL            |
| replicator          | 0.1            | ACTIVE        | REPLICATOR         | NULL           | Brian Aker                           | Basic replication module                                                 | GPL            |
| signal_handler      | 0.1            | ACTIVE        | DAEMON             | NULL           | Brian Aker                           | Default Signal Handler                                                   | GPL            |
| single_thread       | 0.1            | ACTIVE        | SCHEDULING         | NULL           | Brian Aker                           | Single Thread Scheduler                                                  | GPL            |
| uncompressed_length | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for compress()                                                       | GPL            |
| uncompress          | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for compress()                                                       | GPL            |
| ARCHIVE             | 3.5            | ACTIVE        | STORAGE ENGINE     | NULL           | Brian Aker, MySQL AB                 | Archive storage engine                                                   | GPL            |
| CSV                 | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | Brian Aker, MySQL AB                 | CSV storage engine                                                       | GPL            |
| MEMORY              | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | MySQL AB                             | Hash based, stored in memory, useful for temporary tables                | GPL            |
| InnoDB              | 1.0.1          | ACTIVE        | STORAGE ENGINE     | NULL           | Innobase Oy                          | Supports transactions, row-level locking, and foreign keys               | GPL            |
| INNODB_TRX          | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | InnoDB transactions                                                      | GPL            |
| INNODB_LOCKS        | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | InnoDB conflicting locks                                                 | GPL            |
| INNODB_LOCK_WAITS   | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | InnoDB which lock is blocking which                                      | GPL            |
| INNODB_CMP          | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compression                                    | GPL            |
| INNODB_CMP_RESET    | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compression; reset cumulated counts            | GPL            |
| INNODB_CMPMEM       | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compressed buffer pool                         | GPL            |
| INNODB_CMPMEM_RESET | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | GPL            |
| MyISAM              | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | MySQL AB                             | Default engine as of MySQL 3.23 with great performance                   | GPL            |
+---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+
24 rows in set (0.00 sec)

This is where we start to see some significant differences. Storage engines are now all plugins. We see some entire new features for SCHEDULING, LOGGING and REPLICATOR.

drizzle> select plugin_type,count(*) from information_schema.plugins group by plugin_type order by 2 desc;
+--------------------+----------+
| plugin_type        | count(*) |
+--------------------+----------+
| INFORMATION SCHEMA |        7 |
| STORAGE ENGINE     |        6 |
| UDF                |        4 |
| SCHEDULING         |        3 |
| LOGGER             |        2 |
| REPLICATOR         |        1 |
| DAEMON             |        1 |
+--------------------+----------+
7 rows in set (0.00 sec)

SHOW Commands

Drizzle supports the following SHOW Commands.

SHOW COLUMNS FROM information_schema.tables;
SHOW CREATE DATABASE information_schema;
SHOW DATABASES;
SHOW ENGINE INNODB STATUS;
SHOW ERRORS;
SHOW INDEX FROM information_schema.tables;
SHOW OPEN TABLES;
SHOW PROCESSLIST;
SHOW STATUS;
SHOW TABLE STATUS;
SHOW TABLES;
SHOW VARIABLES;
SHOW WARNINGS;
SHOW CREATE SCHEMA;
SHOW SCHEMAS;

The following are no longer valid.

AUTHORS, CHARACTER SET, COLLATION, CONTRIBUTORS, EVENT event_name, FUNCTION func_name, PROCEDURE proc_name, TRIGGER trigger_name, VIEW view_name, ENGINES, EVENTS, FUNCTION CODE func_name, FUNCTION STATUS, GRANTS FOR , INNODB STATUS, PLUGINS, PROCEDURE CODE proc_name, PROCEDURE STATUS, PRIVILEGES, PROFILE, PROFILES, SCHEDULER STATUS, TRIGGERS, BINARY LOGS, MASTER LOGS, BINLOG EVENTS, MASTER STATUS, SLAVE HOSTS, SLAVE STATUS,

A Drizzle update – Running version 2009.03.970-development

by

I’ve not looked at compiling and running Drizzle on my server for the past four weeks. Well overdue time for a check and see how it’s going. I saw in today’s planet.mysql.com by Eric Day a new dependency is needed. libdrizzle 0.2.0 now in Drizzle is now required, so I started there.

cd ~/bzr
bzr branch lp:libdrizzle
cd libdrizzle
./config/autorun.sh
./configure
make
sudo make install

No problems there, also documented at the Drizzle Wiki. Great to see the docs up to date. I see my old work on starting the compiling page still relevant. Tested on CentOS 5 and Mac OS/X 10.5

Compiling drizzle was not much more difficult.

cd ~/bzr/drizzle
bzr update
make distclean
./config/autorun.sh
./configure --prefix=/home/drizzle/deploy
make
make install

The problems happened when I started drizzle. Initially I was using bin/drizzled_safe, but it was recommended via IRC#drizzle I stick with sbin/drizzled

sbin/drizzled &
error while loading shared libraries: libprotobuf.so.2: cannot open shared object file: No such file or directory

An investigation of Google Proto Buffers.

$ protoc --version
libprotoc 2.0.2

I see that protobuf 2.0.3 is now available, but this was not the problem.

I got around the problem by specifying the current library path:

$ LD_LIBRARY_PATH=/usr/local/lib sbin/drizzled &

I corrected this problem by adding /usr/local/lib to the default ld path, both the libdrizzle and libprotobuf libs are located there.

$ echo "/usr/local/lib" > /etc/ld.so.conf.d/drizzle.conf
$ ldconfig
$ ls -l /usr/local/lib
total 37240
-rw-r--r-- 1 root root  1194602 Mar 31 17:42 libdrizzle.a
-rwxr-xr-x 1 root root      940 Mar 31 17:42 libdrizzle.la
lrwxrwxrwx 1 root root       19 Mar 31 17:42 libdrizzle.so -> libdrizzle.so.0.0.2
lrwxrwxrwx 1 root root       19 Mar 31 17:42 libdrizzle.so.0 -> libdrizzle.so.0.0.2
-rwxr-xr-x 1 root root  1117979 Mar 31 17:42 libdrizzle.so.0.0.2
-rw-r--r-- 1 root root 12199302 Nov 30 23:32 libprotobuf.a
-rwxr-xr-x 1 root root      836 Nov 30 23:32 libprotobuf.la
lrwxrwxrwx 1 root root       20 Nov 30 23:32 libprotobuf.so -> libprotobuf.so.2.0.0
lrwxrwxrwx 1 root root       20 Aug 27  2008 libprotobuf.so.0 -> libprotobuf.so.0.0.0
-rwxr-xr-x 1 root root  5027949 Aug 27  2008 libprotobuf.so.0.0.0
lrwxrwxrwx 1 root root       20 Nov 30 23:32 libprotobuf.so.2 -> libprotobuf.so.2.0.0
-rwxr-xr-x 1 root root  5586965 Nov 30 23:32 libprotobuf.so.2.0.0
-rw-r--r-- 1 root root  9264068 Nov 30 23:32 libprotoc.a
-rwxr-xr-x 1 root root      852 Nov 30 23:32 libprotoc.la
lrwxrwxrwx 1 root root       18 Nov 30 23:32 libprotoc.so -> libprotoc.so.0.0.0
lrwxrwxrwx 1 root root       18 Nov 30 23:32 libprotoc.so.0 -> libprotoc.so.0.0.0
-rwxr-xr-x 1 root root  3645396 Nov 30 23:32 libprotoc.so.0.0.0
drwxr-xr-x 2 root root     4096 Mar 31 17:42 pkgconfig

Starting

$ sbin/drizzled &
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
090331 18:38:08  InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090331 18:38:08  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090331 18:38:08 InnoDB Plugin 1.0.3 started; log sequence number 46419
sbin/drizzled: ready for connections.
Version: '2009.03.970-development'  socket: ''  port: 4427  Source distribution

Verifying

$ bin/drizzle -uroot
Welcome to the Drizzle client..  Commands end with ; or g.
Your Drizzle connection id is 1
Server version: 2009.03.970-development Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
drizzle> select version();
+-------------------------+
| version()               |
+-------------------------+
| 2009.03.970-development |
+-------------------------+
1 row in set (0.00 sec)
drizzle> exit

Sweet! Now to try some testing & benchmarking before the barrage of conferences next month, 2009 MySQL Camp, Percona Performance Conference and MySQL Conference & Expo.

I’m going to check out The Juice Database Benchmark next as a more realistic benchmark to DBT2 and sysbench.