Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

kmc internal server error without mysql root privileges #384

Closed
fxpz opened this issue Apr 21, 2015 · 9 comments
Closed

kmc internal server error without mysql root privileges #384

fxpz opened this issue Apr 21, 2015 · 9 comments

Comments

@fxpz
Copy link

fxpz commented Apr 21, 2015

Hi All,

I've configured my KalturaCE in a single machine with external mysql and SSL Offloading on load balancer.
As reported in documentation, after installation finished I removed user root@% from mysql.

Everything seems works fine but when I go to kmc console I'm getting an "internal server error". From kaltlog:

2015-04-21 14:35:14 [0.002586] [192.168.10.8] [779312001] [API] [myReportsMgr::executeQuery]     NOTICE: Reports query using database host: [dev-mysql01] user [etl]
2015-04-21 14:35:14 [0.001050] [192.168.10.8] [779312001] [API] [myReportsMgr::executeQuery] ERR: exception 'Exception' with message 'Invalid query: The user specified as a definer ('root'@'%') does not exist' in /opt/kaltura/app/infra/log/KalturaLog.php:82
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(1320): KalturaLog::err('Invalid query: ...')
--
#11 {main}
2015-04-21 14:35:14 [0.001282] [192.168.10.8] [779312001] [API] [kCoreException->__construct] ERR: exception 'kCoreException' with message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
--
2015-04-21 14:35:14 [0.000347] [192.168.10.8] [779312001] [API] [myReportsMgr::executeQueryByType] NOTICE: Invalid query
2015-04-21 14:35:14 [0.000667] [192.168.10.8] [779312001] [API] [KalturaFrontController->getExceptionObject] CRIT: exception 'kCoreException' with message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')

I believe the error is

I seen `The user specified as a definer ('root'@'%') does not exist'`

in fact I see that all procedures have as definer root@% or etl@localhost. What I've wrong?

mysql> select db,specific_name,definer from proc;
+--------------+----------------------------------------+---------------+
| db           | specific_name                          | definer       |
+--------------+----------------------------------------+---------------+
| kalturadw    | calc_aggr_day_api_calls                | root@%        |
| kalturadw    | calc_aggr_day_errors                   | root@%        |
| kalturadw    | calc_aggr_day_play                     | root@%        |
| kalturadw    | calc_updated_batch_job                 | etl@localhost |
| kalturadw    | calc_updated_batch_job_day             | etl@localhost |
| kalturadw    | calc_aggr_day_ingestion                | etl@localhost |
| kalturadw    | calc_aggr_day_transcoding_usage        | root@%        |
| kalturadw    | post_aggregation_widget                | root@%        |
| kalturadw    | post_aggregation_partner               | root@%        |
| kalturadw    | post_aggregation_entry                 | root@%        |
| kalturadw    | pre_aggregation_entry                  | root@%        |
| kalturadw    | post_aggregation_live_entry            | root@%        |
| kalturadw    | pre_aggregation_live_entry             | root@%        |
| kalturadw    | add_plays_views                        | root@%        |
| kalturadw    | remove_plays_views                     | root@%        |
| kalturadw    | resolve_aggr_name                      | root@%        |
| kalturadw    | add_live_plays_views                   | root@%        |
| kalturadw    | remove_live_plays_views                | root@%        |
| kalturadw    | calc_month_id                          | root@%        |
| kalturadw    | calc_partner_storage_data_time_range   | root@%        |
| kalturadw    | get_overage_charge                     | root@%        |
| kalturadw    | calc_partner_overage                   | root@%        |
| kalturadw    | calc_partner_billing_data              | root@%        |
| kalturadw    | get_data_for_operational               | root@%        |
| kalturadw    | mark_operational_sync_as_done          | root@%        |
| kalturadw    | calc_partner_usage_data                | root@%        |
| kalturadw    | calc_median_ff_convert_job_wait_time   | etl@localhost |
| kalturadw    | calc_entries_sizes                     | root@%        |
| kalturadw    | add_daily_partition_for_table          | root@%        |
| kalturadw    | add_monthly_partition_for_table        | root@%        |
| kalturadw    | add_partitions                         | root@%        |
| kalturadw    | move_innodb_to_archive                 | root@%        |
| kalturadw    | do_tables_to_new                       | root@%        |
| kalturadw    | all_tables_to_new                      | root@%        |
| kalturadw    | apply_table_partitions_to_target_table | root@%        |
| kalturadw    | calc_time_shift                        | root@%        |
| kalturadw    | calc_partner_monthly_storage           | root@%        |
| kalturadw    | calc_aggr_day                          | root@%        |
| kalturadw    | populate_time_dim                      | root@%        |
| kalturadw    | calc_aggr_day_partner_storage          | root@%        |
| kalturadw    | calc_aggr_day_user_usage               | root@%        |
| kalturadw    | calc_aggr_day_bandwidth                | root@%        |
| kalturadw    | generate_daily_usage_report            | root@%        |
| kalturadw_ds | register_file                          | root@%        |
| kalturadw_ds | get_ip_country_location                | root@%        |
| kalturadw_ds | restore_file_status                    | root@%        |
| kalturadw_ds | set_file_status_full                   | root@%        |
| kalturadw_ds | set_file_status                        | root@%        |
| kalturadw_ds | fms_sessionize                         | root@%        |
| kalturadw_ds | fms_sessionize_by_date_id              | root@%        |
| kalturadw_ds | get_error_code                         | root@%        |
| kalturadw_ds | insert_invalid_ds_line                 | root@%        |
| kalturadw_ds | set_cycle_status                       | root@%        |
| kalturadw_ds | unify_incomplete_api_calls             | root@%        |
| kaltura      | update_entries                         | root@%        |
| kaltura      | update_kusers                          | root@%        |
| kalturadw_ds | empty_cycle_partition                  | root@%        |
| kalturadw_ds | add_cycle_partition                    | root@%        |
| kalturadw_ds | drop_cycle_partition                   | root@%        |
| kalturadw_ds | transfer_cycle_partition               | root@%        |
+--------------+----------------------------------------+---------------+
@jessp01
Copy link
Contributor

