Upgrade 2.3.1 to 3.1.1 -> DM Media Upload Issue

[MarkCanada]

To be completed by the original poster:

CMS Version

3.1.1

Installation Method

Custom install

Operating System

Linux

Issue

Just upgraded from 2.3.1 to 3.1.1 and when trying to upload a video (after “physical” uploading) it shows the error message:
“SQLSTATE[HY000]: General error: 1270 Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_general_ci,IMPLICIT), (utf16_general_ci,IMPLICIT) for operation ‘concat_ws’”

Anyone come across that and knows which tables to change how?

Thanks
Mark

[dan]

Hi there,

Strange - it is probably a difference between collations on the tag.tag column and the lktagmedia.value column.

You could try and run:

ALTER TABLE `tag` CHARACTER SET utf8 COLLATE utf8_general_ci,
  CHANGE COLUMN `tag` `tag` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

ALTER TABLE `lktagmedia` CHARACTER SET utf8 COLLATE utf8_general_ci,
  CHANGE COLUMN `value` `value` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

There are a bunch of other tag link tables too - all starting with lktag.... - it might be advisable to check them all.

Thanks,
Dan

[MarkCanada]

Hi Dan

That seems to have worked. Thank you.
When trying to make a new layout we get “Invalid Layout - Empty Region”
I noticed that the following tables are “latin1_swedish_ci” for some reason.
Would you have by chance also ALTER commands for those? I wouldn’t know which columns would need changing.
bandwidth
bandwithtype
displayprofile
lkmediadisplaygroup
lkwidgetaudio
oauth_client_redirect_uris
oauth_scopes

Thank you very much
Mark

[dan]

Hi Mark,

@MarkCanada

That seems to have worked. Thank you.

Great, glad to hear it.

I don’t think you need to worry about all of those tables - they are tables that we’ve changed over time but wouldn’t be causing any further problems - not with layouts in any case.

You must have a really old Xibo install!

A new layout, being completely empty, would be invalid to start with. As you add stuff to it though you’d expect that message to change.

Thanks,
Dan

[MarkCanada]

Thanks for letting me know.
Yes - I think we started at 1.3 or something…maybe even earlier.? I think it was 2016.
Haven’t done anything the last couple years though through COVID since the screens were not needed during that time. Trying to get the hang of things with everything new in Xibo.
Every new layout we try has the issue after we add an image or video (starting with Full Screen Layout), but we were able to copy an old one and change it and that doesn’t throw the error.
So … just have to get a screen back up now and check if it actually works (since the preview doesn’t show video files).

Thanks
Mark