Still Struggling with DWH cycle - new install

@jess

Do you have any suggestions on how to produce more details related to this ‘bail out error’? As mention I was not able to get any errors to display or register with the ‘bash -x’ entry. I think the more direct way to to manually replace the missing table or install the DWH database and structure. This is assuming the only issue is the database not being properly setup during configuration.

@jess

I figured out what I was doing wrong that caused the error. I drop all table but not all the stored procedures and functions. Oddly, this has to be done one by one using phpMyAdmin.

After clearing the entire database tables as specified I was able to run the command without errors. Which appears to have fixed the issue on both servers having the DWH issue described.

@jess

okay. I got the DWH configuration script to run without a failure or bailout error. However, the stats are still not being recorded on either system I did this on. Any other suggestions or could another process be affecting the proper configuration?

Hi @hiphopservers,

If
# grep ERROR /opt/kaltura/dwh/logs/*.log

returns nothing and you are positive there are views from OUTSIDE the KMC, then let’s go through the troubleshooting steps from https://github.com/kaltura/platform-install-packages/blob/Kajam-11.16.0/doc/kaltura-packages-faq.md. specifically:

check if access files were processed:

mysql> select * from kalturadw_ds.files where insert_time >=%Y%m%d;

check if actual data about entries play and views was inserted:

mysql> select * from kalturadw.dwh_fact_events where event_date_id >=%Y%m%d ;

If no files were processed, check /opt/kaltura/web/logs/-kaltura_apache_accesslog
and ensure you actually see entries where the service is ‘stats’ and the action is ‘collect’. If there aren’t any, then try to play an entry from OUTSIDE KMC and check again. This is the first step.

We’ll know more once you’ve gone though these checks.

Hello,

I’ve read all the discussion and I have the same problem.
When I try this command :
mysql> select * from kalturadw_ds.files where insert_time >=%Y%m%d;
I have this error :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘%Y-%m-%d’ at line 1

When I check the DB (kalturadw_ds) with PhpMyAdmin, I have nothing in files table.

I think it’s a server configuration issue …
I do a clean install with the latest Kaltura CE version.

Thanks a lot,
GreG

Hello @ZgreG,

%Y%m%d should be replaced with <FULL_YEAR><TWO_DIGIT_MONTH><DAY>, like:

mysql> select * from kalturadw_ds.files where insert_time >=20170601;

I’m very sorry about my ignorance :frowning:
Ok, the command is going done and I have data in “kalturadw_ds.files” and “kalturadw.dwh_fact_events”.

So I don’t understand after a fresh install without any problems the Analytics isn’t working …
Everything works well except analytics and the view counter in Content.
Did I miss something, perhaps with Publishers rights or configurations ?

GreG

What’s the output for:

mysql> select * from kalturadw.dwh_fact_events where event_date_id >=%Y%m%d ;

And also:

# grep ERROR /opt/kaltura/dwh/logs/*

?

±--------±---------±--------------±---------------±--------------------±--------------±--------------±-------------------------------------±-----------±-----------±--------------±----------±-----------±-----±--------------±---------±---------------±---------------±-----------±------------±-----------------±-----------±-----±----------±----------±--------------------±-----------------±------------±------±-----------±-----------±--------±---------------+
| file_id | event_id | event_type_id | client_version | event_time | event_date_id | event_hour_id | session_id | partner_id | entry_id | unique_viewer | widget_id | ui_conf_id | uid | current_point | duration | user_ip | user_ip_number | country_id | location_id | process_duration | control_id | seek | new_point | domain_id | entry_media_type_id | entry_partner_id | referrer_id | os_id | browser_id | context_id | user_id | application_id |
±--------±---------±--------------±---------------±--------------------±--------------±--------------±-------------------------------------±-----------±-----------±--------------±----------±-----------±-----±--------------±---------±---------------±---------------±-----------±------------±-----------------±-----------±-----±----------±----------±--------------------±-----------------±------------±------±-----------±-----------±--------±---------------+
| 1 | 54 | 17 | 2.57 | 2017-06-06 15:26:45 | 20170606 | 15 | 85475a47-fe1d-40e5-1063-05cb594ea1d1 | 101 | 0_lvw3ht1g | | _101 | 23448170 | | 10 | 119 | 82.235.134.184 | 1391167160 | 68 | 68 | 0 | -1 | 0 | -1 | 1 | -1 | -1 | 1 | 1 | 1 | -1 | 0 | 0 |
| 1 | 56 | 16 | 2.57 | 2017-06-06 15:26:45 | 20170606 | 15 | 85475a47-fe1d-40e5-1063-05cb594ea1d1 | 101 | 0_lvw3ht1g | | _101 | 23448170 | | 10 | 119 | 82.235.134.184 | 1391167160 | 68 | 68 | 0 | -1 | 0 | -1 | 1 | -1 | -1 | 1 | 1 | 1 | -1 | 0 | 0 |
±--------±---------±--------------±---------------±--------------------±--------------±--------------±-------------------------------------±-----------±-----------±--------------±----------±-----------±-----±--------------±---------±---------------±---------------±-----------±------------±-----------------±-----------±-----±----------±----------±--------------------±-----------------±------------±------±-----------±-----------±--------±---------------+
2 rows in set (0.00 sec)

