3.8. SQL configuration

SQL clauses use the Perl DBI/DBD interface to connect to your database. You can therefore use SQL clauses with a large number of commercial and free SQL database systems. In order to use SQL, the minimum installation to get your SQL system to work is:
The SQL parameters DBSource, DBUsername, and DBAuth are passed to DBI like this:
DBI->connect(DBSource, DBUsername, DBAuth)
DBSource is a specification, which usually starts with dbi:drivername:..., but the exact meaning of these variables depends on the Perl DBD driver you use. For more information about the syntax of DBSource, DBUsername, and DBAuth for different database vendors, see Section 19. Using SQL with various database vendors.
You can specify multiple databases by using multiple DBSource, DBUsername, and DBAuth parameters. Whenever Radiator tries to connect to a database, SQL tries to connect to the first DBSource listed, using the first DBUsername and DBAuth parameters. If that connection fails, it tries the second, third and so on, until all the databases are tried, and finally gives up without replying to the NAS. This gives your NAS the opportunity to fall back to another RADIUS server if all your SQL databases are down. You can change this default behaviour with the RoundRobinOnFailure configuration parameter. For more information, see Section 3.8.8. RoundRobinOnFailure.
SQL clauses are tolerant of database failures. If the database server goes down, Radiator tries to reconnect to a database as described above, starting again at the first database or using round robin, depending on the configuration. Whichever database Radiator connects to, it stays connected to it until that database becomes unreachable, at which time it searches again for a database, starting at the first. If Radiator is not able to connect to any SQL server, it returns an IGNORE, which causes Radiator to ignore the request. This causes most NASs to fall back to a secondary RADIUS server.
Tip
Remember the SQL database needs maintenance. Make sure you have someone who is knowledgeable about installing, configuring, maintaining, and backing up your SQL database.
Tip
In some cases SQL database server use a significant number of CPU cycles and become the performance bottleneck. For example, doing a simple lookup on a user/password database with a simple index on the user name is usually very quick, in the order of milliseconds per lookup, even if the table has millions of rows. However, an insert into a large accounting table with a complicated index can be very slow. Make sure you understand how to design and tune your database tables, otherwise it could have a significant effect on SQL performance.
The following clauses utilise SQL:
The following clauses utilise AuthBy SQL and thus support all the common SQL parameters:

3.8.1. SQL bind variables

Most SQL servers support the use of bind variables. Bind variables are used by the SQL server to do a dynamic replacement of variables in an SQL statement. In some SQL servers this can increase query performance by allowing the server to compile and reuse the SQL compiled SQL query many times.
With Radiator SQL clause query parameters that support bind variables, you can specify the query separately from the values for the bind variables. With most SQL servers, the position of each bound variable is marked by a question mark (‘?’) character. The bound variables will be replaced (after special characters are replaced) at run-time one by one in the order of the question marks in the query, and in the order of the bound variable specifications.
For example, <AuthBy SQL> supports bound variables with the AuthSelect query parameter. In this sample configuration fragment:
AuthSelect select PASSWORD from SUBSCRIBERS where USERNAME=? and CLIENT=?
AuthSelectParam %0
AuthSelectParam %N
%0 (user name) will be used to replace the first ? (the one for the USERNAME column), and %N (NAS id) will be used to replace the second (the one for the CLIENT column).
Some SQL queries are cached when configured with bind variables. By default as much as 32 queries can be cached. This can be changed in SqlDb.pm if required.
Note
Most SQL servers and their Perl DBD modules support bound variables. Check the documentation for your SQL server, and the Perl DBD module for your server.

3.8.2. DBSource

This parameter is used by Perl DBI to specify the database driver and database system to connect to. It usually begins with dbi:drivername:. There is no standard for the text following the driver name, consult the details for your DBD (database driver) documentation. You can use any of the special characters described in Section 3.3. Special formatters. Here are some examples.
# Connect to MySQL database called radius. Typically defaults to Unix socket
DBSource dbi:mysql:database=radius

# Or... Use SQLIte file called users.db located in the DbDir directory
DBSource dbi:SQLite:%D/users.db

