You are here: Home / Archives for Technologies / Drizzle

Why SQL_MODE is important

by

Today was another example of where a correct SQL_MODE saved customer data from being corrupted. By default, MySQL does not enforce data integrity. It allows what is called silent truncations where the result of what you INSERT or UPDATE does not represent truth. NOTE: I see very few customers ever have this correctly configured, those that do have actually listened to my advice.

If you do not read any further, your production MySQL environments should be running with at the bare minimum of SQL_MODE=STRICT_ALL_TABLES however I would also advocate for additional SQL_MODE settings.

For this example, some modified undesirable code attempted to reduce a counter by 1, however because of an UNSIGNED data type and a correctly set SQL_MODE, the application produced an error and data was not corrupted.

This is what should happen with your SQL.

mysql> update stats set loss_count=loss_count - 1 where user_id=42;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`stats`.`loss_count` - 1)'

It is interesting to note that the error message actually is misleading. The datatype for the column is SMALLINT however the error message prompted an unnecessary schema verification. Even with the calculation I would have assumed the inherited data type would be the column definition before the subtraction. If you try to set the value automatically to negative you get a better message.

mysql> update stats set loss_count=-1 where user_id=42;
ERROR 1264 (22003): Out of range value for column 'loss_count' at row 1

So why is this a big deal? If your MySQL instance is not using SQL_MODE then the result would have been a value of 0 and this would never highlight the bug in the code.

I should point out that only Drizzle of the MySQL forks and variants has tightened up this data integrity, however I have to agree to disagree with the decision to drop UNSIGNED. While I under internally the code simplification behind the decision, without check constraints in MySQL, UNSIGNED is a saving grace. In this example, Drizzle in this case would not have reported an error.

The Drizzle Census

by

One thing I have often wondered is just how many MySQL instances exist in the world and what MySQL versions and architectures are in use. We hear of 50,000 windows downloads per day but this is misleading because MySQL is basically bundled with Linux by default or installed from various repositories. Linux servers powers many websites.

In Drizzle we have a proposed plan, the Drizzle Census. From the productive Drizzle Developers Day recently at the 2010 MySQL conference we sat down and created a blueprint, and subsequent high level spec of what we considered this optional plugin should do. We didn’t get as far as I would have liked in a code skeleton to at least gather and store a sample result, but the hope is that with the community we will in the near future.

Here is the list of information we decided was appropriate for anonymous information of value.

  • Kernel Version/Architecture
  • CPU type
  • SID – HASH(processor_id,listener address,first listener port)
  • Drizzle Version
  • Drizzle Uptime
  • Drizzled process memory usage

How to find MySQL developers?

by

Brian wrote recently Where did all of the MySQL Developers Go?, while over in Drizzle land they have been accepted for the Google Summer of code along with many other open source projects. MySQL from my observation a noticeable absentee.

Historically, the lack of opportunity to enable community contributions and see them implemented in say under 5 years, has really hurt MySQL in recent times. There is plenty of history here so that’s not worth repeating. The current landscape of patches, forks and custom MySQL binaries for storage engine provider has provided a boom of innovation that sadly is now lost from the core MySQL product.

In Drizzle, community contribution is actively sought and a good portion of committed code is not from the core Drizzle developers (wherever they work). As a Drizzle GSoC project contributor last year Padraig for example this year is helping to mentor. The Drizzle project contribution philisophy, GSoC and other activities such as the Drizzle Developer Day all enable the next generation of developers to be part of ongoing project developement.

Oracle, what are you going to do to foster an active community and new long term developers for MySQL?

Understanding Drizzle user authentication options – Part 2

by

A key differentiator in Drizzle from it’s original MySQL roots is user based authentication. Gone is the host/user and schema/table/column model that was stored in the MyISAM based mysql.user table.

Authentication is now completely pluggable, leveraging existing systems such as PAM, LDAP via PAM and Http authentication.

In this post I’ll talk about HTTP authentication which requires an external http server to implement successfully. You can look at Part 1 for PAM authentication.

Compiling for http auth support

By default during compilation you may find.

checking for libcurl... no
configure: WARNING: libcurl development lib not found: not building auth_http plugin. On Debian this is found in libcurl4-gnutls-dev. On RedHat it's in libcurl-devel.

In my case I needed:

$ sudo yum install curl-devel

NOTE: Bug #527255 talks about issues of the message being incorrect for libcurl-devel however this appears it may be valid in Fedora Installs

After successfully installing the necessary pre-requisite you should see.

checking for libcurl... yes
checking how to link with libcurl... -lcurl
checking if libcurl has CURLOPT_USERNAME... no

HTTP Authentication

We need to enable the plugin at server startup.

$ sbin/drizzled --mysql-protocol-port=3399 --plugin_add=auth_http &

You need to ensure the auth_http plugin is active by checking the data dictionary plugin table.

drizzle> select * from data_dictionary.plugins where plugin_name='auth_http';
+-------------+----------------+-----------+-------------+
| PLUGIN_NAME | PLUGIN_TYPE    | IS_ACTIVE | MODULE_NAME |
+-------------+----------------+-----------+-------------+
| auth_http   | Authentication | TRUE      |             |
+-------------+----------------+-----------+-------------+

The auth_http plugin also has the following system variables.

drizzle> SHOW GLOBAL VARIABLES LIKE '%http%';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| auth_http_enable | OFF               |
| auth_http_url    | http://localhost/ |
+------------------+-------------------+
2 rows in set (0 sec)

In order to configure Http authentication, you need to have the following settings added to your drizzled.cnf file. For example:

$ cat etc/drizzled.cnf
[drizzled]
auth_http_enable=TRUE
auth_http_url=http://thedrizzler.com/auth

