QoE Data Cleanup

Overview

This procedure removes all QoE monitoring data from an ACS installation. It targets the upgrade path 6.3.x → 6.4.14. Use it when a customer who was running QoE monitoring is upgrading to 6.4.14 and wants to start from an empty QoE data set, or when QoE is being decommissioned on that installation.

QoE data does not live in one place. Five scripts cover the three databases plus the on-disk spool:

Store Engine What it holds

ACS main schema (typically ftacs / FTACS)

MySQL / Oracle

Monitor definitions and per-device monitoring state — qoe_cpe, qoe_cpe_parameter, qoe_cpe_in_monitor, monitor_result, and the seven qoe_monitoring* config tables

QoE UI schema (typically ftacs_qoe_ui / FTACS_QOE_UI)

MySQL / Oracle

KPI definitions, custom views, device groups, group templates, alarm thresholds, dashboard settings

ftacs_qoe_ui_data

ClickHouse

All time-series measurements — see ClickHouse Time-Series Storage

csv.war/

Filesystem

The CSV measurement spool and the cache exports ACS writes when qoeDataStoreMode = 0

TRUNCATE is DDL on all three engines involved (Oracle, MySQL, ClickHouse): it commits implicitly, it cannot be rolled back, and there is no flashback query or recycle bin to recover from. The backups taken in Step 1 are the only way back.

Two mistakes account for nearly every failed run of this procedure:

  1. Not stopping ACS and QoE Web before cleaning. If they keep running, the tables refill while you clean them. The "AFTER" counts come back non-zero and it looks like the scripts are broken, when in fact new data simply arrived mid-run.

  2. Setting qoeDataStoreMode = 1 after cleaning instead of before. Clean first and disable collection second, and the data comes right back the moment ACS restarts.

This is a full data wipe performed during a maintenance window. It is not the same thing as the runtime brake described in Emergency QoE Stop, which sheds load on a live system without removing historical data.

Script Package

The scripts are distributed through SharePoint, not with the product package:

RD / prerelease / ft-qoe-web / Script for clearing QoE data from ACS up to (and including) 6.4.14

File Engine Scope

qoe_cleanup_main_oracle.sql

Oracle 12c+

ACS main schema. Run as the schema owner that holds CPE.

qoe_cleanup_main_mysql.sql

MySQL 8.0

ACS main schema.

qoe_cleanup_ui_oracle.sql

Oracle 12c+

QoE UI schema. Run as the schema owner that holds KPI and VIEW_GROUP.

qoe_cleanup_ui_mysql.sql

MySQL 8.0

QoE UI schema.

qoe_cleanup_clickhouse.sql

ClickHouse 24.8+

ftacs_qoe_ui_data. Standalone installs only — no ON CLUSTER clause.

*_runbook.md

 — 

The authoritative long-form runbook, in the same folder as the scripts. This page mirrors its step numbering; consult it for the full rationale behind each decision.

Run one main script and one UI script — the pair matching your engine — plus the ClickHouse script. Do not run the Oracle and MySQL variants of the same scope.

Step 1 — Before You Start

Confirm the liquibase prerequisite

The 6.3.x → 6.4.14 upgrade runs changeSet v6.4.9.a-16 (in changelog-6.4.9.a.xml), which creates a unique index qoe_cpe_param_name_uniqe_idx on qoe_cpe_parameter_name(name). The index exists for startup performance: without it, ACS start times out on large databases. It is not a logical constraint.

On installations where that table already holds duplicate name values — which is common on databases grown through TR-069 provisioning — the unique index cannot be built: liquibase fails, ACS does not start, and the upgrade never reaches the point where these cleanup scripts would run.

Check your own installation before opening the maintenance window:

SELECT name, COUNT(*)
  FROM qoe_cpe_parameter_name
 GROUP BY name
HAVING COUNT(*) > 1;

If that query returns nothing, the changeSet applies cleanly and there is nothing to do. If it returns rows, the ACS build you are upgrading to must carry the guarded version of v6.4.9.a-16 — the changeSet wrapped in a preConditions block with an sqlCheck expectedResult="0" over the same duplicate query and onFail="MARK_RAN", the mechanism already used by v6.4.9.a-14. With the guard in place, liquibase records the changeSet as run, skips the index, and the upgrade proceeds. Without it, the upgrade aborts.

Confirm with your delivery contact which of the two you have. Running this cleanup procedure against an unguarded build does not help — the upgrade still fails at the liquibase step, before any of these scripts execute.

