SQL Connections Log

The SQL connection to the NuoDB database is a two-stage process. First, the SQL client establishes a connection to the NuoDB Admin tier, which selects a specific Transaction Engine (TE) to service the request. Then the SQL client establishes a new connection to the returned TE where the authentication is performed.

For more information about SQL connection authentication logging, see login-audit category in Logging Options.

Each Admin Process (AP) maintains an SQL connections log which includes messages for all attempted SQL client connections through a particular AP. This includes database name, SQL connection properties, and the TE process that SQL connection has been routed to.

The log is written to $NUODB_LOGDIR/nuoadmin_sqlconn.log, which is /var/log/nuodb/nuoadmin_sqlconn.log by default on Linux. See Environment Variables for more information on log file locations.

Examples

Successfully returned Transaction Engine (TE)

For example, when a TE is successfully found based on the configured load balancer policies and returned to the SQL client, a similar message is logged.

2021-03-17T15:14:11.607+0000 SQL connection for dbName=demo, properties={Ciphers=AES-256-CTR,AES-128-CTR,RC4, clientProcessID=280, User=dba, Schema=user, Service=SQL2, clientInfo=nuosql, ClientAddress=172.21.0.2, LBPolicy=zone1, Thread=280, connection_protocol=1, TimeZone=UTC, LocalServerId=nuoadmin1, Database=demo}: DomainProcess{startId=1, hostId=nuoadmin1, engineType=TE, requestState=MONITORED, node=EngineNode{databaseName=demo, address=test-te-1, port=48006, type=TE, pid=38, state=RUNNING, nodeId=2, version=4.2.master-8493, ipAddress=172.21.0.6, hostname=test-te-1}, handoffMode=Optional[false]}

The above connection has been made by nuosql client utility which is seen in the clientInfo SQL property using the custom zone1 load balancer policy seen in LBPolicy SQL property.

If the database default load balancer has been used when processing client SQL connection, the below log message will be seen right before the SQL connection message.

2021-03-17T16:01:47.294+0000 Using generic load-balancer for database demo

For more details filtering TEs and NuoDB Admin load balancer, see Load Balancer Policies.

No Transaction Engine (TE) returned

When there is no matching TE, the below message is logged.

2021-03-17T16:37:21.208+0000 No suitable TEs for dbName=demo, properties={Ciphers=AES-256-CTR,AES-128-CTR,RC4, clientProcessID=447, User=dba, Schema=user, Service=SQL2, clientInfo=nuosql, ClientAddress=172.21.0.2, LBPolicy=zone2-only, Thread=447, connection_protocol=1, TimeZone=UTC, LocalServerId=nuoadmin1, Database=demo}

Reporting

A report of all SQL connections made through the NuoDB Admin tier for the NuoDB domain can be extracted from the SQL connections log files using a combination of text parsing tools.

For example, to get a report structured in form of time database user schema clientInfo remoteAddress nodeId:

grep "SQL connection" /var/log/nuodb/nuoadmin_sqlconn.log | \
    sed 's/\([^ ]*\).*dbName=\([^,]*\).*User=\([^,]*\).*Schema=\([^,]*\).*clientInfo=\([^,]*\).*ClientAddress=\([^,]*\).*nodeId=\([^,]*\).*/\1\t\2\t\3\t\4\t\5\t\6\t\7/'

2020-11-13T10:41:42.961+0000    demo    dba     user    nuosql  127.0.0.1   2
2020-11-13T10:43:30.482+0000    demo    admin   user    nuosql  127.0.0.1   2
2020-11-13T11:01:02.051+0000    demo    admin   user    nuosql  127.0.0.1   3
2020-11-13T11:01:15.942+0000    demo    admin   user    nuosql  127.0.0.1   3
2020-11-13T11:04:37.334+0000    demo    admin   user    nuosql  127.0.0.1   3
2020-11-13T11:04:50.861+0000    demo    admin   user    nuosql  127.0.0.1   3
2020-11-13T11:10:40.076+0000    demo    admin   user    nuosql  127.0.0.1   2
2020-11-13T11:10:47.607+0000    demo    admin   user    nuosql  127.0.0.1   2
2020-11-13T11:11:52.389+0000    demo    dba     user    nuosql  127.0.0.1   2

The above command is parsing the log from just one AP. To get a global view, SQL connection logs from all APs should be parsed.