NOTE: Replace the domain name with something you have, even localhost.

A Drizzle restart gives us

$ bin/drizzle -e "SHOW GLOBAL VARIABLES LIKE 'auth_http%'"
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| auth_http_enable | ON                          |
| auth_http_url    | http://thedrizzler.com/auth |
+------------------+-----------------------------+

By default, currently if the settings result in an invalid url, then account validation does not fail and you can still login. It is recommended that you always configure pam authentication as well as a fall back.

$ wget -O tmp http://thedrizzler.com/auth
--17:32:32--  http://thedrizzler.com/auth
Resolving thedrizzler.com... 208.43.73.220
Connecting to thedrizzler.com|208.43.73.220|:80... connected.
HTTP request sent, awaiting response... 404 Not Found
17:32:32 ERROR 404: Not Found.

$ bin/drizzle
drizzle > exit

Configuring passwords

To correctly configured your web server to perform the HTTP auth, you can use this Apache syntax as an example.

The following is added to the VirtualHost entry in your web browser.

<Directory /var/www/drizzle/auth>
AllowOverride FileInfo All AuthConfig
AuthType Basic
AuthName "Drizzle Access Only"
AuthUserFile /home/drizzle/.authentication
Require valid-user
</Directory>

$ sudo su -
$ mkdir /var/www/drizzle/auth
$ touch /var/www/drizzle/auth/index.htm
$ apachectl graceful

We check we now need permissions for the URL.

$ wget -O tmp http://thedrizzler.com/auth
--17:35:48--  http://thedrizzler.com/auth
Resolving thedrizzler.com... 208.43.73.220
Connecting to thedrizzler.com|208.43.73.220|:80... connected.
HTTP request sent, awaiting response... 401 Authorization Required
Authorization failed.

You need to create the username/password for access.

$ htpasswd -cb /home/drizzle/.authentication testuser sakila
$ cat /home/drizzle/.authentication
testuser:85/7CbdeVql4E

Confirm that the http auth with correct user/password works.

$ wget -O tmp http://thedrizzler.com/auth --user=testuser --password=sakila
--17:37:45--  http://thedrizzler.com/auth
Resolving thedrizzler.com... 208.43.73.220
Connecting to thedrizzler.com|208.43.73.220|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently

Drizzle HTTP Authentication in action

By default we now can’t login

$ bin/drizzle
ERROR 1045 (28000): Access denied for user ''@'127.0.0.1' (using password: NO)

$ bin/drizzle --user=testuser --password=sakila999
ERROR 1045 (28000): Access denied for user 'testuser'@'127.0.0.1' (using password: YES)

$ bin/drizzle --user=testuser --password=sakila
Welcome to the Drizzle client..  Commands end with ; or g.
Your Drizzle connection id is 6
Server version: 7 Source distribution (trunk)

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

drizzle>

Understanding Drizzle user authentication options – Part 1

by

A key differentiator in Drizzle from it’s original MySQL roots is user based authentication. Gone is the host/user and schema/table/column model that was stored in the MyISAM based mysql.user table.

Authentication is now completely pluggable, leveraging existing systems such as PAM, LDAP via PAM and Http authentication.

In this post I’ll talk about PAM authentication which is effectively your current Linux based user security.

This information is based on the current build 1317.

Compiling for PAM support

Your Drizzle environment needs to be compiled with PAM support. You would have received the following warning during a configure.

$ ./configure
...
checking for libpam... no
configure: WARNING: Couldn't find PAM development support, pam_auth will not be built. On Debian, libpam is in libpam0g-dev. On RedHat it's in pam-devel.

The solution is provided in the warning message which is another great thing about Drizzle. The pre checks for dependencies and the optional messages like these far exceed the MySQL equivalent compilation process. In my case:

$ sudo yum install pam-devel

When correctly configured, it should look like:

checking for libpam... yes
checking how to link with libpam... -lpam

Working with PAM

You need to enable the PAM authentication plugin at drizzled startup.

sbin/drizzled --plugin_add=auth_pam &

Unfortunately connecting fails to work with

time sbin/drizzle --user=testuser --password=***** --port=4427
real 0m0.003s
user 0m0.003s
sys 0m0.001s

A look into the source at src/drizzle-2010.03.1317/plugin/auth_pam/auth_pam.cc shows a needed config file

117     retval= pam_start("check_user", userinfo.name, &conv_info, &pamh);

Configuring PAM

In order to enable PAM with Drizzle you need to have the following system configuration.

$ cat /etc/pam.d/check_user
auth    required        pam_unix.so
account required        pam_unix.so

$ time sbin/drizzle --user=testuser --password=***** --port=4427
ERROR 1045 (28000): Access denied for user 'testuser'@'127.0.0.1' (using password: YES)

real 0m2.055s
user 0m0.002s
sys 0m0.002s

This did some validation but still failed.

It seems Bug #484069 may fix this problem, however this is not currently in the main line!

Stay Tuned!

Drizzle's Data Dictionary and Global Status

by

With the recent news by Brian about the Data Dictionary in Drizzle replacing the INFORMATION_SCHEMA, I was looking into the server status variables (aka INFORMATION_SCHEMA.GLOBAL_STATUS) and I came across an interesting discovery.

select * from data_dictionary.global_status;
...
| Table_locks_immediate      | 0              |
| Table_locks_waited         | 0              |
| Threads_connected          | 8134064        |
| Uptime                     | 332            |
| Uptime_since_flush_status  | 332            |
+----------------------------+----------------+
51 rows in set (0 sec)

This only retrieved 51 rows, which is way less then previous. What I wanted was clearly missing, all the old com_ status variables. Looking at what the data_dictionary actually has available revealed a new table.

