Version: 3.14.x.x LTS

MySQLSessionStoreServlet

The MySQLSessionStoreServlet facilitates the remote storage of session information into a MySQL database, within the context of the Dynamic Session Management Engine. The MySQLSessionStoreServlet is designed for best session availability. See also: Appendix C - Performance Samples.

You can only use the MySQLSessionStoreServlet in combination with the SessionManagementFilter. The SessionManagementFilter defines the method of session binding, for example, by means of cookies. The filter also creates and handles the session. The MySQLSessionStoreServlet loads and stores the session data in the remote session store, that is, into the remote MySQL database. In particular, the MySQLSessionStoreServlet stores the data that is required to identify and control identical sessions in the MySQL data table.

The MySQLSessionStoreServlet is supported only under Linux.

Limitations

All web.xmlfiles that define a MySQLSessionStoreServlet and share the same database (tables) must meet the following conditions:

All web.xml files used by the same nevisProxy instance must have different application IDs.
Each nevisProxy instance must use the same set of web.xml files with the same application IDs.
All web.xml files with the same application ID must fulfill the following conditions:
- They must have equally configured Esauth4ConnectorServlets.
- They must have equally configured WebSocketServlets.
- All LuaFilters including a defined Script.NotifySessionInvalidateFunctionName or Script.GlobalStoreAttributeTimeoutFunctionName attribute must be configured in the same way.
- All SessionManagementFilters with a custom session type must be configured in the same way. The session type is defined by the filter attribute Identification.
- All HttpConnectorServlets with a set logout URI must be configured in the same way. The logout URI is defined by the servlet attribute LogoutURI.
- All SessionFilters where the attribute UseSessionToStoreToken is set to false must be configured in the same way.

Classname:
ch::nevis::nevisproxy::servlet::cache::mysql::MySQLSessionStoreServlet

Library:
libMySQLSessionStoreServlets.so.1

Configuration

Name	Type, Usage Constraints, Defaults	Description
SessionsTableName	string; optional (as of nevisProxy release 3.13.1.0) / required (`for all nevisProxy releases < 3.13.1.0`); default: session	Sets the name of the table that holds the proxy sessions. As of release 3.13.1.0 of nevisProxy, the use of this attribute is optional. If you want to include the attribute, we recommend adopting the default value.
AttributesTableName	string; optional (as of nevisProxy release 3.13.1.0) / required (`for all nevisProxy releases < 3.13.1.0`); default: attribute	Sets the name of the table that holds the proxy attributes. As of release 3.13.1.0 of nevisProxy, the use of this attribute is optional. If you want to include the attribute, we recommend adopting the default value.
KeyToIdMapTableName	string; optional (as of nevisProxy release 3.13.1.0) / required (`for all nevisProxy releases < 3.13.1.0`); default: key_id_map	Sets the name of the table that holds the proxy key-ID map. As of release 3.13.1.0 of nevisProxy, the use of this attribute is optional. If you want to include the attribute, we recommend adopting the default value.
ConfigurationsTableName	string; optional (as of nevisProxy release 3.13.1.0) / required (`for all nevisProxy releases < 3.13.1.0`); default: conf	Sets the name of the table that holds the proxy configuration. As of release 3.13.1.0 of nevisProxy, the use of this attribute is optional. If you want to include the attribute, we recommend adopting the default value.
UserName	string; required	Sets the MySQL database user name.
Password	string; required	Sets the MySQL database user password.
MinConn	integer; required; default: 10	Sets the minimum number of open connections.
MaxConn	integer; required; default: 200	Sets the maximum number of open connections.
ConnectString	string; required	Sets the MySQL database connect string. Use one of the following formats: //<host>:<port>/<path>[?<key>=<value>]``[&<key>=<value>]... replication://<masterhost1>:<master-port1>,<master-host2>:<master-port2>/<path>[?<key>=<value>]``[&<key>=<value>]... The following `<key>`s are supported: connect_timeout: It is usually 10 seconds. read_timeout (in seconds): Specifies the timeout in seconds for reading packets from the server. write_timeout (in seconds): Specifies the timeout in seconds for sending packets to the server. dbclient_cfg_file (string): Specifies the read options from the given option file. ping_timeout (in seconds): If the ping call (including connect) to the database does not return after `<ping_timeout + connect_timeout>` seconds, we assume that the DB is down. Is only implemented for the replication configuration. See also the chapter for more information about replication.
ConnectSocket	string; advanced, optional	Defines a custom UNIX domain socket. You can use this socket for communication if the MySQL server is local. In a master/slave MariaDB setup, the socket only affects the master instance.
ConnectionErrorCodes	string array; required; defaults: 1053, 2006, 2013	Sets the MySQL re-connection error codes. Use comma-separated, integer values.nevisProxy will initiate a re-connection to the MySQL database if these errors occur.
TimeOut	integer; required; default: 3600	Defines how long a connection should stay open (in seconds).
ReaperTimeOut	integer; required; default: 120	Defines the maximum reaping time in seconds allowed for one run. If the session reaper needs more time than defined here, the system takes this as an indication that there are serious performance issues with the session store. In this case, the system traces an error event MY01.
SessionReaping	enum: ON, OFF; advanced, optional; default: ON	Enables session reaping if set to "ON" (default). We recommend enabling session reaping for at least one instance in your setup. If session reaping is disabled for all instances, the database may grow unacceptably. Set this flag to "OFF" only if you configure the database replication differently than proposed in: "Setting up a fail-safe session store using data replication with MariaDB". For example, if you prefer to set the parameter `slave_exec_mode` in the MariaDB service configuration to "STRICT" instead of the recommended "IDEMPOTENT", only one instance should do the reaping (see also: "Step-by-step setup of the replicated session store"). There is no need to set this parameter to "OFF" if you add the following entry to the conf table: `INSERT INTO conf (CACHENAME, PARAMETER, VALUE) VALUES ('session', 'REAPER', '0');` This entry causes the proxy to ensure that always one reaper is reaping at the same time.

