Active Query Listing Tool (AQL)

Welcome to the Active Query Listing (AQL) tool. This tool provides users with a listing of active queries on a set of database servers. AQL supports MySQL, MariaDB, PostgreSQL, and Redis, with additional database types planned. This PHP-based tool shows the list of queries running on a set of servers in a color-coded format showing the longest-running queries at the top of the list.

Please note that this software is somewhere between alpha and beta quality at this time.

Before using this tool:

Before using this tool, you'll need to install PHP 7.2 or greater. You'll also need to run the following composer command:

composer install

If you don't have a xAMP (like WAMP, MAMP, or LAMP) stack or Symphony / composer installed, Google is your friend. :-) There are lots of good guides out there on how to install these tools. I won't reproduce that excellent work here. I personally like XAMPP by ApacheFriends. For more information, see https://www.apachefriends.org/download.html

Configuring AQL

Your installation comes with a config_sample.xml file. Copy this file to aql_config.xml in the application directory. It should be readable only by the web server in order to protect it from prying eyes. This allows multiple AQL instances to run with different configurations on the same server.

Configuration Format (v2)

AQL uses a grouped XML configuration format (version 2). Settings are organized into semantic elements rather than flat key-value pairs:

<config version="2">
    <configdb type="mysql" host="127.0.0.1" port="3306" name="aql_db" />

    <user type="admin" name="aql_app" password="AdminPassword" />
    <user type="monitor" name="aql_mon" password="MonitorPassword" />

    <monitoring baseUrl="https://yourserver/ActiveQueryListing/AJAXgetaql.php"
                timeZone="America/Chicago" ... />

    <dbtype name="mysql" enabled="true" />
    <dbtype name="postgresql" enabled="true" />
    <dbtype name="redis" enabled="true" />
</config>

Key concepts:

• <configdb> — AQL's own database where host list and settings are stored. This is the only host defined in the config file.
• <user type="admin"> — Credentials for the configdb connection.
• <user type="monitor"> — Default credentials for all monitored hosts.
• <dbtype> — Controls which database types are enabled. Per-type credential overrides via username/password attributes when the monitor user differs.
• All monitored hosts are managed via manageData.php, not the config file.

See config_sample.xml for the full list of elements and attributes.

Upgrading from Legacy Format

If you have an existing aql_config.xml using the old flat <param> format, use the upgrade tool:

# Preview the conversion (dry run)
php upgradeConfig.php

# Apply the conversion (backs up to aql_config.xml.bk)
php upgradeConfig.php --write

The legacy format is still fully supported — the parser auto-detects the format.

Database Type Credentials

Monitoring credentials follow this resolution chain:

1. <user type="monitor"> — Default for all monitored hosts
2. <dbtype username="..." password="..."> — Per-type override (e.g., PostgreSQL needs a different user with pg_monitor role)
3. Redis — Uses its own auth model; does not inherit monitor credentials. Set credentials directly on <dbtype name="redis" password="..." />.

Validate your config with: xmllint --valid --noout aql_config.xml

Setup Steps

After creating your aql_config.xml, follow these steps in order:

Step 1: Verify Configuration (verifyAQLConfiguration.php)

Run the configuration verification tool first to catch any errors before deploying the schema:

https://your-server/ActiveQueryListing/verifyAQLConfiguration.php

Fix all errors reported by this tool before proceeding. It checks PHP extensions, config values, database connectivity, and permissions.

Step 2: Deploy Schema (deployDDL.php)

Once verification passes, deploy the database schema:

https://your-server/ActiveQueryListing/deployDDL.php

This creates all required tables and runs any pending migrations. It is idempotent and safe to run multiple times.

Step 3: Add Hosts (manageData.php)

Log in and add the database hosts you want to monitor. Set the DB type, environment, and alert thresholds for each host.

Verifying Your Configuration (verifyAQLConfiguration.php)

After creating your aql_config.xml, run the configuration verification tool to check that everything is set up correctly:

https://your-server/ActiveQueryListing/verifyAQLConfiguration.php

This tool helps new users get AQL running by checking:

