Need to completely wipeout and reset Analytics DB

jeansalim83 · July 9, 2015, 7:11am

I have been updating Kaltura CE successfully from 9.x till 10.14 now.
But Analytics never worked. The graphs are always empty.
I tried everything posted on the forums and Github but it’s still not working.

At this stage, I think my best shot would be to completely wipeout Analytics and reinstall it, including the DB.

Do you have a procedure that can help me?

jess · July 9, 2015, 1:02pm

Hello,

You can follow this:

github.com/kaltura/platform-install-packages

content report doesn't work on kaltura ce

opened 01:12AM - 07 Jan 15 UTC

closed 04:13PM - 26 Jan 15 UTC

mchakroun

tail /opt/kaltura/log/batch/dbcleanup-0-2015-01-07.err.log PHP Fatal error: Uncaught exception 'KalturaClientException' with message 'couldn't connect to host' in /home/kaltura/app/batch/client/KalturaClientBase.php:357 Stack trace: #0 /home/kaltura/app/batch/client/KalturaClient.php(1309): KalturaClientBase->doQueue() #1 /home/kaltura/app/batch/batches/Cleanup/KAsyncDbCleanup.class.php(30): KalturaBatchService->cleanExclusiveJobs() #2 /home/kaltura/app/batch/batches/Cleanup/KAsyncDbCleanupExe.php(11):...

Remember to backup the DB first, it states how.