drizzle> select * from data_dictionary.global_statements;
+-----------------------+----------------+
| VARIABLE_NAME         | VARIABLE_VALUE |
+-----------------------+----------------+
| admin_commands        | 0              |
| alter_db              | 0              |
| alter_table           | 0              |
| analyze               | 0              |
| begin                 | 0              |
| change_db             | 1              |
| check                 | 0              |
| checksum              | 0              |
| commit                | 0              |
| create_db             | 0              |
| create_index          | 0              |
| create_table          | 0              |
| delete                | 0              |
| drop_db               | 0              |
| drop_index            | 0              |
| drop_table            | 0              |
| empty_query           | 0              |
| flush                 | 0              |
| insert                | 0              |
| insert_select         | 0              |
| kill                  | 0              |
| load                  | 0              |
| release_savepoint     | 0              |
| rename_table          | 0              |
| replace               | 0              |
| replace_select        | 0              |
| rollback              | 0              |
| rollback_to_savepoint | 0              |
| savepoint             | 0              |
| select                | 10             |
| set_option            | 0              |
| show_create_db        | 0              |
| show_create_table     | 0              |
| show_errors           | 0              |
| show_warnings         | 0              |
| truncate              | 0              |
| unlock_tables         | 0              |
| update                | 0              |
+-----------------------+----------------+
38 rows in set (0 sec)

Kudos to this. Looking at list I saw an obvious omission, of “ping”. Something that caught me out some years ago with huge (300-500 per second admin_commands). I’m also a fan of Mark’s recent work An evening hack – Com_ping in MySQL.

Getting started with Drizzle JDBC

by

In preparation for some Java work I wanted to configure and test the Drizzle JDBC Driver. Any chance to swing Drizzle into a MySQL discussion is worth the research. What I found was an issue compiling and an issue running on Ubuntu 9.04

You can start by downloading and building the Drizzle JDBC. My first problem was when I tried to build a usable .jar. I got errors in the test cases which caused by default no built .jar to work with. I raised Bug #432146 – org.drizzle.jdbc.MySQLDriverTest Tests fail. As I stated it may not be a real bug, but it seems at present that you require a running MySQL instance as well as a running Drizzle instance. In my case I didn’t have MySQL running, and I think to be fair, I should be able to build a Drizzle driver without MySQL.

Anyway, as per the Wiki Docs I proceeded to package without successful test cases. My next problem was more interesting, and perhaps found earlier from the tests?

I first created a test schema my code was going to use.

$ ~/drizzle/deploy/bin/drizzle
Your Drizzle connection id is 724
Server version: 2009.09.1126 Source distribution (trunk)

drizzle> create schema test_java;
Query OK, 1 row affected (0 sec)
drizzle> exit

I wrote a simple Java program.

$ cat ExampleDrizzle.java
import java.sql.*;

public class ExampleDrizzle {

  public static void main(String args[]) {

    try {
      Class.forName("org.drizzle.jdbc.Driver");
    } catch (Exception e) {
      System.out.println(e.getMessage());
      System.exit(1);
    }

    try {
      Connection con = DriverManager.getConnection("jdbc:drizzle://localhost:4427/test_java");
      Statement st = con.createStatement();
      st.executeUpdate("CREATE TABLE a (id int not null primary key, value varchar(20))");
      st.close();
      con.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }
}

Compiled.

$ javac ExampleDrizzle.java

Ran.

$ java ExampleDrizzle
org.drizzle.jdbc.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:mysql-connector-java-5.1.8-bin.jar,file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}

Oops, been a while since using Java. I was amazed I could write the code in vi in the first place.

$ export CLASSPATH=drizzle-jdbc-0.5-SNAPSHOT.jar:.
$ java ExampleDrizzle
17-Sep-09 6:48:45 PM org.drizzle.jdbc.internal.drizzle.DrizzleProtocol 
INFO: Connected to: localhost:4427
Exception in thread "main" java.lang.NoClassDefFoundError: org.drizzle.jdbc.DrizzleConnection
   at java.lang.Class.initializeClass(libgcj.so.90)
   at org.drizzle.jdbc.Driver.connect(Driver.java:74)
   at java.sql.DriverManager.getConnection(libgcj.so.90)
   at ExampleDrizzle.main(ExampleDrizzle.java:15)
Caused by: java.lang.ClassNotFoundException: java.sql.SQLFeatureNotSupportedException not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:drizzle-jdbc-0.5-SNAPSHOT.jar,file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
   at java.net.URLClassLoader.findClass(libgcj.so.90)
   at java.lang.ClassLoader.loadClass(libgcj.so.90)
   at java.lang.ClassLoader.loadClass(libgcj.so.90)
   at java.lang.Class.forName(libgcj.so.90)
   at java.lang.Class.initializeClass(libgcj.so.90)
   ...3 more

Hmmm, that’s disappointing. I thought about it a minute, figured some guidance would be beneficial , so I sought out the best Java person on #drizzle IRC. Getting a name, but no response from an initial inquiry after about a half hour I thought again at the problem. Just what java are you using?

$ java -version
java version "1.5.0"
gij (GNU libgcj) version 4.3.3

$ ls -l /usr/bin/java
lrwxrwxrwx 1 root root 22 2009-07-17 12:36 /usr/bin/java -> /etc/alternatives/java

$ sudo find / -name java
[sudo] password for rbradfor:
/usr/lib/java
/usr/lib/ure/share/java
/usr/lib/jvm/java-6-sun-1.6.0.16/bin/java
/usr/lib/jvm/java-6-sun-1.6.0.16/jre/bin/java
/usr/lib/jvm/java-1.5.0-gcj-4.3-1.5.0.0/bin/java
/usr/lib/jvm/java-1.5.0-gcj-4.3-1.5.0.0/jre/bin/java
/usr/bin/java
/usr/include/c++/4.3/gnu/java
/usr/include/c++/4.3/java
/usr/local/include/google/protobuf/compiler/java

