ProxySQL Integration Tests #108
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: ProxySQL Integration Tests | |
| on: | |
| push: | |
| branches: [master] | |
| pull_request: | |
| branches: [master] | |
| schedule: | |
| - cron: '0 3 * * *' | |
| workflow_dispatch: | |
| jobs: | |
| proxysql-test: | |
| name: ProxySQL + MySQL ${{ matrix.mysql-version }} | |
| runs-on: ubuntu-22.04 | |
| strategy: | |
| fail-fast: false | |
| matrix: | |
| mysql-version: | |
| - '8.4.4' | |
| - '9.1.0' | |
| env: | |
| GO111MODULE: on | |
| SANDBOX_BINARY: ${{ github.workspace }}/opt/mysql | |
| MYSQL_VERSION: ${{ matrix.mysql-version }} | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: actions/setup-go@v5 | |
| with: | |
| go-version: '1.22' | |
| - name: Install system libraries | |
| run: | | |
| sudo apt-get update | |
| sudo apt-get install -y libaio1 libnuma1 libncurses5 mysql-client | |
| - name: Install ProxySQL | |
| run: | | |
| # Download deb and extract binary without installing the package | |
| PROXYSQL_VERSION="3.0.6" | |
| wget -nv -O /tmp/proxysql.deb \ | |
| "https://github.com/sysown/proxysql/releases/download/v${PROXYSQL_VERSION}/proxysql_${PROXYSQL_VERSION}-ubuntu22_amd64.deb" | |
| mkdir -p /tmp/proxysql-extract | |
| dpkg-deb -x /tmp/proxysql.deb /tmp/proxysql-extract | |
| sudo cp /tmp/proxysql-extract/usr/bin/proxysql /usr/local/bin/proxysql | |
| sudo chmod +x /usr/local/bin/proxysql | |
| which proxysql | |
| - name: Build dbdeployer | |
| run: go build -o dbdeployer . | |
| - name: Cache MySQL tarball | |
| uses: actions/cache@v4 | |
| with: | |
| path: /tmp/mysql-tarball | |
| key: mysql-${{ matrix.mysql-version }}-linux-x86_64-v1 | |
| - name: Download and unpack MySQL | |
| run: | | |
| SHORT_VER=$(echo "$MYSQL_VERSION" | grep -oP '^\d+\.\d+') | |
| TARBALL="mysql-${MYSQL_VERSION}-linux-glibc2.17-x86_64.tar.xz" | |
| mkdir -p /tmp/mysql-tarball | |
| if [ ! -f "/tmp/mysql-tarball/$TARBALL" ]; then | |
| curl -L -f -o "/tmp/mysql-tarball/$TARBALL" \ | |
| "https://dev.mysql.com/get/Downloads/MySQL-${SHORT_VER}/$TARBALL" \ | |
| || curl -L -f -o "/tmp/mysql-tarball/$TARBALL" \ | |
| "https://downloads.mysql.com/archives/get/p/23/file/$TARBALL" | |
| fi | |
| mkdir -p "$SANDBOX_BINARY" | |
| ./dbdeployer unpack "/tmp/mysql-tarball/$TARBALL" --sandbox-binary="$SANDBOX_BINARY" | |
| - name: Run ProxySQL integration tests | |
| run: | | |
| export PATH="$PWD:$PATH" | |
| export MYSQL_VERSION_1="$MYSQL_VERSION" | |
| export MYSQL_VERSION_2="" | |
| ./test/proxysql-integration-tests.sh "$SANDBOX_BINARY" | |
| - name: Test R/W split verification (#66) | |
| run: | | |
| echo "=== Deploy replication + ProxySQL for R/W split test ===" | |
| ./dbdeployer deploy replication "$MYSQL_VERSION" \ | |
| --sandbox-binary="$SANDBOX_BINARY" \ | |
| --with-proxysql | |
| SANDBOX_DIR=~/sandboxes/rsandbox_$(echo "$MYSQL_VERSION" | tr '.' '_') | |
| echo "=== Add query rules for R/W split ===" | |
| # Route SELECTs to reader hostgroup (HG 1), writes stay on HG 0 | |
| ${SANDBOX_DIR}/proxysql/use -e " | |
| INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) | |
| VALUES (1, 1, '^SELECT', 1, 1); | |
| LOAD MYSQL QUERY RULES TO RUNTIME; | |
| SAVE MYSQL QUERY RULES TO DISK; | |
| " 2>&1 | grep -v Warning | |
| echo "=== Baseline: record current query counts per hostgroup ===" | |
| HG0_BEFORE=$(${SANDBOX_DIR}/proxysql/use -BN -e "SELECT COALESCE(SUM(Queries),0) FROM stats_mysql_connection_pool WHERE hostgroup=0;" 2>&1 | grep -v Warning) | |
| HG1_BEFORE=$(${SANDBOX_DIR}/proxysql/use -BN -e "SELECT COALESCE(SUM(Queries),0) FROM stats_mysql_connection_pool WHERE hostgroup=1;" 2>&1 | grep -v Warning) | |
| echo "HG0 queries before: $HG0_BEFORE" | |
| echo "HG1 queries before: $HG1_BEFORE" | |
| echo "=== Run a write (INSERT) through ProxySQL ===" | |
| ${SANDBOX_DIR}/proxysql/use_proxy -e "CREATE DATABASE IF NOT EXISTS rw_split_test; CREATE TABLE IF NOT EXISTS rw_split_test.t1 (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(100)); INSERT INTO rw_split_test.t1 (val) VALUES ('rw_split_write_1');" 2>&1 | grep -v Warning | |
| sleep 2 | |
| echo "=== Run reads (SELECTs) through ProxySQL ===" | |
| for i in $(seq 1 5); do | |
| ${SANDBOX_DIR}/proxysql/use_proxy -BN -e "SELECT val FROM rw_split_test.t1;" > /dev/null 2>&1 || true | |
| done | |
| sleep 2 | |
| echo "=== Check query counts increased ===" | |
| HG0_AFTER=$(${SANDBOX_DIR}/proxysql/use -BN -e "SELECT COALESCE(SUM(Queries),0) FROM stats_mysql_connection_pool WHERE hostgroup=0;" 2>&1 | grep -v Warning) | |
| HG1_AFTER=$(${SANDBOX_DIR}/proxysql/use -BN -e "SELECT COALESCE(SUM(Queries),0) FROM stats_mysql_connection_pool WHERE hostgroup=1;" 2>&1 | grep -v Warning) | |
| echo "HG0 queries after: $HG0_AFTER (was $HG0_BEFORE)" | |
| echo "HG1 queries after: $HG1_AFTER (was $HG1_BEFORE)" | |
| # Writes should have routed to HG0 | |
| [ "$HG0_AFTER" -gt "$HG0_BEFORE" ] || { echo "FAIL: expected HG0 (writer) query count to increase"; exit 1; } | |
| echo "OK: HG0 (writer) received queries" | |
| # Reads should have routed to HG1 via query rules | |
| [ "$HG1_AFTER" -gt "$HG1_BEFORE" ] || { echo "FAIL: expected HG1 (reader) query count to increase"; exit 1; } | |
| echo "OK: HG1 (reader) received queries — R/W split is working" | |
| echo "=== Verify written data is readable through proxy ===" | |
| RESULT=$(${SANDBOX_DIR}/proxysql/use_proxy -BN -e "SELECT val FROM rw_split_test.t1 WHERE val='rw_split_write_1';" 2>&1 | grep -v Warning) | |
| echo "Result: $RESULT" | |
| echo "$RESULT" | grep -q "rw_split_write_1" || { echo "FAIL: written data not readable through proxy"; exit 1; } | |
| echo "OK: R/W split write+read data flow verified" | |
| echo "=== Cleanup ===" | |
| ./dbdeployer delete all --skip-confirm | |
| - name: Cleanup | |
| if: always() | |
| run: | | |
| ./dbdeployer delete all --skip-confirm 2>/dev/null || true | |
| pkill -9 -u "$USER" proxysql 2>/dev/null || true | |
| pkill -9 -u "$USER" mysqld 2>/dev/null || true | |
| # Test ProxySQL + PostgreSQL wiring | |
| proxysql-postgresql: | |
| name: ProxySQL + PostgreSQL | |
| runs-on: ubuntu-22.04 | |
| env: | |
| GO111MODULE: on | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: actions/setup-go@v5 | |
| with: | |
| go-version: '1.22' | |
| - name: Add PostgreSQL apt repo | |
| run: | | |
| sudo apt-get update | |
| sudo apt-get install -y curl ca-certificates | |
| sudo install -d /usr/share/postgresql-common/pgdg | |
| sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc | |
| echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list | |
| sudo apt-get update | |
| - name: Install ProxySQL | |
| run: | | |
| PROXYSQL_VERSION="3.0.6" | |
| wget -nv -O /tmp/proxysql.deb \ | |
| "https://github.com/sysown/proxysql/releases/download/v${PROXYSQL_VERSION}/proxysql_${PROXYSQL_VERSION}-ubuntu22_amd64.deb" | |
| mkdir -p /tmp/proxysql-extract | |
| dpkg-deb -x /tmp/proxysql.deb /tmp/proxysql-extract | |
| sudo cp /tmp/proxysql-extract/usr/bin/proxysql /usr/local/bin/proxysql | |
| sudo chmod +x /usr/local/bin/proxysql | |
| which proxysql | |
| - name: Build dbdeployer | |
| run: go build -o dbdeployer . | |
| - name: Install PostgreSQL and set up binaries | |
| run: | | |
| sudo apt-get install -y postgresql-16 postgresql-client-16 | |
| sudo systemctl stop postgresql || true | |
| PG_FULL=$(dpkg -s postgresql-16 | grep '^Version:' | sed 's/Version: //' | cut -d'-' -f1) | |
| echo "PostgreSQL version: ${PG_FULL}" | |
| mkdir -p ~/opt/postgresql/${PG_FULL}/{bin,lib,share} | |
| cp -a /usr/lib/postgresql/16/bin/. ~/opt/postgresql/${PG_FULL}/bin/ | |
| cp -a /usr/lib/postgresql/16/lib/. ~/opt/postgresql/${PG_FULL}/lib/ | |
| cp -a /usr/share/postgresql/16/. ~/opt/postgresql/${PG_FULL}/share/ | |
| - name: Test replication with --with-proxysql | |
| run: | | |
| PG_FULL=$(ls ~/opt/postgresql/ | head -1) | |
| echo "=== Deploying PostgreSQL $PG_FULL replication + ProxySQL ===" | |
| ./dbdeployer deploy replication "$PG_FULL" --provider=postgresql --with-proxysql | |
| SBDIR=$(ls -d ~/sandboxes/postgresql_repl_*) | |
| echo "=== Check ProxySQL is running ===" | |
| $SBDIR/proxysql/status | |
| echo "=== Check ProxySQL has pgsql_servers configured ===" | |
| $SBDIR/proxysql/use -e "SELECT * FROM pgsql_servers;" || true | |
| echo "=== Functional test: verify PostgreSQL replication works ===" | |
| $SBDIR/primary/use -c "CREATE TABLE proxy_test(id serial, val text); INSERT INTO proxy_test(val) VALUES ('pg_proxysql_test');" | |
| sleep 2 | |
| RESULT=$($SBDIR/replica1/use -c "SELECT val FROM proxy_test;" 2>&1) | |
| echo "$RESULT" | |
| echo "$RESULT" | grep -q "pg_proxysql_test" || { echo "FAIL: PG replication not working"; exit 1; } | |
| echo "OK: PostgreSQL replication verified with ProxySQL deployed" | |
| echo "=== ProxySQL proxy connection test (pgsql auth WIP) ===" | |
| $SBDIR/proxysql/use_proxy -c "SELECT 1;" || echo "WARN: ProxySQL pgsql proxy connection failed (expected - auth config WIP)" | |
| - name: Cleanup | |
| if: always() | |
| run: | | |
| for dir in ~/sandboxes/postgresql_*; do | |
| [ -d "$dir/proxysql" ] && bash "$dir/proxysql/stop" 2>/dev/null || true | |
| [ -d "$dir" ] && bash "$dir/stop" 2>/dev/null || true | |
| rm -rf "$dir" | |
| done 2>/dev/null || true | |
| pkill -9 -u "$USER" proxysql 2>/dev/null || true | |
| pkill -9 -u "$USER" postgres 2>/dev/null || true |