Data Seed Task Fails on Script V1.276 During Upgrade to IDSP 4.0.1

Products

• Symantec Identity Security Platform - IDSP (formerly VIP Authentication Hub)

Issue/Introduction

When upgrading from **VIP Authentication Hub (AH) 3.4.4** to **IDSP 4.0.1**, the data seed task fails during the execution of the following script:

V1.276__AuthHub_4.0_Indexes.sql

While the procedure may succeed in staging or QA environments, it fails in production environments utilizing load balancing or proxy layers.

Environment

IDSP upgrade from 3.4.4 to 4.0.1
MySQL 8.0.44-35.1 (Percona Server)
ProxySQL (or similar database proxies)

Cause

The migration failure is typically caused by "connection multiplexing" and "load balancing" at the proxy layer. 

Schema migration traffic (via Flyway) requires a "non-multiplexed, session-sticky connection". Because migrations involve sequences of reads and writes that must be consistently routed to the primary node, multiplexing can cause the session state to be lost or routed to a read-only replica mid-script, leading to execution errors.

Resolution

To resolve this, isolate the migration traffic to ensure it reaches the writer hostgroup without interference.

1. Create a Dedicated Flyway User: Define a specific database user dedicated solely to Flyway migrations.
2. Disable Multiplexing for Migration User: To disable multiplexing for a specific user in ProxySQL, use the mysql_query_rules table rather than the mysql_users table directly. While mysql_users manages authentication and routing, the control for multiplexing behavior at a granular level is handled by the query rules engine (1).
   
   This ensures the connection remains pinned to the server for the duration of the migration.
   
   
3. Route to Writer Hostgroup: Ensure this user is routed exclusively to the "Writer Hostgroup" to prevent read/write split issues.
4. Load to Runtime: Apply changes to the ProxySQL runtime without requiring a service restart.

Workaround:

If ProxySQL configuration cannot be modified immediately, pointing the application directly to a single "MySQL Data Node" (bypassing the proxy) will allow the data seed task to complete successfully.

 

Additional Information

1. Tuning multiplexing