• PHP Requirements - Verifies required extensions (mysqli, simplexml, curl, json) and optional extensions (ldap, openssl) are installed
• Configuration File - Checks that aql_config.xml exists, is readable, and has valid XML syntax
• Required Parameters - Validates all required config values (database credentials, URLs, timezone, etc.)
• Optional Parameters - Shows which optional features are configured
• Database Connectivity - Tests connection and verifies privileges using SHOW GRANTS (checks for PROCESS, REPLICATION CLIENT, performance_schema access)
• Schema Status - Checks if AQL tables exist and if hosts are configured
• LDAP Connectivity - Tests LDAP server reachability (when enabled)
• Jira Connectivity - Tests Jira API endpoint (when enabled)

The tool provides actionable fix instructions with copy-paste SQL commands and shell commands for both Apache and nginx deployments.

Installing Prerequisites

AQL requires a local MySQL or MariaDB database to store its configuration (hosts to monitor, groups, maintenance windows, etc.). Install the database server and PHP extensions if not already present:

Ubuntu/Debian:

# Database server (choose one)
sudo apt install mariadb-server    # MariaDB (recommended)
sudo apt install mysql-server      # Or MySQL

# PHP extensions
sudo apt install php-mysqli php-simplexml php-curl php-json php-ldap php-redis

# Restart web server
sudo systemctl restart apache2      # For Apache
sudo systemctl restart php-fpm nginx  # For nginx

Fedora/RHEL/CentOS:

# Database server (choose one)
sudo dnf install mariadb-server    # MariaDB (recommended)
sudo dnf install mysql-server      # Or MySQL
sudo systemctl enable --now mariadb

# PHP extensions
sudo dnf install php-mysqli php-xml php-curl php-json php-ldap php-phpiredis

# Restart web server
sudo systemctl restart httpd        # For Apache
sudo systemctl restart php-fpm nginx  # For nginx

After installing the database server, run mysql_secure_installation to set a root password and secure your installation.

Note: This tool works even with incomplete configuration, so you can use it to diagnose setup issues before AQL is fully functional.

Setting up the MySQL database on the "configuration" server

In order to use AQL, you'll need to set up the database that tells AQL where all the hosts will live as well as what the acceptable query thresholds are. To do this, simply play the setup_db.sql file into your configuration master database. It will wipe out the database named aql_db, then re-create it with template data.

The user and password you'll set up in aql_config.xml should be consistent across all your MySQL instances. The user will need the following on all the instances that AQL will manage (I'll assume you'll use aql_app for the user):

-- You should adjust these lines to meet your needs.
-- Minimally, you shoud at least change the password. I recommend also changing
-- the user and host mask (%) so you don't leave yourself overly vulnerable to
-- a denial of service attack. Note that some more recent systems require
-- SUPER privilege in order to kill processes that are owned by others. This
-- doesn't prevent the application from killing processes when the authorized
-- user has the appropriate privileges.
CREATE USER 'aql_app'@'%' IDENTIFIED BY 'SomethingComplicated' ; 
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'aql_app'@'%' ;
GRANT ALL ON aql_db.* TO 'aql_app'@'%' ;
CREATE DATABASE IF NOT EXISTS aql_db ;

Note about the "second" instance

During testing, we use row four from the host table as a replication slave of row 1.

While setting up a replication instance is beyond the scope of these instructions, row four of the host database assumes that you have a replication slave set up on port 3307. If you want to ignore that "system," simply change the decommissioned setting to 1 and should_monitor to 0.

Blocked/Blocking Query Detection

AQL can detect and display blocked and blocking queries. When a query is waiting for a lock held by another query, you'll see:

• BLOCKED (hotpink) - Query is waiting for a lock
• BLOCKING (red) - Query is holding a lock that others are waiting for

Hover over the indicator to see details including the blocking thread ID and query.

Known MySQL Quirks

MySQL has bugs around reporting blocking/blocked state. Sometimes it will report a thread as blocked but show no associated query text (empty or NULL info column). This can happen when:

• The blocking query just completed but the blocked thread hasn't updated yet
• Internal lock manager state is temporarily inconsistent
• The query text has been truncated or is unavailable

AQL displays what MySQL reports. If you see a "BLOCKED" indicator with no query, the thread genuinely was waiting for a lock at the moment of sampling.

Blocking Cache (Optional Redis Setup)

AQL caches blocking relationships for 60 seconds so you can see "who was blocking" even after the blocker finishes. This is especially useful for transient MyISAM table-level locks.

Cache backends (automatic detection):

1. Redis (recommended) - Faster, automatic TTL expiry, no file permission issues
2. File-based (fallback) - Uses /cache directory if Redis unavailable

To enable Redis caching:

