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

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

At least the , and options need to be set:

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}

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:

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 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 .

_{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.

• presentation by Akira Higuchi of DeNA - June 29 2010 - DeNA Technology Seminar

• presentation by Akira Higuchi of DeNA - June 29 2011 - in Japanese

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

In order to make use of the 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

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:

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

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

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

Select the dynamic column 'color' for all items:

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

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

Dynamic Columns Reference

This is a complete reference of dynamic columns in MariaDB.

Dynamic Columns Functions

COLUMN_CREATE

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 section for further details. Note also .

Typical usage:

COLUMN_ADD

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

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

Typical usage:

Note: COLUMN_ADD() is a regular function (just like ), 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

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 section for more information about datatypes.

COLUMN_DELETE

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

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

COLUMN_LIST

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

COLUMN_CHECK

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

Returns a JSON representation of data in dyncol_blob :

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.

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:

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 and functions. However, note that there are currently some differences - see .

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.

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 for details.

Limitations

See Also

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

Syntax

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, , and tables (and should work with most 'normal' storage engines, but not with system tables, or ).

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.

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 .

Key Lookup

A key lookup is started with:

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:

Key Scans

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

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

Table Scans

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

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 .

System-Versioned Tables

HANDLER ... READ reads everything from , 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 , 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.

Error Codes

• (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

Examples

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 :

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

The plugin has the following options.

See also the .

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

handlersocket_accept_balance

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:

Data structures

DYNAMIC_COLUMN

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

DYNAMIC_COLUMN_VALUE

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

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.

mariadb_dyncol_create_many

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

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 :

mariadb_dyncol_exists

Check if column with given names exist in the blob:

The function returns YES, or NO or Error code.

mariadb_dyncol_column_count

Get the number of columns in a dynamic column blob:

mariadb_dyncol_list

List columns in a dynamic column blob:

mariadb_dyncol_get

Get a value of one column:

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

mariadb_dyncol_unpack

Get the value of all columns:

mariadb_dyncol_has_names

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

mariadb_dyncol_check

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

mariadb_dyncol_json

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

mariadb_dyncol_val_TYPE

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

mariadb_dyncol_prepare_decimal

Initialize DYNAMIC_COLUMN_VALUE before setting the value of value.x.decimal.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:

mariadb_dyncol_column_cmp_named

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

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