Can't connect to ClickHouse with default connection flags

[bouk]

Hi, I'm trying to connect to a clickhouse server with this library, but the default configuration requires that the server supports the CONNECT_ATTRS and SESSION_TRACK flags, which clickhouse doesn't support. The clickhouse server sends:

capability flags:19433992=(connect with db, protocol 41, ssl, secure connection, plugin auth, plugin auth lenenc client data, deprecate eof)

We could do something like this:

diff --git a/lib/commands/client_handshake.js b/lib/commands/client_handshake.js
index ac0d8634..13010d8a 100644
--- a/lib/commands/client_handshake.js
+++ b/lib/commands/client_handshake.js
@@ -121,6 +121,8 @@ class ClientHandshake extends Command {
     connection.serverCapabilityFlags = this.handshake.capabilityFlags;
     connection.serverEncoding = CharsetToEncoding[this.handshake.characterSet];
     connection.connectionId = this.handshake.connectionId;
+    // Only use capabilities that both client and server support
+    this.clientFlags = this.clientFlags & this.handshake.capabilityFlags;
     const serverSSLSupport =
       this.handshake.capabilityFlags & ClientConstants.SSL;
     // multi factor authentication is enabled with the

But I'm not sure if that's a good idea, I'm not deeply familiar with this library. This would only use the capabilities both client and server support i.e. negotiation.

Logs

I've created a reproduction (requires clickhouse to be installed). It fails on master

$ clickhouse-server --version
ClickHouse server version 25.11.2.24.
$ git rev-parse HEAD
40ad043bcc89059f66e38dfb5e86533b949fef60

run-clickhouse

#!/usr/bin/env bash
# Set up a temporary ClickHouse server and run the integration tests against them

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SSL_CERTS_DIR="$SCRIPT_DIR/test/fixtures/ssl/certs"

dir=$(mktemp -d)
trap "rm -rf $dir" EXIT

# Set up ClickHouse
HTTP_PORT=$(python3 -c 'import socket; s=socket.socket(); s.bind(("", 0)); print(s.getsockname()[1]); s.close()')
TCP_PORT=$(python3 -c 'import socket; s=socket.socket(); s.bind(("", 0)); print(s.getsockname()[1]); s.close()')
MYSQL_PORT=$(python3 -c 'import socket; s=socket.socket(); s.bind(("", 0)); print(s.getsockname()[1]); s.close()')

mkdir -p "$dir/clickhouse/config"
mkdir -p "$dir/clickhouse/data"
mkdir -p "$dir/clickhouse/logs"

cat > "$dir/clickhouse/config/config.xml" <<EOF
<clickhouse>
    <logger>
        <level>warning</level>
        <log>$dir/clickhouse/logs/clickhouse-server.log</log>
        <errorlog>$dir/clickhouse/logs/clickhouse-server.err.log</errorlog>
    </logger>
    <http_port>$HTTP_PORT</http_port>
    <tcp_port>$TCP_PORT</tcp_port>
    <mysql_port>$MYSQL_PORT</mysql_port>
    <listen_host>127.0.0.1</listen_host>
    <path>$dir/clickhouse/data</path>
    <tmp_path>$dir/clickhouse/tmp</tmp_path>
    <user_files_path>$dir/clickhouse/user_files</user_files_path>
    <format_schema_path>$dir/clickhouse/format_schemas</format_schema_path>
    <users_config>$dir/clickhouse/config/users.xml</users_config>
    <!-- OpenSSL configuration required for TLS on MySQL protocol -->
    <openSSL>
        <server>
            <certificateFile>$SSL_CERTS_DIR/server-cert.pem</certificateFile>
            <privateKeyFile>$SSL_CERTS_DIR/server-key.pem</privateKeyFile>
            <caConfig>$SSL_CERTS_DIR/ca.pem</caConfig>
            <verificationMode>none</verificationMode>
            <loadDefaultCAFile>false</loadDefaultCAFile>
            <cacheSessions>true</cacheSessions>
            <disableProtocols>sslv2,sslv3</disableProtocols>
            <preferServerCiphers>true</preferServerCiphers>
        </server>
    </openSSL>
</clickhouse>
EOF

cat > "$dir/clickhouse/config/users.xml" <<EOF
<clickhouse>
  <logger>
    <console>1</console>
  </logger>
    <users>
        <default>
            <password></password>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>
    </users>
    <profiles>
        <default>
        </default>
    </profiles>
    <quotas>
        <default>
        </default>
    </quotas>
</clickhouse>
EOF

clickhouse-server --config-file="$dir/clickhouse/config/config.xml" --pid-file="$dir/clickhouse/clickhouse-server.pid" &
CLICKHOUSE_PID=$!

trap "kill $CLICKHOUSE_PID 2>/dev/null || true; rm -rf $dir" EXIT

for i in {1..30}; do
    if clickhouse-client --host 127.0.0.1 --port "$TCP_PORT" --query "SELECT 1" > /dev/null 2>&1; then
        break
    fi
    if [ "$i" -eq 30 ]; then
        echo "ClickHouse failed to start"
        exit 1
    fi
    sleep 1
done

clickhouse-client --host 127.0.0.1 --port "$TCP_PORT" --query "CREATE DATABASE IF NOT EXISTS testdb" > /dev/null 2>&1

export CLICKHOUSE_URL="http://127.0.0.1:$HTTP_PORT"
export CLICKHOUSE_MYSQL_PORT="$MYSQL_PORT"

# Run passed-in command

"${@}"

test/integration/connection/test-clickhouse-connection.test.cjs

'use strict';

const { assert } = require('poku');
const fs = require('node:fs');
const path = require('node:path');
const process = require('node:process');

// Skip test if not running with ClickHouse
if (!process.env.CLICKHOUSE_MYSQL_PORT) {
  console.log('Skipping ClickHouse test - CLICKHOUSE_MYSQL_PORT not set');
  process.exit(0);
}

const mysql = require('../../../index.js');

const sslConfig = {
  rejectUnauthorized: false,
  ca: fs.readFileSync(
    path.join(__dirname, '../../fixtures/ssl/certs/ca.pem'),
    'utf-8'
  ),
};

const connection = mysql.createConnection({
  host: '127.0.0.1',
  port: parseInt(process.env.CLICKHOUSE_MYSQL_PORT, 10),
  user: 'default',
  password: '',
  database: 'testdb',
  ssl: sslConfig,
});

connection.on('error', (err) => {
  console.error('Connection error:', err);
  process.exit(1);
});

// Test basic query with TLS
connection.query('SELECT 1 AS result', (err, rows, fields) => {
  assert.ifError(err);
  assert.deepEqual(rows, [{ result: 1 }]);
  assert.equal(fields[0].name, 'result');

  // Verify we're connected over TLS by checking the connection's encrypted property
  assert.equal(connection.stream.encrypted, true, 'Connection should be encrypted');

  // Test another query
  connection.query('SELECT 2 AS value', (err, rows, fields) => {
    assert.ifError(err);
    assert.deepEqual(rows, [{ value: 2 }]);
    assert.equal(fields[0].name, 'value');

    console.log('ClickHouse connection with TLS: OK');

    connection.end((err) => {
      assert.ifError(err);
      process.exit(0);
    });
  });
});

Test by running: ./run-clickhouse node test/integration/connection/test-clickhouse-connection.test.cjs

On master it fails with:

2025.12.22 13:28:26.711541 [ 42068898 ] {} <Error> MySQLHandler: DB::Exception: Packet payload is not fully read. Stopped after 70 bytes, while 50 bytes are in buffer.
✘ Expected no error, but received an error
      Code ERR_ASSERTION
  Operator ifError

AssertionError [ERR_ASSERTION]: ifError got unwanted exception: Connection lost: The server closed the connection.
    at message.message (/Users/bouke/src/github.com/sidorares/node-mysql2/node_modules/poku/lib/builders/assert.js:25:89)
    at processAssert (/Users/bouke/src/github.com/sidorares/node-mysql2/node_modules/poku/lib/services/assert.js:26:13)
    at Function.ifError (/Users/bouke/src/github.com/sidorares/node-mysql2/node_modules/poku/lib/builders/assert.js:25:71)
    at Query.onResult (/Users/bouke/src/github.com/sidorares/node-mysql2/test/integration/connection/test-clickhouse-connection.test.cjs:40:10)
    at Connection._notifyError (/Users/bouke/src/github.com/sidorares/node-mysql2/lib/base/connection.js:239:17)
    at Socket.<anonymous> (/Users/bouke/src/github.com/sidorares/node-mysql2/lib/base/connection.js:119:12)
    at Socket.<anonymous> (/Users/bouke/src/github.com/sidorares/node-mysql2/lib/base/connection.js:113:31)
    at Socket.emit (node:events:531:35)
    at TCP.<anonymous> (node:net:346:12) {
  generatedMessage: false,
  code: 'ERR_ASSERTION',
  actual: [Error],
  expected: null,
  operator: 'ifError',
  diff: 'simple'
}

Workaround

Doing the following also works

const connection = mysql.createConnection({
  ...
  // ClickHouse doesn't support CONNECT_ATTRS or SESSION_TRACK flags
  flags: '-CONNECT_ATTRS,-SESSION_TRACK',
});