DataStream Reference

This reference describes the MySQL and TDS protocols, the database versions, the authentication methods, and the character sets supported by the DataStream feature. It also describes how the NetScaler handles transaction requests and special queries that modify the state of a connection.

You can also configure the NetScaler appliance to generate audit log messages for the DataStream feature.

Supported Database Versions, Protocols, and Authentication Methods

  MySQL Database MS SQL Database
Database Versions MySQL database versions 4.1, 5.0, 5.1, 5.4, 5.5, and 5.6. MS SQL database versions 2000, 2000SP1, 2005, 2008, 2008R2, and 2012.
Protocols MySQL protocol version 10. For information about the MySQL protocol, see MySQL Client/Server Protocol Tabular Data Stream (TDS) protocol version 7.1 and higher. For information about the TDS protocol, see Tabular Data Stream Protocol
Authentication Methods MySQL native authentication is supported. SQL server authentication and Windows Authentication (Kerberos/NTLM) are supported.

Character Sets

The DataStream feature supports only the UTF-8 charset.

The character set used by the client while sending a request may be different from the character set used in the database server responses. Although the charset parameter is set during the connection establishment, it can be changed at any time by sending an SQL query. The character set is associated with a connection, and therefore, requests on connections with one character set cannot be multiplexed onto a connection with a different character set.

The NetScaler appliance parses the queries sent by the client and the responses sent by the database server.

The character set associated with a connection can be changed after the initial handshake by using the following two queries:

  • SET NAMES \<charset\> COLLATION \<collation\>
  • SET CHARACTER SET \<charset\>

Transactions

In MySQL, transactions are identified by using the connection parameter AUTOCOMMIT or the BEGIN:COMMIT queries. The AUTOCOMMIT parameter can be set during the initial handshake, or after the connection is established by using the query SET AUTOCOMMIT.

The NetScaler appliance explicitly parses each and every query to determine the beginning and end of a transaction.

In MySQL protocol, the response contains two flags to indicate whether the connection is a transaction, the TRANSACTION and AUTOCOMMIT flags.

If the connection is a transaction, the TRANSACTION flag is set. Or, if the AutoCommit mode is OFF, the AUTOCOMMIT flag is not set. The ADC appliance parses the response, and if either the TRANSACTION flag is set or the AUTOCOMMIT flag is not set, it does not do connection multiplexing. When these conditions are no longer true, the ADC appliance begins connection multiplexing.

Note

Transactions are also supported for MS SQL.

Special Queries

There are special queries, such as SET and PREPARE, that modify the state of the connection and may break request switching, and therefore, these need to be handled differently.

On receiving a request with special queries, the NetScaler appliance sends an OK response to the client and additionally, stores the request in the connection.

When a non-special query, such as INSERT and SELECT, is received along with a stored query, the NetScaler appliance first, looks for the server-side connection on which the stored query has already been sent to the database server. If no such connections exist, the ADC appliance creates a new connection, and sends the stored query first, and then, sends the request with the non-special query.

In case of SET, USE db, and INIT_DB special queries, the appliance modifies a field in the server side connection corresponding to the special query. This results in better reuse of the server side connection.

Only 16 queries are stored in each connection.

The following is a list of the special queries for which the ADC appliance has a modified behavior.

  • SET query

    The SET SQL queries define variables that are associated with the connection. These queries are also used to define global variables, but as of now, the ADC appliance is unable to differentiate between local and global variables. For this query, the ADC appliance uses the ‘store and forward’ mechanism.

  • USE <db> query

    Using this query, the user can change the database associated with a connection. In this case, the ADC appliance parses the <db> value sent and modifies a field in the server side connection to reflect the new database to be used.

  • INIT_DB command

    Using this query, the user can change the database associated with a connection. In this case, ADC appliance parses the <init_db> value sent and modifies a field in the server side connection to reflect the new database to be used.

  • COM_PREPARE

    The ADC appliance stops request switching on receiving this command.

  • PREPARE query

    This query is used to create prepared statements that are associated with a connection. For this query, the ADC appliance uses the ‘store and forward’ mechanism.

Audit Log Message Support

You can now configure the NetScaler appliance to generate audit log messages for the DataStream feature. Audit log messages are generated when client-side and server-side connections are established, closed, or dropped. The categories of messages that you can log and view are ERROR and INFO. Error messages for client-side connections begin with “CS” and error messages for server-side connections begin with “SS.” Additional information is provided where necessary. For example, log messages for closed connections (CS_CONN_CLOSED) include only the connection ID. However, log messages for established connections (CS_CONN_ESTD) include information such as the user name, database name, and the client IP address in addition to the connection ID.