And nothing for the grep command.

OK, so there are two events here from today.
Do you not see them in KMC->Analytics->Top Content?

No nothing :frowning:

Since these events are from today, the processing may not have finished yet. Note that the analytics do not update in real time.
Try running:
/opt/kaltura/bin/kaltura-run-dwh.sh

reading config file /etc/logrotate.d/kaltura_apache
reading config info for /opt/kaltura/log/kaltura_apache_errors.log 
error: /etc/logrotate.d/kaltura_apache:12 unknown option 'su' -- ignoring line
error: /etc/logrotate.d/kaltura_apache:12 unexpected text
reading config info for /opt/kaltura/log/kaltura_apache_access.log 
error: /etc/logrotate.d/kaltura_apache:26 unknown option 'su' -- ignoring line
error: /etc/logrotate.d/kaltura_apache:26 unexpected text
reading config info for /opt/kaltura/log/kaltura_apache_errors_ssl.log 
error: /etc/logrotate.d/kaltura_apache:40 unknown option 'su' -- ignoring line
error: /etc/logrotate.d/kaltura_apache:40 unexpected text
reading config info for /opt/kaltura/log/kaltura_apache_access_ssl.log 
error: /etc/logrotate.d/kaltura_apache:54 unknown option 'su' -- ignoring line
error: /etc/logrotate.d/kaltura_apache:54 unexpected text

Handling 4 logs

rotating pattern: /opt/kaltura/log/kaltura_apache_errors.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_errors.log
  log needs rotating
rotating log /opt/kaltura/log/kaltura_apache_errors.log, log->rotateCount is 5
dateext suffix '-20170606'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
destination /opt/kaltura/log/kaltura_apache_errors.log-20170606.gz already exists, skipping rotation

rotating pattern: /opt/kaltura/log/kaltura_apache_access.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_access.log
  log needs rotating
rotating log /opt/kaltura/log/kaltura_apache_access.log, log->rotateCount is 5
dateext suffix '-20170606'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
glob finding old rotated logs failed
renaming /opt/kaltura/log/kaltura_apache_access.log to /opt/kaltura/log/kaltura_apache_access.log-20170606
compressing log with: /bin/gzip
running last action script
Reloading httpd: 

rotating pattern: /opt/kaltura/log/kaltura_apache_errors_ssl.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_errors_ssl.log
  log /opt/kaltura/log/kaltura_apache_errors_ssl.log does not exist -- skipping
not running postrotate script, since no logs were rotated

rotating pattern: /opt/kaltura/log/kaltura_apache_access_ssl.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_access_ssl.log
  log /opt/kaltura/log/kaltura_apache_access_ssl.log does not exist -- skipping
