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.
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 
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?
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 :
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 
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
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