#!/usr/bin/env bash
#   ./repro_mdev38697.sh --socket /path/to/socket.sock --user <user> --duration 900 --concurrency 96 --client <mariadb client location>

set -uo pipefail

HOST=""
PORT=""
SOCKET=""
DBUSER="root"
DBPASS=""
DURATION=600
CONCURRENCY=64
DB="mdev38697_repro"
CLIENT_BIN=""

while [[ $# -gt 0 ]]; do
  case "$1" in
    --host) HOST="$2"; shift 2 ;;
    --port) PORT="$2"; shift 2 ;;
    --socket) SOCKET="$2"; shift 2 ;;
    --user) DBUSER="$2"; shift 2 ;;
    --password) DBPASS="$2"; shift 2 ;;
    --duration) DURATION="$2"; shift 2 ;;
    --concurrency) CONCURRENCY="$2"; shift 2 ;;
    --client) CLIENT_BIN="$2"; shift 2 ;;
    *) echo "Unknown argument: $1"; exit 1 ;;
  esac
done

# Auto-detect the client binary if not explicitly given: prefer 'mariadb',
# fall back to 'mysql' (older client name), both checked on PATH first.
if [[ -z "$CLIENT_BIN" ]]; then
  if command -v mariadb >/dev/null 2>&1; then
    CLIENT_BIN="mariadb"
  elif command -v mysql >/dev/null 2>&1; then
    CLIENT_BIN="mysql"
  else
    echo "[!] No 'mariadb' or 'mysql' client found on PATH."
    echo "    For a custom build, pass the client binary explicitly, e.g.:"
    echo "    --client /home/qauser/Projects/mariadb/10.11_release/data/Server_bin/10.11_release/bin/mariadb"
    exit 1
  fi
fi

if [[ ! -x "$(command -v "$CLIENT_BIN" 2>/dev/null || echo "$CLIENT_BIN")" ]]; then
  echo "[!] Client binary not found or not executable: $CLIENT_BIN"
  exit 1
fi

MYSQL_BASE=("$CLIENT_BIN" -u "$DBUSER")
if [[ -n "$SOCKET" ]]; then
  MYSQL_BASE+=(-S "$SOCKET")
elif [[ -n "$HOST" ]]; then
  MYSQL_BASE+=(-h "$HOST" -P "${PORT:-3306}")
else
  echo "[!] You must specify either --socket <path> or --host <host> (--port optional, default 3306)."
  exit 1
fi
if [[ -n "$DBPASS" ]]; then
  MYSQL_BASE+=(-p"$DBPASS")
fi

LOGDIR="$(mktemp -d)"
echo "Working dir / logs: $LOGDIR"
echo "Target: $HOST:$PORT  user=$DBUSER  duration=${DURATION}s  concurrency=$CONCURRENCY"

# ---------------------------------------------------------------------------
# 1. Schema setup: recreate the reported 'users' table shape (trimmed to the
#    columns that matter for building a long, realistic SELECT list) and
#    seed it with enough rows that WHERE lookups return real data.
# ---------------------------------------------------------------------------
echo "[*] Creating schema..."
"${MYSQL_BASE[@]}" <<SQL
DROP DATABASE IF EXISTS ${DB};
CREATE DATABASE ${DB};
USE ${DB};