The guard trades the index away to let the upgrade through: on an installation where it fires, qoe_cpe_param_name_uniqe_idx is never created, so the startup speed-up it was introduced for is not in place. On a large database, watch ACS start time after the upgrade. Creating the index as non-unique achieves the same lookup speed-up without depending on a dedupe — ask your delivery contact if start time turns out to be a problem.

Deleting the duplicate rows instead of guarding the changeSet is not a shortcut you can take here. qoe_cpe_parameter_name is a shared parameter-name dictionary referenced by 41 TR-069 / provisioning tables with no declared foreign keys — see Known Limitations. Because no foreign key is declared, the database will let you delete a row that is still referenced, and break provisioning silently.

A correct dedupe means repointing every dependent name_id to one canonical id per name (the MIN(id) of each duplicate group) across all 41 tables, and only then deleting the orphaned duplicates. That is a DBA task with its own maintenance window and is out of scope for this procedure.

Capture the JBoss paths

Do this while ACS is still running — these values are far easier to read off the live process than to reconstruct afterwards. Neither is a shell environment variable the product sets for you; both are JVM system properties passed on the ACS startup command line. The export below simply copies them into ordinary shell variables for the rest of the window.

export JBOSS_SERVER_CONFIG_DIR=$(ps -ef | grep -oP '(?<=-D[j]boss.server.config.dir=)\S+' | head -1)
export FTACS_SERVER_BASE_DIR=$(ps -ef | grep -oP '(?<=-D[j]boss.server.base.dir=)\S+' | head -1)

echo "config dir: $JBOSS_SERVER_CONFIG_DIR"
echo "base dir:   $FTACS_SERVER_BASE_DIR"
[ -d "$JBOSS_SERVER_CONFIG_DIR" ] && [ -d "$FTACS_SERVER_BASE_DIR" ] && echo OK || echo "STOP"

The [j] is deliberate: it stops grep from matching its own entry in the ps -ef listing. Do not simplify it to a bare j.

Verify the values. A failed capture exports an empty variable with no error, and every command downstream still runs — just against the wrong path. A correct value is a non-empty absolute path to an existing directory, typically ending in …​/standalone/configuration and …​/standalone.

If the capture fails (wrapper script, unusual ps output, ACS already down), read the same two -D flags out of the startup script or service unit and export them by hand.

Stop ACS and QoE Web

Stop both applications on every node and confirm they are actually down via the process list or your service manager — do not rely on having issued a stop command. They stay stopped until Step 7.

Take backups

Oracle — expdp per schema
expdp <main_schema_user>/<password>@<connect_string> \
  schemas=FTACS directory=DATA_PUMP_DIR \
  dumpfile=ftacs_pre_qoe_cleanup_%U.dmp logfile=ftacs_pre_qoe_cleanup.log

expdp <ui_schema_user>/<password>@<connect_string> \
  schemas=FTACS_QOE_UI directory=DATA_PUMP_DIR \
  dumpfile=ftacs_qoe_ui_pre_qoe_cleanup_%U.dmp logfile=ftacs_qoe_ui_pre_qoe_cleanup.log
MySQL — mysqldump per database
mysqldump -u <user> -p --single-transaction --routines --triggers ftacs \
  > ftacs_pre_qoe_cleanup.sql

mysqldump -u <user> -p --single-transaction --routines --triggers ftacs_qoe_ui \
  > ftacs_qoe_ui_pre_qoe_cleanup.sql
ClickHouse — ALTER TABLE …​ FREEZE per table
clickhouse-client --query "SELECT name FROM system.tables WHERE database='ftacs_qoe_ui_data'" \
  | while read -r t; do
      clickhouse-client --query "ALTER TABLE ftacs_qoe_ui_data.\`$t\` FREEZE;"
    done

Schema and database names are renameable per installation — substitute your own if they differ from the conventional FTACS / FTACS_QOE_UI / ftacs / ftacs_qoe_ui.

FREEZE creates a hard-link snapshot under each table’s shadow/ directory on the same disk. It is fast and cheap, but it is not a real backup until you copy that directory elsewhere. If the disk is lost, the live data and the frozen snapshot are lost together.

Step 2 — Disable Collection First

Do this before running any cleanup script.

Edit $JBOSS_SERVER_CONFIG_DIR/acs_configuration.xml on every ACS node and set:

qoeDataStoreMode = 1
Value Meaning

0