$ ls -l /etc/alternatives/j*
...
lrwxrwxrwx   1 root root    33 2009-09-17 17:50 jar -> /usr/lib/jvm/java-gcj/jre/bin/jar
lrwxrwxrwx   1 root root    39 2009-09-17 17:50 jar.1.gz -> /usr/lib/jvm/java-gcj/man/man1/jar.1.gz
lrwxrwxrwx   1 root root    35 2009-09-17 17:50 jarsigner -> /usr/lib/jvm/java-gcj/bin/jarsigner
lrwxrwxrwx   1 root root    45 2009-09-17 17:50 jarsigner.1.gz -> /usr/lib/jvm/java-gcj/man/man1/jarsigner.1.gz
lrwxrwxrwx   1 root root    34 2009-09-17 17:50 java -> /usr/lib/jvm/java-gcj/jre/bin/java
lrwxrwxrwx   1 root root    40 2009-09-17 17:50 java.1.gz -> /usr/lib/jvm/java-gcj/man/man1/java.1.gz
lrwxrwxrwx   1 root root    31 2009-09-17 17:50 javac -> /usr/lib/jvm/java-gcj/bin/javac
lrwxrwxrwx   1 root root    41 2009-09-17 17:50 javac.1.gz -> /usr/lib/jvm/java-gcj/man/man1/javac.1.gz
...

I wonder if I should use the real Sun Java.

$ sudo apt-get install sun-java6-jdk
Reading package lists... Done
Building dependency tree
Reading state information... Done
sun-java6-jdk is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 2 not upgraded.
$ sudo update-alternatives --config java

There are 4 alternatives which provide `java'.

  Selection    Alternative
-----------------------------------------------
          1    /usr/lib/jvm/java-6-sun/jre/bin/java
          2    /usr/bin/gij-4.3
          3    /usr/bin/gij-4.2
*+        4    /usr/lib/jvm/java-gcj/jre/bin/java

Press enter to keep the default[*], or type selection number: 1
Using '/usr/lib/jvm/java-6-sun/jre/bin/java' to provide 'java'.

$ ls -l /usr/bin/java
lrwxrwxrwx 1 root root 22 2009-07-17 12:36 /usr/bin/java -> /etc/alternatives/java
$ ls -l /etc/alternatives/java
lrwxrwxrwx 1 root root 36 2009-09-17 18:53 /etc/alternatives/java -> /usr/lib/jvm/java-6-sun/jre/bin/java

Yep, it took a minute to discover the update-alternatives command, lucky I didn’t try that manually.

A second try.

$ javac ExampleDrizzle.java
$ java ExampleDrizzle
Sep 17, 2009 6:54:22 PM org.drizzle.jdbc.internal.drizzle.DrizzleProtocol 
INFO: Connected to: localhost:4427
Sep 17, 2009 6:54:22 PM org.drizzle.jdbc.internal.drizzle.DrizzleProtocol close
INFO: Closing connection
Sep 17, 2009 6:54:22 PM org.drizzle.jdbc.internal.common.packet.AsyncPacketFetcher run
INFO: Connection closed

$ ~/drizzle/deploy/bin/drizzle test_java
Server version: 2009.09.1126 Source distribution (trunk)

drizzle> show tables;
+---------------------+
| Tables_in_test_java |
+---------------------+
| a                   |
+---------------------+
1 row in set (0 sec)

drizzle> desc a;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| value | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0 sec)

And I’ve got a working testcase.

SQL Analysis with MySQL Proxy – Part 2

by

As I outlined in Part 1 MySQL Proxy can be one tool for performing SQL analysis. The impact with any monitoring is the art of monitoring will affect the results, in this case the performance. I don’t recommend enabling this level of detailed monitoring in production, these techniques are designed for development, testing, and possibly stress testing.

This leads to the question, how do I monitor SQL in production? The simple answer to this question is, Sampling. Take a representative sample of your production system. The implementation of this depends on many factors including your programming technology stack, and your MySQL topology.

If for example you are using PHP, then defining MySQL proxy on a production system, and executing firewall rules to redirect incoming 3306 traffic to 4040 for a period of time, e.g. 2 seconds can provide a wealth of information as to what’s happening on the server now. I have used this very successfully in production as an information gathering an analysis tool. It is also reasonably easy to configure, execute and the impact on any failures for example are minimized due to the sampling time.

If you run a distributed environment with MySQL Slaves, or many application servers, you can also introduce sampling to a certain extent as these specific points, however like scaling options, it is key to be able to handle and process the write load accurately.

Another performance improvement is to move processing of the gathered information in MySQL proxy to a separate thread or process, removing this work from the thread execution path and therefore increasing the performance. I’m interested to explore the option of passing this information off to memcached or gearman and having MySQL proxy simply capture the packet information and distributing the output. I have yet to see how memcached and/or gearman integrate with the Lua/C bindings. If anybody has experience or knowledge I would be interested to know more.

It is interesting to know that Drizzle provides a plugin to send this level of logging information to gearman automatically.

Seeking public data for benchmarks

by

I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.

You have two options with benchmarks, the brute force approach such as Sysbench, TPC, sysbench, Juice Benchmark, iibench, mysqlslap, skyload. I prefer the realistic approach however these are always on client’s private data. What is first needed is better access to public data for benchmarks. I have compiled this list to date and I am seeking additional sources for reference.

Of course, the data is only the starting point, having representative transactions and queries to execute and a framework to execute and a reporting module are also necessary. The introduction of Lua into Sysbench may now be a better option then my tool of choice mybench which I use simply because I can configure, write and deploy generally for a client in under 1 hour.

If anybody has other good references to free public data that’s easily loadable into MySQL please let me know.

Setting up sysbench with MySQL & Drizzle

by

Sysbench is a open source product that enables you to perform various system benchmarks including databases. Drizzles performs regression testing of every trunk revision with a branched version of sysbench within Drizzle Automation.

A pending branch https://code.launchpad.net/~elambert/sysbench/trunk_drizzle_merge by Eric Lambert now enables side by side testing with MySQL and Drizzle. On a system running MySQL and Drizzle I was able install this sysbench branch with the following commands.

cd bzr
bzr branch lp:~elambert/sysbench/trunk_drizzle_merge
cd trunk_drizzle_merge/
./autogen.sh
./configure
make
sudo make install

Running the default lua tests supplied required me to ensure drizzle was in my path and that I created the ‘sbtest’ schema. I’ll be sure it add that checking to my future developed benchmark scripts.

$ cd sysbench/tests/db
$ sysbench --test=insert.lua --db_driver=drizzle prepare
sysbench v0.4.10:  multi-threaded system evaluation benchmark

FATAL: unable to connect to Drizzle server: 23
FATAL: error 0: Unknown database 'sbtest'
FATAL: failed to execute function `prepare': insert.lua:7: Failed to connect to the database