jessp01 commented Apr 21, 2015

Hello,

The DWH should work with its own user called etl which is created during
the kaltura-db-config.sh phase. The DB is then populated when running
kaltura-dwh-config.sh

Can you check that the values in
/opt/kaltura/dwh/.kettle/kettle.properties are correct and you are able to
connect with them?
Also, can you check the full query in the log to understand what fails?

May the source be with you,

Jess Portnoy

Join us for the Kaltura Connect Virtual Summits 2015 - Register now!
connect.kaltura.com

On Tue, 21 Apr 2015, fxpz wrote:

Hi All,

I've configured my KalturaCE in a single machine with external mysql and SSL Offloading on load balancer.
As reported in documentation, after installation finished I removed user root@% from mysql.

Everything seems works fine but when I go to kmc console I'm getting an "internal server error". From kaltlog:

2015-04-21 14:35:14 [0.002586] [192.168.10.8] [779312001] [API] [myReportsMgr::executeQuery] NOTICE: Reports query using datab
ase host: [dev-mysql01] user [etl]
2015-04-21 14:35:14 [0.001050] [192.168.10.8] [779312001] [API] [myReportsMgr::executeQuery] ERR: exception 'Exception' with messa
ge 'Invalid query: The user specified as a definer ('root'@'%') does not exist' in /opt/kaltura/app/infra/log/KalturaLog.php:82
Stack trace:

#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(1320): KalturaLog::err('Invalid query: ...')

#11 {main}
2015-04-21 14:35:14 [0.001282] [192.168.10.8] [779312001] [API] [kCoreException->__construct] ERR: exception 'kCoreException' with
message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:

#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')

2015-04-21 14:35:14 [0.000347] [192.168.10.8] [779312001] [API] [myReportsMgr::executeQueryByType] NOTICE: Invalid query
2015-04-21 14:35:14 [0.000667] [192.168.10.8] [779312001] [API] [KalturaFrontController->getExceptionObject] CRIT: exception 'kCor
eException' with message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')

I believe the error is

I seen The user specified as a definer ('root'@'%') does not exist'

in fact I see that all procedures have as definer root@% or etl@localhost. What I've wrong?

