DWH analytics problem(ERROR 1499 (HY000))

Community Forums Backend Server Support
1

Hi,
I have Kaltura CE 15.20.0 installed on CentOS 7 and MariaDB 5.5.64 .
Everything seems to work except analytics.

the error log showing in /opt/kaltura/dwh/setup/installation_log.log
on last line:

now executing /opt/kaltura/dwh/ddl//dw//maintenance/populate_table_partitions.sql
ERROR 1499 (HY000) at line 1: Too many partitions (including subpartitions) were defined
1
Error - bailing out!

I running bellow command :
select table_name, count(1) from information_schema.partitions where table_name like ‘dwh_fact_%’ group by table_name;

the output is :

±------------------------------------±---------+
| table_name | count(1) |
±------------------------------------±---------+
| dwh_fact_active_users | 1 |
| dwh_fact_api_calls | 1 |
| dwh_fact_api_calls_archive | 1 |
| dwh_fact_bandwidth_usage | 1 |
| dwh_fact_bandwidth_usage_archive | 1 |
| dwh_fact_convert_job | 1 |
| dwh_fact_entries_sizes | 1 |
| dwh_fact_errors | 1 |
| dwh_fact_errors_archive | 1 |
| dwh_fact_events | 1024 |
| dwh_fact_events_archive | 1 |
| dwh_fact_fms_sessions | 1 |
| dwh_fact_fms_sessions_archive | 1 |
| dwh_fact_fms_session_events | 1 |
| dwh_fact_fms_session_events_archive | 1 |
| dwh_fact_incomplete_api_calls | 1 |
| dwh_fact_plays_archive | 1 |
±------------------------------------±---------+
17 rows in set (0.04 sec)

i running bellow command
update kalturadw_ds.retention_policy set archive_delete_days_back = 365 where archive_delete_days_back >= 1000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Thank you very much for any help

2

Im having this exact issue on a fresh install, new server. I followed the instructions Jess gave in this thread to try to solve the issue: DWH Issue on Retention Policy

The result is that my “dwh_fact_events” went from “1024” to “1023”.

Since the configuration stopped at “now executing /opt/kaltura/dwh/ddl//dw//maintenance/populate_table_partitions.sql”,

im assuming that im missing data in the database. How can I restart the process?

I have tried dpkg-reconfigure kaltura-dwh, and the result was:
“The Kaltura DWH DB seems to already be installed.
DB creation will be skipped.
DWH configured.”

Should I DROP the entire kalturadw database and rerun the reconfiguration (dpkg-reconfigure kaltura-dwh)?

Why did my fresh database (Percona) fill upp with so many events? (I’m not a expert and I dont know what those are).

Please advice,
Ed

3

I’m using Ubuntu 16.

4

Hello @borikhaan and @Edward,

I have the solution for this,
look for the sql scripts into /opt/kaltura/dwh/ddl/

grep -rl p_2012 /opt/kaltura/dwh/ddl/
grep -rl p_2013 /opt/kaltura/dwh/ddl/
grep -rl p_2014 /opt/kaltura/dwh/ddl/
grep -rl p_2015 /opt/kaltura/dwh/ddl/
grep -rl p_2016 /opt/kaltura/dwh/ddl/
grep -rl p_2017 /opt/kaltura/dwh/ddl/

and replace the partition data there, the first value, for example: p_20170430
must be replaced with the last day of the previous month, for example: p_20201031

then the second value must be the first day of the current month, for example: 20201101

it should look like this:
(PARTITION p_20201031 VALUES LESS THAN (20201101) ENGINE = INNODB) */;

you could create a script to replace all incidences automatically, after that, clean the DWH databases and run the DWH configuration again,

please note that I don’t know the technical details about this or if is the optimal solution or not, but at least it lets you install the DWH correctly and start getting play counts.

please share your results.

Bryan

5

Bryan, thank you so much for this!

6

I have the exact same issue. Is there a script available for the string replacement?

This issue can be found in other posts as well. Se for example

7

Hi @aquileasfx1

I have tried your solution but still same the issue, so another way to fix it? I’m using MySQL 5.5.

Thanks.