Kaltura databases mix up Latin1 and utf8
pkExec opened this issue · 3 comments
- 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;
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
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,
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);
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,