# Install Redis server (if not already installed)
sudo apt install redis-server

# Install PHP Redis extension
sudo apt install php-redis

# Restart Apache to load the extension
sudo systemctl restart apache2

The cache directory (/cache) must be writable by the web server if using file-based caching:

sudo chown www-data:www-data /path/to/aql/cache

Lock Detection Debug Mode

Add &debugLocks=1 to the URL to enable lock detection debugging. This shows:

• Cache type (redis/file)
• Lock wait count
• Open tables with locks
• Blocking cache contents

Additional MySQL Permissions for Lock Detection

For enhanced lock detection on MySQL 8.0+, grant these permissions to the AQL user:

-- Optional: for InnoDB row-level and metadata lock detection
GRANT SELECT ON performance_schema.data_lock_waits TO 'aql_app'@'%';
GRANT SELECT ON performance_schema.data_locks TO 'aql_app'@'%';
GRANT SELECT ON performance_schema.metadata_locks TO 'aql_app'@'%';
GRANT SELECT ON performance_schema.threads TO 'aql_app'@'%';

Note: Basic table-level lock detection works without these additional permissions.

Blocking History Logging

AQL automatically logs blocking queries to the blocking_history table for pattern analysis. This helps identify repeat offender queries that frequently cause lock contention.

Features:

• Queries are normalized (strings/numbers replaced with placeholders) to avoid storing sensitive data
• Deduplication via query hash - each unique query pattern stored once per host to reduce table bloat and allow statistics to be aggregated
• Tracks how many times a query was seen blocking and total blocked queries
• Auto-purges entries older than 90 days (runs on ~1% of requests)

Table schema: Run deployDDL.php to create the blocking_history table.

Viewing history: Query the table directly:

-- Top 10 most frequent blocking queries
SELECT h.hostname, bh.user, bh.blocked_count, bh.total_blocked, bh.query_text, bh.last_seen
FROM aql_db.blocking_history bh
JOIN aql_db.host h ON h.host_id = bh.host_id
ORDER BY bh.blocked_count DESC
LIMIT 10;

Setting Up Redis Monitoring

AQL can monitor Redis instances for memory usage, connected clients, command statistics, slowlog entries, and more. Redis monitoring requires the phpredis PHP extension (php-redis package).

Redis Authentication

Redis supports two authentication modes. AQL works with both.

Option 1: Legacy Password (Redis < 6, or simple setups)

This mode uses a single shared password with no usernames. It is configured in redis.conf:

requirepass YourRedisPassword

Restart Redis after changing the config:

sudo systemctl restart redis

In AQL's aql_config.xml, set the password only (leave username empty):

<dbtype name="redis" enabled="true" password="YourRedisPassword" />

Option 2: ACL Users (Redis 6+, recommended)

Redis 6+ supports per-user access control with fine-grained command permissions. This is the recommended approach for production.

Create a monitoring user on the Redis instance:

redis-cli
> ACL SETUSER aql_mon on >YourRedisPassword ~* &* +@all -@dangerous

This creates a user aql_mon that can read all keys and run most commands but is blocked from dangerous operations (FLUSHALL, DEBUG, CONFIG SET, etc.).

For a more restrictive monitoring-only user:

> ACL SETUSER aql_mon on >YourRedisPassword ~* &* +info +client +slowlog +memory +ping +select +scan +type +object +command +latency +pubsub +xinfo +xlen +xrange +xpending

To persist ACL changes across restarts, either:

• Save to the ACL file: ACL SAVE (requires aclfile in redis.conf)
• Or add the rule to redis.conf directly: user aql_mon on >YourRedisPassword ...

In AQL's aql_config.xml:

<dbtype name="redis" enabled="true" username="aql_mon" password="YourRedisPassword" />

No Authentication (not recommended)

Redis instances without authentication work with AQL out of the box — just enable Redis monitoring with no credentials:

<dbtype name="redis" enabled="true" />

This is a security risk. Any client that can reach the Redis port has full access. At minimum, use firewall rules to restrict access and consider enabling authentication even in development environments.

Redis Network Access

Ensure the Redis instance is listening on a network interface (not just localhost) and that firewall rules allow the AQL server to reach port 6379:

# In redis.conf, change bind to include the server's IP or 0.0.0.0:
bind 0.0.0.0

# Also set protected-mode to no if using network access without auth:
protected-mode no

Restart Redis after changes: sudo systemctl restart redis

