Overview

Dynamic columns allow one to store different sets of columns for each row in a table. It works by storing a set of columns in a blob and having a small set of functions to manipulate it.

Dynamic columns should be used when it is not possible to use regular columns.

A typical use case is when one needs to store items that may have many different attributes (like size, color, weight, etc), and the set of possible attributes is very large and/or unknown in advance. In that case, attributes can be put into dynamic columns.

Dynamic Columns Basics

The table should have a blob column which will be used as storage for dynamic columns:

main
CREATE TABLE assets (
  item_name VARCHAR(32) PRIMARY KEY, -- A common attribute for all items
  dynamic_cols  BLOB  -- Dynamic columns will be stored here
);

Once created, you can access dynamic columns via dynamic column functions.

Insert a row with two dynamic columns: color=blue, size=XL:

INSERT INTO assets VALUES 
  ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

Insert another row with dynamic columns: color=black, price=500:

INSERT INTO assets VALUES
  ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

Select the dynamic column 'color' for all items:

SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) 
  AS color FROM assets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+

It is possible to add and remove dynamic columns from a row:

-- Remove a column:
UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") 
WHERE COLUMN_GET(dynamic_cols, 'color' AS CHAR)='black'; 

-- Add a column:
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';

You can also list all columns, or get them together with their values in JSON format:

SELECT item_name, column_list(dynamic_cols) FROM assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB T-shirt | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+

SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;
+-----------------+----------------------------------------+
| item_name       | COLUMN_JSON(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Dynamic Columns Reference

This is a complete reference of dynamic columns in MariaDB.

Dynamic Columns Functions

COLUMN_CREATE

COLUMN_CREATE(column_name, value [as type], [column_name, value 
  [as type]]...);

Returns a dynamic columns blob that stores the specified columns with values. The return value is suitable for

• storing in a table,

• further modification with other dynamic columns functions.

The as type part allows one to specify the value type. In most cases, this is redundant because MariaDB will be able to deduce the type of the value. Explicit type specification may be needed when the type of the value is not apparent. For example, a literal '2012-12-01' has a CHAR type by default, one will need to specify '2012-12-01' AS DATE to have it stored as a date. See the Datatypes section for further details. Note also MDEV-597.

Typical usage:

INSERT INTO tbl SET dyncol_blob=COLUMN_CREATE("column_name", "value");

COLUMN_ADD

COLUMN_ADD(dyncol_blob, column_name, value [as type], 
  [column_name, value [as type]]...);

Adds or updates dynamic columns.

• dyncol_blob must be either a valid dynamic columns blob (for example, COLUMN_CREATE returns such blob), or an empty string.

• column_name specifies the name of the column to be added. If dyncol_blob already has a column with this name, it will be overwritten.

• value specifies the new value for the column. Passing a NULL value will cause the column to be deleted.

• as type is optional. See the datatypes section for a discussion about types.

The return value is a dynamic column blob after the modifications.

Typical usage:

UPDATE t1 SET dyncol_blob=COLUMN_ADD(dyncol_blob, "column_name", "value") 
  WHERE id=1;

Note: COLUMN_ADD() is a regular function (just likeCONCAT()), hence, in order to update the value in the table you have to use the UPDATE ... SET dynamic_col=COLUMN_ADD(dynamic_col, ....) pattern.

COLUMN_GET

COLUMN_GET(dyncol_blob, column_name as type);

Retrieves the value of a dynamic column by its name. If no column with the given name exists, NULL is returned.

column_name as type requires that one specify the datatype of the dynamic column they are reading.

This may seem counter-intuitive: Why would you need to specify which datatype they're retrieving? Can't the dynamic columns system figure the datatype from the data being stored?

The answer is: SQL is a statically-typed language. The SQL interpreter needs to know the datatypes of all expressions before the query is run (for example, when one is using prepared statements and runs"select COLUMN_GET(...)", the prepared statement API requires the server to inform the client about the datatype of the column being read before the query is executed and the server can see what datatype the column actually has).

See the Datatypes section for more information about datatypes.

COLUMN_DELETE

COLUMN_DELETE(dyncol_blob, column_name, column_name...);

Deletes a dynamic column with the specified name. Multiple names can be given.

The return value is a dynamic column blob after the modification.

COLUMN_EXISTS

COLUMN_EXISTS(dyncol_blob, column_name);

Checks if a column with name column_name exists in dyncol_blob. If yes, return 1, otherwise return 0.

COLUMN_LIST

COLUMN_LIST(dyncol_blob);

Returns a comma-separated list of column names. The names are quoted with backticks.

SELECT column_list(column_create('col1','val1','col2','val2'));
+---------------------------------------------------------+
| column_list(column_create('col1','val1','col2','val2')) |
+---------------------------------------------------------+
| `col1`,`col2`                                           |
+---------------------------------------------------------+

COLUMN_CHECK

COLUMN_CHECK(dyncol_blob);

Checks if dyncol_blob is a valid packed dynamic columns blob. A return value of 1 means the blob is valid, a return value of 0 means it is not.

Rationale: Normally, you work with valid dynamic column blobs. Functions likeCOLUMN_CREATE, COLUMN_ADD, COLUMN_DELETE always return valid dynamic column blobs. However, if a dynamic column blob is accidentally truncated, or transcoded from one character set to another, it is corrupted. This function can be used to check if a value in a blob field is a valid dynamic column blob.

COLUMN_JSON

COLUMN_JSON(dyncol_blob);

Returns a JSON representation of data in dyncol_blob :

SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;
+-----------------+----------------------------------------+
| item_name       | COLUMN_JSON(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Limitation: COLUMN_JSON decodes nested dynamic columns at a nesting level of not more than 10 levels deep. Dynamic columns that are nested deeper than 10 levels are shown as a BINARY string, without encoding.

Nesting Dynamic Columns

It is possible to use nested dynamic columns by putting one dynamic column blob inside another. The COLUMN_JSON function will display nested columns.

SET @tmp= column_create('parent_column', 
  column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

SELECT column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+

SELECT column_get(column_get(@tmp, 'parent_column' AS char), 
  'child_column' AS int);
+------------------------------------------------------------------------------+
| column_get(column_get(@tmp, 'parent_column' as char), 'child_column' as int) |
+------------------------------------------------------------------------------+
|                                                                        12345 |
+------------------------------------------------------------------------------+

If you are trying to get a nested dynamic column as a string, use AS BINARY as the last argument of COLUMN_GET . Otherwise, problems with character set conversion and illegal symbols are possible:

SELECT column_json( column_get(
  column_create('test1', 
    column_create('key1','value1','key2','value2','key3','value3')),
  'test1' AS BINARY));

Datatypes

In SQL, one needs to define the type of each column in a table. Dynamic columns do not provide any way to declare a type in advance ("whenever there is a column 'weight', it should be integer" is not possible). However, each particular dynamic column value is stored together with its datatype.

The set of possible datatypes is mostly the same as that used by the CAST and CONVERT functions. However, note that there are currently some differences - see MDEV-597.

A Note About Lengths

If you're running queries without specifying a maximum length (i.e. using #as CHAR#, not as CHAR(n)), MariaDB reports the maximum length of the result set column to be53,6870,911 (bytes or characters?). This may cause excessive memory usage in some client libraries, because they try to pre-allocate a buffer of maximum result set width. If you suspect you're hitting this problem, use CHAR(n) whenever you're using COLUMN_GET in the select list.

SELECT COLUMN_GET(blob, 'colname' as CHAR) ...

Client-side API

It is also possible to create or parse dynamic columns blobs on the client side. libmysql client library now includes an API for writing/reading dynamic column blobs. See dynamic-columns-api for details.

Limitations

See Also

• Dynamic Columns

_{This page is licensed: CC BY-SA / Gnu FDL}

This page describes the client-side API for reading and writing Dynamic Columns blobs.

Normally, you should use Dynamic column functions which are run inside the MariaDB server and allow one to access Dynamic Columns content without any client-side libraries.

If you need to read/write dynamic column blobs on the client for some reason, this API enables that.

Where to get it

The API is a part of libmysql C client library. In order to use it, you need to include this header file and link against libmysql:

#include <mysql/ma_dyncol.h>

Data structures

DYNAMIC_COLUMN

DYNAMIC_COLUMN represents a packed dynamic column blob. It is essentially a string-with-length and is defined as follows:

/* A generic-purpose arbitrary-length string defined in MySQL Client API */
typedef struct st_dynamic_string
{
  char *str;
  size_t length,max_length,alloc_increment;
} DYNAMIC_STRING;

...

typedef DYNAMIC_STRING DYNAMIC_COLUMN;

DYNAMIC_COLUMN_VALUE

Dynamic columns blobs store {name, value} pairs. The DYNAMIC_COLUMN_VALUE structure is used to represent the value in accessible form.

struct st_dynamic_column_value
{
  DYNAMIC_COLUMN_TYPE type;
  union
  {
    long long long_value;
    unsigned long long ulong_value;
    double double_value;
    struct {
      MYSQL_LEX_STRING value;
      CHARSET_INFO *charset;
    } string;
    struct {
      decimal_digit_t buffer[DECIMAL_BUFF_LENGTH];
      decimal_t value;
    } decimal;
    MYSQL_TIME time_value;
  } x;
};
typedef struct st_dynamic_column_value DYNAMIC_COLUMN_VALUE;

Every value has a type, which is determined by the type member.

Notes

• Values with type DYN_COL_NULL do not ever occur in dynamic columns blobs.

• Type DYN_COL_DYNCOL means that the value is a packed dynamic blob. This is how nested dynamic columns are done.

• Before storing a value to value.x.decimal.value, you must call mariadb_dyncol_prepare_decimal() to initialize the space for storage.

enum_dyncol_func_result

enum enum_dyncol_func_result is used as return value.

Result codes that are less than zero represent error conditions.

Function reference

Functions come in pairs:

• xxx_num() operates on the old (pre-MariaDB-10.0.1) dynamic column blob format, where columns were identified by numbers.

• xxx_named() can operate on both old or new data format. If it modifies the blob, it converts it to the new data format.

You should use xxx_named() functions, unless you need to keep the data compatible with MariaDB versions before 10.0.1.

mariadb_dyncol_init

First, define mariadb_dyncol_init(A) memset((A), 0, sizeof(*(A))). It is the correct initialization for an empty packed dynamic blob.

mariadb_dyncol_free

Copy where str is IN. Packed dynamic blob which memory should be freed.

void mariadb_dyncol_free(DYNAMIC_COLUMN *str);

mariadb_dyncol_create_many

Create a packed dynamic blob from arrays of values and names.

enum enum_dyncol_func_result
mariadb_dyncol_create_many(DYNAMIC_COLUMN *str,
                           uint column_count,
                           uint *column_numbers,
                           DYNAMIC_COLUMN_VALUE *values,
                           my_bool new_string);
enum enum_dyncol_func_result
mariadb_dyncol_create_many_named(DYNAMIC_COLUMN *str,
                                 uint column_count,
                                 MYSQL_LEX_STRING *column_keys,
                                 DYNAMIC_COLUMN_VALUE *values,
                                 my_bool new_string);

Here are the names and values:

mariadb_dyncol_update_many

Add or update columns in a dynamic columns blob. To delete a column, update its value to a "non-value" of type DYN_COL_NULL :

enum enum_dyncol_func_result
mariadb_dyncol_update_many(DYNAMIC_COLUMN *str,
                           uint column_count,
                           uint *column_numbers,
                           DYNAMIC_COLUMN_VALUE *values);
enum enum_dyncol_func_result
mariadb_dyncol_update_many_named(DYNAMIC_COLUMN *str,
                                 uint column_count,
                                 MYSQL_LEX_STRING *column_keys,
                                 DYNAMIC_COLUMN_VALUE *values);

mariadb_dyncol_exists

Check if column with given names exist in the blob:

enum enum_dyncol_func_result
mariadb_dyncol_exists(DYNAMIC_COLUMN *str, uint column_number);
enum enum_dyncol_func_result
mariadb_dyncol_exists_named(DYNAMIC_COLUMN *str, MYSQL_LEX_STRING *column_key);

The function returns YES, or NO or Error code.

mariadb_dyncol_column_count

Get the number of columns in a dynamic column blob:

enum enum_dyncol_func_result
mariadb_dyncol_column_count(DYNAMIC_COLUMN *str, uint *column_count);

mariadb_dyncol_list

List columns in a dynamic column blob:

enum enum_dyncol_func_result
mariadb_dyncol_list(DYNAMIC_COLUMN *str, uint *column_count, uint **column_numbers);
enum enum_dyncol_func_result
mariadb_dyncol_list_named(DYNAMIC_COLUMN *str, uint *column_count,
                          MYSQL_LEX_STRING **column_keys);

mariadb_dyncol_get

Get a value of one column:

enum enum_dyncol_func_result
mariadb_dyncol_get(DYNAMIC_COLUMN *org, uint column_number,
                   DYNAMIC_COLUMN_VALUE *value);
enum enum_dyncol_func_result
mariadb_dyncol_get_named(DYNAMIC_COLUMN *str, MYSQL_LEX_STRING *column_key,
                         DYNAMIC_COLUMN_VALUE *value);

If the column is not found, NULL is returned as the value of the column.

mariadb_dyncol_unpack

Get the value of all columns:

enum enum_dyncol_func_result
mariadb_dyncol_unpack(DYNAMIC_COLUMN *str,
                      uint *column_count,
                      MYSQL_LEX_STRING **column_keys,
                      DYNAMIC_COLUMN_VALUE **values);

mariadb_dyncol_has_names

Check whether the dynamic columns blob uses the new data format (the one where columns are identified by names):

my_bool mariadb_dyncol_has_names(DYNAMIC_COLUMN *str);

mariadb_dyncol_check

Check whether the dynamic column blob has the correct data format:

enum enum_dyncol_func_result
mariadb_dyncol_check(DYNAMIC_COLUMN *str);

mariadb_dyncol_json

Get contents of a dynamic columns blob in a JSON form:

enum enum_dyncol_func_result
mariadb_dyncol_json(DYNAMIC_COLUMN *str, DYNAMIC_STRING *json);

mariadb_dyncol_val_TYPE

Get the dynamic column value as one of the base types:

enum enum_dyncol_func_result
mariadb_dyncol_val_str(DYNAMIC_STRING *str, DYNAMIC_COLUMN_VALUE *val,
                       CHARSET_INFO *cs, my_bool quote);
enum enum_dyncol_func_result
mariadb_dyncol_val_long(longlong *ll, DYNAMIC_COLUMN_VALUE *val);
enum enum_dyncol_func_result
mariadb_dyncol_val_double(double *dbl, DYNAMIC_COLUMN_VALUE *val);

mariadb_dyncol_prepare_decimal

Initialize DYNAMIC_COLUMN_VALUE before setting the value of value.x.decimal.value:

void mariadb_dyncol_prepare_decimal(DYNAMIC_COLUMN_VALUE *value);

This function links value.x.decimal.value to value.x.decimal.buffer.

mariadb_dyncol_value_init

Initialize a DYNAMIC_COLUMN_VALUE structure to a safe default:

#define mariadb_dyncol_value_init(V) (V)->type= DYN_COL_NULL

mariadb_dyncol_column_cmp_named

Compare two column names (column names are compared with memcmp()):

int mariadb_dyncol_column_cmp_named(const MYSQL_LEX_STRING *s1,
                                    const MYSQL_LEX_STRING *s2);

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

HANDLER tbl_name OPEN [ [AS] alias]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE

Description

The HANDLER statement provides direct access to table storage engine interfaces for key lookups and key or table scans. It is available for at least Aria, Memory, MyISAM and InnoDB tables (and should work with most 'normal' storage engines, but not with system tables, MERGE or views).

HANDLER ... OPEN opens a table, allowing it to be accessible to subsequent HANDLER ... READ statements. The table can either be opened using an alias, or a table name. If opened with an alias, references to this table by further HANDLER statements must use this alias, and not the table name. If opened with a table name qualified by database name, further references to this table must use the unqualified table name. For example, if a table is opened with db1.t1, further references must use t1.

The table object is only closed when HANDLER ... CLOSE is called by the session, or the session closes, and is not shared by other sessions.

Prepared statements work with HANDLER READ, which gives a much higher performance (50% speedup) as there is no parsing and all data is transformed in binary (without conversions to text, as with the normal protocol).

The HANDLER command does not work with partitioned tables.

Key Lookup

A key lookup is started with:

HANDLER tbl_name READ index_name { = | >= | <= | < }  (value,value) [LIMIT...]

The values stands for the value of each of the key columns. For most key types, except for HASH keys in MEMORY storage engine, you can use a prefix subset of its columns.

If you are using LIMIT, then in case of >= or > then there is an implicit NEXT implied, while if you are using <= or < then there is an implicit PREV implied.

After the initial read, you can use the following to scan rows in key order:

HANDLER tbl_name READ index_name NEXT [ LIMIT ... ]
or
HANDLER tbl_name READ index_name PREV [ LIMIT ... ]

Key Scans

You can scan a table in key order by doing this:

HANDLER tbl_name READ index_name FIRST [ LIMIT ... ]
HANDLER tbl_name READ index_name NEXT  [ LIMIT ... ]

Alternatively, if the handler supports backwards key scans (which most do), you can use this:

HANDLER tbl_name READ index_name LAST [ LIMIT ... ]
HANDLER tbl_name READ index_name PREV [ LIMIT ... ]

Table Scans

You can scan a table in row order by doing this:

HANDLER tbl_name READ FIRST [ LIMIT ... ]
HANDLER tbl_name READ NEXT  [ LIMIT ... ]

Limitations

As this is a direct interface to the storage engine, some limitations may apply for what you can do and what happens if the table changes. Here are some of the common limitations.

Finding 'Old Rows'

HANDLER READ is not transaction-safe, consistent or atomic. It's okay for the storage engine to return rows that existed when you started the scan, but that were later deleted. This can happen as the storage engine may cache rows as part of the scan from a previous read.

You may also find rows committed since the scan originally started.

Invisible Columns

HANDLER ... READ also reads the data of invisible columns.

System-Versioned Tables

HANDLER ... READ reads everything from system-versioned tables, and so includes row_start and row_end fields, as well as all rows that have since been deleted or changed, including when history partitions are used.

Other Limitations

• If you do an ALTER TABLE, all your HANDLERs for that table are automatically closed.

• If you do an ALTER TABLE for a table that is used by some other connection with HANDLER, the ALTER TABLE query waits for the HANDLER to be closed.

• For HASH keys, you must use all key parts when searching for a row.

• For HASH keys, you can't do a key scan of all values. You can only find all rows with the same key value.

• While each HANDLER READ command is atomic, if you do a scan in many steps, some engines may give you error 1020 if the table changed between the commands. Please refer to the specific engine handler page if this happens.

Error Codes

• Error 1031 (ER_ILLEGAL_HA) Table storage engine for 't1' doesn't have this option

  • If you get this for HANDLER OPEN it means the storage engine doesn't support HANDLER calls.

  • If you get this for HANDLER READ , it means you are trying to use an incomplete HASH key.

• Error 1020 (ER_CHECKREAD) Record has changed since last read in table '...'

  • This means that the table changed between two reads, and the handler can't handle this case for the given scan.

Examples

CREATE TABLE t1 (f1 INT);

INSERT INTO t1 VALUES (1),(2),(3);

HANDLER t1 OPEN;

HANDLER t1 READ NEXT;
+------+
| f1   |
+------+
|    1 |
+------+

HANDLER t1 READ NEXT;
+------+
| f1   |
+------+
|    2 |
+------+

In the previous example, the HANDLER was opened with the t1 table name. Since HANDLERs use unqualified table names, trying to access another table with this same name, even though it's in another database, will result in ambiguity. An alias needs to be used to avoid the ambiguity, resulting in Error 1066: Not unique table/alias:

CREATE DATABASE db_new;

CREATE TABLE db_new.t1 (id INT);

INSERT INTO db_new.t1 VALUES (4),(5),(6);

HANDLER db_new.t1 OPEN;
ERROR 1066 (42000): Not unique table/alias: 't1'

HANDLER db_new.t1 OPEN AS db_new_t1;

HANDLER db_new_t1 READ NEXT LIMIT 3;
+------+
| id   |
+------+
|    4 |
|    5 |
|    6 |
+------+

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}

This article explains how to use HANDLER commands efficiently with MEMORY/HEAP tables.

If you want to scan a table for different key values, not just search for exact key values, you should create your keys with USING BTREE:

CREATE TABLE t1 (a INT, b INT, KEY(a), KEY b USING BTREE (b)) ENGINE=memory;

In the above table, a is a HASH key that only supports exact matches (=) while b is a BTREE key that you can use to scan the table in key order, starting from start or from a given key value.

The limitations for HANDLER READ with MEMORY|HEAP tables are:

Limitations for HASH keys

• You must use all key parts when searching for a row.

• You can't do a key scan of all values. You can only find all rows with the same key value.

• READ NEXT gives an error 1031 if the tables changed since last read.

Limitations for BTREE keys

• READ NEXT gives an error 1031 if the tables changed since last read. This limitation can be lifted in the future.

Limitations for table scans

• READ NEXT gives an error 1031 if the table was truncated since last READ call.

See also

See also the limitations listed in HANDLER commands.

_{This page is licensed: CC BY-SA / Gnu FDL}

After MariaDB is installed, use the INSTALL PLUGIN command (as the root user) to install the HandlerSocket plugin. This command only needs to be run once, like so:

INSTALL PLUGIN handlersocket SONAME 'handlersocket.so';

After installing the plugin, SHOW PROCESSLIST shows you first need to configure some settings. All HandlerSocket configuration options are placed in the [mysqld] section of your my.cnf file.

At least the handlersocket_address, handlersocket_port and handlersocket_port_wr options need to be set:

handlersocket_address="127.0.0.1"
handlersocket_port="9998"
handlersocket_port_wr="9999"

After updating the configuration options, restart MariaDB.

On the client side, to make use of the plugin you will need to install the appropriate client library (that is, libhsclient for C++ applications and perl-Net-HandlerSocket for perl applications).

_{This page is licensed: CC BY-SA / Gnu FDL}

In order to make use of the HandlerSocket plugin in your applications, you will need to use the appropriate client library. The following client libraries are available:

• C++

  • libhsclient (included with the HandlerSocket plugin source)

• Perl

  • perl-Net-HandlerSocket (included with the HandlerSocket plugin source)

• PHP

  • Net_HandlerSocket

  • HSPHP

  • php-ext-handlersocketi

• Java

  • hs4j

  • handlersocketforjava

• Python

  • python-handler-socket

  • pyhandlersocket

• Ruby

  • ruby-handlersocket

  • handlersocket

• JavaScript

  • node-handlersocket

• Scala

  • hs2client

• Haskell

  • HandlerSocket-Haskell-Client

_{This page is licensed: CC BY-SA / Gnu FDL}

The HandlerSocket plugin has the following options.

See also the Full list of MariaDB options, system and status variables.

Add the options to the [mysqld] section of your my.cnf file.

handlersocket_accept_balance

• Description: When set to a value other than zero ('0'), handlersocket will try to balance accepted connections among threads. Default is 0 , but if you use persistent connections (for example if you use client-side connection pooling), a non-zero value is recommended.

• Command line: --handlersocket-accept-balance="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 0 to 10000

• Default Value: 0

handlersocket_address

• Description: Specify the IP address to bind to.

• Command line: --handlersocket-address="value"

• Scope: Global

• Dynamic: No

• Type: IP Address

• Default Value: Empty, previously 0.0.0.0

handlersocket_backlog

• Description: Specify the listen backlog length.

• Command line: --handlersocket-backlog="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 5 to 1000000

• Default Value: 32768

handlersocket_epoll

• Description: Specify whether to use epoll for I/O multiplexing.

• Command line: --handlersocket-epoll="value"

• Scope: Global

• Dynamic: No

• Type: number

• Valid values:

  • Min: 0

  • Max: 1

• Default Value: 1

handlersocket_plain_secret

• Description: When set, enables plain-text authentication for the listener for read requests, with the value of the option specifying the secret authentication key.

• Command line: --handlersocket-plain-secret="value"

• Dynamic: No

• Type: string

• Default Value: Empty

handlersocket_plain_secret_wr

• Description: When set, enables plain-text authentication for the listener for write requests, with the value of the option specifying the secret authentication key.

• Command line: --handlersocket-plain-secret-wr="value"

• Dynamic: No

• Type: string

• Default Value: Empty

handlersocket_port

• Description: Specify the port to bind to for reads. An empty value disables the listener.

• Command line: --handlersocket-port="value"

• Scope: Global

• Dynamic: No

• Type: number

• Default Value: Empty, previously 9998

handlersocket_port_wr

• Description: Specify the port to bind to for writes. An empty value disables the listener.

• Command line: --handlersocket-port-wr="value"

• Scope: Global

• Dynamic: No

• Type: number

• Default Value: Empty, previously 9999

handlersocket_rcvbuf

• Description: Specify the maximum socket receive buffer (in bytes). If '0' then the system default is used.

• Command line: --handlersocket-rcvbuf="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 0 to 1677216

• Default Value: 0

handlersocket_readsize

• Description: Specify the minimum length of the request buffer. Larger values consume available memory but can make handlersocket faster for large requests.

• Command line: --handlersocket-readsize="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 0 to 1677216

• Default Value: 0 (possibly 4096)

handlersocket_sndbuf

• Description: Specify the maximum socket send buffer (in bytes). If '0' then the system default is used.

• Command line: --handlersocket-sndbuf="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 0 to 1677216

• Default Value: 0

handlersocket_threads

• Description: Specify the number of worker threads for reads. Recommended value = number of CPU cores * 2.

• Command line: --handlersocket-threads="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 1 to 3000

• Default Value: 16

handlersocket_threads_wr

• Description: Specify the number of worker threads for writes. Recommended value = 1.

• Command line: --handlersocket-threads-wr="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 1 to 3000

• Default Value: 1

handlersocket_timeout

• Description: Specify the socket timeout in seconds.

• Command line: --handlersocket-timeout="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 30 to 3600

• Default Value: 300

handlersocket_verbose

• Description: Specify the logging verbosity.

• Command line: --handlersocket-verbose="value"

• Scope: Global

• Dynamic: No

• Type: number

• Valid values:

  • Min: 0

  • Max: 10000

• Default Value: 10

handlersocket_wrlock_timeout

• Description: The write lock timeout in seconds. When acting on write requests, handlersocket locks an advisory lock named 'handlersocket_wr' and this option sets the timeout for it.

• Command line: --handlersocket-wrlock-timeout="value"

• Scope: Global

• Dynamic: No

• Type: number

• Range: 0 to 3600

_{This page is licensed: CC BY-SA / Gnu FDL}

Some resources and documentation about HandlerSocket.

• The home of HandlerSocket is here.

• The story of handlersocket can be found here.

• Comparison of HANDLER and HandlerSocket can be found here.

• HandlerSocket plugin for MySQL presentation by Akira Higuchi of DeNA - June 29 2010 - DeNA Technology Seminar

• HandlerSocket plugin for MySQL presentation by Akira Higuchi of DeNA - June 29 2011 - in Japanese

_{This page is licensed: CC BY-SA / Gnu FDL}