Default. Store QoE data to CSV and to the QoE UI database, if available.

1

Do not store QoE data. This is what stops new data from being written once ACS restarts.

3

Store to the QoE UI database only.

While the file is open, review the related switches and confirm they match what this installation should do going forward:

Parameter Default Effect

qoeMonitorMode

1

1 = QoE monitoring handled by a single ACS node; 2 = handled across a cluster.

qoeMacStoreEnabled

1

If 1, ACS retrieves and stores the MAC address of QoE-monitored devices.

qoeTruncateMonitoringParameter

0

If 1, ACS runs a scheduled truncate of selected monitored-parameter data on its own interval.

Save the file. Do not restart ACS yet — it stays stopped until Step 7, and the change takes effect on that restart.

Step 3 — Clean the Main Schema

Script: qoe_cleanup_main_oracle.sql or qoe_cleanup_main_mysql.sql. Run as the main ACS schema owner (the schema that holds CPE).

The switches near the top of each script are hard-coded assignments — DEFINE DRY_RUN = Y on Oracle, SET @DRY_RUN = 'Y'; on MySQL — not command-line parameters. To change one, edit that line in the file and save. Anything set beforehand at session level is overwritten the moment the script runs.

Pass 1 — dry run

Leave DRY_RUN = Y (the default).

# Oracle
sqlplus <main_schema_user>/<password>@<connect_string>
SQL> SPOOL qoe_cleanup_main_dryrun.log
SQL> @qoe_cleanup_main_oracle.sql

# MySQL
mysql -u <main_schema_user> -p ftacs < qoe_cleanup_main_mysql.sql \
  > qoe_cleanup_main_dryrun.log 2>&1

The Oracle script ends with EXIT, which closes the spool and the session automatically.

MySQL exits 0 even when it did nothing. A wrong-schema MySQL run does not fail loudly the way the Oracle script does. The guard prints a single warning row — wrong schema: connected to <db>, …​ — among many other lines, the stored procedure then quietly skips every TRUNCATE, and the mysql CLI still returns exit code 0. A zero exit code does not mean the cleanup ran. Always check the first line of output: it must read guard ok: connected to <db>.

Review the log before doing anything else:

  • Row counts BEFORE — what is there today.

  • Inbound FK report — tables outside the QoE target list holding a foreign key into a QoE table.

    • On Oracle, these are not handled automatically — the script only disables foreign keys where both parent and child are QoE tables. Resolve them manually (export and delete the child rows, or drop the FK) before Pass 2. Otherwise that one TRUNCATE logs an ERROR line and leaves that table untouched; the rest of the script still runs.

    • On MySQL, such a table is detected and automatically excluded, logged as SKIPPED <table> (external FK from another area). No FK-disabling happens across areas.

  • Every DRY-RUN: TRUNCATE TABLE …​ line shows exactly what Pass 2 will execute.

Pass 2 — the real run

Change DRY_RUN from Y to N, save, and re-run the identical command, spooling to a new file name.

In the "Row counts AFTER" section, QOE_CPE, QOE_CPE_PARAMETER, QOE_CPE_IN_MONITOR, MONITOR_RESULT and the seven QOE_MONITORING* config tables must all read 0 (or (not present), which is also fine).

Two tables are expected to stay non-zero:

QOE_CPE_PARAMETER_NAME

Deliberately never truncated. Despite the name it is a shared parameter-name dictionary referenced by 41 other TR-069 / provisioning tables with no declared foreign keys — see Known Limitations. Truncating it breaks TR-069 provisioning even for customers who never used QoE.

MONITORED_PARAMETER

A legacy table used only by the internal QoE test-data generator. The script reports its row count for visibility but never touches it. If you want it emptied, the script’s own output prints the exact manual statement.

Where those two counts are printed differs by engine. On Oracle they appear as two extra rows inside the same flat "Row counts AFTER" list. On MySQL they do not appear in that block at all — they are printed afterwards as two separate one-row result sets, labelled dictionary and probe, following the sh110_log output.

Step 4 — Clean the UI Schema

Script: qoe_cleanup_ui_oracle.sql or qoe_cleanup_ui_mysql.sql. Run as the QoE UI schema owner — the schema that holds KPI and VIEW_GROUP.

On Oracle this is a separate database user with separate credentials from Step 3: open a second sqlplus session. If you connect to the wrong schema, Oracle fails loudly and safely — the guard runs under WHENEVER SQLERROR EXIT FAILURE and raises RAISE_APPLICATION_ERROR(-20001, 'wrong schema…​'), aborting immediately with a non-zero exit code. You cannot silently run this script against the wrong Oracle schema.