# Or... Connect to the Oracle SID called users
DBSource dbi:Oracle:users

# Or... Connect to PostgreSQL database called radius on localhost, default port
DBSource dbi:Pg:dbname=radius;host=127.0.0.1
Tip
For some applications, it is useful to use a GlobalVar to specify the name of the SQL database. That way your Radiator can be parameterised from the command line:
DBSource dbi:mysql:%{GlobalVar:databasename}
radiusd -config_file xxxxxx.cfg databasename=radius

3.8.3. DBUsername

For most database types, this specifies the user name to log in to the database. For some databases, this has a different meaning. For example, for SQLite user name is meaningless. You can use any of the special characters described in Section 3.3. Special formatters.
# For SQLite, it is ignored
DBUsername ignored

# For Oracle, it is the name of the Oracle user to log in as
DBUsername scott

3.8.4. DBAuth

This parameter is usually used by Perl DBI to specify the password for the user specified in DBUsername. For some databases, this has a different meaning. For example, for SQLite it is meaningless and can be ignored. You can use any of the special characters described in Section 3.3. Special formatters.
# For SQLite, it is ignored
DBAuth ignored

# For Oracle, it is Oracle password for DBUsername
DBAuth tiger

3.8.5. Timeout

This optional parameter specifies a timeout interval in seconds that Radiator waits for SQL queries. When a query times out, it is retried until SQLRetries limit is reached. For more information about SQLRetries, see Section 3.8.7. SQLRetries.
If ConnectTimeout is not defined, Timeout is also be used when trying to connect or disconnect an SQL database specified by DBSource.
If the server does not respond within the Timeout period, Radiator considers the SQL server to be failed, and stops trying to contact the SQL server until the FailureBackoffTime is expired. The default value for Timeout is 60 seconds. If you set Timeout to 0, no timeouts are implemented, and Radiator relies on the underlying implementation to timeout any SQL operations.
# Set the timeout to two seconds
Timeout 2
Note
Timeout is not supported on Perl for Windows. On Windows platforms, the timeout usually is determined by the TCP timeouts built in to your Windows TCP stack.
Note
When DBD supports defining connection or query timeouts, those driver-specific timeout options must be used in DBSource. In this case, the value of Timeout must be larger than the timeout value in DBSource.
Tip
Set Timeout to 0 If you are using Sybase ODBC libraries.

3.8.6. FailureBackoffTime

If Radiator detects an SQL server failure, it waits for this number of seconds before trying to contact the SQL server again. The default value is 600 (10 minutes).
# Try again after 3 minutes
FailureBackoffTime 180

3.8.7. SQLRetries

If Radiator detects certain SQL errors while running a query, it will reconnect and retry until it has tried SQLRetries times, then declares an SQL server failure. The default value is 2. Applies to SQL errors other than primary key violations, or Oracle error 'ORA-00001'.
CAUTION
If SQLRetries is set to 0, no connection is made and no queries are executed.

3.8.8. RoundRobinOnFailure

This optional flag helps with some types of overloaded database that can be connected but then time out when a query is sent. It causes the next database in the DBSource list to be tried next instead of the first one.
# Try to skip databases that have become slow to respond
RoundRobinOnFailure

3.8.9. ConnectTimeout

This optional parameter specifies a timeout interval in seconds that Radiator waits for when trying to connect or disconnect an SQL server specified by DBSource. If this parameter is not set, value of Timeout is used for connection handling. For more information about Timeout, see Section 3.8.5. Timeout.
Some databases may leak resources, such as file descriptors, when Radiator times out a connection before the DB driver does. With ConnectTimeout, SQL connection timeout can different than Timeout that is used for SQL queries.
# DB driver connect timeout is shorter than 20 seconds
# but we want 2 second query timeout
ConnectTimeout 20
Timeout 2

3.8.10. SQLRecoveryFile

