How to fix "Data too long for column 'external_id'" when trying to sync Azure Subnets

Infrastructure
, ,
1

Hello,

While working on a customer use case, we came across this issue where the Azure Subnets were failing to sync in Morpheus because the Subnet’s external_id had exceeded the max allowed character limit (varchar(255)) in the database and were seeing the following error followed by a stack trace in the logs.

ERROR o.h.e.j.s.SqlExceptionHelper - Data truncation: Data too long for column 'external_id' at row 1
ERROR c.m.c.a.AzureComputeService - cacheNetworks error: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement 
2023-06-30_16:20:16.97387 'org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement

The Azure Subnet external_id is stored in the following format in the database.

/subscriptions/<subscription_id>/resourceGroups/<resourceGroup_name>/providers/Microsoft.Network/virtualNetworks/<virtualNetwork_name>/subnets/<subnet_name>

To resolve this we increased the max character limit to 1000 using the following query in the database.

ALTER TABLE network_subnet MODIFY COLUMN external_id VARCHAR(1000);

After modifying if the subnets don’t sync straight away then do this.
Edit the cloud > select a different resource group > save changes > let the cloud sync complete > change back to your preferred resource group > save changes > let the cloud sync complete.
You should see the subnets now synced.

IMPORTANT: This process involves directly querying and updating the Morpheus database. When altering your database, it’s good practice to back up its contents immediately prior to making changes. Effective database backups can be made either by creating a MySQL dump or by running a Morpheus appliance backup Job from Morpheus UI. Morpheus documentation includes a guide on backing up and restoring your Morpheus database, if necessary.

Hope this will be helpful. :slight_smile:

Thanks
Deepti

5 Likes