Transactd Admin Manual

Overview

Protocol

Transactd Plugin and Transactd clients communicate by exchanging binary data in Transactd protocol.

TCP port

Transactd Plugin use TCP Port 8610 by default.

If you want to access the server from remote clients, please set the firewall to allow TCP port 8610.

Local named pipe

On only Windows, it is available that high-speed communication through shared memory using local named pipe.

If you use local pipe server, need to run mysqld.exe in a user who has "Create Global Objects" privilege. SERVICE, NETWORK_SERVICE, LOCAL_SERVICE, and Administrators users are have this privilege by default in Windows. If you want to grant this privilege to existing users, set in [Administrative Tools] - [Local Security Policy] - [Local Policies] - [User Rights Assignment] - [Create Global Objects].

Connection and Thread

Transactd Plugin has "Connection Per Thread (CPT) model" (by default) and "Thread Pool (TPOOL) model".

In CPT model, the thread is no longer required if connection is disconnected. Transactd put to waiting thread pool it and re-use for next connection. It reduces the cost of thread creating.

If the client application is running in multithreaded, the communication library creates different connection for each thread when receive a connection request. There is an application that is implemented by the connection pool, such as a Web server. In order to realize the connection pool, it has an option for create a new connection in the connection request from a same thread.

Connection and Database

You can open multiple databases in one connection. For example, in a single-threaded desktop application, you can open two windows and can connect to database from these windows with one connection. Two databases are not operated at the same time because they are operated by one connection. This saves connection and reduces the server load.

Database and Transaction

Transaction unit is a database. Even if you open multiple databases from one connection, the transaction is processed independently for each database.

Setting host permission to access Transactd

Please refer to security page for detail of Transactd client authentications.

Changing the server port

The default TCP port for Transactd Plugin is 8610. The port can be changed to any port.

Changing the server listening port

It is possible to change the listening port of the server with my.cnf or my.ini.

# my.cnf or my.ini
[mysqld]
loose-transactd_port=8610

Changing the client port

Create a configuration file which have following contents and path.

# contents
[transctd_client]
port = 8610

file paths are:

Disabling Local named pipe communication (Windows only)

If your server is Windows and use localhost, default communication protocol is the local named pipe. You do not need to change this usually. But, you can change it to TCP communication. If you want to change the communication protocol, create a configuration file which have following contents and path. 
# contents
[transctd_client]
port = 8610
use_piped_local = 0

The file path is c:\windows\transactd.ini. Set use_piped_local to 0 to disable local named pipe.

Error Log

Error log of Transactd Plugin has been integrated into error.log of MySQL.

Error log of Transactd client will be output to %ProgramData%\BizStation\Transactd\transactd_clinet_err.log on Windows.
It will be output to /var/log/transactd_clinet_err.log on Linux.(Prior to Version 3.5 was trnsctcl_error.log)

Please check both of server and client error logs to pursue the cause of the error. If trnsctcl_error.log was created by an administrator account on the first error, other users may can not write it. Please check whether there is a write permission to trnsctcl_error.log if the log is not output well.

Show status of Transactd Plugin

You can check status of Transactd Plugin using show status command on MySQL command line tool.

mysql> show status like 'tr%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Transactd_cur_open_databases | 2     |
| Transactd_ha                 | 1     |
| Transactd_pipe_connections   | 2     |
| Transactd_pipe_wait_threads  | 0     |
| Transactdd_tcp_connections   | 0     |
| Transactd_tcp_wait_threads   | 0     |
| Transactd_tpool_connections  | 1     |
| Transactd_tpool_threads      | 15    |
+------------------------------+-------+

Meaning of the status is following:

Transactd_cur_open_databases Number of databases that are currently opened.
Transactd_ha

Current value of the Transactd High Availability (THA) settings. It is specified by sum of following values:

  • 0 The role of this server is slave.
  • 1 The role of this server is master.
  • 2 The role is unknown.
  • 4 Restore the role which has been set before restarting.
  • 8 Automatic failover is enabled.
Transactd_pipe_connections Number of local pipe connection thread. (only Windows.) It Includes accpter thread. Accpter thread is waiting for a new connection, the number of connections that are actually connected is this number - 1.
Transactd_pipe_wait_threads The number of local pipe connection thread that are waiting. The thread change status to waiting for the next connection when connection closed.
Transactd_tcp_connections Number of TCP connections thread. [1]
Transactd_tcp_wait_threads Number of TCP connection threads that are waiting. [1]
Transactd_tpool_connections Number of TCP connections. [2]
Transactd_tpool_threads Number of threads that are pooling. [2]

[1] It is available when transactd_tcp_server_type = 1 (connection per thread of (cpt) server).

[2] It is available when transactd_tcp_server_type = 2 ( thread poool (tpool) server).

In the example above, it means that two databases are opened, one local pipe connection are used, one accepter thread is waiting, pooled connections are 15.

Configurations in my.cnf (my.ini)

To avoid an error, we recommend to add loose- prefix.