$ drizzle -e "create schema sbtest"
$ sysbench --test=insert.lua --db_driver=drizzle prepare
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...

And running produces the following results.

$ sysbench --num-threads=1 --test=insert.lua --db_driver=drizzle run
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Threads started!

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           10000
        other:                           0
        total:                           10000
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 10000  (879.68 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          11.3678s
    total number of events:              10000
    total time taken by event execution: 11.3354s
    per-request statistics:
         min:                                  0.32ms
         avg:                                  1.13ms
         max:                                 68.74ms
         approx.  95 percentile:               2.41ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   11.3354/0.0

Rerunning the prepare also lacked some auto cleanup to allow for automated re-running.

$ sysbench --test=insert.lua --db_driver=drizzle prepare
Creating table 'sbtest'...
ALERT: Drizzle Query Failed: 1050:Table 'sbtest' already exists
FATAL: failed to execute function `prepare': insert.lua:57: Database query failed

For MySQL

$ sysbench --test=insert.lua --db_driver=mysql --mysql_table_engine=innodb prepare
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...

Unfortunately this doesn’t actually create the table in the right storage engine, I had to hack the code to ensure I was comparing InnoDB in each test.

$ sysbench --num-threads=1 --test=insert.l
ua --db_driver=mysql run
sysbench v0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Threads started!

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           10000
        other:                           0
        total:                           10000
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 10000  (897.67 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          11.1399s
    total number of events:              10000
    total time taken by event execution: 11.1084s
    per-request statistics:
         min:                                  0.27ms
         avg:                                  1.11ms
         max:                                252.63ms
         approx.  95 percentile:               2.48ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   11.1084/0.00

Armed with a working environment I can now write some more realistic production like tests in Lua.

Drizzle Query logging

by

Currently Drizzle offers three (3) separate query logging plugins. These plugins offer an extensible means of gathering all or selected queries and provide the foundation for a query analyser tool. Additional filtering includes selecting queries by execution time, result size, rows processed and by any given regular expression via PCRE.

During this tutorial I’ll be stepping though the various logging_query parameters which log SQL in a CSV format.

Confirm Logging Plugins

You can view the current ACTIVE plugins in Drizzle with the following SQL.

drizzle> select version();
+--------------+
| version()    |
+--------------+
| 2009.07.1097 |
+--------------+

drizzle> select * from information_schema.plugins where plugin_name like 'logging%';
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
| PLUGIN_NAME     | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_AUTHOR                        | PLUGIN_DESCRIPTION              | PLUGIN_LICENSE |
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
| logging_gearman | 0.1            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log queries to a Gearman server | GPL            |
| logging_query   | 0.2            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log queries to a CSV file       | GPL            |
| logging_syslog  | 0.2            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log to syslog                   | GPL            |
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
3 rows in set (0.01 sec)

Logging all queries

You can define the following configuration variables to enable query logging.

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/general.csv

You can confirm the settings with the following SHOW VARIABLES.

drizzle> show global variables like 'logging_query%';
+---------------------------------------+------------------------------+
| Variable_name                         | Value                        |
+---------------------------------------+------------------------------+
| logging_query_enable                  | ON                           |
| logging_query_filename                | /var/log/drizzle/general.csv |
| logging_query_pcre                    |                              |
| logging_query_threshold_big_examined  | 0                            |
| logging_query_threshold_big_resultset | 0                            |
| logging_query_threshold_slow          | 0                            |
+---------------------------------------+------------------------------+

This command showing queries to be logged.

$ cat /var/log/drizzle/general.csv
1248214561824590,1,1,"","select @@version_comment limit 1","Query",1248214561824590,1240,1240,1,00,0
1248214582588346,1,3,"","show global variables like 'logging_query%'","Query",1248214582588346,1958,1706,6,62,0

Unfortunately the log does not yet provide a header. You need to turn the source code to get a better description of the columns.

      snprintf(msgbuf, MAX_MSG_LEN,
               "%"PRIu64",%"PRIu64",%"PRIu64","%.*s","%s","%.*s","
               "%"PRIu64",%"PRIu64",%"PRIu64",%"PRIu64",%"PRIu64
               "%"PRIu32",%"PRIu32"n",
               t_mark,
               session->thread_id,
               session->query_id,
               // dont need to quote the db name, always CSV safe
               dbl, dbs,
               // do need to quote the query
               quotify((unsigned char *)session->query,
                       session->query_length, qs, sizeof(qs)),
               // command_name is defined in drizzled/sql_parse.cc
               // dont need to quote the command name, always CSV safe
               (int)command_name[session->command].length,
               command_name[session->command].str,
               // counters are at end, to make it easier to add more
               (t_mark - session->connect_utime),
               (t_mark - session->start_utime),
               (t_mark - session->utime_after_lock),
               session->sent_row_count,
               session->examined_row_count,
               session->tmp_table,
               session->total_warn_count);

The important parts of this information include:

  • getmicrotime – 1248214561824590
  • Session Id – 1
  • Query Id – 1
  • Schema
  • The Query: “show global variables like ‘logging_query%'”
  • The Query type “Query”
  • Time session connected – 1248214582588346
  • The total execution time – 1958
  • The execution time after necessary locks – 1706
  • The number of rows returned – 6
  • The number of rows examined – 6
  • The number of temporary tables used – 2
  • The total warning count – 0

I also found what I believe is a formatting problem logged as Bug #402831.

You can enable logging dynamically.

drizzle> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-07-22 02:14:31 |
+---------------------+
1 row in set (0 sec)

drizzle> set global logging_query_enable=true;
Query OK, 0 rows affected (0 sec)

drizzle> select curdate();
+------------+
| curdate()  |
+------------+
| 2009-07-22 |
+------------+
1 row in set (0 sec)

drizzle> set global logging_query_enable=false;
Query OK, 0 rows affected (0 sec)

drizzle> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-07-22 02:14:54 |
+---------------------+
1 row in set (0 sec)

1248228876381645,4,3,"","set global logging_query_enable=true","Query",1248228876381645,761,761,0,00,0
1248228886866882,4,4,"","select curdate()","Query",1248228886866882,105,105,1,00,0

I was not able to alter the logging_query_filename dynamically. Need to confirm with the development team about this functionality for the future.

drizzle> set global logging_query_filename='/tmp/general.csv';
ERROR 1238 (HY000): Variable 'logging_query_filename' is a read only variable

Logging slow queries

If you just wanted to emulate the MySQL slow query log, with a long_query_time of 1 second, you could use the following.

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_threshold_slow=1000000

Drizzle supports the ability to set a threshold in microseconds.

NOTE: I wanted to demonstrate this using the popular MySQL SLEEP() function, only to find this is currently not available in Drizzle. This is an ideal example of a simple UDF that can be written and added to Drizzle. One day if I ever have the time.

Here is some sample output using queries > 1 second.

1248216457856195,1,43,"test","insert into numbers   select...","Query",1248216457856195,2160680,2160620,0,26214420,0
1248216462738678,1,45,"test","insert into numbers   select...","Query",1248216462738678,4530327,4530263,0,52428821,0
1248216472430813,1,47,"test","insert into numbers   select...","Query",1248216472430813,8990965,8990890,0,104857622,0
1248216473592812,1,48,"test","select @counter := count(*) from numbers","Query",1248216473592812,1152319,1152257,1,104857622,0

Logging by threshold

Drizzle Query Logging provides the ability to return results by 2 thresholds, the number of rows in the result, and the number of rows examined by the storage engine.

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_threshold_big_resultset=100

1248216631322097,1,5,"test","select * from numbers limit 100","Query",1248216631322097,281,217,100,1002,0
1248216642763174,1,6,"test","select * from numbers limit 101","Query",1248216642763174,268,215,101,1012,0

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_threshold_big_examined=1000

1248216785430588,1,6,"test","select * from numbers limit 1000","Query",1248216785430588,8055,7983,1000,10002,0
1248216800327928,1,7,"test","select count(*) from numbers","Query",1248216800327928,1041322,1041222,1,10485762,0

Logging by pattern

The final option is to return queries that match a given pattern via a PCRE expression.



/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_pcre=now



drizzle> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-07-22 03:24:32 |
+---------------------+
1 row in set (0 sec)

drizzle> select curdate();
+------------+
| curdate()  |
+------------+
| 2009-07-22 |
+------------+
1 row in set (0 sec)

drizzle> select "now";
+-----+
| now |
+-----+
| now |
+-----+
1 row in set (0 sec)

drizzle> select "know how";
+----------+
| know how |
+----------+
| know how |
+----------+
1 row in set (0 sec)



1248233072792211,3,2,"","select now()","Query",1248233072792211,154,154,1,00,0
1248233085807520,3,4,"","select "now"","Query",1248233085807520,92,92,1,00,0
1248233096659018,3,5,"","select "know how"","Query",1248233096659018,75,75,1,00,0



Another example using a pattern.


/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_pcre="[0-9][0-9][0-9]"



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

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

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

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

drizzle> select 11+22;
+-------+
| 11+22 |
+-------+
|    33 |
+-------+
1 row in set (0 sec)



1248233336460373,3,4,"","select 111","Query",1248233336460373,79,79,1,00,0
1248233339300429,3,5,"","select 1111","Query",1248233339300429,82,82,1,00,0



Unfortunately it seems that this variable is also not configurable dynamically at this time.


drizzle> set global logging_query_pcre="now";
ERROR 1238 (HY000): Variable 'logging_query_pcre' is a read only variable



This is definitely an improvement over current MySQL logging.


An important Drizzle/MySQL difference
 

 by   
There are many features that are similar in MySQL and Drizzle. There are also many that are not.


I’ve previously discussed topics like Datatypes and tables, SQL_MODE and SHOW.


A key difference in Drizzle is the definition of utf8 as 4 bytes, not 3 bytes as in MySQL. This combined with no other character sets leads to an impact on the length in keys supported in Innodb.


During a recent test with a client, I was unable to successfully migrated the schema and provide the same schema due to unique indexes defined for utf8 VARHAR(255) fields.


Here is the problem.


mysql> create table t1(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t2(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t3(c1 int unsigned not null auto_increment primary key, c2 varchar(256) not null, unique key (c2)) engine=innodb default charset utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes



drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
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 'charset latin1' at line 1
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes



Only a maximum of 191 is now possible.


drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(191) not null, unique key (c2)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(192) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes



Benchmarking Drizzle with MyBench(DBD::drizzle)
 

 by   
With thanks to  Patrick Galbraith and his DBD::drizzle 0.200 I am now able to test client benchmarks side by side with MySQL and Drizzle.


For simple benchmarking with clients, generally when I have little time, I use a simple Perl framework mybench. I was able to change just the connection string and run tests.


The diff of my two scripts where:


---
> my $user      = $opt{u} || "appuser";
> my $pass      = $opt{p} || "password";
> my $port      = $opt{P} || 3306;
> my $dsn       = "DBI:mysql:$db:$host;port=$port";
---
< my $user      = $opt{u} || "root";
< my $pass      = $opt{p} || "";
< my $port      = $opt{P} || 4427;
< my $dsn       = "DBI:drizzle:$db:$host;port=$port";
---



It's too early to tell what improvement Drizzle will make. Just running my first test with single and multi thread tests shows an improvement in all figures in Drizzle via MySQL, however I will need to run this on various different versions of MySQL including the latest 5.0 to confirm.


Problems compiling MySQL 5.4
 

 by   
Seem’s the year Sun had for improving MySQL, and with an entire new 5.4 branch the development team could not fix the autoconf and compile dependencies that has been in MySQL for all the years I’ve been compiling MySQL.  Drizzle has got it right, thanks to the great work of Monty Taylor.


I’m working on the Wafflegrid AWS EC2 AMI’s for Matt Yonkovit and while compiling 5.1 was straight forward under Ubuntu 8.10 Intrepid, compiling 5.4 was more complicated.


For MySQL 5.1 I needed only to do the following:


apt-get install -y build-essential
apt-get install libncurses5-dev
./configure
make
make install



For MySQL 5.4, I elected to use the BUILD scripts (based on Wafflegrid recommendations).  That didn’t go far before I needed.


apt-get install -y automake libtool



You then have to go compiling MySQL 5.4 for 10+ minutes to get an abstract error, then you need to consider what dependencies may be missing.

I don’t like to do a blanket apt-get of a long list of proposed packages unless I know they are actually needed.


The error was:


make[1]: Entering directory `/src/mysql-5.4.0-beta/sql'
make[1]: warning: -jN forced in submake: disabling jobserver mode.
/bin/bash ../ylwrap sql_yacc.yy y.tab.c sql_yacc.cc y.tab.h sql_yacc.h y.output sql_yacc.output -- -d --verbose
make -j 6 gen_lex_hash
make[2]: Entering directory `/src/mysql-5.4.0-beta/sql'
rm -f mini_client_errors.c
/bin/ln -s ../libmysql/errmsg.c mini_client_errors.c
make[2]: warning: -jN forced in submake: disabling jobserver mode.
rm -f pack.c
../ylwrap: line 111: -d: command not found
/bin/ln -s ../sql-common/pack.c pack.c
....
make[1]: Leaving directory `/src/mysql-5.4.0-beta/sql'
make: *** [all-recursive] Error 1



What a lovely error  ../ylwrap: line 111: -d: command not found


ylwrap is part of yacc, and by default in this instance it’s not even an installed package. I’ve compiled MySQL long enough that it requires yacc, and actually bison but to you think it would hurt if the configure told the user this.


It’s also been some time since I’ve compiled MySQL source, rather focusing on Drizzle.  I had forgotten just how many compile warnings MySQL throws. Granted a warning is not an error, but you should not just ignore them in building a quality product.


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)



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;



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.


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.


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.


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.