| EngineLeaksOnCascadeDelete | stringadvanced default: AUTO | Due to a bug in the NDB cluster (MySQL Bug #89511), it is necessary to execute some extra sql commands to avoid some leaks. The parameter EngineLeaksOnCascadeDelete ensures the execution of these extra sql commands. The parameter EngineLeaksOnCascadeDelete can have one of the following values: "AUTO": Executes the extra sql commands only for the NDB cluster. "OFF": Does not execute any extra sql command at all. Select this option only if an NDB cluster version is used where the bug mentioned above has been fixed (note that no such version is available yet (March 2018)). "ON": Executes the extra sql commands on each engine. | | MaxLimitOnDelete | integer; advanced, optional | Defines the maximal number of sessions to be deleted on a single SQL command while reaping. This parameter can be useful if the reaping takes too long, causing the following error to occur: "ERROR(1205): Lock wait timeout exceeded; try restarting transaction". This may happen if you are using the NDB Cluster.Note that depending on the value of the limit defined here, the session reaper may use more time to reap over all entries than without the limit. This can happen if the reaper performs the reaping based on several SQL commands instead of on a single one, and does not stop before it has reaped all expired sessions. If so, you need to increase the value of the parameter ReaperTimeOut. | | MaxAttributeSize | integer; advanced; default: 120000 | Defines the maximum expected size in bytes of a session attribute. Usually the default (120000) is enough, unless you have hundreds of roles per login. Keep in mind that the column VALUE of the attribute table (see parameter AttributesTableName) has to be big enough as well. |

Behavior if one or more DB nodes are down on startup

Configuration without replication:

The proxy will start successfully, but no sessions can be created as long as the node is down. Once the node is up, sessions can be created again, without having to restart the proxy.

Configuration with replication:

If one of the two nodes is down:
- The proxy will start successfully, using the working node for the session management. Whether the other node will also be used when it is up again depends on the configured stickiness.
If both nodes are down:
- The proxy will start successfully, but no sessions can be created as long as both nodes are down. Once one of the nodes is up, it is possible to create sessions again
- Limitations: Because the session stickiness is evaluated on startup, when both nodes were down, the system will use stickiness = "NONE", even if stickiness is set to "SESSION".

Ready-to-use SQL setup script

The nevisProxy package contains a ready-to-use setup script to create the tables of the remote session store. You find the script here: /opt/nevisproxy/<version>/sql/mariadb/sessionStoreSetup.sql.

If you use the script to set up your DB tables, not specify the names in the MySQLSessionStoreServlet configuration anymore. Using this script to set up your tables is therefore recommended.

After running the script, select values for the inactive session timeout INACTIVE_TIMEOUTand the final session timeout FINAL_TIMEOUT. Save these values in the following configuration table:

INSERT INTO conf (CACHENAME, PARAMETER, VALUE) VALUES ('session', 'INACTIVE_TIMEOUT', '300');
INSERT INTO conf (CACHENAME, PARAMETER, VALUE) VALUES ('session', 'FINAL_TIMEOUT', '43100');

The parameter INACTIVE_TIMEOUT is used to set the maximum inactivity time interval (in seconds) of a new session in the session store. You can override this parameter via the SessionManagementFilter to fine-tune the creation of sessions.
- Be aware that this timeout can be overwritten by the IdentityCreationFilter (parameter InactiveInterval), by the LuaFilter (via the setting session:setInactiveTimeout(timeout)), or by nevisAuth for specific sessions.
The parameter FINAL_TIMEOUT is used to set the maximum duration of a new session. You can override this parameter for a specific session, via the LuaFilter.

In a typical setup, the INACTIVE_TIMEOUT must be less than or equal to the FINAL_TIMEOUT. If not, only the FINAL_TIMEOUT will be taken into account.

Configuration of MariaDB-based remote session store

This section describes the setup of a single remote session store. Chapter: Setting up a fail-safe session store using data replication with MariaDB, describes how to set up a fail-safe replicated session store.

Perform the following steps to configure a remote session store based on a MySQL database. You use the MySQL-based remote session store within the context of the Dynamic Session Management Engine.

Create the following database tables (see the code below). The recommended engines for the MySQL tables are:

session: INNODB key_id_map: INNODB attribute: INNODB conf: INNODB

-- Database setup for session management engine.

drop table if exists attribute;
drop table if exists key_id_map;
drop table if exists session;
drop table if exists conf;

--

CREATE TABLE session (
 SESSION_ID VARCHAR(255) NOT NULL,
 CREATION_TIME INTEGER(22) NOT NULL,
 LAST_ACCESS_TIME INTEGER(22) NOT NULL,
 INACTIVE_TIMEOUT INTEGER(22) NOT NULL,
 FINAL_TIMEOUT INTEGER(22) NOT NULL,
 OWNER_ID BIGINT(22) NOT NULL DEFAULT 0,
 PRIMARY KEY(SESSION_ID)
) ENGINE = INNODB;

CREATE INDEX owner_id ON session (OWNER_ID);

CREATE TABLE key_id_map (
 KEY_ID VARCHAR(255) NOT NULL,
 SESSION_ID VARCHAR(255) NOT NULL,
 PRIMARY KEY(KEY_ID),
 FOREIGN KEY(SESSION_ID) REFERENCES session(SESSION_ID) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE attribute (
 ATTRIBUTE_ID INT NOT NULL AUTO_INCREMENT,
 ID VARCHAR(255) NOT NULL,
 NAME VARCHAR(100) NOT NULL,
 VALUE VARBINARY(60000),
 PRIMARY KEY(ATTRIBUTE_ID),
 FOREIGN KEY(ID) REFERENCES session(SESSION_ID) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE conf (
 CACHENAME VARCHAR(100) not null,
 PARAMETER VARCHAR(100) not null,
 VALUE VARCHAR(255) not null
) ENGINE = INNODB;

insert into conf (CACHENAME, PARAMETER, VALUE) values('session', 'VERSION', '<nevisproxy-release-version>');

Comment on the previous code block

The place holder <nevisproxy-release-version> in the last line of the previous code block refers to the relevant software release number (currently, this is 3.14.3.21 LTS).

For all nevisProxy releases < 3.13.1.x, we only support the innoDB storage engine in combination with the Dynamic Session Management Engine. But as of release 3.13.1.0, it is also possible to use an NDB engine together with the Dynamic Session Management Engine. If you want to do this, replace "INNODB" by "NDBCLUSTER" in the above configuration. The NDB CLUSTER has to support delete cascade on foreign keys (supported since 'MySQL NDB Cluster 7.3.1'). Furthermore, you have to use "TEXT" instead of "VALUE VARBINARY(60000)" for the attributes table:

CREATE TABLE attribute
 (ATTRIBUTE_ID INT NOT NULL AUTO_INCREMENT,
 ID VARCHAR(255) NOT NULL,
 NAME VARCHAR(100) NOT NULL,
 VALUE TEXT,
 PRIMARY KEY(ATTRIBUTE_ID),
 FOREIGN KEY(ID) REFERENCES session(SESSION_ID) ON DELETE CASCADE
) ENGINE = NDBCLUSTER;

This is due to the limitation of the maximum row size being 14000 for the NDBCLUSTER.

Save the values for the inactive session timeout and the final session timeout in the nevis_proxy_configuration table:
```
INSERT INTO conf (CACHENAME, PARAMETER, VALUE) VALUES ('session', 'INACTIVE_TIMEOUT', '300');
INSERT INTO conf (CACHENAME, PARAMETER, VALUE) VALUES ('session', 'FINAL_TIMEOUT', '43100');
```
- The parameter INACTIVE_TIMEOUT is used to set the maximum inactive time interval (in seconds) of a new session in the session store. You can override this parameter with the SessionManagementFilter to fine-tune the creation of the sessions.
- The parameter FINAL_TIMEOUT is used to set the maximum duration of a new session. You can override this parameter for a specific session, via the LuaFilter.
In a typical setup, the INACTIVE_TIMEOUT must be less than or equal to the FINAL_TIMEOUT. If not, only the FINAL_TIMEOUT will be taken into account.
nevisProxy will try to get the maximum character length available for the attribute name and value. For this reason, you might need to add some privileges for the nevisProxy user. The system traces a notice if an attribute cannot be saved due to this limitation, with the following or a similar content: "The attribute name or value is too long" (error codes CACH-0043 and CACH-0042). If you encounter any error because of this limitation, the VALUE column of the attributes table can be changed to "BLOB" or "MEDIUMBLOB" without any side-effects.
- Do not forget to restart nevisProxy afterwards! If you have adapted the database table without restarting nevisProxy, the above tracing will not be correct. This is because the system only checks the values on startup.
Be aware that 120000 bytes is the maximum size supported by nevisProxy, even if a BLOB is configured in the database.

The remote cache uses prepared statements for performance reasons. 21 prepared statements per connection are needed. To calculate the total prepared statements use this formula

<calculated number of prepared statements> = <number of instances> * <number of configured connections per instance> * 22

And in the database you have to set the following statement to make sure you have enough prepared statements configured:

set global max_prepared_stmt_count=<calculated number of prepared statements>;

The following error may appear in the navajo.log:

2017 01 09 10:50:18.704 isi3web NavajoMysq 17208.139795399653120.5500aaa3-4338-15ac4151-15982a2b634-0003511b 3-ERROR : MysqlConnector_1_0::connectToDatabase: could not prepare statement-cache for '<your SQL statement>' (Error: Can't create more than max_prepared_stmt_count statements (current value: 16382))

This occurs if the max_prepared_stmt_count is too low for your setup.

MySQLSessionStoreServlet

Configuration​

Behavior if one or more DB nodes are down on startup​

Ready-to-use SQL setup script​

Configuration of MariaDB-based remote session store​

Configuration

Behavior if one or more DB nodes are down on startup

Ready-to-use SQL setup script

Configuration of MariaDB-based remote session store