Skip to main content
Version: 7.2405.x.x LTS

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.

Limitations

All web.xml files 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:

    <!--Configuring application ID-->
    <context-param>
        <param-name>application-id</param-name>
        <param-value>proxySampleConf</param-value>
    </context-param>
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.

info

If SessionReaping is OFF and a TelemetricFilter with metric enabled is configured, no metrics data will be send to the TelemetricFilter for this instance.

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;

    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.

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

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

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