Different column size for project_qprofiles.profile_key

Hi there,

in preparation for the outrunning support for MySQL we tried to migrate to PostgreSQL according to the instructions given on github. We ran into the following problem:

[main] INFO org.sonarsource.sqdbmigrator.migrator.ContentCopier - copying table project_qprofiles ...
Error while copying rows of table 'project_qprofiles': Batch entry 497 insert into project_qprofiles (id, project_uuid, profile_key) values (115,'a79586a1-b9f5-44de-83f1-971beea1a8a4','some-long-profile-name-redacted-to-have-the-same-length-50510') was aborted:
ERROR: value too long for type character varying(50)

Having a look at both DBs we discored that in the old MySQL DB the column is a varchar(255) as opposed to a varchar(50) in the PostgreSQL DB.

Does any body happen to know where this difference comes from?
Which columns in which tables also have to be changed if we change the profile key?

The column schema_migrations contains the following values in version:

version
1
2
1153
1200
1205
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1246
1247
1248
1249
1250
1251
1252
1253
1254
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1300
1301
1302
1303
1304
1307
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1814
1830
1831
1832
1833
1834
1835
1836
1837
1838

Any help would be appreciated!

TIA & KR,
Oliver

Hi there,

just an update to this issue:
After shortening the only project_key which was too long, the migration ran smoothly. Out of curiosity, I changed the name of the quality profile to the original too long one just to see which error message occurs.
But in a kind of plot twist, the was no error message, the quality profile has the long name and the column in the database is now also varchar(255)…

KR,
Oliver

Thank you for reporting this, I’m investigating this issue.

I’m confused by your second message:

It seems to suggest that you got different behavior on migrating the same source database, and I don’t see how that’s possible. Can you please share your testing steps in more detail?

I only did one successful migration.

Changing the quality profile name was done via the web UI after SQ was up and running again. After that change the database column was extended.

KR,
Oliver

To conclude, having values in project_qprofiles.profile_key in the source database longer than 50 is unexpected, we don’t know how it’s possible to get into that state. When this happens, the values need to be manually reviewed and deleted.

I am surprised, you say you do not know how to get into that state, as I have explained how to reproduce the long values one post above yours…