Understanding the various MySQL Products & Variants
 

 by   
The MySQL marketplace today is far more complex then simply choosing between a particular version of MySQL that Sun/MySQL produces.

The MySQL server product in general is released under the GNU General Public License (GPL) v2, however you should carefully review the MySQL Legal Policies as a number of exceptions and different license agreements operate for companion tools such as MySQL Cluster, MySQL client libraries and documentation for example. 


Looking into the MySQL ecosystem for products, I’ve produced the following categories:


    

  • Sun/MySQL Official Products

      

    • MySQL Versions
      • 

    

    • 

  • MySQL Variants

      

    • Community
      • 

    • Enterprise
      • 

    

    • 

  • MySQL Plugins
    • 

  • MySQL Patches
    • 

  • MySQL Alternatives
    • 




Why does such a diversification occur?  I attribute this to three primary causes:


    

  • The GPL license by nature allows for an organization to take the product, modify it and use it for their specific needs. They can also provide these patches under GPL for others to use and incorporate. While this has occurred for example  Google , FaceBook, eBay , Proven Scaling and Percona to name a few, Sun/MySQL has elected not to undertake any proactive process of incorporating these in any timely fashion.

    • 

  • The policy of Sun/MySQL to allow for contributions was so strict, and combined with a properietory Version Control System BitKeeper you had to purchase, there was little incentive for community contributions in relation to so many other open source projects
    • 

  •  The Sun/MySQL management and decision makers didn’t listen to the community and paying customers, and over the past 3-5 years the product life cycle, features, release schedule and quality can be questioned. 
    • 



