Kaltura databases mix up Latin1 and utf8

[pkExec]

• I've read the guidelines for Contributing to Kaltura Projects
• I've read the installation guides and troubleshooting FAQ
• This is not a support request or question that should be posted on the Kaltura forum

Bug report

Kaltura uses a mix of latin1 and utf8 in its databases. The result is latin1 characters, stored inside utf8 tables, inside a latin1 database (!)
Proof:

USE KALTURA; SET NAMES UTF8; SELECT id,name FROM entry;

See attach file 1

USE KALTURA; SET NAMES LATIN1; SELECT id,name FROM entry;
![Capture2](https://user-images.githubusercontent.com/7925305/79862445-c340be80-83de-11ea-9e8d-cbf4b6a12626.JPG)

Linux distribution name and version:
Ubuntu 16.04.6 LTS, Xenial

Are you using the RPM or deb packages?

• RPM
• deb

For deb based systems:

# dpkg -l "kaltura-*"

||/ Name                                        Version                    Architecture               Description
+++-===========================================-==========================-==========================-===========================================================================================
ii  kaltura-base                                15.20.0-1+b2               all                        Kaltura Open Source Video Platform
ii  kaltura-batch                               15.18.0-1                  all                        Kaltura Open Source Video Platform - batch server
ii  kaltura-clipapp                             1.3-1+b2                   all                        Kaltura Video Presentations Manager
ii  kaltura-db                                  15.10.0-1+b1               all                        Kaltura Open Source Video Platform - batch server
ii  kaltura-dwh                                 15.1.0-1                   amd64                      Kaltura Open Source Video Platform - Analytics
ii  kaltura-elasticsearch                       1.0.0-1+b9                 all                        Kaltura Open Source Video Platform - Elasticsearch
ii  kaltura-ffmpeg                              4.0.2-3                    amd64
ii  kaltura-ffmpeg-aux                          2.1.3-1                    amd64
ii  kaltura-flexwrapper                         1.2-1+b1                   all                        Kaltura Video Presentations Manager
ii  kaltura-front                               15.18.0-1+b1               all                        Kaltura Open Source Video Platform - frontend server
ii  kaltura-html5-analytics                     0.3                        all                        Kaltura Open Source Video Platform - frontend server
ii  kaltura-html5-studio                        2.2.1-1                    all                        Kaltura Open Source Video Platform - frontend server
ii  kaltura-html5-studio3                       3.4.0-1                    all                        Kaltura Open Source Video Platform - frontend server
ii  kaltura-html5lib                            2.81.3-1+b1                all                        Kaltura Open Source Video Platform - HTML5 player
ii  kaltura-html5lib3                           0.51.3-2                   all                        Kaltura Open Source Video Platform - HTML5 player
ii  kaltura-kclip                               1.1.2.1-1                  all                        Kaltura Video Presentations Manager
ii  kaltura-kcw                                 2.2.4-1+b6                 all                        Kalture Contribution Wizard
ii  kaltura-kdp                                 2.7.0-1+b1                 all                        Kaltura Dynamic Player
ii  kaltura-kdp3                                3.9.9-1+b4                 amd64                      Kaltura Dynamic Player
ii  kaltura-kdp3wrapper                         37-1+b3                    all                        KDP3 Wrapper
ii  kaltura-kdpwrapper                          11-1+b2                    all                        KDP Wrapper
ii  kaltura-kmc                                 5.43.13-11+b32             all                        Kaltura Management Console
ii  kaltura-kmcng                               5.19-3+really5.17-1        all                        Kaltura Management Console
ii  kaltura-krecord                             1.7-1+b1                   all                        Kaltura kRecord - used for recording from web cam
ii  kaltura-kupload                             1.2.16-1+b2                all                        Kaltura kupload widget
un  kaltura-libapache2-mod-php7                 <none>                     <none>                     (no description available)
ii  kaltura-nginx                               1.16.0-1                   amd64                      Nginx web/proxy server - for Kaltura
ii  kaltura-pentaho                             4.2.1+b2                   amd64                      Pentaho Open Source Data Integration Community Edition (CE).
un  kaltura-php7-apcu                           <none>                     <none>                     (no description available)
un  kaltura-php7-cli                            <none>                     <none>                     (no description available)
un  kaltura-php7-curl                           <none>                     <none>                     (no description available)
un  kaltura-php7-gd                             <none>                     <none>                     (no description available)
un  kaltura-php7-gmp                            <none>                     <none>                     (no description available)
un  kaltura-php7-mcrypt                         <none>                     <none>                     (no description available)
un  kaltura-php7-memcache                       <none>                     <none>                     (no description available)
un  kaltura-php7-mysql                          <none>                     <none>                     (no description available)
un  kaltura-php7-ssh2                           <none>                     <none>                     (no description available)
un  kaltura-php7-xsl                            <none>                     <none>                     (no description available)
ii  kaltura-playkit-bundler                     1.2.0-1                    all                        Kaltura Open Source Video Platform - frontend server
ii  kaltura-postinst                            1.0.33-3+b13               all                        Kaltura Open Source Video Platform
ii  kaltura-sphinx                              2.2.1-4+b4                 amd64                      Sphinx full-text search server - for Kaltura
ii  kaltura-widgets                             14.1.0-1                   all                        Kaltura Open Source Video Platform - widgets meta package

Is this an all in one instance [single server] or a cluster?

• All in one
• [] Cluster

[jessp01]

Hello @pkExec,

Thank you for your report. This is not actually a packaging/installation issue but rather, stems from how the schema is defined here: https://github.com/kaltura/server/blob/Propus-16.1.0/deployment/base/sql/01.kaltura_ce_tables.sql
Since you were the one to find it, would you like to submit a pull request to the repo in question? That one, you'll be credited for your contribution.

Cheers,

[pkExec]

This is not actually a packaging/installation issue

Sorry I wasn't clear enough. The fix to the schema is easy enough, however this will break any existing databases, with garbled characters wherever non-latin language is used. This is how I noticed the problem.
If the schema is upgraded to use utf8 (I may get the time to submit the pull request for that), the packaging needs to use an upgrade script for the existing databases.
The upgrade script needs to run through all the text and varchar columns of all tables, and run a command similar to this:
UPDATE entry SET name = CONVERT(CONVERT(CONVERT(name USING latin1) USING binary) USING UTF8);

[jessp01]

Hello @pkExec,

Indeed but these SQL alter/update scripts are also maintained in the server repo, see:
https://github.com/kaltura/server/tree/Propus-16.1.0/deployment/updates/sql

The postinst hook in the kaltura-base package then invokes these. See:
https://github.com/kaltura/platform-install-packages/blob/Propus-16.0.0/RPM/SPECS/kaltura-base.spec#L301
https://github.com/kaltura/platform-install-packages/blob/Propus-16.0.0/deb/kaltura-base/debian/postinst#L86

Cheers,