CREATE TABLE users (
  USERINDEX int(11) NOT NULL AUTO_INCREMENT,
  USERID varchar(253) NOT NULL,
  PASSWORD varchar(129) DEFAULT NULL,
  PASSWORDSOURCE smallint(6) DEFAULT 0,
  GROUPNAME varchar(64) DEFAULT NULL,
  USERSERVICE bigint(20) DEFAULT 0,
  USERIP bigint(20) DEFAULT NULL,
  FILTERNAME varchar(253) DEFAULT NULL,
  STARTDATE datetime DEFAULT NULL,
  USEREXPIRYDATE datetime DEFAULT NULL,
  USERACTIVE smallint(6) NOT NULL DEFAULT 1,
  CALLBACKNUMBER varchar(20) DEFAULT NULL,
  CALLERID varchar(30) DEFAULT NULL,
  LOCKOUT smallint(6) NOT NULL DEFAULT 0,
  LOCKOUTTIME datetime DEFAULT NULL,
  LOCKOUTCOUNT smallint(6) DEFAULT 0,
  PSEUDOIDENTITY varchar(255) DEFAULT NULL,
  PASSWORDENCRYPTIONMETHOD int(11) DEFAULT 0,
  SESSIONLIMIT int(11) DEFAULT 0,
  USERTOKENS varchar(200) DEFAULT NULL,
  MAXUSERSESSIONS int(11) NOT NULL DEFAULT 0,
  SKIPPASSWORDCHECK smallint(6) NOT NULL DEFAULT 0,
  HOTLINESTATUS int(11) DEFAULT 0,
  BARRINGSTATUS smallint(6) NOT NULL DEFAULT 0,
  BARRINGREASON varchar(512) DEFAULT NULL,
  MAXVOLUMECAP bigint(20) DEFAULT 0,
  ALLOWEDACCESSTYPES varchar(64) DEFAULT NULL,
  COPYRADIUSREQUESTS int(11) DEFAULT NULL,
  SUBNETIP int(11) NOT NULL DEFAULT 0,
  SUBNETMASK varchar(20) DEFAULT NULL,
  CIDR varchar(20) DEFAULT NULL,
  CUSTOMFIELD1 varchar(128) DEFAULT NULL,
  CUSTOMFIELD2 varchar(128) DEFAULT NULL,
  CUSTOMFIELD3 varchar(128) DEFAULT NULL,
  CUSTOMFIELD4 varchar(128) DEFAULT NULL,
  CUSTOMFIELD5 varchar(256) DEFAULT NULL,
  CUSTOMFIELD6 varchar(128) DEFAULT NULL,
  CUSTOMFIELD7 varchar(128) DEFAULT NULL,
  CUSTOMFIELD8 varchar(128) DEFAULT NULL,
  CUSTOMFIELD9 varchar(128) DEFAULT NULL,
  CUSTOMFIELD10 varchar(256) DEFAULT NULL,
  CUSTOMFIELD11 varchar(128) DEFAULT NULL,
  CUSTOMFIELD12 varchar(128) DEFAULT NULL,
  CUSTOMFIELD13 varchar(128) DEFAULT NULL,
  CUSTOMFIELD14 varchar(128) DEFAULT NULL,
  CUSTOMFIELD15 varchar(2048) DEFAULT NULL,
  CUSTOMNUMFIELD1 int(11) DEFAULT 0,
  CUSTOMNUMFIELD2 int(11) DEFAULT 0,
  CUSTOMNUMFIELD3 int(11) DEFAULT 0,
  CUSTOMNUMFIELD4 int(11) DEFAULT 0,
  CUSTOMNUMFIELD5 int(11) DEFAULT 0,
  CUSTOMNUMFIELD6 double DEFAULT 0,
  CUSTOMNUMFIELD7 double DEFAULT 0,
  CUSTOMNUMFIELD8 double DEFAULT 0,
  CUSTOMNUMFIELD9 double DEFAULT 0,
  CUSTOMNUMFIELD10 double DEFAULT 0,
  FIRSTLOGON datetime DEFAULT NULL,
  FRAMEDIPV6PREFIX varchar(64) DEFAULT NULL,
  FRAMEDINTERFACEID varchar(64) DEFAULT NULL,
  PERIODICTIMEUSAGE int(11) DEFAULT NULL,
  PERIODICVOLUMEUSAGE bigint(20) DEFAULT NULL,
  TIMEUSAGETRIGGERTHRESHOLDS varchar(1024) DEFAULT NULL,
  VOLUMEUSAGETRIGGERTHRESHOLDS varchar(1024) DEFAULT NULL,
  CUSTOMREPLYLIST varchar(2048) DEFAULT NULL,
  CUSTOMCHECKLIST varchar(1024) DEFAULT NULL,
  BILLINGSYSTEMCORRELATIONID varchar(256) DEFAULT NULL,
  CHARGINGSYSTEMCORRELATIONID varchar(256) DEFAULT NULL,
  CHARGINGMODE int(11) DEFAULT NULL,
  CUSTOMDATE1 datetime DEFAULT NULL,
  CUSTOMDATE2 datetime DEFAULT NULL,
  CUSTOMDATE3 datetime DEFAULT NULL,
  CUSTOMDATE4 datetime DEFAULT NULL,
  CUSTOMDATE5 datetime DEFAULT NULL,
  LASTMODIFY datetime DEFAULT NULL,
  TENANTID varchar(255) DEFAULT NULL,
  CREATEDBY int(11) DEFAULT NULL,
  PASSWORDDATE datetime DEFAULT NULL,
  RESTRICTEDLOCATIONS varchar(255) DEFAULT NULL,
  MAXTIMECAP bigint(20) DEFAULT 0,
  SESSIONLIMITUNIT int(11) DEFAULT 0,
  LASTLOGOFFTIME datetime DEFAULT NULL,
  LASTUSAGECHARGED datetime DEFAULT NULL,
  PORTID varchar(255) DEFAULT NULL,
  SPEEDMAPNAME varchar(32) DEFAULT NULL,
  USERIPV6 varchar(64) DEFAULT NULL,
  NETWORKSERVICE varchar(256) DEFAULT NULL,
  CREATEDATE datetime DEFAULT NULL,
  PRIMARY KEY (USERINDEX),
  UNIQUE KEY UQ_USERS_LOGIN (USERID),
  KEY USERACTIVE (USERACTIVE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Seed rows via a numbers-table cross join so we get enough realistic data
-- for concurrent lookups to hit real rows.
INSERT INTO users (USERID, PASSWORD, GROUPNAME, STARTDATE, CUSTOMFIELD1, CUSTOMFIELD3,
                    CUSTOMNUMFIELD1, CUSTOMNUMFIELD2, USERTOKENS, PORTID)
SELECT
  CONCAT('034299995', LPAD(seq, 8, '0'), 'FGH'),
  MD5(seq),
  678,
  NOW() - INTERVAL (seq % 1000) DAY,
  CONCAT('1/9/9/', seq % 20),
  CONCAT('10.139.', seq % 255, '.', (seq*7) % 255),
  seq % 5,
  seq % 3,
  IF(seq % 2 = 0, 'TURBOBOOST', NULL),
  CONCAT('10.139.155.', seq % 255, '-LTR-LHR-Site-Z-G pon 1/1/09/9/', seq % 20, '/1:', 1000+seq)
FROM (
  SELECT a.seq + b.seq*1000 AS seq
  FROM (SELECT @r := @r + 1 AS seq FROM information_schema.columns, (SELECT @r := 0) init LIMIT 1000) a,
       (SELECT @s := @s + 1 AS seq FROM information_schema.columns, (SELECT @s := 0) init2 LIMIT 20) b
) nums;

SELECT COUNT(*) AS seeded_rows FROM users;
SQL

if [[ $? -ne 0 ]]; then
  echo "[!] Schema setup failed -- aborting."
  exit 1
fi

echo "[*] Schema ready."

# ---------------------------------------------------------------------------
# 2. Build the long, ~1293-byte-style SELECT statement matching the
#    reporter's crashing query shape (many named columns, single WHERE).
# ---------------------------------------------------------------------------
LONG_SELECT_TEMPLATE="Select Users.UserIndex, UserID, Users.Password, GroupName, PasswordSource, \
UserService, UserIP, FilterName, UserActive, Users.StartDate, UserExpiryDate, \
CallBackNumber, CallerID, Lockout, Users.PasswordEncryptionMethod, \
MaxUserSessions, SkipPasswordCheck, HotlineStatus, BarringStatus, BarringReason, \
MaxVolumeCap, AllowedAccessTypes, CopyRadiusRequests, SubnetIP, SubnetMask, CIDR, \
CustomField1, CustomField2, CustomField3, CustomField4, CustomField5, \
CustomNumField1, CustomNumField2, CustomNumField3, CustomNumField4, CustomNumField5, \
SessionLimit, UserTokens, FirstLogon, FramedInterfaceId, FramedIPv6Prefix, \
PeriodicTimeUsage, PeriodicVolumeUsage, TimeUsageTriggerThresholds, \
VolumeUsageTriggerThresholds, CustomReplylist, CustomChecklist, \
BillingSystemCorrelationId, ChargingSystemCorrelationId, ChargingMode, LockOutTime, \
LockOutCount, Users.PseudoIdentity, CustomDate1, CustomDate2, CustomDate3, \
CustomDate4, CustomDate5, CustomField6, CustomField7, CustomField8, CustomField9, \
CustomField10, RestrictedLocations, MaxTimeCap, PortId, CustomNumField6, \
CustomNumField7, CustomNumField8, CustomNumField9, CustomNumField10, SpeedMapName, \
UserIPv6, NetworkService, CustomField11, CustomField12, CustomField13, \
CustomField14, CustomField15, PasswordDate \
From Users \
Where UserID = '__USERID__';"

# ---------------------------------------------------------------------------
# 3. Worker function: hammers the server with the long SELECT (varying the
#    UserID literal each time), interleaved with:
#      - PREPARE/EXECUTE/DEALLOCATE of the same statement (stresses PS path)
#      - short unrelated queries (simulates realistic mixed OLTP traffic)
#      - occasional new-connection churn (each iteration reconnects)
#    This mix is meant to increase the chance of hitting whatever race /
#    corruption condition underlies the original crash.
# ---------------------------------------------------------------------------
worker() {
  local worker_id="$1"
  local end_time="$2"
  local logfile="$LOGDIR/worker_${worker_id}.log"
  local i=0

  while [[ "$(date +%s)" -lt "$end_time" ]]; do
    i=$((i+1))
    local uid_num=$(( (RANDOM * RANDOM + worker_id) % 20000 ))
    local uid
    uid=$(printf "034299995%08dFGH" "$uid_num")
    local query="${LONG_SELECT_TEMPLATE/__USERID__/$uid}"

    {
      echo "USE ${DB};"
      echo "$query"
      # Also exercise the prepared-statement path with the same long text
      echo "PREPARE s${worker_id} FROM \"${query%;}\";"
      echo "EXECUTE s${worker_id};"
      echo "DEALLOCATE PREPARE s${worker_id};"
      # Mixed short traffic
      echo "SELECT COUNT(*) FROM users WHERE USERACTIVE = 1;"
      echo "UPDATE users SET LASTMODIFY = NOW() WHERE USERID = '${uid}' LIMIT 1;"
    } | "${MYSQL_BASE[@]}" --force >>"$logfile" 2>&1

    local rc=$?
    if [[ $rc -ne 0 ]]; then
      echo "[worker $worker_id] mysql client exited rc=$rc at iteration $i (possible server crash or connection error)" >>"$logfile"
    fi
  done
}

# ---------------------------------------------------------------------------
# 4. Launch concurrent workers + a watchdog that polls server availability.
# ---------------------------------------------------------------------------
echo "[*] Launching $CONCURRENCY concurrent workers for ${DURATION}s..."
END_TIME=$(( $(date +%s) + DURATION ))

PIDS=()
for w in $(seq 1 "$CONCURRENCY"); do
  worker "$w" "$END_TIME" &
  PIDS+=("$!")
done

# Watchdog: poll every 2s, report if the server stops responding (candidate crash)
WATCHDOG_LOG="$LOGDIR/watchdog.log"
(
  while [[ "$(date +%s)" -lt "$END_TIME" ]]; do
    if ! "${MYSQL_BASE[@]}" -e "SELECT 1;" >/dev/null 2>>"$WATCHDOG_LOG"; then
      echo "[$(date '+%Y-%m-%d %H:%M:%S')] *** SERVER UNRESPONSIVE -- possible crash detected ***" | tee -a "$WATCHDOG_LOG"
      # capture whatever we can once we notice
      break
    fi
    sleep 2
  done
) &
WATCHDOG_PID=$!

wait "${PIDS[@]}" 2>/dev/null
kill "$WATCHDOG_PID" 2>/dev/null

echo
echo "[*] Stress run complete."
echo "[*] Logs are in: $LOGDIR"
echo "[*] Check for:"
echo "      - '$WATCHDOG_LOG' for unresponsive-server detection"
echo "      - worker_*.log files for 'mysql client exited rc=' lines"
echo "      - the MariaDB error log (usually /var/log/mysql/error.log or similar)"
echo "        for 'got signal 11' / core dump notices, if the server crashed"
echo
echo "If it crashed: check the server's configured core dump location"
echo "(core-file / core_pattern) and run gdb against mariadbd + the core file"
echo "to get a fresh backtrace to compare against MDEV-38697's."