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

For more information on how to share one or multiple remote session stores, see the chapter Sharing remote session stores.

Classname

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

Library

libMySQLSessionStoreServlets.so.1

Configuration

SessionsTableName

Type: String
Usage Constraints: optional
Default: session

Sets the name of the table that holds the proxy sessions.

AttributesTableName

Type: String
Usage Constraints: optional
Default: attribute

Sets the name of the table that holds the proxy attributes.

KeyToIdMapTableName

Type: String
Usage Constraints: optional
Default: key_id_map

Sets the name of the table that holds the proxy key-ID map.

ConfigurationsTableName

Type: String
Usage Constraints: optional
Default: conf

Sets the name of the table that holds the proxy configuration.

UserName

Type: String
Usage Constraints: required

Sets the MariaDB database user name.

Password

Type: String
Usage Constraints: required

Sets the MariaDB database user password.

MinConn

Type: Integer
Usage Constraints: optional, min 1, max 10000
Default: 10

Sets the minimum number of open connections.

MaxConn

Type: Integer
Usage Constraints: optional, min 1, max 10000
Default: 200

Sets the maximum number of open connections.

ConnectString

Type: String
Usage Constraints: 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> values are supported:

• connect_timeout: 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: Specifies the timeout in seconds for reading packets from the server.
• write_timeout: Specifies the timeout in seconds for sending packets to the server.
• dbclient_cfg_file: Specifies the read options from the given option file.
• ping_timeout: 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

Type: String
Usage Constraints: optional, advanced
Default: conf

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

Type: String
Usage Constraints: optional
Default: 1053,1927,2006,2013

Sets the MariaDB connection error codes. Use comma-separated, integer values. nevisProxy will initiate a reconnection to the MySQL database if these errors occur.

TimeOut

Type: Integer
Unit: seconds
Usage Constraints: optional, min 1, max 10000
Default: 3600

Defines how long a connection should stay open.

ReaperTimeOut

Type: Integer
Unit: seconds
Usage Constraints: optional, 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 MY01 error event.

SessionReaping

Type: Enum
Possible values: ON, OFF
Default: ON

Controls the session reaping of the remote DB. 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.

MaxLimitOnDelete

Type: Integer
Usage Constraints: 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.

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

Type: Integer
Unit: bytes Usage Constraints: optional, min 1, max unlimited
Default: 256 kB

Defines the expected maximum size of a session attribute. Usually the default is enough, unless you have hundreds of roles per login. Keep in mind that the column VALUE of the attribute table - see the parameter AttributesTableName - has to be big enough per login.

DisableDatabaseSchemaCheck

Type: Boolean
Usage Constraints: 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.

StrategyType

Type: Enum
Possible values: NONE, ALWAYS, ONLY_IF_NULL
Default: NONE

The StrategyType defines the strategy to use to get the session parameters from the mysql table:

• NONE: all parameters are got when the session is accessed. This means that one SQL command gets the whole session. This is the most performant configuration.
• ONLY_IF_NULL: get the parameter from the database only if this parameter was not set when the whole session was accessed. This parameter should be used in connection with the parameter 'StrategyType.SubType'
• ALWAYS: get the parameter always from the database. This configuration is not recommended because it will result in a performance loss. If set, use it always in connection with a 'StrategyType.SubType'

StrategyType.SubType

Type: Enum
Possible values: ALL, COOKIE
Default: COOKIE

This parameter is only evaluated it the StrategyType is not NONE.

• ALL: use the configured StrategyType for all parameters. Be aware that this may result in a performance loss.
• COOKIE: use the configured StrategyType only for the attributes related to the CookieCacheFilter. The most common use case is the race-condition, that after a successful login two requests for the same session reach two different instances of the proxy at the same time. If the backend sends back a cookie, one of the requests may not recognizes it, which may result that the backend resends the cookie and a new login may have to be done. It is a common good practice to parallelize requests after a successful authentication.

EnableMetrics

Type: boolean
Usage Constraints: optional
Default: true

Controls the OpenTelemetry metrics generation of the servlet. If disabled, the servlet won't forward the session statistics as metrics. The metrics generation is also disabled if SessionReaping is configured to OFF, even though this parameter is enabled.

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 in /opt/nevisproxy/<version>/sql/mariadb/sessionStoreSetup.sql.

If you use the script to set up your DB tables, you do not need to 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_TIMEOUT and 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. Perform the following steps to configure a remote session store based on a MySQL database.

1. Create the following database tables. 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 MEDIUMBLOB,
       PRIMARY KEY(ATTRIBUTE_ID),
       FOREIGN KEY(ID) REFERENCES session(SESSION_ID) ON DELETE CASCADE
   ) ENGINE = INNODB;
   alter table attribute add constraint uc_id_name unique (ID, NAME);
   
   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.

The place holder <nevisproxy-release-version> in the last line of the previous code block refers to the relevant software release number.

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

3. 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 "LONGBLOB" 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.

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