transactd_address = 0.0.0.0 Address the TCP server is listening. (default is 0.0.0.0)
Compatible with both v6 and IP v4. "0::0" allows all addresses of v6 and IP v4.
transactd_port = 8610 Port number the TCP server to use. (default is 8610)
transactd_table_name_lowercase = 1 0 or 1. (default is 1)
If 1, it uses lowercase as a table name when you create a table.
ttransactd_auth_type = "" Specify authentication method.
"host_auth" or not specified (default) : host authentication
"mysql_native" : users and hosts authentication
transactd_hostcheck_username = "root" User name that you use to configure the reachable hosts in host authencication. (default is "root")
transactd_tcp_server_type = 1 0: Do not use the TCP server.
1: Use the Connection per thread (cpt) server. (default)
2: Use Thread pool (tpool) server.Currently, please use only read.
transactd_max_tcp_connections = 200 1-3000. (default is 200)
The number of TCP connections connectable at the same time. It is available when transactd_tcp_server_type is 1.
transactd_pool_threads = 15 1-1000. (default is 15)
It is available when transactd_tcp_server_type is 2.
transactd_use_piped_local = 1 0: Do not use the local pipe server.
1: Use local pipe server. (default)
It is available on Windows.
transactd_max_pipe_connections = 10 1-100.
Number of connectable local pipe connections at the same time. It is available on Windows.
transactd_pipe_comm_sharemem_size = 3145728 66000 - 52428800(50MB). (default 3145728 = 3MB)
Buffer size of the local pipe server. If you want to access a large TEXT and BLOB, you must increase this size.
transactd_use_btrv_variable_table = 1 0 or 1. (default is 1)
Specify that use Btrieve compatible variable-length table. It is available when all of the following conditions are met: The last field is VARBINARY. There is only one VAR type field. Not contains BLOB TEXT.When enabled, the last VARBINARY field can not be handled correctly from the SQL.
transactd_use_handlersocket = 1[3] 0: Do not use HandlerSocket protocol on Transactd (default)
1: Use HandlerSocket protocol on Transactd.
transactd_hs_port = 9999[3] Port number for HandlerSocket protocol on Transactd. (default is 9999)
transactd_lock_wait_timeout = 1 lock_wait_timeout for Transactd. (default is 1 second. The possible range is from 1 to 3600.)
transactd_transaction_isolation = "READ-COMMITTED" transaction_isolation for Transactd. (default is "READ-COMMITTED")
transactd_timestamp_always = 1

Specify auto NULL-Value (zero) setting on INSERT or UPDATE to TIMESTAMP type or DATETIME type fields with DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP option. (default is 1)

  • 1: NULL-Value (zero) will be set automatically before INSERT or UPDATE. Then current timestamp on INSERT or UPDATE will be saved.
  • 0: NULL-Value (zero) will NOT be set automatically. You must select the way, and write program expressly:
    • Leave previous value. The value will not be change.
    • Set NULL-Value (zero). Current timestamp on INSERT or UPDATE will be saved.
  • Regardless of transactd_timestamp_always value, if you set NULL (invalid value) on NULL-able field, the field value will be NULL (invalid value). The previous value or the current timestamp will not be saved.
transactd_startup_ha

Start up value of Transactd High Availability (THA) settings. It is specified by sum of following values:

  • 0 The role of this server is slave.
  • 1 The role of this server is master.
  • 2 The role is unknown.
  • 4 Restore the role which has been set before restarting.
  • 8 Automatic failover is enabled.

[3] It is enabled only if it was building the plugin defined the USE_HANDLERSOCKET macro.

You can check status of Transactd Plugin using show variables command on MySQL command line tool.

mysql> show variables like 'transactd_use_piped_local';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| transactd_use_piped_local  | 1     |
+----------------------------+-------+

Character code settings

To handle character encoding on Transactd plugin, please refer to Character encoding on Transactd and Character code page on SDK document (Japanese).

Data type

Please refer Data type page on SDK document (Japanese) and data type correspondence table on SDK document (Japanese) about available data type on Transactd Plugin.

Specifications and Restrictions

Field name

Field names that begin with $nf are reserved for special fields that are used internally.

FULLTEXT indexes

Access in the FULLTEXT indexes are not supported.

Key segments

Colmun number of multi-column key (multi-segment key) is up to 8 up each index. This is the limitations of Transactd schema.

NUMERIC, ENUM, SET, BIT

Table with Enum, Set, a Bit is not supported currently.

Enough testing has not been performed for Numeric type.

Language Support

Other languages have not been tested.

Hardware CPU

Little-endian CPU is supported. Big-endian CPU is not supported and is not planed for support.

VIEW

Displaying or creating VIEW is not supported.

Character set

There is no checking or converting of character code when save or read data on the server side of Transacd Plugin. Ensuring correct character code is the responsibility of the client.

Compatibility with Btrieve API

Following section explains about the compatibility of SQL and Transaction + MySQL.

File spec and Flag

Operations

Owner Names

Mapping of field type

In the application that allocates structure as a record image of the Btrieve API, you need to map fields to type that is accessible from SQL if you want. In such a case, a few measures are required when create table.

Key flag

Null Key

MySQL does not support null key, but Transactd can emulate it.

When you migrate Btrieve table to MySQL, Transactd automatically adds 1 byte Null Indicator Field (NIF) that starts with $nf for each null key (including any segments null key). This field is visible from SQL access, but it is removed from record image when value is returned in Btrieve API. NIF field is added to the end of the record image.

This emulation works well, but in seekPrev operation, current record will move to a null value from a non-null value. In this case, it is possible to move to non-null value, so you have to check if the record is not null key or not. In addition, accessing to records including a null value is possible with this method only.

Specifying null value

You can not specify a null value in null key. Zero is treated as null in numbers. String that has zero length is treated as null in strings. If the null value was specified in the null key, the field will be treated as NOT NULL field when table will be created.

Specifying Null key is typically used to case like following: The field contains zero value but I want to include it to indexes, so I specify not used non-zero value as null value in any segments null key. In this case, you can solve problem with setting field to NOT NULL. In the other case, you will need to add a field, and control with setting whether zero or not in the field.

Other

If you use meta-programming, such as changing the process by field type, and use the type of new MySQL, you have to add processing corresponding to them to the application.