{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{},"macroMetadata":{"macroId":{"value":"2c42b734-c260-4219-ba0a-01fb4743f7c6"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"06e0cc87-75c8-49f7-853a-baf3e166ace2"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"Identified by ","type":"text"},{"text":"type=\"ODBC\"","type":"text","marks":[{"type":"code"}]},{"text":", 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.","type":"text"}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"Use of this plugin requires that its extension library ","type":"text"},{"text":"odbc-store.so","type":"text","marks":[{"type":"code"}]},{"text":" be loaded by the Service Provider via the ","type":"text"},{"text":"<","type":"text","marks":[{"type":"code"}]},{"text":"OutOfProcess","type":"text","marks":[{"type":"code"},{"type":"link","attrs":{"href":"/wiki/spaces/SP3/pages/2065334599"}}]},{"text":">","type":"text","marks":[{"type":"code"}]},{"text":" element's ","type":"text"},{"text":"","type":"text","marks":[{"type":"code"}]},{"text":" option.","type":"text"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"panel","attrs":{"panelType":"error"},"content":[{"type":"paragraph","content":[{"text":"You should be aware that the unixODBC software and/or many drivers tend to be unstable on non-Windows platforms. They aren't used much, even less often by multi-threaded software, and have been found to contain a lot of apparent race conditions that cause crashes under load. There are several bugs open in the project issue tracker about them, and we encourage that even though there's absolutely nothing we can do about them, simply to warn people off.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Attributes","type":"text"}]},{"type":"paragraph","content":[{"text":"Settings specific to this type include:","type":"text"}]},{"type":"table","attrs":{"layout":"full-width","localId":"ad7cd10f-edbf-4542-a77d-81de2f44c00a"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[168.0]},"content":[{"type":"paragraph","content":[{"text":"Name","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[239.0]},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[121.0]},"content":[{"type":"paragraph","content":[{"text":"Default","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[1104.0]},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[168.0]},"content":[{"type":"paragraph","content":[{"text":"cleanupInterval ","type":"text"},{"type":"hardBreak"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[239.0]},"content":[{"type":"paragraph","content":[{"text":"time in seconds","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[121.0]},"content":[{"type":"paragraph","content":[{"text":"900","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1104.0]},"content":[{"type":"paragraph","content":[{"text":"Interval in seconds between cleanup runs, when expired entries are swept by a background thread. Set to 0 to disable.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[168.0]},"content":[{"type":"paragraph","content":[{"text":"isolationLevel ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[239.0]},"content":[{"type":"paragraph","content":[{"text":"\"SERIALIZABLE\", \"REPEATABLE_READ\", \"READ_COMMITTED\" \"READ_UNCOMMITTED\"","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[121.0]},"content":[{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1104.0]},"content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]}]}]}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"include","parameters":{"macroParams":{"":{"value":"StorageServiceCommonAttributes"}},"macroMetadata":{"macroId":{"value":"fcd400a6-ffc5-48e2-8ece-908ca33e42b7"},"schemaVersion":{"value":"1"},"title":"Include Page"}},"localId":"0e99075e-1c49-4a64-bf69-b5d19d781169"}},{"type":"heading","attrs":{"level":3},"content":[{"text":"Child Elements","type":"text"}]},{"type":"table","attrs":{"layout":"full-width","localId":"f1be84a0-5c46-4f4a-8070-f407deabe156"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[225.0]},"content":[{"type":"paragraph","content":[{"text":"Name","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[130.0]},"content":[{"type":"paragraph","content":[{"text":"Cardinality","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[211.0]},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[1066.0]},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[225.0]},"content":[{"type":"paragraph","content":[{"text":"","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[130.0]},"content":[{"type":"paragraph","content":[{"text":"1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[211.0]},"content":[{"type":"paragraph","content":[{"text":"string","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1066.0]},"content":[{"type":"paragraph","content":[{"text":"Required element containing an ODBC connection string. Exact contents differ across drivers.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[225.0]},"content":[{"type":"paragraph","content":[{"text":"","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[130.0]},"content":[{"type":"paragraph","content":[{"text":"0 or 1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[211.0]},"content":[{"type":"paragraph","content":[{"text":"whitespace-delimited list of strings","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[1066.0]},"content":[{"type":"paragraph","content":[{"text":"Instructs the plugin to retry certain operations if a matching ODBC status code is returned from the operation.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"paragraph","content":[{"text":"The below is excerpted from the configuration file to illustrate the key pieces involved.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n\t\n\t\t\n\t\n\n\n\n\t\n\tDRIVER=drivername;SERVER=dbserver;UID=shibboleth;PWD=password;DATABASE=shibboleth;APP=Shibboleth\n\t\n","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Database Setup","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]}]},{"type":"paragraph","content":[{"text":"This plugin obviously requires a database to store its data. The plugin does ","type":"text"},{"text":"not","type":"text","marks":[{"type":"strong"}]},{"text":" 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:","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Rough DDL for the database","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"CREATE TABLE version (\n major int NOT NULL,\n minor int NOT NULL\n )\n \nCREATE TABLE strings (\n context varchar(255) not null,\n id varchar(255) not null,\n expires datetime not null,\n version smallint not null,\n value varchar(255) not null,\n PRIMARY KEY (context, id)\n )\n \nCREATE TABLE texts (\n context varchar(255) not null,\n id varchar(255) not null,\n expires datetime not null,\n version smallint not null,\n value text not null,\n PRIMARY KEY (context, id)\n )","type":"text"}]},{"type":"paragraph","content":[{"text":"You may need to adjust data types when moving between database systems, but this has to be done carefully to maintain compatibility. The SQL itself is not separate from the plugin code, so you can't change it without changing the code.","type":"text"}]},{"type":"paragraph","content":[{"text":"Once the tables exist, you need to prepopulate the ","type":"text"},{"text":"version","type":"text","marks":[{"type":"code"}]},{"text":" table with the right information, which at the moment should be 1 and 0:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"insert into version values (1,0)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"ODBC Connection Strings","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]}]},{"type":"paragraph","content":[{"text":"The primary piece of information the SP configuration needs is the connection string to use, as described earlier. The string has to be self-contained, and needs to identify the database server, the database or tablespace, and the username and password to connect with. All of this is driver specific, and may involve setting up other files on the machine to create an alias for the database server, or even the connection as a whole. See below for specific database tips.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Database-Specific Notes","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Microsoft SQL Server","type":"text"}]},{"type":"paragraph","content":[{"text":"This was the primary test platform","type":"text","marks":[{"type":"strong"}]},{"text":" way back in the misty past, and 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.","type":"text"}]},{"type":"paragraph","content":[{"text":"The simplest way to set this up is to use the SQL Server or FreeTDS client and define a server alias so you can give the server a logical name. A typical connection string would then be:","type":"text"}]},{"type":"codeBlock","content":[{"text":"DRIVER=drivername;SERVER=dbserver;UID=shibboleth;PWD=password;DATABASE=shibboleth;APP=Shibboleth","type":"text"}]},{"type":"paragraph","content":[{"text":"Creating the database with the default DDL worked fine.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"MySQL","type":"text"}]},{"type":"paragraph","content":[{"text":"The connection string may look like as simple as this:","type":"text"}]},{"type":"codeBlock","content":[{"text":"DRIVER=MySql;OPTION=65536","type":"text"}]},{"type":"paragraph","content":[{"text":"The cryptic ","type":"text"},{"text":"OPTION argument","type":"text","marks":[{"type":"link","attrs":{"href":"https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags"}}]},{"text":" above tells ODBC to look for MySql configuration files for connection data. Hence, you should create a MySql config file (ie. ","type":"text"},{"text":"/etc/mysql/conf.d/odbc.cnf","type":"text","marks":[{"type":"code"}]},{"text":") accordingly, and make it readable by the user running the ","type":"text"},{"text":"shibd","type":"text","marks":[{"type":"em"}]},{"text":" process (eg. ","type":"text"},{"text":"shibd","type":"text","marks":[{"type":"code"}]},{"text":") only.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"[odbc]\nhost = dbhost\nuser = shibboleth\npassword = secret\ndatabase = shibboleth","type":"text"}]},{"type":"paragraph","content":[{"text":"Alternatively, you can also specify the DSN in the main config file if you prefer to leave the password there.","type":"text"}]},{"type":"codeBlock","content":[{"text":"DRIVER=MySQL;SERVER=sp.example.org;DATABASE=shibboleth;USER=root;PASSWORD=password","type":"text"}]},{"type":"paragraph","content":[{"text":"Creating the database with the default DDL worked fine with the InnoDB MySQL table type, which is somewhat (but not fully) transactional.","type":"text"}]},{"type":"paragraph","content":[{"text":"In general, MySQL presents a lot of problems because it doesn't take transactions seriously. It claims to support ACID behavior, but it mixes code for data storage with higher level code that doesn't honor those requirements, and tells you bluntly that many errors will result in only partial transaction rollback. It also has storage engines that operate like Oracle does, not taking locks when told to. As such, we don't know how the plugin behaves under exceptional conditions.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"PostgreSQL","type":"text"}]},{"type":"paragraph","content":[{"text":"In PostgreSQL the ","type":"text"},{"text":"datetime","type":"text","marks":[{"type":"strong"}]},{"text":" data type is called ","type":"text"},{"text":"timestamp","type":"text","marks":[{"type":"strong"}]},{"text":" which requires a small change to the reference SQL from above:","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"DDL","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"CREATE TABLE version (\n major int NOT NULL,\n minor int NOT NULL\n );\nINSERT INTO version VALUES (1,0);\n \nCREATE TABLE strings (\n context varchar(255) NOT NULL,\n id varchar(255) NOT NULL,\n expires timestamp NOT NULL,\n version smallint NOT NULL,\n value varchar(255) NOT NULL,\n PRIMARY KEY (context, id)\n );\n \nCREATE TABLE texts (\n context varchar(255) NOT NULL,\n id varchar(255) NOT NULL,\n expires timestamp NOT NULL,\n version smallint NOT NULL,\n value text NOT NULL,\n PRIMARY KEY (context, id)\n );","type":"text"}]},{"type":"paragraph","content":[{"text":"GOTCHA:","type":"text","marks":[{"type":"strong"}]},{"text":" Be sure that your ","type":"text"},{"text":"pg_hba.conf","type":"text","marks":[{"type":"em"}]},{"text":" file is set up to allow IPv4 MD5 authentication from the network location of your SP host:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"local all postgres peer\nhost all all 127.0.0.1/32 md5\nhost all all 10.0.0.0/8 md5","type":"text"}]},{"type":"paragraph","content":[{"text":"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:","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Install postgresql-libs and create symlink","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"yum install -y postgresql-libs\nln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so","type":"text"}]},{"type":"paragraph","content":[{"text":"Finally, an example connection string:","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"shibboleth3.xml","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n \n","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Oracle","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"\"Quiet in here, isn't it Sarge?\" \"Too quiet, son.\"","type":"text"},{"type":"hardBreak"}]}],"version":1}