mysql> select db,specific_name,definer from proc;
+--------------+----------------------------------------+---------------+
| db | specific_name | definer |
+--------------+----------------------------------------+---------------+
| kalturadw | calc_aggr_day_api_calls | root@% |
| kalturadw | calc_aggr_day_errors | root@% |
| kalturadw | calc_aggr_day_play | root@% |
| kalturadw | calc_updated_batch_job | etl@localhost |
| kalturadw | calc_updated_batch_job_day | etl@localhost |
| kalturadw | calc_aggr_day_ingestion | etl@localhost |
| kalturadw | calc_aggr_day_transcoding_usage | root@% |
| kalturadw | post_aggregation_widget | root@% |
| kalturadw | post_aggregation_partner | root@% |
| kalturadw | post_aggregation_entry | root@% |
| kalturadw | pre_aggregation_entry | root@% |
| kalturadw | post_aggregation_live_entry | root@% |
| kalturadw | pre_aggregation_live_entry | root@% |
| kalturadw | add_plays_views | root@% |
| kalturadw | remove_plays_views | root@% |
| kalturadw | resolve_aggr_name | root@% |
| kalturadw | add_live_plays_views | root@% |
| kalturadw | remove_live_plays_views | root@% |
| kalturadw | calc_month_id | root@% |
| kalturadw | calc_partner_storage_data_time_range | root@% |
| kalturadw | get_overage_charge | root@% |
| kalturadw | calc_partner_overage | root@% |
| kalturadw | calc_partner_billing_data | root@% |
| kalturadw | get_data_for_operational | root@% |
| kalturadw | mark_operational_sync_as_done | root@% |
| kalturadw | calc_partner_usage_data | root@% |
| kalturadw | calc_median_ff_convert_job_wait_time | etl@localhost |
| kalturadw | calc_entries_sizes | root@% |
| kalturadw | add_daily_partition_for_table | root@% |
| kalturadw | add_monthly_partition_for_table | root@% |
| kalturadw | add_partitions | root@% |
| kalturadw | move_innodb_to_archive | root@% |
| kalturadw | do_tables_to_new | root@% |
| kalturadw | all_tables_to_new | root@% |
| kalturadw | apply_table_partitions_to_target_table | root@% |
| kalturadw | calc_time_shift | root@% |
| kalturadw | calc_partner_monthly_storage | root@% |
| kalturadw | calc_aggr_day | root@% |
| kalturadw | populate_time_dim | root@% |
| kalturadw | calc_aggr_day_partner_storage | root@% |
| kalturadw | calc_aggr_day_user_usage | root@% |
| kalturadw | calc_aggr_day_bandwidth | root@% |
| kalturadw | generate_daily_usage_report | root@% |
| kalturadw_ds | register_file | root@% |
| kalturadw_ds | get_ip_country_location | root@% |
| kalturadw_ds | restore_file_status | root@% |
| kalturadw_ds | set_file_status_full | root@% |
| kalturadw_ds | set_file_status | root@% |
| kalturadw_ds | fms_sessionize | root@% |
| kalturadw_ds | fms_sessionize_by_date_id | root@% |
| kalturadw_ds | get_error_code | root@% |
| kalturadw_ds | insert_invalid_ds_line | root@% |
| kalturadw_ds | set_cycle_status | root@% |
| kalturadw_ds | unify_incomplete_api_calls | root@% |
| kaltura | update_entries | root@% |
| kaltura | update_kusers | root@% |
| kalturadw_ds | empty_cycle_partition | root@% |
| kalturadw_ds | add_cycle_partition | root@% |
| kalturadw_ds | drop_cycle_partition | root@% |
| kalturadw_ds | transfer_cycle_partition | root@% |
+--------------+----------------------------------------+---------------+

—
Reply to this email directly or view it on GitHub.[AFBqvddw3xtQn_-qBGYI94pgrqETpkbKks5oBlodgaJpZM4EFOTX.gif]

@fxpz
Copy link
Author

fxpz commented Apr 21, 2015

Thx Jess,
I checked /opt/kaltura/dwh/.kettle/kettle.properties and I can connect fine but I see only this databases:

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| kaltura             |
| kalturadw           |
| kalturadw_bisources |
| kalturadw_ds        |
| kalturalog          |
+---------------------+

How can I check the full query? Are there a simple way?

@KalturaCommunity-zz
Copy link

Yes, please open /opt/kaltura/log/kaltura_api_v3.log and check for it.

May the source be with you,

Jess Portnoy

Join us for the Kaltura Connect Virtual Summits 2015 - Register now!
connect.kaltura.com

On Tue, 21 Apr 2015, fxpz wrote:

Thx Jess,
I checked /opt/kaltura/dwh/.kettle/kettle.properties and I can connect fine but I see only this databases:

mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| kaltura |
| kalturadw |
| kalturadw_bisources |
| kalturadw_ds |
| kalturalog |
+---------------------+

How can I check the full query? Are there a simple way?

—
Reply to this email directly or view it on GitHub.[ACH7XpzrVNk2hpi9SV3M277Uiaf8aAfBks5oBmG_gaJpZM4EFOTX.gif]

@fxpz
Copy link
Author

fxpz commented Apr 21, 2015

Hi, not sure is what you want:

2015-04-21 15:25:02 [0.000113] [192.168.10.8] [1149288334] [API] [kPermissionManager::init] NOTICE: Already initalized for this security context
2015-04-21 15:25:02 [0.000678] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: /* dev-kaltura01[1149288334][propel] */ SELECT partner.ID, partner.PARTNER_NAME, par
tner.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.D
ESCRIPTION, 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_LI
MIT_WARNING, partner.MONITOR_USAGE, partner.PRIORITY_GROUP_ID, partner.PARTNER_GROUP_TYPE, partner.PARTNER_PARENT_ID, partner.KMC_VERSION FROM `partner` WHERE partner.ID='101'
2015-04-21 15:25:02 [0.000696] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: Sql took - 0.00055217742919922 seconds
2015-04-21 15:25:02 [0.000358] [192.168.10.8] [1149288334] [API] [KalturaActionReflector->initService] DEBUG: Create or retrieve instance of action class [PartnerService]
2015-04-21 15:25:02 [0.000619] [192.168.10.8] [1149288334] [API] [KalturaDispatcher->dispatch] DEBUG: Invoke start
2015-04-21 15:25:02 [0.002301] [192.168.10.8] [1149288334] [API] [myReportsSqlFileMgr::getSqlFilePath] NOTICE: myReportsSqlFileMgr::getSqlFilePath: [partner_usage_dashboard] [count] [] [] -> [/opt/kaltura/app/alpha/apps/kaltura/lib/reports/partner_usage_dashboard/partner_usage_dashboard_count_no_text.sql]
2015-04-21 15:25:02 [0.000312] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE: 
/* -- partner_usage_dashboard count -- */
# return count_all positive, instead of running the calc_billing_data stored-procedure twice
select 1 as count_all;

2015-04-21 15:25:02 [0.001924] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQuery] NOTICE: Reports query using database host: [dev-mysql01] user [etl]
2015-04-21 15:25:02 [0.001087] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE: Query took [0.0034430027008057]
2015-04-21 15:25:02 [0.000239] [192.168.10.8] [1149288334] [API] [myReportsMgr::getTotalTableCount] NOTICE: count: [1]
2015-04-21 15:25:02 [0.000253] [192.168.10.8] [1149288334] [API] [myReportsSqlFileMgr::getSqlFilePath] NOTICE: myReportsSqlFileMgr::getSqlFilePath: [partner_usage_dashboard] [detail] [] [] -> [/opt/kaltura/app/alpha/apps/kaltura/lib/reports/partner_usage_dashboard/partner_usage_dashboard_detail_no_text.sql]
2015-04-21 15:25:02 [0.000438] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE: 
/* -- partner_usage_dashboard detail -- */

CALL kalturadw.calc_partner_usage_data("20150421", 101,TRUE);