not running last action script, since no logs will be rotated
PHP Warning:  file_get_contents(/opt/kaltura/app/cache//scripts/classMap.cache): failed to open stream: Permission denied in /opt/kaltura/app/infra/KAutoloader.php on line 415
PHP Class map could not be loaded from path [/opt/kaltura/app/cache//scripts/classMap.cache] file permissions [0600]

OK, so there’s a permissions issue on the cache dir that you should fix… see:

The:

error: /etc/logrotate.d/kaltura_apache:12 unknown option 'su' -- ignoring line
error: /etc/logrotate.d/kaltura_apache:12 unexpected text

errors can be ignored but other than these, there should be no errors.

After that, run it again and check if you see anything in KMC.

If you do not, put this code in /opt/kaltura/bin/test_dwh.php:

<?php

if (count($argv)<4){
    echo 'Usage:' .__FILE__ .' <service_url> <partnerid> <partner admin secret> '."\n";
    exit (1);
}
$service_url = $argv[1];
$partnerId=$argv[2];
$secret=$argv[3];
$basedir=dirname(__FILE__);
require_once($basedir.'/create_session.php');
$client=generate_ks($service_url,$partnerId,$secret,$type=KalturaSessionType::ADMIN,$userId=null);
$reportType = KalturaReportType::TOP_CONTENT;
$reportInputFilter = new KalturaReportInputFilter();
$reportInputFilter->fromDay = 20170601;
$reportInputFilter->toDay = 20170607;
$pager = new KalturaFilterPager();
$order = null;
$objectIds = null;
$result = $client->report->getTable($reportType, $reportInputFilter, $pager, $order, $objectIds);
var_dump($result);

Then run it like so:

# php /opt/kaltura/bin/test_dwh.php <service_url> <partnerid> <partner admin secret>

Your partner ID is 101, to get the partner’s admin secret, go to KMC->Settings->Integration settings.
Paste the results here.

I fix the permissions issues


I run it again :

/opt/kaltura/bin/kaltura-run-dwh.sh

Nothing happens in KMC and there is the result for :

rotating pattern: /opt/kaltura/log/kaltura_apache_access_ssl.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_access_ssl.log
  log /opt/kaltura/log/kaltura_apache_access_ssl.log does not exist -- skipping
not running last action script, since no logs will be rotated
PHP Fatal error:  Uncaught exception 'Zend_Log_Exception' with message '"/opt/kaltura/log/kaltura_scripts.log" cannot be opened with mode "a"' in /opt/kaltura/app/infra/log/KalturaSerializableStream.php:59
Stack trace:
#0 /opt/kaltura/app/infra/log/KalturaSerializableStream.php(31): KalturaSerializableStream->initStream()
#1 /opt/kaltura/app/infra/log/KalturaLogFactory.php(59): KalturaSerializableStream->__construct('/opt/kaltura/lo...', 'a')
#2 /opt/kaltura/app/infra/log/KalturaLogFactory.php(18): KalturaLogFactory::getWriter(Object(Zend_Config))
#3 /opt/kaltura/app/infra/log/KalturaLog.php(40): KalturaLogFactory::getLogger(Object(Zend_Config))
#4 /opt/kaltura/app/alpha/scripts/bootstrap.php(24): KalturaLog::initLog(Object(Zend_Config))
#5 /opt/kaltura/app/alpha/scripts/dwh/updateEntryPlaysViews.php(3): require_once('/opt/kaltura/ap...')
#6 {main}
  thrown in /opt/kaltura/app/infra/log/KalturaSerializableStream.php on line 59

I put the PHP code in the file as you write but when I try to execute it I have :

[root@vodyou bin]# nano /opt/kaltura/bin/test_dwh.php
[root@vodyou bin]# /opt/kaltura/bin/test_dwh.php vodyou.fr 101
-bash: /opt/kaltura/bin/test_dwh.php: Permission denied
[root@vodyou bin]# php /opt/kaltura/bin/test_dwh.php vodyou.fr 101 xxxxxxxxxxxxxxxxxxx
object(KalturaReportTable)#91 (4) {
  ["header"]=>
  NULL
  ["data"]=>
  NULL
  ["totalCount"]=>
  NULL
  ["relatedObjects"]=>
  NULL
}
[root@vodyou bin]#

Hi,

The script should be run using php, like so:

# php  /opt/kaltura/bin/test_dwh.php <your_service_url> <your_partner_id> <your_admin_secret>

Note that you posted your partner’s secret. You should never do that as this is a public forum. I edited your post.

As for your error. the log file /opt/kaltura/log/kaltura_scripts.log has the wrong permissions. Make sure it’s owned by ‘apache’ [or www-data on Ubuntu/Debian], with the group ‘kaltura’ and is set to 660.

Do the same for /opt/kaltura/log/cron.log and try to run /opt/kaltura/bin/kaltura-run-dwh.sh again.
Look for any errors thrown to STDOUT/STDERR as well as errors in /opt/kaltura/dwh/logs/*log

Sorry :slight_smile:

Ok, the permissions are fixed and the result seems to be fine.
Handling 4 logs

rotating pattern: /opt/kaltura/log/kaltura_apache_errors.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_errors.log
  log needs rotating
rotating log /opt/kaltura/log/kaltura_apache_errors.log, log->rotateCount is 5
dateext suffix '-20170606'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
destination /opt/kaltura/log/kaltura_apache_errors.log-20170606.gz already exists, skipping rotation

rotating pattern: /opt/kaltura/log/kaltura_apache_access.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_access.log
  log needs rotating
rotating log /opt/kaltura/log/kaltura_apache_access.log, log->rotateCount is 5
dateext suffix '-20170606'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
glob finding old rotated logs failed
renaming /opt/kaltura/log/kaltura_apache_access.log to /opt/kaltura/log/kaltura_apache_access.log-20170606
compressing log with: /bin/gzip
running last action script
Reloading httpd: 

rotating pattern: /opt/kaltura/log/kaltura_apache_errors_ssl.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_errors_ssl.log
  log /opt/kaltura/log/kaltura_apache_errors_ssl.log does not exist -- skipping
not running postrotate script, since no logs were rotated

rotating pattern: /opt/kaltura/log/kaltura_apache_access_ssl.log  forced from command line (5 rotations)
empty log files are not rotated, old logs are removed
considering log /opt/kaltura/log/kaltura_apache_access_ssl.log
  log /opt/kaltura/log/kaltura_apache_access_ssl.log does not exist -- skipping
not running last action script, since no logs will be rotated

So nothing happens in KMC …

When I do # grep ERROR /opt/kaltura/dwh/logs/*, nothing happens.
When I do # kaltlog, I have many errors, perhaps you’ll find an idea …

==> /opt/kaltura/log/batch/clearcuepoints-0-2017-06-06.err.log <==
PHP Fatal error:  Uncaught exception 'KalturaException' with message 'Internal server error occurred' in /opt/kaltura/app/batch/client/KalturaClientBase.php:894
Stack trace:
#0 /opt/kaltura/app/batch/client/KalturaClient.php(4285): KalturaClientBase->throwExceptionIfError(Array)
--
==> /opt/kaltura/log/batch/extractmedia-0-2017-06-06.err.log <==
PHP Warning:  Division by zero in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 281
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Notice:  Trying to get property of non-object in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282
PHP Warning:  Division by zero in /opt/kaltura/app/infra/media/mediaInfoParser/KMediaFileComplexity.php on line 282

--
==> /opt/kaltura/log/batch/import-0-2017-06-06.err.log <==
PHP Notice:  Undefined index: content-type in /opt/kaltura/app/batch/batches/Import/KAsyncImport.class.php on line 122

--
==> /opt/kaltura/log/batch/import-1-2017-06-06.err.log <==
PHP Notice:  Undefined index: content-type in /opt/kaltura/app/batch/batches/Import/KAsyncImport.class.php on line 122

--
==> /opt/kaltura/log/batch/import-3-2017-06-06.err.log <==
PHP Notice:  Undefined index: content-type in /opt/kaltura/app/batch/batches/Import/KAsyncImport.class.php on line 122

--
==> /opt/kaltura/log/batch/synchronizedistribution-0-2017-06-06.err.log <==
PHP Fatal error:  Uncaught exception 'KalturaClientException' with message 'Empty reply from server' in /opt/kaltura/app/batch/client/KalturaClientBase.php:362
Stack trace:
#0 /opt/kaltura/app/batch/client/KalturaPlugins/KalturaContentDistributionClientPlugin.php(2757): KalturaClientBase->doQueue()

When I play the video :

--
]
2017-06-06 18:54:45 [0.000841] [82.235.134.184] [2132572990] [4] [API] [KalturaDispatcher->dispatch] CRIT: exception 'Exception' with message 'Action does not exist!' in /opt/kaltura/app/infra/log/KalturaLog.php:74
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(56): KalturaLog::crit('Action does not...')
--
#3 {main}
2017-06-06 18:54:45 [0.001520] [82.235.134.184] [2132572990] [5] [API] [KalturaFrontController->getExceptionObject] ERR: exception 'KalturaAPIException' with message 'Action "trackevent" does not exists for service "analytics"' in /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php:57
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(113): KalturaDispatcher->dispatch('analytics', 'trackEvent', Array)
--
]
2017-06-06 18:54:45 [0.000670] [82.235.134.184] [125760075] [4] [API] [KalturaDispatcher->dispatch] CRIT: exception 'Exception' with message 'Action does not exist!' in /opt/kaltura/app/infra/log/KalturaLog.php:74
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(56): KalturaLog::crit('Action does not...')
--
#3 {main}
2017-06-06 18:54:45 [0.001198] [82.235.134.184] [125760075] [5] [API] [KalturaFrontController->getExceptionObject] ERR: exception 'KalturaAPIException' with message 'Action "trackevent" does not exists for service "analytics"' in /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php:57
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(113): KalturaDispatcher->dispatch('analytics', 'trackEvent', Array)
--
2017-06-06 18:54:45 [0.001008] [82.235.134.184] [522517505] [13] [API] [KalturaStatement->execute] DEBUG: /* vodyou.fr[522517505][propel] */ SELECT partner.ID, partner.PARTNER_NAME, partner.PARTNER_ALIAS, partner.URL1, partner.URL2, partner.SECRET, partner.ADMIN_SECRET, partner.MAX_NUMBER_OF_HITS_PER_DAY, partner.APPEAR_IN_SEARCH, partner.DEBUG_LEVEL, partner.CREATED_AT, partner.UPDATED_AT, partner.ANONYMOUS_KUSER_ID, partner.KS_MAX_EXPIRY_IN_SECONDS, partner.CREATE_USER_ON_DEMAND, partner.PREFIX, partner.ADMIN_NAME, partner.ADMIN_EMAIL, partner.DESCRIPTION, partner.COMMERCIAL_USE, partner.MODERATE_CONTENT, partner.NOTIFY, partner.CUSTOM_DATA, partner.SERVICE_CONFIG_ID, partner.STATUS, partner.CONTENT_CATEGORIES, partner.TYPE, partner.PHONE, partner.DESCRIBE_YOURSELF, partner.ADULT_CONTENT, partner.PARTNER_PACKAGE, partner.USAGE_PERCENT, partner.STORAGE_USAGE, partner.EIGHTY_PERCENT_WARNING, partner.USAGE_LIMIT_WARNING, partner.MONITOR_USAGE, partner.PRIORITY_GROUP_ID, partner.PARTNER_GROUP_TYPE, partner.PARTNER_PARENT_ID, partner.KMC_VERSION FROM `partner` WHERE partner.ID='0'
2017-06-06 18:54:45 [0.000659] [82.235.134.184] [123038757] [4] [API] [KalturaDispatcher->dispatch] CRIT: exception 'Exception' with message 'Action does not exist!' in /opt/kaltura/app/infra/log/KalturaLog.php:74
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(56): KalturaLog::crit('Action does not...')
--
==> /opt/kaltura/log/kaltura_api_v3.log <==
2017-06-06 18:54:45 [0.001230] [82.235.134.184] [123038757] [5] [API] [KalturaFrontController->getExceptionObject] ERR: exception 'KalturaAPIException' with message 'Action "trackevent" does not exists for service "analytics"' in /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php:57
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(113): KalturaDispatcher->dispatch('analytics', 'trackEvent', Array)
--
]
2017-06-06 18:54:55 [0.000839] [82.235.134.184] [1347449762] [4] [API] [KalturaDispatcher->dispatch] CRIT: exception 'Exception' with message 'Action does not exist!' in /opt/kaltura/app/infra/log/KalturaLog.php:74
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(56): KalturaLog::crit('Action does not...')
--
#3 {main}
2017-06-06 18:54:55 [0.001490] [82.235.134.184] [1347449762] [5] [API] [KalturaFrontController->getExceptionObject] ERR: exception 'KalturaAPIException' with message 'Action "trackevent" does not exists for service "analytics"' in /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php:57
Stack trace:
#0 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(113): KalturaDispatcher->dispatch('analytics', 'trackEvent', Array)
--

Thanks you very much for your time :slight_smile:

Hi Jess,
This is the result of the file test_dwh.php.

Hello Jess,

After a clean install, it’s seems the analytics is working fine.

I open another topic for new problems …

Thanks a lot,
GreG