Analytics install failed (dwh blues)

Community Forums Backend Server Support
1

Hello,

I just performed a clean 16.5 install but kaltura-dwh failed. I got this error:

ERROR 1499 (HY000) at line 1: Too many partitions (including subpartitions) were defined

I´m using a standard Centos 7.8, mariadb-5.5, etc. I went through the process twice but something is wrong with the database.

Any help (Jess) will be greatly appreciated !

Thank you,

David

2

Hi @david.eusse,

I’d start by invoking manually:

mysql> call kalturadw.add_partitions();

That’s probably the stored proc that fails. If that’s the case, need to debug it and see why. It is defined here:

So /opt/kaltura/dwh/ddl/dw/maintenance/add_partition_procedure.sql on the server.

3

Hello @jess,

I just ran mysql> call kalturadw.add_partitions();
This is the result:

MariaDB [(none)]> call kalturadw.add_partitions();
ERROR 1499 (HY000): Too many partitions (including subpartitions) were defined

What is interesting here is that this is a brand new install on Centos strictly following the instructions (that I have performed dozens of times, BTW).

I will try to find the source of the problem and report it if possible.

Regards,

David

4

Hi @david.eusse,

Yes, I know you are well expreinced in deploying Kaltura CE, I never doubted you:)
As I said, need to debug to find the source of the problem, then, we can discuss possible fixes.

Cheers,

5

Hello @Jess,
Analytics are my bad karma… But I got used to it somehow…

Regards

David

6

Hello,

Going back to this issue, I went through kaltura-dwh-config.sh script and found this:

Replace various old dates to avoid issues with partitions

    olddates=(20130831 201308 20130901 20130902 20131231 201312 20140101 201

406 20140701 201510 20151101)

When performing the grep /find part by hand, I noticed that no files are found, therefore, no expressions are replaced in order to solve the partitions error.

So, @jess , are these dates specific or can anything be done in order to fix this script and the partitions error again ?

Thank again for your help,

David

7

Hi @david.eusse ,

The commit that introduced this change is here: https://github.com/kaltura/platform-install-packages/commit/9a3187b27cc940b1095b257f761a80e2f7694194#diff-c1635cc60a15abddcf01efd4bd686688b777ae14d8ab0119c9f82d3601715332

As you can see, the code in question is:

# Replace various old dates to avoid issues with partitions
	olddates="p_20130831 p_201308 20130901 20130902 p_20131231 p_201312 20140101 p_201406 20140701 p_201510 20151101"
	for olddate in $olddates; do
	    FILES=`grep -rl $olddate`
	    if [ -n "$FILES" ]; then
	    	sed -i $FILES "s/$olddate/p_$LDAYLASTMONTH/g"
	    fi
	done

But, the grep operation should actually be done on the /opt/kaltura/dwh dir, see https://github.com/kaltura/dwh/search?q=20140101

Could you apply that change locally and see if that solves the issue?
We can then push the change so it makes it to the next release.

Cheers,

8

Thank you @jess !

I’l work on it and let you know.

Regards,

David

9

Hello,

So far, I fixed the script but it continued to bail out on the number of partitions. So I did this:

I found this script:
/opt/kaltura/dwh/ddl/dw/maintenance/add_partition_procedure.sql

And changed this:

SELECT NOW()
INTO _current_date;
Into this:
SELECT NOW() - interval 3 year
INTO _current_date;

It finally let the script run and populated the dwh database!

Just to make sure, I ran a couple of kalturadw.add_partitions() and I’l wait until tomorrow to see if it works.

I know it’s a horrible fix but if it works, I’l go through the populate scripts and fix the date issue.

David

10

Hi @david.eusse,

When you’ve verified the fix, you’re more than welcome to submit a pull. I’ll review it and merge if all is well.

Cheers,

11

Hello @jess ,

I didn’t work because it didn’t have partitions for the current date…:frowning:

After going again through the scripts and docs, I noticed that you say on kaltura-base-config.sh that mysql 5.6 is supported. Is it correct ?

Mysql 5.6 supports up to 8192 partitions per table, un like Maria 5.5 (1024 partitions).

Probably I am hitting my head against a wall with Maria 5.5?

I will try a different path and test mysql 5.6 and Maria 10.6 and post the results. I know that Maria 10 did work for us (at least for the kaltura db).

Regards,

David

12

Hello,

After testing different database setups, I post my results to anyone interested in making DWH work again.

The main problem right now is that mariadb 5.5 does not support more than 1024 innodb partitions /table. Even if one manages to install kaltura-dwh, it might fail soon complaining for this error.

I tried installing different versions of MariaDB and Mysql and here are the results:

  • Mysql 5.6: DWH installation failed. The main reason is key length restriction on this version. I gave up on this version

-MariaDB 10.0. DWH installation went well and it works !

I had had no partitions issues and installation went smoothly.

Had an issue with a table that was not created (dwh_fact_plays.sql, I don’t know why) but so far so good. I just ran the script again for that table.

Just needed to change the installation scripts (kaltura-db-config.sh, kaltura-base-config.sh) and put 10 instead of 5 here:

if [ “$MYMAJVER” -ne 10 ];then
echo -e "${BRIGHT_RED}Your version of MySQL is incompatible.

Everything works and I haven’t found any issues with kaltura/api or analytics.

-MariaDB 10.1:
Works but need to add the ARCHIVE engine. Given that both 10.0 and 10.1 ar obsolete, I stuck with 10.0 to make things easier

-MariaDB 10.2 and up:
The main issues is SQL2003 compliance and sql_mode. I managed to advance in the installation but I gave up because is not worth the effort.

So, if anyone wants to use kaltura-dwh right now, Mariadb 10.0 is an option. Just add the correct repository and go ahead.

Regards,

David

13

Hi @david.eusse ,

Thanks for sharing your research, much appreciated.
If you’d like to contribute these notes to the deployment guide, I’ll be happy to receive a pull from you.
With regards to the version check, could you share the exact string you get for select version();? I’ll revise the code accordingly, to allow working with this ver as well.

14

Hello @Jess,

You’re welcome.

This is my version on Centos 7.9:

MariaDB [(none)]> select version();
±----------------+
| version() |
±----------------+
| 10.0.38-MariaDB |
±----------------+
1 row in set (0.00 sec)

This is how I setup the MariaDB repository:

[root@dev01 ~]# cat /etc/yum.repos.d/MariaDB.repo

MariaDB 10.1 CentOS repository list

MariaDB - Setting up MariaDB Repositories - MariaDB

[mariadb]
name = MariaDB
baseurl = Index of /10.0/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

It is possible to use the same procedure up to Maria 10.1. With a little effort kaltura-dwh can be upgraded up to MariaDB 10.6, I think. Right now I stick to 10.0

The only mysterious issue is that this script apparently failed during setup and the table was missing:

/opt/kaltura/dwh/ddl//dw//facts/dwh_fact_plays.sql

I ran it again and everything works OK.

After the installation was successful, I ran kalturadw.add_partitions(); once

We started working on a new elastic analytics module but we have stalled because of other issues :slight_smile:

I’l write down everything I did and share it, of course.

Regards,

David