2015-04-21 15:25:02 [0.003037] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQuery] NOTICE: Reports query using database host: [dev-mysql01] user [etl]
2015-04-21 15:25:02 [0.002957] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQuery] ERR: exception 'Exception' with message 'Invalid query: The user specified as a definer ('root'@'%') does not exist' in /opt/kaltura/app/infra/log/KalturaLog.php:82
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(1320): KalturaLog::err('Invalid query: ...')
#1 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
#2 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(382): myReportsMgr::executeQueryByType(101, 202, 3, Object(reportsInputFilter), 10000, 1, '', NULL, NULL)
#3 /opt/kaltura/app/alpha/apps/kaltura/lib/myPartnerUtils.class.php(870): myReportsMgr::getTable(101, 202, Object(reportsInputFilter), 10000, 1, '', NULL)
#4 /opt/kaltura/app/api_v3/services/PartnerService.php(337): myPartnerUtils::collectPartnerStatisticsFromDWH(Object(Partner), Array, '2015-04-21')
#5 [internal function]: PartnerService->getStatisticsAction()
#6 /opt/kaltura/app/api_v3/lib/KalturaActionReflector.php(233): call_user_func_array(Array, Array)
#7 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(121): KalturaActionReflector->invoke(Array)
#8 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(230): KalturaDispatcher->dispatch('partner', 'getStatistics', Array)
#9 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(102): KalturaFrontController->handleMultiRequest()
#10 /opt/kaltura/app/api_v3/web/index.php(20): KalturaFrontController->run()
#11 {main}
2015-04-21 15:25:02 [0.000288] [192.168.10.8] [1149288334] [API] [kCoreException->__construct] ERR: exception 'kCoreException' with message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
#1 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(382): myReportsMgr::executeQueryByType(101, 202, 3, Object(reportsInputFilter), 10000, 1, '', NULL, NULL)
#2 /opt/kaltura/app/alpha/apps/kaltura/lib/myPartnerUtils.class.php(870): myReportsMgr::getTable(101, 202, Object(reportsInputFilter), 10000, 1, '', NULL)
#3 /opt/kaltura/app/api_v3/services/PartnerService.php(337): myPartnerUtils::collectPartnerStatisticsFromDWH(Object(Partner), Array, '2015-04-21')
#4 [internal function]: PartnerService->getStatisticsAction()
#5 /opt/kaltura/app/api_v3/lib/KalturaActionReflector.php(233): call_user_func_array(Array, Array)
#6 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(121): KalturaActionReflector->invoke(Array)
#7 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(230): KalturaDispatcher->dispatch('partner', 'getStatistics', Array)
#8 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(102): KalturaFrontController->handleMultiRequest()
#9 /opt/kaltura/app/api_v3/web/index.php(20): KalturaFrontController->run()
#10 {main}
2015-04-21 15:25:02 [0.000334] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE: Invalid query
2015-04-21 15:25:02 [0.001993] [192.168.10.8] [1149288334] [API] [KalturaFrontController->getExceptionObject] CRIT: exception 'kCoreException' with message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
#1 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(382): myReportsMgr::executeQueryByType(101, 202, 3, Object(reportsInputFilter), 10000, 1, '', NULL, NULL)
#2 /opt/kaltura/app/alpha/apps/kaltura/lib/myPartnerUtils.class.php(870): myReportsMgr::getTable(101, 202, Object(reportsInputFilter), 10000, 1, '', NULL)
#3 /opt/kaltura/app/api_v3/services/PartnerService.php(337): myPartnerUtils::collectPartnerStatisticsFromDWH(Object(Partner), Array, '2015-04-21')
#4 [internal function]: PartnerService->getStatisticsAction()
#5 /opt/kaltura/app/api_v3/lib/KalturaActionReflector.php(233): call_user_func_array(Array, Array)
#6 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(121): KalturaActionReflector->invoke(Array)
#7 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(230): KalturaDispatcher->dispatch('partner', 'getStatistics', Array)
#8 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(102): KalturaFrontController->handleMultiRequest()
#9 /opt/kaltura/app/api_v3/web/index.php(20): KalturaFrontController->run()
#10 {main}
2015-04-21 15:25:02 [0.001845] [192.168.10.8] [1149288334] [API] [kMemoryManager::clearMemory] NOTICE: Clearing memory
2015-04-21 15:25:02 [0.000704] [192.168.10.8] [1149288334] [API] [KalturaDispatcher->dispatch] DEBUG: Dispatching service [partner], action [getinfo], reqIndex [3] with params Array
(
)

2015-04-21 15:25:02 [0.000172] [192.168.10.8] [1149288334] [API] [kPermissionManager::init] NOTICE: Already initalized for this security context
2015-04-21 15:25:02 [0.000476] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: /* dev-kaltura01[1149288334][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='101'
2015-04-21 15:25:02 [0.002073] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: Sql took - 0.00082206726074219 seconds
2015-04-21 15:25:02 [0.000751] [192.168.10.8] [1149288334] [API] [KalturaActionReflector->initService] DEBUG: Create or retrieve instance of action class [PartnerService]
2015-04-21 15:25:02 [0.000487] [192.168.10.8] [1149288334] [API] [KalturaDispatcher->dispatch] DEBUG: Invoke start
2015-04-21 15:25:02 [0.000835] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: /* dev-kaltura01[1149288334][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.PARTNER_PARENT_ID='101' OR partner.ID='101') AND partner.ID='101' LIMIT 1

@KalturaCommunity-zz
Copy link

It seems to be trying to call this proc:
CALL kalturadw.calc_partner_usage_data("20150421", 101,TRUE);

And fails.
Does the procedure exist? should have been created during
kaltura-dwh-config

May the source be with you,

Jess Portnoy

Join us for the Kaltura Connect Virtual Summits 2015 - Register now!
connect.kaltura.com

On Tue, 21 Apr 2015, fxpz wrote:

Hi, not sure is what you want:

2015-04-21 15:25:02 [0.000113] [192.168.10.8] [1149288334] [API] [kPermissionManager::init] NOTICE: Already initalized for this se
curity context
2015-04-21 15:25:02 [0.000678] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: /* dev-kaltura01[1149288334][p
ropel] / SELECT partner.ID, partner.PARTNER_NAME, par
tner.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.PREF
IX, partner.ADMIN_NAME, partner.ADMIN_EMAIL, partner.D
ESCRIPTION, partner.COMMERCIAL_USE, partner.MODERATE_CONTENT, partner.NOTIFY, partner.CUSTOM_DATA, partner.SERVICE_CONFIG_ID, part
ner.STATUS, partner.CONTENT_CATEGORIES, partner.TYPE,
partner.PHONE, partner.DESCRIBE_YOURSELF, partner.ADULT_CONTENT, partner.PARTNER_PACKAGE, partner.USAGE_PERCENT, partner.STORAGE_U
SAGE, partner.EIGHTY_PERCENT_WARNING, partner.USAGE_LI
MIT_WARNING, partner.MONITOR_USAGE, partner.PRIORITY_GROUP_ID, partner.PARTNER_GROUP_TYPE, partner.PARTNER_PARENT_ID, partner.KMC
VERSION FROM partner WHERE partner.ID='101'
2015-04-21 15:25:02 [0.000696] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: Sql took - 0.00055217742919922
seconds
2015-04-21 15:25:02 [0.000358] [192.168.10.8] [1149288334] [API] [KalturaActionReflector->initService] DEBUG: Create or retrieve i
nstance of action class [PartnerService]
2015-04-21 15:25:02 [0.000619] [192.168.10.8] [1149288334] [API] [KalturaDispatcher->dispatch] DEBUG: Invoke start
2015-04-21 15:25:02 [0.002301] [192.168.10.8] [1149288334] [API] [myReportsSqlFileMgr::getSqlFilePath] NOTICE: myReportsSqlFileMgr
::getSqlFilePath: [partner_usage_dashboard] [count] [] [] -> [/opt/kaltura/app/alpha/apps/kaltura/lib/reports/partner_usage_dashbo
ard/partner_usage_dashboard_count_no_text.sql]
2015-04-21 15:25:02 [0.000312] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE:
/ -- partner_usage_dashboard count -- */

