Table of Contents |
---|
Overview
Identified by type="ODBC"
, this storage option stores data using an ODBC-compliant database. This allows for persistence across software restarts and supports shared access across the nodes of a cluster, provided the ODBC driver and the database support transactions. No explicit database locking is performed inside the plugin itself.
Info |
---|
Use of this plugin requires that its extension library |
...
Settings specific to this type include:
Name | Type | Default | Description |
---|
cleanupInterval
cleanupInterval | time in seconds | 900 | Interval in seconds between cleanup runs, when expired entries are swept by a background thread |
isolationLevel
. Set to 0 to disable. | |||
isolationLevel | "SERIALIZABLE", "REPEATABLE_READ", "READ_COMMITTED" "READ_UNCOMMITTED" | Transaction isolation level for database access. Defaults to SERIALIZABLE to prevent race conditions while updating records. Reduce at your own risk, as the code is written to assume the guarantees that level provides. |
Include Page | ||||
---|---|---|---|---|
|
Child Elements
Name | Cardinality | Type | Description |
---|---|---|---|
<ConnectionString> | 1 | string | Required element containing an ODBC connection string. Exact contents differ across drivers. Note that you may wish to use a so-called "named" connection defined in odbc.ini (or for Windows inside the ODBC control panel applet or registry) to keep the database password out of this file, since it will be readable by the web server account/user. |
<RetryOnError> | 0 or 1 | whitespace-delimited list of strings | Instructs the plugin to retry certain operations if a matching ODBC status code is returned from the operation. This |
is used to detect rolled back transactions caused by optimistic locking and retry them, by including the driver-specific status code that indicates the condition. Under load, you'll typically see problems on most databases that have to be worked around using this setting. |
Examples
The below is excerpted from the configuration file to illustrate the key pieces involved.
Code Block | ||
---|---|---|
| ||
<OutOfProcess> <Extensions> <Library path="odbc-store.so" fatal="true"/> </Extensions> </OutOfProcess> <StorageService type="ODBC" id="db" cleanupInterval="900"> <ConnectionString> DRIVER=drivername;SERVER=dbserver;UID=shibboleth;PWD=password;DATABASE=shibboleth;APP=Shibboleth </ConnectionString> </StorageService> |
...
This plugin obviously requires a database to store its data. The plugin does not automatically create any tables for itself, so this has to be done ahead of time. The rough SQL DDL for the database is the following:
...
...
Rough DDL for the database
Code Block | ||
---|---|---|
| ||
CREATE TABLE version ( major int NOT NULL, minor int NOT NULL ) CREATE TABLE strings ( context varchar(255) not null, id varchar(255) not null, expires datetime not null, version smallint not null, value varchar(255) not null, PRIMARY KEY (context, id) ) CREATE TABLE texts ( context varchar(255) not null, id varchar(255) not null, expires datetime not null, version smallint not null, value text not null, PRIMARY KEY (context, id) ) |
...
Database-Specific Notes
Microsoft SQL Server
This is was the primary test platform way back in the misty past, and has been was tested under only light load on both Windows and CentOS 5. On Linux, the FreeTDS client and ODBC manager and driver included with the OS were used.
...
In PostgreSQL the datetime data type is called timestamp which requires a small change to the reference SQL from above:
DDL
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
CREATE TABLE version ( major int NOT NULL, minor int NOT NULL ); INSERT INTO version VALUES (1,0); CREATE TABLE strings ( context varchar(255) NOT NULL, id varchar(255) NOT NULL, expires timestamp NOT NULL, version smallint NOT NULL, value varchar(255) NOT NULL, PRIMARY KEY (context, id) ); CREATE TABLE texts ( context varchar(255) NOT NULL, id varchar(255) NOT NULL, expires timestamp NOT NULL, version smallint NOT NULL, value text NOT NULL, PRIMARY KEY (context, id) ); |
GOTCHA: Be sure that your pg_hba.conf file is set up to allow IPv4 MD5 authentication from the network location of your SP host:
Code Block | ||||
---|---|---|---|---|
| ||||
local all postgres peer host all all 127.0.0.1/32 md5 host all all 10.0.0.0/8 md5 |
On RHEL/CentOS Linux, the ODBC driver links itself to an unversioned library, /usr/lib64/libpq.so, but the underlying postgresql package fails to create that symlink, so after installing the postgresql-libs package, you'll need to do so:
...
Install postgresql-libs and create symlink
Code Block | ||
---|---|---|
| ||
yum install -y postgresql-libs ln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so |
Finally, an example connection string:
shibboleth3.xml
Code Block | ||||
---|---|---|---|---|
| ||||
<StorageService type="ODBC" id="db" cleanupInterval="900"> <ConnectionString><![CDATA[ Driver=PostgreSQL;Server=127.0.0.1;Port=5432;Database=shibboleth-sp;Uid=shibboleth-sp;Password=shibboleth-sp-password ]]></ConnectionString> </StorageService> |
...
"Quiet in here, isn't it Sarge?" "Too quiet, son."