Follow the same two-pass procedure as Step 3.

# Oracle, as the UI schema owner
sqlplus <ui_schema_user>/<password>@<connect_string>
SQL> SPOOL qoe_cleanup_ui_dryrun.log
SQL> @qoe_cleanup_ui_oracle.sql

# MySQL
mysql -u <ui_schema_user> -p ftacs_qoe_ui < qoe_cleanup_ui_mysql.sql \
  > qoe_cleanup_ui_dryrun.log 2>&1

The same MySQL caveat as Step 3 applies here: a wrong-schema run prints one wrong schema: connected to <db>, …​ line, skips every TRUNCATE, and still exits 0. Check that the first line reads guard ok: connected to <db>.

SECTION_UI_SYSTEM defaults to N, and that default is intentional — it leaves USERS, USER_ROLES, SETTINGS and CUSTOMIZATION in place. USERS / USER_ROLES hold every QoE UI login, so truncating them locks every operator out of the UI; SETTINGS / CUSTOMIZATION hold UI configuration (mail/SNMP settings, retention, branding) that no script in this set reseeds. Those four tables will still show non-zero counts in "Row counts AFTER" — expected with the default switch, not a failed run.

Step 5 — Clean ClickHouse

Script: qoe_cleanup_clickhouse.sql, run against ftacs_qoe_ui_data with clickhouse-client.

This script has no dry-run mode. There is no DRY_RUN switch: every TRUNCATE executes immediately and for real on the very first run. The three phases below are therefore called Run 1 / Run 2 / Run 3, not "Pass 1 / Pass 2" — Run 1 already truncates data.

Standalone installations only. The script has no ON CLUSTER clause. On a sharded install, running it against one node cleans that node only — the other shards keep their QoE data, and every check in this step still reports COMPLETE/empty, because they only ever look at the node you connected to. Repeat all three runs against every node, or adapt the script to your topology first.

Baseline the Flyway journal

schema_version is never touched by the script, and the script’s own "Row counts BEFORE" query cannot show it — that query reads system.parts, which never tracks the TinyLog engine at any row count. Capture the baseline yourself and write it down:

clickhouse-client --query "SELECT count() FROM ftacs_qoe_ui_data.schema_version;"

Run 1 — static TRUNCATEs and statement generation

clickhouse-client --multiquery --queries-file qoe_cleanup_clickhouse.sql \
  > qoe_cleanup_clickhouse_run1.log 2>&1

This truncates the 28 baseline tables and prints three generated TRUNCATE TABLE IF EXISTS …​; statements — one per materialized-view inner table (cpe_data_latest, kpi_data_latest, kpi_data_aggregated). ClickHouse cannot execute statements it generated within the same run, so the log also shows a status line reading INCOMPLETE at this point. That is expected, and it is exactly why Run 2 exists.

The three views were created with POPULATE and without TO, so their rows live in separate inner tables — truncating cpe_data does not clear them. The inner-table name depends on the engine of the database, not of the views:

Database engine Inner-table name When

Atomic

.inner_id.<uuid>

ClickHouse default since 20.10

Ordinary

.inner.<view_name>

The default before that

Upgrading the ClickHouse server does not convert an existing Ordinary database to Atomic, so an installation originally set up on ACS 6.0/6.3 — exactly the population this procedure targets — can still be Ordinary today. A mix is impossible: all three names have the same shape. To check in advance:

clickhouse-client --query "SELECT engine FROM system.databases WHERE name='ftacs_qoe_ui_data';"

Count the generated statements: there must be exactly three. Fewer — including zero — means the generator did not find every inner table. Use the fallback below; do not proceed to Run 3 on a short list. Three is the only outcome that lets the COMPLETE in Run 3 mean what it says.

Run 2 — execute the generated statements

Open qoe_cleanup_clickhouse_run1.log, find the three printed statements, and run each one. Quote the name exactly as printed, backticks included, since it starts with a dot:

# Atomic database
clickhouse-client --query "TRUNCATE TABLE IF EXISTS ftacs_qoe_ui_data.\`.inner_id.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx\`;"

# Ordinary database
clickhouse-client --query "TRUNCATE TABLE IF EXISTS ftacs_qoe_ui_data.\`.inner.cpe_data_latest\`;"
Fallback — fewer than three statements generated