return count_all positive, instead of running the calc_billing_data stored-procedure twice

select 1 as count_all;

2015-04-21 15:25:02 [0.001924] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQuery] NOTICE: Reports query using database
host: [dev-mysql01] user [etl]
2015-04-21 15:25:02 [0.001087] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE: Query took [0.00344300
27008057]
2015-04-21 15:25:02 [0.000239] [192.168.10.8] [1149288334] [API] [myReportsMgr::getTotalTableCount] NOTICE: count: [1]
2015-04-21 15:25:02 [0.000253] [192.168.10.8] [1149288334] [API] [myReportsSqlFileMgr::getSqlFilePath] NOTICE: myReportsSqlFileMgr
::getSqlFilePath: [partner_usage_dashboard] [detail] [] [] -> [/opt/kaltura/app/alpha/apps/kaltura/lib/reports/partner_usage_dashb
oard/partner_usage_dashboard_detail_no_text.sql]
2015-04-21 15:25:02 [0.000438] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE:
/* -- partner_usage_dashboard detail -- */

CALL kalturadw.calc_partner_usage_data("20150421", 101,TRUE);

2015-04-21 15:25:02 [0.003037] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQuery] NOTICE: Reports query using database
host: [dev-mysql01] user [etl]
2015-04-21 15:25:02 [0.002957] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQuery] ERR: exception 'Exception' with mess
age 'Invalid query: The user specified as a definer ('root'@'%') does not exist' in /opt/kaltura/app/infra/log/KalturaLog.php:82
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(1320): KalturaLog::err('Invalid query: ...')
#1 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
#2 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(382): myReportsMgr::executeQueryByType(101, 202, 3, Obje
ct(reportsInputFilter), 10000, 1, '', NULL, NULL)
#3 /opt/kaltura/app/alpha/apps/kaltura/lib/myPartnerUtils.class.php(870): myReportsMgr::getTable(101, 202, Object(reportsInputFilt
er), 10000, 1, '', NULL)
#4 /opt/kaltura/app/api_v3/services/PartnerService.php(337): myPartnerUtils::collectPartnerStatisticsFromDWH(Object(Partner), Arra
y, '2015-04-21')
#5 [internal function]: PartnerService->getStatisticsAction()
#6 /opt/kaltura/app/api_v3/lib/KalturaActionReflector.php(233): call_user_func_array(Array, Array)
#7 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(121): KalturaActionReflector->invoke(Array)
#8 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(230): KalturaDispatcher->dispatch('partner', 'getStatistics', Array)
#9 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(102): KalturaFrontController->handleMultiRequest()
#10 /opt/kaltura/app/api_v3/web/index.php(20): KalturaFrontController->run()
#11 {main}
2015-04-21 15:25:02 [0.000288] [192.168.10.8] [1149288334] [API] [kCoreException->__construct] ERR: exception 'kCoreException' wit
h message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
#1 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(382): myReportsMgr::executeQueryByType(101, 202, 3, Obje
ct(reportsInputFilter), 10000, 1, '', NULL, NULL)
#2 /opt/kaltura/app/alpha/apps/kaltura/lib/myPartnerUtils.class.php(870): myReportsMgr::getTable(101, 202, Object(reportsInputFilt
er), 10000, 1, '', NULL)
#3 /opt/kaltura/app/api_v3/services/PartnerService.php(337): myPartnerUtils::collectPartnerStatisticsFromDWH(Object(Partner), Arra
y, '2015-04-21')
#4 [internal function]: PartnerService->getStatisticsAction()
#5 /opt/kaltura/app/api_v3/lib/KalturaActionReflector.php(233): call_user_func_array(Array, Array)
#6 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(121): KalturaActionReflector->invoke(Array)
#7 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(230): KalturaDispatcher->dispatch('partner', 'getStatistics', Array)
#8 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(102): KalturaFrontController->handleMultiRequest()
#9 /opt/kaltura/app/api_v3/web/index.php(20): KalturaFrontController->run()
#10 {main}
2015-04-21 15:25:02 [0.000334] [192.168.10.8] [1149288334] [API] [myReportsMgr::executeQueryByType] NOTICE: Invalid query
2015-04-21 15:25:02 [0.001993] [192.168.10.8] [1149288334] [API] [KalturaFrontController->getExceptionObject] CRIT: exception 'kCo
reException' with message 'Invalid query' in /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php:1322
Stack trace:
#0 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(869): myReportsMgr::executeQuery('?CALL kalturadw...')
#1 /opt/kaltura/app/alpha/apps/kaltura/lib/reports/myReportsMgr.class.php(382): myReportsMgr::executeQueryByType(101, 202, 3, Obje
ct(reportsInputFilter), 10000, 1, '', NULL, NULL)
#2 /opt/kaltura/app/alpha/apps/kaltura/lib/myPartnerUtils.class.php(870): myReportsMgr::getTable(101, 202, Object(reportsInputFilt
er), 10000, 1, '', NULL)
#3 /opt/kaltura/app/api_v3/services/PartnerService.php(337): myPartnerUtils::collectPartnerStatisticsFromDWH(Object(Partner), Arra
y, '2015-04-21')
#4 [internal function]: PartnerService->getStatisticsAction()
#5 /opt/kaltura/app/api_v3/lib/KalturaActionReflector.php(233): call_user_func_array(Array, Array)
#6 /opt/kaltura/app/api_v3/lib/KalturaDispatcher.php(121): KalturaActionReflector->invoke(Array)
#7 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(230): KalturaDispatcher->dispatch('partner', 'getStatistics', Array)
#8 /opt/kaltura/app/api_v3/lib/KalturaFrontController.php(102): KalturaFrontController->handleMultiRequest()
#9 /opt/kaltura/app/api_v3/web/index.php(20): KalturaFrontController->run()
#10 {main}
2015-04-21 15:25:02 [0.001845] [192.168.10.8] [1149288334] [API] [kMemoryManager::clearMemory] NOTICE: Clearing memory
2015-04-21 15:25:02 [0.000704] [192.168.10.8] [1149288334] [API] [KalturaDispatcher->dispatch] DEBUG: Dispatching service [partner
], action [getinfo], reqIndex [3] with params Array
(
)