Setting Up PostgreSQL Monitoring

AQL monitors PostgreSQL instances using pg_stat_activity for active queries, with lock detection and replication monitoring planned.

PostgreSQL Monitoring User

Create a dedicated monitoring user with the pg_monitor role (PostgreSQL 10+):

CREATE USER aql_mon WITH PASSWORD 'YourPassword';
GRANT pg_monitor TO aql_mon;

The pg_monitor role grants read access to pg_stat_activity, pg_stat_replication, and other monitoring views without requiring superuser privileges.

PostgreSQL Network Access (pg_hba.conf)

Add a line to pg_hba.conf allowing the AQL server to connect:

# Allow AQL monitoring connections
host    all    aql_mon    <aql-server-ip>/32    scram-sha-256

Reload PostgreSQL after changes:

sudo systemctl reload postgresql

AQL Configuration

In aql_config.xml:

<dbtype name="postgresql" enabled="true" username="aql_mon" password="YourPassword" />

Test Harness (testAQL.php)

AQL includes a test harness (testAQL.php) for validating your configuration and testing AQL features. The test harness is safe to run on production servers as it uses a dedicated test database and does not modify production data.

Available Tests

• Validate Configuration - Checks aql_config.xml parameters, validates values (URLs, ports, timezones), and tests database connectivity. Run this first to ensure AQL is properly configured.

• Application Smoke Test - Fetches main AQL pages (index.php, manageData.php, testAQL.php) and verifies they return HTTP 200 without PHP errors. Useful for verifying the application is properly installed.

• Database User Verification - Tests both the application user (usually aql_app) and test user (usually aql_test) connectivity on the config server and all monitored MySQL/MariaDB hosts. For the app user, also checks PROCESS, REPLICATION CLIENT, and performance_schema privileges.

• Schema Verification - Read-only check that verifies the aql_db database exists, all required tables are present (host, host_group, maintenance_window, etc.), and required columns exist in each table.

• Deploy DDL Verification - Verifies that deployDDL.php will run without errors. Shows which tables exist vs would be created, and which migrations are pending vs already applied. For up-to-date installs, confirms schema is current.

• Automated Blocking Test - Creates a test table, runs two MySQL sessions in parallel (one holding a lock, one waiting), and verifies lock detection works correctly.

• Test Blocking JavaScript - Verifies that the JavaScript modifications for the "File Issue" button work correctly when a query is blocking others.

• Jira Integration Test - Manual test with step-by-step instructions for verifying Jira issue filing works. Shows current Jira configuration status and simple test steps.

• Cleanup Test Data - Removes test tables created during testing.

Test Database Setup (Optional but Recommended)

To use tests that require database operations, create a dedicated test user and database:

-- Create a database for testing
CREATE DATABASE IF NOT EXISTS aql_test;

-- Create the test user with privileges on the test database
CREATE USER 'aql_test'@'localhost' IDENTIFIED BY 'YourTestPassword';
GRANT ALL PRIVILEGES ON aql_test.* TO 'aql_test'@'localhost';

-- The test user also needs PROCESS privilege to see other connections
GRANT PROCESS ON *.* TO 'aql_test'@'localhost';

Then add the test configuration to aql_config.xml:

<param name="testDbUser">aql_test</param>
<param name="testDbPass">YourTestPassword</param>
<param name="testDbName">aql_test</param>

Access the Test Harness

Navigate to https://your-server.your-company.com/ActiveQueryListing/testAQL.php

Note: The test harness only operates on the local configuration database server and the dedicated test database. It will not affect production database servers or data.

Command Line Test Runner

For bash users, a command-line test runner is available:

# Run all CLI-compatible tests
./run_tests.sh

# Run a specific test
./run_tests.sh config_validate
./run_tests.sh schema_verify

# Show available tests
./run_tests.sh --help

Some tests require web context and are skipped when run from CLI (marked with * in help). Run these tests via browser instead.

SELinux Installation Tips for Fedora/Redhat/CentOS

In order to allow this program to run under Fedora-based systems, it's important to either turn off SELinux completely (yuck), or to make it possible for programs running under your web server to connect to the database and read files stored in the web server's directory. Older versions of Fedora Linux required these commands.

sudo setsebool -P httpd_can_network_connect_db 1
sudo setsebool -P httpd_read_user_content 1

For more information on ActiveQueryListing, please see:

https://github.com/kbcmdba/ActiveQueryListing/