But before you do so, what errors do you see in /opt/kaltura/dwh/logs?
Also, you can try to run the steps manually with:
rm /opt/kaltura/dwh/logs/*
logrotate -vvv -f /etc/logrotate.d/kaltura_apache
su kaltura -c “/opt/kaltura/dwh/etlsource/execute/etl_hourly.sh -p /opt/kaltura/dwh -k /opt/kaltura/pentaho/pdi/kitchen.sh”
su kaltura -c “/opt/kaltura/dwh/etlsource/execute/etl_update_dims.sh -p /opt/kaltura/dwh -k /opt/kaltura/pentaho/pdi/kitchen.sh”
su kaltura -c “/opt/kaltura/dwh/etlsource/execute/etl_daily.sh -p /opt/kaltura/dwh -k /opt/kaltura/pentaho/pdi/kitchen.sh”
su kaltura -c “/opt/kaltura/dwh/etlsource/execute/etl_perform_retention_policy.sh -p /opt/kaltura/dwh -k /opt/kaltura/pentaho/pdi/kitchen.sh”
su kaltura -c “/opt/kaltura/app/alpha/scripts/dwh/dwh_plays_views_sync.sh >> /opt/kaltura/log/cron.log”

And check what’s failing.

Finally, here are some queries to run to check where it might fail:
check if a process lock is stuck:
mysql> select * from kalturadw_ds.locks ;
if lock_state says 1 for any of these, make sure you have no stuck dwh procs running, update it to read 0 and retry.

check if access files were processed:
mysql> select * from kalturadw_ds.files where insert_time >=20150706;
check if actual data about entries play and views was inserted:
mysql> select * from kalturadw.dwh_fact_events where event_date_id >=20150706 ;

jeansalim83 · July 12, 2015, 8:45am

Hi Jess,

Thanks for the help.
I completely reset the DB as per the instructions (I already had did that three months ago and it didn’t solve it). It’s still not showing any stats.

I also checked the locks, kalturadw_ds.files, kalturadw.dwh_fact_events and it’s populated.

Jean.

jess · July 12, 2015, 11:54am

Where are you looking for the stats?
Also, try running this from the shell:

github.com

kaltura/platform-install-packages/blob/Jupiter-10.15.0/RPM/scripts/postinst/dwh_stats.php

<?php
if($argc<6){
    die('Usage: '.$argv[0] .' <partner id> <admin secret> <service_url> <start date i.e "01 Jul 2015"> <end date i.e "10 Jul 2015">'."\n");
}
require_once('/opt/kaltura/web/content/clientlibs/php5/KalturaClient.php');

// generate a KS
$userId = null;
$expiry = null;
$privileges = null;
$partnerId=$argv[1];
$secret = $argv[2];
$type = KalturaSessionType::ADMIN;
$config = new KalturaConfiguration($partnerId);
$config->serviceUrl = $argv[3];
$start_date=date_format(date_create($argv[4]), 'U');
$end_date=date_format(date_create($argv[5]), 'U');
$client = new KalturaClient($config);
$ks = $client->session->start($secret, $userId, $type, $partnerId, $expiry, $privileges);
$client->setKs($ks);

This file has been truncated. show original

Its a simple script that makes some calls to the report API, like the KMC does… Lets see what it returns.
Also, what about errors in /opt/kaltura/dwh/logs/?

jeansalim83 · July 18, 2015, 10:18am

I’m displaying the stats Through KMC.
The output is:
[root@backend1 tmp]# php ./dwh_stats.php 102 MGYzNWRjM2YwZDExOWRiNmNiZmY1OTEwZGFmMDY4MDFlNWJiNDY5YnwxMDI7MTAyOzE0MzcyOTkzMTQ7MjsxNDM3MjEyOTE0Ljk5MzU7YmF0c2F0QGJhdHNhdC5jb207ZGlzYWJsZWVudGl0bGVtZW50Ozs= “backend1.flomina.com” “01 Jul 2015” "10 Jul 2015"
PHP Fatal error: Uncaught exception ‘KalturaException’ with message ‘Error while starting session for partner [102]’ in /opt/kaltura/web/content/clientlibs/php5/KalturaClientBase.php:768
Stack trace:
#0 /opt/kaltura/web/content/clientlibs/php5/KalturaClient.php(6599): KalturaClientBase->throwExceptionIfError(Array)
#1 /var/tmp/dwh_stats.php(18): KalturaSessionService->start(‘MGYzNWRjM2YwZDE…’, NULL, 2, ‘102’, NULL, NULL)
#2 {main}
thrown in /opt/kaltura/web/content/clientlibs/php5/KalturaClientBase.php on line 768
[root@backend1 tmp]# php ./dwh_stats.php 102 MGYzNWRjM2YwZDExOWRiNmNiZmY1OTEwZGFmMDY4MDFlNWJiNDY5YnwxMDI7MTAyOzE0MzcyOTkzMTQ7MjsxNDM3MjEyOTE0Ljk5MzU7YmF0c2F0QGJhdHNhdC5jb207ZGlzYWJsZWVudGl0bGVtZW50Ozs= backend1.flomina.com “01 Jul 2015” "10 Jul 2015"
PHP Fatal error: Uncaught exception ‘KalturaException’ with message ‘Error while starting session for partner [102]’ in /opt/kaltura/web/content/clientlibs/php5/KalturaClientBase.php:768
Stack trace:
#0 /opt/kaltura/web/content/clientlibs/php5/KalturaClient.php(6599): KalturaClientBase->throwExceptionIfError(Array)
#1 /var/tmp/dwh_stats.php(18): KalturaSessionService->start(‘MGYzNWRjM2YwZDE…’, NULL, 2, ‘102’, NULL, NULL)
#2 {main}
thrown in /opt/kaltura/web/content/clientlibs/php5/KalturaClientBase.php on line 768

As for the errors in /opt/kaltura/dwh/logs
./etl_daily-20150118.log:46:ERROR 18-01 00:59:06,413 - Run kjbVar job - Unable to open file appender for file [${LogPath}/log_${jobVar}.log] : org.pentaho.di.core.exception.KettleFileException:
./etl_daily-20150118.log:53:ERROR 18-01 00:59:06,413 - Run kjbVar job - org.pentaho.di.core.exception.KettleFileException:
./etl_daily-20150118.log:87:ERROR 18-01 00:59:06,415 - Abort job 1 - Aborting job.

jess · July 19, 2015, 5:16am

2 issues here:
0. seems partner 102’s secret is wrong…
you need to pass the admin secret which you can find in KMC under ‘Settings->Integration Settings’ or with:
mysql> select admin_secret from partner where user=102;

The other issue is what’s in etl_daily-20150118.log open the file and look at the full error. If you’re not sure what it means, paste it here so I can take a look.

Thanks,

jeansalim83 · July 23, 2015, 9:16am

[root@backend1 tmp]# php ./dwh_stats.php 102 09e45bc31a45103317acad286669b0e8 backend1.flomina.com “01 Jul 2015” "23 Jul 2015"
Getting TOP_CONTENT total info for 01 Jul 2015 - 23 Jul 2015…

object(KalturaReportTotal)#91 (3) {
[“header”]=>
string(89) “count_plays,sum_time_viewed,avg_time_viewed,count_loads,load_play_ratio,avg_view_drop_off”
[“data”]=>
string(5) “,”
[“relatedObjects”]=>
NULL
}
Getting TOP_CONTENT graph info for 01 Jul 2015 - 23 Jul 2015…

array(0) {
}
Getting OPERATION_SYSTEM total info for 01 Jul 2015 - 23 Jul 2015…

object(KalturaReportTotal)#90 (3) {
[“header”]=>
string(89) “count_plays,sum_time_viewed,avg_time_viewed,count_loads,load_play_ratio,avg_view_drop_off”
[“data”]=>
string(5) “,”
[“relatedObjects”]=>
NULL
}
Getting OPERATION_SYSTEM graph info for 01 Jul 2015 - 23 Jul 2015…

array(0) {
}

jess · July 24, 2015, 12:56am

Hey,

OK, so no stats…

Thanks