The script matches both known naming conventions, so a short list means this installation matches neither. Confirm what is actually there:

clickhouse-client --query "SELECT name, engine, uuid FROM system.tables WHERE database='ftacs_qoe_ui_data' AND (name LIKE '.inner%' OR name IN ('cpe_data_latest','kpi_data_latest','kpi_data_aggregated'));"

If the three views are listed but their inner tables are not, do not guess at a name. Drop and recreate the affected views from their CREATE MATERIALIZED VIEW statements in the 6.4.14 package’s clickhouse_full.sql — grep the file rather than trusting a line number, it is regenerated between releases.

Run 3 — final verification

The TRUNCATE …​ IF EXISTS statements are idempotent, so re-running the whole file is safe from the TRUNCATE side. Checks 3 and 4 below, however, hard-reference seven table names without IF EXISTS; if any one of those is absent, clickhouse-client aborts on that statement and every query after it — including check 4 — never runs.

clickhouse-client --multiquery --queries-file qoe_cleanup_clickhouse.sql \
  > qoe_cleanup_clickhouse_run3.log 2>&1

Scroll to the FINAL VERIFICATION section at the end of the log and check all four results:

# Expected result

1

Materialized-view inner-table status reads COMPLETE. If it still reads INCOMPLETE, either Run 2’s statements were not actually executed, or ACS/QoE Web was not really stopped and new data has already arrived — go back to Run 2.

2

The MergeTree-family table list is empty (zero rows). Any row names a table that still holds data. schema_version, flyway_schema_history and flyway_schema_history_acs are excluded on purpose — never truncate them. This query is also blind to Join- and Memory-engine tables, which is what check 3 is for.

3

The six Join/Memory tables (ft_cust_device, ft_cpe_domain_info, ft_qoe_cpe_info, tmp_ft_cust_device, tmp_ft_cpe_domain_info, tmp_ft_qoe_cpe_info) read COMPLETE. system.parts never tracks these engines, so check 2 cannot see them.

4

schema_version row count is identical to the baseline captured before Run 1, and non-zero.

schema_version is the ClickHouse Flyway migration journal and is deliberately never truncated. Clearing it makes Flyway believe no migration has ever run and re-runs every migration from scratch against a live schema on the next ACS startup. The same rule applies to flyway_schema_history and flyway_schema_history_acs if either is present — neither is part of the 6.4.14 baseline, but some upgrade paths leave them behind.

Do not report this step done until all four read as expected.

Step 6 — Clean the CSV Spool

QoE also writes CSV files to disk, under <jboss.server.base.dir>/deployments/csv.war/ — that is $FTACS_SERVER_BASE_DIR from Step 1. There is no FTACS_HOME variable in the shipped product; do not look for one. With ACS and QoE Web still stopped:

ls "$FTACS_SERVER_BASE_DIR/deployments/csv.war/"
cd "$FTACS_SERVER_BASE_DIR/deployments/csv.war/" || { echo "STOP -- cd failed, FTACS_SERVER_BASE_DIR is wrong or unset"; exit 1; }
pwd
# QoE measurement spool: the live mmap buffer, plus its rotated snapshots.
rm -f monitor_results.csv monitor_results_*.csv
# QoE cache exports, rewritten every generation interval by ResultGenerationUtil.
rm -f cpe.csv cpe_mac.csv cpe_periodic.csv monitored_parameter.csv cpe_parameter_name.csv custDevice.csv
ls

monitor_results.csv is the live 10 MB memory-mapped buffer QoE writes measurements into; monitor_results_*.csv are its timestamped rotations. Note the plural — a singular monitor_result.csv is never produced.

The second rm covers the files ACS writes when qoeDataStoreMode = 0: one CSV per cache, named after the cache, overwritten in full each interval.

custDevice.csv holds subscriber contact details (login_name, name, location, telephone). Leaving it behind leaves customer PII on disk after a cleanup that was supposed to remove QoE data entirely.

The cd is quoted and guarded on purpose: an unguarded cd to an empty or wrong path fails silently, leaves the shell where it was, and the following rm -f then runs there instead. The pwd and both ls calls exist so you can confirm, before and after, which directory you actually deleted from.

Do not delete the csv.war/ directory itself, and do not delete csv.war/WEB-INF/web.xml. This is a deployed JBoss artifact, not a plain data folder — removing either breaks the deployment and ACS will not redeploy it cleanly on the next startup. Leave csv.war/props/ alone as well: it holds QoE GPV-model and MAC-path property files, which are configuration, not collected data.