Sun/MySQL Official Products



Sun/MySQL holds the license to the MySQL server products. They release official binaries and the source code (due to GPL).  Even within MySQL, there are several products that differ subtly and to the untrained eye it can be confusing to understand and determine what is best. Your can download from www.mysql.com the following versions:


    

  • MySQL Server 5.1  GA
    • 

  • MySQL Community Server 5.0 GA
    • 

  • MySQL Enterprise Server 5.0 GA
    • 

  • MySQL Cluster NDB 6.3
    • 





    

  • MySQL Server 4.1 (EOL)
    • 

  • MYSQL Server 6.0 (Alpha)
    • 



MySQL Versions


It is important that you understand the MySQL Versions, especially in evaluating any of the following referenced variants, patches etc.

The common path for MySQL Server versions is with a generally linear numbering systems including historical versions 3.23, 4.0 and 4.1.  These versions have now reached End Of Life (EOL) for support, however emergency security patches are applied where necessary.

Continuing from 4.1, you have the 5.0, 5.1 versions which are both Generally Available (GA), and then version 6.0 which is currently Alpha.


Further complexity happens when within the Sun/MySQL Official products, several forks/branches have occurred.  These include:


    

  • The MySQL 5.0 Community & Enterprise split occurred at MySQL Version 5.0.27

    • 

  • At this time, the Community version (free to download) continued with the intention of allowing for community contributions. Only one patch was ever accepted, and SHOW PROFILES was introduced in  MySQL 5.0.37.  To date, 11 versions have been released to the current 5.0.77 version.

    • 

  • MySQL Enterprise (available under subscription) is itself comprised of three subtypes, these are Rapid Update Service Packs(monthly), Quarterly Service Packs (quarterly) and Hot-fix releases.  To date 37 versions have been released to the current 5.0.78 version.
    

    • 

  • MySQL Cluster, was part of the MySQL Server product until this was branched/forked at  MySQL Version 5.1.23. This enabled MySQL Cluster to be labeled as Production Ready for Cluster clients, and not be held back by continued delays in the 5.1 server release.   Starting with a new versioning scheme with 6.1, the MySQL Cluster NDB produces new versions far exceeding the volume of the server, with to date 23 versions in 6.1 , 18 in version 6.2 and 24 in version 6.3.      I am not advocating that features and quality are better or worse, simply that activity and interaction with community and users is far greater.

    • 

  • MySQL 5.1 Maria is a special branch starting at MySQL Version 5.1.24 that includes the Maria Storage Engine. This is the next generation of the MyISAM Storage Engine, both architected by the creator of MySQL, Monty Widenius.  It is undercertain this will continue as a product released officially by Sun/MySQL.

    • 



