import { logger } from '@powersync/lib-services-framework'; import { SourceTableRef } from '@powersync/service-sync-rules'; import mysql from 'mysql2'; import mysqlPromise from 'mysql2/promise'; import { coerce, gte, satisfies } from 'semver'; import * as types from '../types/types.js'; export type RetriedQueryOptions = { connection: mysqlPromise.Connection; query: string; params?: any[]; retries?: number; }; /** * TCP keepalive initial delay in milliseconds for connections to the MySQL server. * Keepalive prevents long-lived idle connections from being silently dropped by stateful * firewalls, which commonly time out idle flows after an hour. */ export const TCP_KEEPALIVE_INITIAL_DELAY = 40_000; /** * Retry a simple query - up to 2 attempts total. */ export async function retriedQuery(options: RetriedQueryOptions) { const { connection, query, params = [], retries = 2 } = options; for (let tries = retries; ; tries--) { try { logger.debug(`Executing query: ${query}`); return connection.query(query, params); } catch (e) { if (tries == 1) { throw e; } logger.warn('Query error, retrying', e); } } } export function createPool(config: types.NormalizedMySQLConnectionConfig, options?: mysql.PoolOptions): mysql.Pool { const sslOptions = { ca: config.cacert, key: config.client_private_key, cert: config.client_certificate }; const hasSSLOptions = Object.values(sslOptions).some((v) => !!v); // URL connection parameters provide defaults; explicit options take precedence const params = config.connectionParams; // TODO: Use config.lookup for DNS resolution return mysql.createPool({ host: config.hostname, user: config.username, port: config.port, password: config.password, database: config.database, ssl: hasSSLOptions ? sslOptions : undefined, supportBigNumbers: true, decimalNumbers: true, timezone: 'Z', // Ensure no auto timezone manipulation of the dates occur jsonStrings: true, // Return JSON columns as strings dateStrings: true, // We parse and format them ourselves // mysql2 enables TCP keepalive by default, but without an initial delay the OS default of // 7200 seconds applies, which is too late for common 3600 second firewall idle timeouts. enableKeepAlive: true, keepAliveInitialDelay: TCP_KEEPALIVE_INITIAL_DELAY, // Apply URL connection parameters (explicit options override these via spread below) ...(params.connectTimeout != null ? { connectTimeout: params.connectTimeout } : {}), ...(params.connectionLimit != null ? { connectionLimit: params.connectionLimit } : {}), ...(params.queueLimit != null ? { queueLimit: params.queueLimit } : {}), ...(options || {}) }); } /** * Return a random server id for a given replication stream id. * Expected format is: 00 * The max value for server id in MySQL is 2^32 - 1. * We use the GTID format to keep track of our position in the binlog, no state is kept by the MySQL server, therefore * it is ok to use a randomised server id every time. * @param syncRuleId */ export function createRandomServerId(syncRuleId: number): number { return Number.parseInt(`${syncRuleId}00${Math.floor(Math.random() * 10000)}`); } export async function getMySQLVersion(connection: mysqlPromise.Connection): Promise { const [[versionResult]] = await retriedQuery({ connection, query: `SELECT VERSION() as version` }); return versionResult.version as string; } /** * Check if the current MySQL version is newer or equal to the target version. * @param version * @param minimumVersion */ export function isVersionAtLeast(version: string, minimumVersion: string): boolean { const coercedVersion = coerce(version); const coercedMinimumVersion = coerce(minimumVersion); return gte(coercedVersion!, coercedMinimumVersion!, { loose: true }); } export function satisfiesVersion(version: string, targetVersion: string): boolean { const coercedVersion = coerce(version); return satisfies(coercedVersion!, targetVersion!, { loose: true }); } export function qualifiedMySQLTable(table: SourceTableRef): string; export function qualifiedMySQLTable(table: string, schema: string): string; export function qualifiedMySQLTable(table: SourceTableRef | string, schema?: string): string { if (typeof table === 'object') { return `\`${table.schema.replaceAll('`', '``')}\`.\`${table.name.replaceAll('`', '``')}\``; } else if (schema) { return `\`${schema.replaceAll('`', '``')}\`.\`${table.replaceAll('`', '``')}\``; } else { return `\`${table.replaceAll('`', '``')}\``; } }