Step 7 — Start and Verify

Start ACS and QoE Web, then work through this table before considering the cleanup complete.

# Check How

1

Main / UI schema row counts

Re-open the "Row counts AFTER" section of each log from Steps 3 and 4. Do not re-run a cleanup script to get a fresh snapshot — Steps 3 and 4 left DRY_RUN set to N in those files, ACS is running again by now, and a re-run would execute every TRUNCATE a second time against a live schema. For current numbers, query the tables directly instead, e.g. SELECT COUNT(*) FROM qoe_cpe;. All target tables read 0 or (not present). QOE_CPE_PARAMETER_NAME, MONITORED_PARAMETER and — with the default switch — the UI system tables are expected to stay non-zero.

2

ClickHouse final verification

Do not re-run the whole qoe_cleanup_clickhouse.sql file here — ACS is live again at this point, and re-running would re-execute every TRUNCATE against a live schema. Copy everything from the FINAL VERIFICATION marker to the end of the file into a scratch file and run that instead. None of those four queries contain a TRUNCATE, so it is safe with ACS running.

3

Materialized-view inner tables

Covered by check 2 — confirm it specifically. This is the step most often left half-done.

4

Collection is actually off

Wait 15 minutes after startup, then run SELECT table, sum(rows) FROM system.parts WHERE database='ftacs_qoe_ui_data' AND active AND table='cpe_data' GROUP BY table; — it must still return zero rows. If it does not, qoeDataStoreMode was not applied: Step 2 was skipped, misspelled, or ACS did not pick up the config on restart.

5

Quartz scheduler

SELECT trigger_name FROM qrtz_triggers WHERE lower(trigger_name) LIKE '%qoe%'; against the Quartz schema — FTACS_QUARTZ on Oracle, database ftacs_quartz on MySQL. The qrtz_* tables do not live in the ACS main schema; running this as the main-schema owner returns ORA-00942 / ERROR 1146 and tells you nothing. QoE trigger definitions are expected to still be listed — this only confirms the scheduler is healthy after restart.

6

QoE UI

The UI opens, login works (the default SECTION_UI_SYSTEM=N is what preserves this), and the views inside are empty.

7

Server log

server.log on every ACS node is free of QoE-related ERROR entries during and after startup.

8

No legacy QoE database (MySQL)

SHOW DATABASES LIKE 'ftacs_qoe'; must return nothing. Nothing in the 6.4.14 delivery chain creates that database. If it does return a row, stop and escalate rather than improvising — the installation acquired it by some route not covered here, and its contents need assessing first.

Do not close the maintenance window until every row checks out.

Known Limitations

This procedure empties QoE’s data stores. It does not undo every trace of QoE configuration on the devices themselves or in the surrounding system.

Notification attributes remain set on the CPEs

Devices that were under QoE monitoring keep sending TR-069 value-change informs for the parameters they were configured to watch. ACS discards these now that qoeDataStoreMode = 1, but the network traffic from the devices does not stop — nothing here reaches out to CPEs to clear their notification configuration.

qoe_cpe_parameter_name keeps its rows

Despite the prefix, this is a shared parameter-name dictionary referenced by 41 other TR-069 / provisioning tables with no formal foreign keys. It is intentionally out of scope for every script in this set; truncating it breaks provisioning even for customers who never enabled QoE.

Reversible only from the Step 1 backups

Once the TRUNCATE statements have run there is no other way to bring the data back.

Client Requirements

Run every script with the native command-line client for its engine:

Engine Client Why a GUI client fails

Oracle

sqlplus

SQL Developer (and Toad, and other GUI clients) captures an inline comment following a DEFINE into the variable’s value. Both Oracle scripts read their switches with UPPER(SUBSTR('&&DRY_RUN', 1, 1)), expecting a single Y/N character; a polluted value raises ORA-06502 and aborts the script.

MySQL

mysql CLI

These scripts use DELIMITER $$ to define stored procedures. GUI DELIMITER handling is inconsistent across tools and versions — a client that does not honour it the way the CLI does will misparse the procedure body.

ClickHouse

clickhouse-client

 — 

To inspect or edit a script’s switches, use a plain text editor, not a database GUI’s SQL editor pane. Editing the file directly, as instructed in Steps 3 and 4, avoids both problems entirely. Step 5 has no switches to edit — the ClickHouse script has no DRY_RUN and no sections.

← Back | Main Page