Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround.

Here it is. Create 2 procedures as following:

DROP PROCEDURE IF EXISTS raise_application_error;
DROP PROCEDURE IF EXISTS get_last_custom_error;
DROP TABLE IF EXISTS RAISE_ERROR;

DELIMITER $$
CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);

  SELECT CODE, MESSAGE INTO @error_code, @error_message;
  INSERT INTO RAISE_ERROR VALUES(NULL);
…

I didn’t take many photos at the Drizzle Meeting, although I did take a couple at the end at the Hopvine (just down the road from Brian’s place).

A good read is Brian’s wrap up of the meeting.

But we have (courtesy of Brian):

• Drizzle/Gearman/Memcached authors photo
• Jay’s “fo’ Drizzle” shirt
• Drizzle developers hard at work

and a couple I took at the Hopvine:

…

I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.

First of all, a look at some output from the script:

mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38   throughput 3 queries/sec
2009 Sep 17 16:28:41   throughput 4 queries/sec
2009 Sep 17 16:28:44   throughput 528 queries/sec
2009 Sep 17 16:28:47   throughput 1603 queries/sec
2009 Sep 17 16:28:50   throughput 1676 queries/sec
^C
Tracing ended   at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us): 
           value  ------------- Distribution ------------- count    
              16 |                                         0        
              32 |@@                                       170      
              64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ …

Mark writes Now we all need the storage-engine independent test suite. I could not agree more. I have made comments about this probably as early as 4 years ago, and both before and while working for MySQL Inc.

There is however a way to do it with the current mysql-test syntax. While not ideal, it does actually work.
It took me like an hour to dig though old, old backup code, but I found it.

The Test Case:

$ cat t/engine_agnostic.test
CREATE TABLE i(id INT UNSIGNED NOT NULL);
let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`;
--replace_result $ENGINE ENGINE
SHOW CREATE TABLE i;

The Test Result:

cat r/engine_agnostic.result
CREATE TABLE i(id INT UNSIGNED NOT NULL);
SHOW CREATE TABLE i;
Table   Create Table
i       CREATE TABLE `i` (
  `id` int(10) unsigned …

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2 describes installing MySQL. You can also download a copy of the manual in …

This works:

mysql> show variables like "innodb_version"\G
*************************** 1. row ***************************
Variable_name: innodb_version
        Value: 1.0.4

This does not because it always reports version 1.0 for PLUG_VERSION, at least until InnoDB plugin 2.0 arrives.

mysql> select * from plugins where PLUGIN_NAME="InnoDB"\G
*************************** 1. row ***************************
           PLUGIN_NAME: InnoDB
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 50138.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Innobase Oy
    PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys
        PLUGIN_LICENSE: GPL

This seems quite a trivial question, but developers don’t often know what a MySQL storage engine is and how to determine what storage engine is used for a table.

The first choice is to describe the table with the DESC[RIBE] command. Side Note: people often don’t realize that DESC is a short acceptable version here.

mysql> desc stats;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| stat_id | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| created | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| version | tinyint(3) unsigned | NO   |     | NULL              |                |
| referer | varchar(500) …

I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.

First of all, a look at some output from the script:

mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38   throughput 3 queries/sec
2009 Sep 17 16:28:41   throughput 4 queries/sec
2009 Sep 17 16:28:44   throughput 528 queries/sec
2009 Sep 17 16:28:47   throughput 1603 queries/sec
2009 Sep 17 16:28:50   throughput 1676 queries/sec
\^C
Tracing ended   at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us): 
           value  ------------- Distribution ------------- count    
              16 |                                         0        
              32 |@@                                       170      
              64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ …

I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.

First of all, a look at some output from the script:

mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38   throughput 3 queries/sec
2009 Sep 17 16:28:41   throughput 4 queries/sec
2009 Sep 17 16:28:44   throughput 528 queries/sec
2009 Sep 17 16:28:47   throughput 1603 queries/sec
2009 Sep 17 16:28:50   throughput 1676 queries/sec
\^C
Tracing ended   at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us): 
           value  ------------- Distribution ------------- count    
              16 |                                         0        
              32 |@@                                       170      
              64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ …

Installing Cherokee With PHP5 And MySQL Support On Fedora 11

Cherokee is a very fast, flexible and easy to configure Web Server. It supports the widespread technologies nowadays: FastCGI, SCGI, PHP, CGI, TLS and SSL encrypted connections, virtual hosts, authentication, on the fly encoding, load balancing, Apache compatible log files, and much more. This tutorial shows how you can install Cherokee on a Fedora 11 server with PHP5 support (through FastCGI) and MySQL support.