2015-04-21 15:25:02 [0.000172] [192.168.10.8] [1149288334] [API] [kPermissionManager::init] NOTICE: Already initalized for this se
curity context
2015-04-21 15:25:02 [0.000476] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: /* dev-kaltura01[1149288334][p
ropel] / SELECT partner.ID, partner.PARTNER_NAME, partner.PARTNER_ALIAS, partner.URL1, partner.URL2, partner.SECRET, partner.ADMI
N_SECRET, partner.MAX_NUMBER_OF_HITS_PER_DAY, partner.APPEAR_IN_SEARCH, partner.DEBUG_LEVEL, partner.CREATED_AT, partner.UPDATED_A
T, 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, pa
rtner.ADULT_CONTENT, partner.PARTNER_PACKAGE, partner.USAGE_PERCENT, partner.STORAGE_USAGE, partner.EIGHTY_PERCENT_WARNING, partne
r.USAGE_LIMIT_WARNING, partner.MONITOR_USAGE, partner.PRIORITY_GROUP_ID, partner.PART
NER_GROUP_TYPE, partner.PARTNER_PARENT_ID, partner.KMC_VERSION FROM partner WHERE partner.ID='101'
2015-04-21 15:25:02 [0.002073] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: Sql took - 0.00082206726074219
seconds
2015-04-21 15:25:02 [0.000751] [192.168.10.8] [1149288334] [API] [KalturaActionReflector->initService] DEBUG: Create or retrieve i
nstance of action class [PartnerService]
2015-04-21 15:25:02 [0.000487] [192.168.10.8] [1149288334] [API] [KalturaDispatcher->dispatch] DEBUG: Invoke start
2015-04-21 15:25:02 [0.000835] [192.168.10.8] [1149288334] [API] [KalturaStatement->execute] DEBUG: / dev-kaltura01[1149288334][p
ropel] */ SELECT partner.ID, partner.PARTNER_NAME, partner.PARTNER_ALIAS, partner.URL1, partner.URL2, partner.SECRET, partner.ADMI
N_SECRET, partner.MAX_NUMBER_OF_HITS_PER_DAY, partner.APPEAR_IN_SEARCH, partner.DEBUG_LEVEL, partner.CREATED_AT, partner.UPDATED_A
T, 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, pa
rtner.ADULT_CONTENT, partner.PARTNER_PACKAGE, partner.USAGE_PERCENT, partner.STORAGE_USAGE, partner.EIGHTY_PERCENT_WARNING, partne
r.USAGE_LIMIT_WARNING, partner.MONITOR_USAGE, partner.PRIORITY_GROUP_ID, partner.PART
NER_GROUP_TYPE, partner.PARTNER_PARENT_ID, partner.KMC_VERSION FROM partner WHERE (partner.PARTNER_PARENT_ID='101' OR partner.I
D='101') AND partner.ID='101' LIMIT 1

