Upgrading MySQL DB Schema to utf8mb4 does not work for all communs

Help / Discuss
1

Hi there

I used my old instructions to setup a new xWiki Test instance (13.x), when I realized I should have used the new one supporting utf8mb4. Since this was a test instance, I attempted to migrate it over:

ERROR 1833 (HY000) at line 1: Cannot change column 'ase_eventid': used in a foreign key constraint 'FK2bj2ghquiq79toomkvugw7iie' of table 'xwiki.activitystream_events_targets'
ERROR 1832 (HY000) at line 1: Cannot change column 'ases_eventid': used in a foreign key constraint 'FKmwu6uxkc2jl3yvej4wi1p0pv0'
ERROR 1832 (HY000) at line 1: Cannot change column 'eventId': used in a foreign key constraint 'FK2bj2ghquiq79toomkvugw7iie'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWS_NAME': used in a foreign key constraint 'FK1xm6gpw6wh35haho3tf7i3n4b'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWD_NAME': used in a foreign key constraint 'FKislk01fhfjl018fsievpl1i8g'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWF_NAME': used in a foreign key constraint 'FKgomsxak3f8adh9r2r7i4rrwqw'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWI_NAME': used in a foreign key constraint 'FK8k6ecdg0ekjyin8x7v1jk1c4f'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FK38o52utm62kct6bou41whwk74'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKir4nsg6xqdx6mbc0ginj17ckd'
ERROR 1833 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKir4nsg6xqdx6mbc0ginj17ckd' of table 'xwiki.xwikilistitems'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKsvs1wxa1omg5hfh6pn0giyqv8'
ERROR 1833 (HY000) at line 1: Cannot change column 'XWP_NAME': used in a foreign key constraint 'FK1xm6gpw6wh35haho3tf7i3n4b' of table 'xwiki.xwikidates'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWS_NAME': used in a foreign key constraint 'FKplajldi1yq2cutcbwh1c433n0'

Not sure what the implications of this are and if one could just ignore those?

I ended up droping and re-creating the schema, but maybe this is important for someone upgrading?

2

utf8mb4 works fine for those columns. The problem is more the way you convert them, as MySQL will refuse to change the type of column to something incompatible with another column it’s linked with through a foreign key constraint.

I’m not sure you have a way to tell MySQL to convert two columns from different tables at the same time but what we usually do in migrations facing this kind of constrains is to drop them before doing the conversion and XWiki (or more accurately Hibernate) will recreate them automatically.

3

Thank you @tmortagne for the swift reply. So simply dumping the constraints, run the script from there:

and re-starting xWiki should do the trick?

4

Yes, they will automatically be re-created.

5

Perfect! Added note to the section in the Wiki referenced above …