Note
This feature is known not to work as expected with some types of database. Its use is deprecated: you are strongly discouraged from using this feature. Support for it may be removed in future versions.
This optional parameter specifies the name of a file where any failed SQL do queries aree logged, perhaps for later recovery. The default is no logging. The SQLRecovery-File file is always opened written and closed for each failed SQL do query, so you can safely rotate it at any time.
Tip
Make the file name depend on the date. This way, all the missed accounting records per day are located in a single file.
Here is an example of using SQLRecoveryFile and logging all failed queries of one day into a one file:
# Log all failed SQL queries to a log file per day
SQLRecoveryFile %L/sqlfailures-%Y-%m-%d

3.8.11. ConnectionHook

This optional parameter specifies a Hook that is run every time this clause connects or reconnects to the SQL database. This is most useful for executing func() to configure the database connection in customised ways. The hook is called with 2 arguments. The first is a reference to the clause object that is making the connection. The second argument is the DBH handle to the newly connected database.
In the following example, the hook calls DBI func() to configure an Interbase database connection for custom requirements:
ConnectionHook sub {$_[1]->func(-access_mode => 'read_write',\
      -isolation_level => 'read_committed',\
      -lock_resolution => 'wait',\
      'ib_set_tx_param')}

3.8.12. ConnectionAttemptFailedHook

You can run this hook whenever Radiator attempts to connect to an SQL database and fails to connect. The default is to log the failure. The hook is called with 4 arguments: $object, $dbsource, $dbusername, $dbauth. $object is the SqlDb object trying to connect. The other parameters are the currently used values for DBSource, DBUsername, and DBAuth.
In the following example the default hook is replaced with a hook that logs unobscured password.
ConnectionAttemptFailedHook sub { \
 my $self = $_[0]; my $dbsource = $_[1]; \
 my $dbusername = $_[2]; my $dbauth = $_[3]; \
 $self->log($main::LOG_ERR, "Could not connect to SQL database with DBI->connect \
            $dbsource, $dbusername, $dbauth: $@ $DBI::errstr"); }

3.8.13. NoConnectionsHook

You can run this hook whenever Radiator fails connect to any SQL server. The default is to log the failure. The hook is called with 1 argument: $object. $object is the SqlDb object that was trying to connect.
In the following example the default hook is replaced with a hook that logs a very short message.
NoConnectionsHook sub { \
  my $self = $_[0]; \
  $self->log($main::LOG_ERR, "Could not connect to any SQL database"); }

3.8.14. ConnectSQLAtStartup

This optional flag parameter causes radiusd to connect to SQL database immediately when Radiator starts. This flag is not set by default and the connection to SQL is first made when the first SQL query is done.
See AsynchronousSQL and goodies/addressallocator.sql for a configuration sample.

3.8.15. AsynchronousSQL

This optional flag parameter tells radiusd to use asynchronous SQL queries. This flag is not set by default and the queries are synchronous which means no processing is done before the reply, or timeout, is received from the SQL database. With asynchronous queries, radiusd can do other processing while the query is being processed by the database. When the the database result is ready, radiusd continues from the point where the asynchronous query was started.
Starting with Radiator 4.18, asynchronous queries are supported with MySQL, MariaDB and PostgreSQL. Testing was mainly done with DBD::mysql 4.035 with MariaDB 10.1.13.
Note
Asynchronous SQL queries were introduced with Radiator 4.18 and are only supported by AddressAllocator SQL clause.
Here is an example of AsynchronousSQL and the parameters typically used with it:
# See goodies/addressallocator.cfg for full example
# Run address allocator in asynchronous mode
<AddressAllocator SQL>
    # Other parameters
    AsynchronousSQL
    AsynchronousSQLConnections 10
    ConnectSQLAtStartup
    RoundRobinQueries
</AddressAllocator>

3.8.16. AsynchronousSQLConnections

This optional parameter defines the size of connection pool used when AsynchronousSQL is enabled. The default value is 1 which means that only a single connection is established with the database.
See AsynchronousSQL and goodies/addressallocator.sql for a configuration sample.

3.8.17. RoundRobinQueries

This optional flag parameter enables load balancing SQL queries to all defined DBSources when AsynchronousSQL is enabled. This flag is not set by default and only one of the defined DBSources is used at the time.
See AsynchronousSQL and goodies/addressallocator.sql for a configuration sample.