—
Reply to this email directly or view it on GitHub.[ACH7XuJqH7PEQGFPBG00k93yNKoXEHLbks5oBmUUgaJpZM4EFOTX.gif]

@fxpz
Copy link
Author

fxpz commented Apr 21, 2015

Thx Jess,
Yes the procedure exists and it run correctly but it has root@% as definer, this means that when the procedure is executed (by whatever user) it runs with privileges of root@%. If the user root@% doesn't exists the procedure can't run.

The definer root@% is correct?

@jessp01
Copy link
Contributor

jessp01 commented Apr 21, 2015

Hm.. to be honest, where it comes to DWH, I only know it from an
integration perspective. Can you please open an issue at
https://github.com/kaltura/dwh? and if you can also try changing it to use
the etl user and see if it works that would be nice to mention in the
issu:)

May the source be with you,

Jess Portnoy

Join us for the Kaltura Connect Virtual Summits 2015 - Register now!
connect.kaltura.com

On Tue, 21 Apr 2015, fxpz wrote:

Thx Jess,
Yes the procedure exists and it run correctly but it has root@% as definer, this means that when the procedure is executed (by
whatever user) it runs with privileges of root@%. If the user root@% doesn't exists the procedure can't run.

The definer root@% is correct?

—
Reply to this email directly or view it on GitHub.[AFBqvexHv8rW7QMcnVSQEYHTmo95oteiks5oBmivgaJpZM4EFOTX.gif]

@fxpz
Copy link
Author

fxpz commented Apr 21, 2015

Ok, changing definer as etl@% it works, but I don't believe it's the correct way...
update proc SET definer='etl@%';

I'll try to

  • drop-dwh-data.sh
  • kaltura-dwh-config
  • check logs

Thanks for your support!

@KalturaCommunity-zz
Copy link

Welcome. What should really be done is changing the proc definition in the
SQLs to run as etl and make sure that works, then, a pull request to dwh
would be great.
I am closing this issue though, since we know the cause is not
installation related, in the sense that it is not something the RPM
packages do wrong.

May the source be with you,

Jess Portnoy

Join us for the Kaltura Connect Virtual Summits 2015 - Register now!
connect.kaltura.com

On Tue, 21 Apr 2015, fxpz wrote:

Ok, changing definer as etl@% it works, but I don't believe it's the correct way...
update proc SET definer='etl@%';

I'll try to

  • drop-dwh-data.sh
  • kaltura-dwh-config
  • check logs

Thanks for your support!

—
Reply to this email directly or view it on GitHub.[ACH7Xu04vc3ZtZQn6tMzqfrI9dV16GFwks5oBmrvgaJpZM4EFOTX.gif]

@jessp01 jessp01 closed this as completed Apr 21, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@KalturaCommunity-zz @jessp01 @fxpz