In Review


With just reading this introduction you can understand the confusion that exists when new customers/clients are beginning to evaluate the different MySQL Versions.


In my next post, I’ll talk more about:


    

  • MySQL Variants, those I consider variants use the MySQL Interface, protocol and support the standard connectors.  These include community versions (e.g. Solid, Infobright, Sphinx) and commercial versions (e.g. KickFire, InfoBright, Nitro).   
    • 

  • MySQL patches are improvements that have been released to the community and are now becoming part of common third party MySQL packages, such as Percona, Proven Scaling and Out Delta
    • 

  • MySQL Plugins are a feature of MySQL 5.1, and allow for pluggable storage engines into MySQL.  While several companies have had to produce custom binaries due to the API limitions (especially with the optimizer), a number of engines support the API including Innodb, PBXT and filesystem engine.
    • 

  • MySQL Alternatives include any MySQL related products that have now deviated from being supported under the MySQL protocol.  Most notably here is Drizzle.
    • 



More Information




Ronald Bradford is Principal at 42SQL. We provide consulting and advisory services for the MySQL ecosystem with a focus on MySQL database performance, architecture and scalability. 42SQL also provides education in MySQL including the “MySQL Essentials” training course.  You can find more information regarding this offering and an upcoming schedule at 42SQL Education.


Where is the innovation?
 

 by   
The 2009 MySQL Conference has closed it’s submissions for papers.  This year the motto is “Innovation Everywhere”.


Last weekend’s Open SQL Camp in Charlottesville, Virginia, we had the chance to talk about the movements in the MySQL ecosystem.  I was impressed to get the details of the Percona MySQL Patches, but focus is still in 5.0. (Welcome to the Percona team Tom Basil)  Our Delta is attempting now to integrate patches into various MySQL branches.  There was an opening keynote by Brian Aker from  Drizzle, and Drizzle team Jay Pipes and Stewart Smith on hand. It was also announced that MySQL 5.1.30 will be GA, available in early December.


But these are not innovations that are ground breaking.  Last year, it was the announcement of KickFire that I found most intriguing regarding innovation.


What is there this year?.  The most interesting thing I read last week was Memcached as a L2 Cache for Innodb – The Waffle Grid Project.  This is my kind of innovation.  It’s sufficiently MySQL, but just adds another dimension with another companion technology.  The patch seems relatively simple in concept and code size, and I’m almost prepared to fire up a few EC2’s to take this one for a spin.  I’m doubly impressed because the creators are two friends and colleagues that are not hard core kernel hackers, but professionals on the front line dealing with clients daily.  Will it be successful, or viable? That is the question about innovation.


Unfortunately I spend more time these days not seeing innovation in MySQL, but in other alternative database solutions in general.  Projects like Clustrix, Inc.,   LucidDB, and Mongo in the 10gen stack.


Get linked to Drizzle
 

 by   
We are always looking at different ways to help promote, inform and identify contributers, users and supports for Drizzle.


One way is to join the Linked In Drizzle group  (click here  when logged in).  You will already see a few MySQL die hards, but also the need breed of names that are part of Drizzlemania.