MySQLSessionStoreServlet
The MySQLSessionStoreServlet facilitates the remote storage of session information into a MariaDB 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 MariaDB database. In particular, the MySQLSessionStoreServlet stores the data that is required to identify and control identical sessions in the MariaDB data table.
Compared to the LocalSessionStoreServlet, the MySQLSessionStoreServlet preserves existing sessions when restarting the proxy. It also allows sharing sessions among multiple proxy instances. However, using the MySQLSessionStoreServlet implies separating the proxy from its session store, which will extend the communication time between them. This can also impact the overall performance negatively, compared to using the LocalSessionStoreServlet.
By default, the MySQLSessionStoreServlet opens a plain connection with the database. You can encrypt the communication by using a TLS connection, which will improve the security but may also affect the performance. For more information, check the chapter: Configuring a TLS connection between nevisProxy and MariaDB.
All web.xmlfiles that define a MySQLSessionStoreServlet and share the same database (tables) must meet the following conditions:
- All separate web.xml files used by the same nevisProxy instance, which configures a shared remote session store, must have different application ID configured.
- Each nevisProxy instance, which shares one (or multiple) MySqlSessionStore, must use the same (set of) web.xml file(s) configured for the shared remote session store(s). The other web.xml files, which does not configure a shared remote session store, can differ from each other.
- 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.
To set an Application ID, you have to configure it in the web.xml, for example:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "/var/opt/nevisproxy/verification/conf/web-app_2_3.dtd">
<!-- $Id$ -->
<web-app>
<!--Configuring application ID-->
<context-param>
<param-name>application-id</param-name>
<param-value>proxySampleConf</param-value>
</context-param>
<!-- rest of the configuration -->
...
</web-app>
Classname:
ch::nevis::nevisproxy::servlet::cache::mysql::MySQLSessionStoreServlet
Library:
libMySQLSessionStoreServlets.so.1
Configuration
| Name | Type, Usage Constraints, Defaults | Description |
|---|---|---|
| SessionsTableName | string; optional; default: session | Sets the name of the table that holds the proxy sessions. |
| AttributesTableName | string; optional; default: attribute | Sets the name of the table that holds the proxy attributes. |
| KeyToIdMapTableName | string; optional; default: key_id_map | Sets the name of the table that holds the proxy key-ID map. |
| ConfigurationsTableName | string; optional; default: conf | Sets the name of the table that holds the proxy configuration. |
| UserName | string; required | Sets the MariaDB database user name. |
| Password | string; required | Sets the MariaDB database user password. |
| MinConn | integer; required; min: 1, max: 10000, default: 10 | Sets the minimum number of open connections. |
| MaxConn | integer; required; min: 1, max: 10000, default: 200 | Sets the maximum number of open connections. |
| ConnectString | string; required | Sets the MariaDB 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 (in seconds): Specifies the connect timeout in seconds. You can set the default value in the bc-property ch.nevis.bc.sql.mysql.ConnectTimeout. 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 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. If you do not set the ping timeout, the connection to the database depends on the connect timeout of the database and the TCP timeout of the system. This can be up to 20 minutes. For this reason, always setting a ping timeout of 2 to 10 seconds, depending on the network, is recommended. See also the chapter: Setting up a fail-safe session store using data replication with MariaDB, for more information about replication. |
| ConnectSocket | string; advanced, optional | Defines a custom UNIX domain socket. You can use this socket for communication if the MariaDB 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 MariaDB 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 [sec]; required; min: 1, max: 10000, default: 3600 | Defines how long a connection should stay open (in seconds). |
| ReaperTimeOut | integer [sec]; required; min: 1, max: 86400, 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 | This parameter has been deprecated. 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 extrasqlcommands 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 extrasql*commands on each engine. |
| MaxLimitOnDelete | integer; advanced, optional; min:1 / max:1000 | 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; min: 1, max: unlimited, default: 256 kB | Defines the expected maximum size of a session attribute, in bytes. Usually the default (256 kB) 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 per login. See also: "Appendix G - Sizing Parameters in the Nevis Proxy". |
| DisableDatabaseSchemaCheck | boolean; optional; default:false | At startup, the proxy checks the database schema and integrity constraints what are required by the MySQLSessionStoreServlet. On certain MariaDB versions, the check produces fake errors due to a MariaDB bug. By setting this parameter to true, you can skip the check. |
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 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 BIGINT NOT NULL AUTO_INCREMENT,
ID VARCHAR(255) NOT NULL,
NAME VARCHAR(200) 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>');
insert into conf (CACHENAME, PARAMETER, VALUE) values ('session', 'REAPER', '0');To improve session reaping, add the following entry to the conf configuration table:
INSERT INTO conf (CACHENAME, PARAMETER, VALUE) VALUES ('session', 'REAPER', '0');This entry causes the reaper to replace "0" by its own ID, thus making sure that only one reaper is reaping at the same time.
To avoid that the database is filled up when using a MultiLevelSessionStore, we recommend to add this unique key as well:
alter table attribute add constraint uc_id_name unique (ID, NAME);
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 4.6.5 LTS).
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.
The remote cache uses prepared statements for performance reasons. 22 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> * 22And 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.