killbill / killbill-analytics-plugin Goto Github PK
View Code? Open in Web Editor NEWKill Bill plugin for financial reporting
Home Page: https://killbill.io
License: Apache License 2.0
Kill Bill plugin for financial reporting
Home Page: https://killbill.io
License: Apache License 2.0
Need a way to easily find the latest for a given bundle_external_key (and thus filter out others).
Possible ways to address:
-add a boolean flag
-change the rank so that the latest one is a constant (like 1) and rank group by bundle_external_key
Currently, most next_* fields are set to NULL
. The next event (e.g. STOP_ENTITLEMENT_*
) will have all its prev_* fields set to NULL
.
This breaks sanity J6.
For example if an invoice has been WRITTEN_OFF
, it would be convenient to have this info directly into the analytics_invoice
table instead of having to join with the analytics_tags
table.
Got this exception while running our integration tests with analytics plugin:
[analytics_notifications-th] INFO org.slf4j.impl.OSGISlf4jLoggerAdapter - NotificationRunner analytics_notifications-th-58: dispatch error, will attempt a retry
org.killbill.notificationq.NotificationQueueException: java.lang.IllegalStateException: Object id=null type=ACCOUNT doesn't belong to tenant id=d2001ce9-3d13-4f55-91c7-5c353a171596
at org.killbill.notificationq.NotificationQueueDispatcher$NotificationRunner.handleNotificationWithMetrics(NotificationQueueDispatcher.java:354)
at org.killbill.notificationq.NotificationQueueDispatcher$NotificationRunner.run(NotificationQueueDispatcher.java:275)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)
Caused by: java.lang.IllegalStateException: Object id=null type=ACCOUNT doesn't belong to tenant id=d2001ce9-3d13-4f55-91c7-5c353a171596
at org.killbill.billing.util.callcontext.InternalCallContextFactory.getAccountRecordIdSafe(InternalCallContextFactory.java:265)
at org.killbill.billing.util.callcontext.InternalCallContextFactory.createInternalTenantContext(InternalCallContextFactory.java:111)
at org.killbill.billing.util.callcontext.InternalCallContextFactory.createInternalTenantContext(InternalCallContextFactory.java:87)
at org.killbill.billing.util.audit.api.DefaultAuditUserApi.getAccountAuditLogs(DefaultAuditUserApi.java:57)
at org.killbill.billing.util.glue.KillbillApiAopModule$ProfilingMethodInterceptor$1.execute(KillbillApiAopModule.java:52)
at org.killbill.commons.profiling.Profiling.executeWithProfiling(Profiling.java:33)
at org.killbill.billing.util.glue.KillbillApiAopModule$ProfilingMethodInterceptor.invoke(KillbillApiAopModule.java:49)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessFactoryBase.getAccountAuditLogs(BusinessFactoryBase.java:154)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessContextFactory.<init>(BusinessContextFactory.java:112)
at org.killbill.billing.plugin.analytics.AnalyticsListener.handleAnalyticsJob(AnalyticsListener.java:195)
at org.killbill.billing.plugin.analytics.AnalyticsListener.access$000(AnalyticsListener.java:64)
at org.killbill.billing.plugin.analytics.AnalyticsListener$1.handleReadyNotification(AnalyticsListener.java:125)
at org.killbill.notificationq.NotificationQueueDispatcher$NotificationRunner.handleNotificationWithMetrics(NotificationQueueDispatcher.java:352)
... 4 more
Please remove the internal record id that is being populated into analytics_payments second ref ID
While running a small integration test (create an account, a subscription and cancel) with the analytics plugin, i noticed a lot of traces for that account:
Refreshing Analytics data for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Refreshing Analytics data for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics tags for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics tags for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics custom fields for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics custom fields for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics account transitions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics tags for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics tags for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics custom fields for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics custom fields for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics account transitions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics account transitions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics account transitions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Refreshing Analytics data for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Refreshing Analytics data for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Refreshing Analytics data for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Refreshing Analytics data for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Starting rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics subscriptions for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Finished rebuild of Analytics invoices and payments for account 93354a36-7534-4c73-9d3d-8497d4cb5893
Seems like the plugin keeps making the same calls over and over for that account.
As data grows over time, refreshes take more and more time. Can we implement incremental refreshes?
analytics_payments.plugin_first_reference_id is null for most PayPal payments
Expected: analytics_payments.plugin_first_reference_id should be set to paypal_express_transactions.paypal_express_txn_id
In case audit logs are missing for an object, we should fallback to createdDate/createdBy/updatedDate/updatedBy from the raw table.
Enhancement: I think it would be appropriate for payments.state_name and payments.last_success_state_name to be added to analytics_payments_* tables. (note: I don't have a specific need for them at the moment)
The first issue seems to be that after we post a new report, nothing happens (this may be an issue with the curl, which i don't have but both Pierre and I ran it, and jobs were not scheduled.
The second issue is that after i updated the job, the notification was correctly created, but then code keeps inserting notifications:
CURL to submit the job:
curl -v \
-X PUT \
-u 'superadmin:$uper@dmin13' \
-H 'X-Killbill-ApiKey:demoKey' \
-H 'X-Killbill-ApiSecret:demo$3cr3t' \
-H 'Content-Type: application/json' \
-d '{"reportPrettyName": "MRR",
"refreshProcedureName": "refresh_mrr_daily",
"refreshFrequency": "DAILY"}' \
"http://127.0.0.1:8080/plugins/killbill-analytics/reports/mrr_daily"
> select count(*) from analytics_notifications_history where queue_name = 'AnalyticsService:reports-jobs'\G
count(*): 238
1 row in set (0.03 sec)
The code inserted it 238 times (and the next one is in a few minutes)... all for the same storeproc refresh_mrr_daily
.
We currently report on the first and second reference ids, which are exposed at the API level. While these are plugin specific, they are meant to represent the gateway and the acquirer transaction ids.
It would be nice to report on more plugin specific fields, such as Stripe's customer_id
, CyberSource's merchant_account
or simply the result of the CVV and AVS checks. To avoid having to expose all of these at the API level, we could instead add in the analytics_payment_*
tables a few generic fields (e.g. plugin_property_1
through plugin_property_5
). The Analytics plugin could have a per-plugin configuration option to populate these based on the plugin properties.
Note that we already do something similar for payment methods.
There a synchronized statement when fetching the catalog. Why is this needed?
From user report we see lots of threads waiting on this lock:
osgi-analytics-refresh-77" id=271 state=BLOCKED
- waiting to lock <0x758459f0> (a org.killbill.billing.plugin.analytics.dao.factory.BusinessContextFactory)
owned by analytics_notifications-th id=296
at org.killbill.billing.plugin.analytics.dao.factory.BusinessContextFactory.getCatalog(BusinessContextFactory.java:438)
...
Those that are null ==> *_UNSPECIFIED
Those that are unexpected ==> ERROR
&title1=Something%20useful
) ✅^dimension:product(USD|EUR=Euro%zone|BTC=Crypto%20zone)
) ✅Other
(^dimension:product(USD|EUR|BTC|-)
) ✅Just noticed a minor inconsistency with which columns are included in the analytics tables. The older analytics tables typically include the record_id field of the source table while the more recent tables include the id field of the source table.
analytics_bundles.created_date and other columns from audit_log are null
When running the test test_create_an_entitlement_with_addOn_products
, the following stack trace was seen:
2019-05-08T00:36:15,960+0000 lvl='ERROR', log='AnalyticsListener', th='analytics_notifications-th', xff='', rId='', tok='46818f96-7583-44f2-9a8b-3544559155b9', aRId='18467', tRId='1249', Unable to process event
org.killbill.billing.plugin.analytics.AnalyticsRefreshException: java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Object id=bedb905c-38e4-47b0-a989-c8e7f534c829 type=BUNDLE doesn't belong to tenant id=d614a2c7-a024-42b7-8f74-62f72acdd112
at org.killbill.billing.plugin.analytics.dao.factory.BusinessBundleFactory.createBusinessBundles(BusinessBundleFactory.java:115)
at org.killbill.billing.plugin.analytics.dao.BusinessSubscriptionTransitionDao.update(BusinessSubscriptionTransitionDao.java:70)
at org.killbill.billing.plugin.analytics.dao.AllBusinessObjectsDao.update(AllBusinessObjectsDao.java:57)
at org.killbill.billing.plugin.analytics.AnalyticsListener.handleAnalyticsJob(AnalyticsListener.java:292)
at org.killbill.billing.plugin.analytics.AnalyticsListener.access$200(AnalyticsListener.java:70)
at org.killbill.billing.plugin.analytics.AnalyticsListener$1.handleReadyNotification(AnalyticsListener.java:143)
at org.killbill.notificationq.NotificationQueueDispatcher.handleNotificationWithMetrics(NotificationQueueDispatcher.java:219)
at org.killbill.notificationq.dispatching.NotificationCallableCallback.dispatch(NotificationCallableCallback.java:51)
at org.killbill.notificationq.dispatching.NotificationCallableCallback.dispatch(NotificationCallableCallback.java:32)
at org.killbill.queue.dispatching.Dispatcher$CallableQueueHandler.call(Dispatcher.java:137)
at org.killbill.queue.dispatching.Dispatcher$CallableQueueHandler.call(Dispatcher.java:105)
at org.killbill.commons.concurrent.WrappedCallable.call(WrappedCallable.java:42)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.killbill.commons.concurrent.WrappedRunnable.run(WrappedRunnable.java:48)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Object id=bedb905c-38e4-47b0-a989-c8e7f534c829 type=BUNDLE doesn't belong to tenant id=d614a2c7-a024-42b7-8f74-62f72acdd112
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:192)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessBundleFactory.createBusinessBundles(BusinessBundleFactory.java:111)
... 16 common frames omitted
Trying to refresh account 8a2bdb21-ec88-4e15-ba60-d534dbbe6770 in Analytics and get the exception below. It appears that there are lots of accounts missing account info in analytics_accounts and I suspect that it's the same cause.
2015-07-01 21:18:50,599 [catalina-exec-1] INFO c.s.j.a.c.filter.LoggingFilter - 4157 * Server in-bound request
4157 > PUT http://killbill-uat1.snc1:8080/plugins/killbill-analytics/8a2bdb21-ec88-4e15-ba60-d534dbbe6770
4157 > authorization: Basic YWRtaW46cGFzc3dvcmQ=
4157 > user-agent: curl/7.30.0
4157 > host: killbill-uat1.snc1:8080
4157 > accept: /
2015-07-01 21:18:50,720 [Thread-6] INFO o.k.b.p.j.a.1.0.2.SNAPSHOT - Starting Analytics refresh for account 8a2bdb21-ec88-4e15-ba60-d534dbbe6770
2015-07-01 21:18:50,961 [catalina-exec-1] WARN o.k.b.j.m.RuntimeExceptionMapper - Exception : null
2015-07-01 21:18:50,961 [catalina-exec-1] WARN o.k.b.j.mappers.ExceptionMapperBase - Bad request
java.lang.NullPointerException: null
at org.killbill.billing.plugin.analytics.dao.model.BusinessPaymentBaseModelDao.(BusinessPaymentBaseModelDao.java:415)
at org.killbill.billing.plugin.analytics.dao.model.BusinessPaymentBaseModelDao.(BusinessPaymentBaseModelDao.java:366)
at org.killbill.billing.plugin.analytics.dao.model.BusinessPaymentVoidModelDao.(BusinessPaymentVoidModelDao.java:47)
at org.killbill.billing.plugin.analytics.dao.model.BusinessPaymentBaseModelDao.create(BusinessPaymentBaseModelDao.java:203)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessPaymentFactory.createBusinessPayments(BusinessPaymentFactory.java:73)
at org.killbill.billing.plugin.analytics.dao.BusinessInvoiceAndPaymentDao.createBusinessPojos(BusinessInvoiceAndPaymentDao.java:108)
at org.killbill.billing.plugin.analytics.dao.BusinessInvoiceAndPaymentDao.update(BusinessInvoiceAndPaymentDao.java:85)
at org.killbill.billing.plugin.analytics.dao.AllBusinessObjectsDao.update(AllBusinessObjectsDao.java:59)
at org.killbill.billing.plugin.analytics.api.user.AnalyticsUserApi.rebuildAnalyticsForAccount(AnalyticsUserApi.java:108)
at org.killbill.billing.plugin.analytics.http.AnalyticsServlet.doPut(AnalyticsServlet.java:66)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.killbill.billing.plugin.analytics.http.ServletRouter.forward(ServletRouter.java:118)
at org.killbill.billing.plugin.analytics.http.ServletRouter.doPut(ServletRouter.java:74)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at sun.reflect.GeneratedMethodAccessor1448.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.killbill.billing.osgi.ContextClassLoaderHelper$1$1.execute(ContextClassLoaderHelper.java:83)
at org.killbill.commons.profiling.Profiling.executeWithProfiling(Profiling.java:33)
at org.killbill.billing.osgi.ContextClassLoaderHelper$1.invoke(ContextClassLoaderHelper.java:80)
at com.sun.proxy.$Proxy133.service(Unknown Source)
at org.killbill.billing.osgi.http.OSGIServlet.serviceViaPlugin(OSGIServlet.java:82)
at org.killbill.billing.osgi.http.OSGIServlet.doPut(OSGIServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.killbill.billing.jaxrs.resources.PluginResource.serviceViaOSGIPlugin(PluginResource.java:179)
at org.killbill.billing.jaxrs.resources.PluginResource.serviceViaOSGIPlugin(PluginResource.java:165)
at org.killbill.billing.jaxrs.resources.PluginResource.doPUT(PluginResource.java:148)
at com.palominolabs.metrics.guice.TimedInterceptor.invoke(TimedInterceptor.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
at com.groupon.killbill.gkb.filters.GKBTenantFilter.doFilter(GKBTenantFilter.java:64)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.codahale.metrics.servlet.AbstractInstrumentedFilter.doFilter(AbstractInstrumentedFilter.java:104)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:74)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:516)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1015)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:652)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1575)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1533)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
2015-07-01 21:18:50,962 [catalina-exec-1] INFO c.s.j.a.c.filter.LoggingFilter - 4157 * Server out-bound response
4157 < 400
4157 < Content-Type: application/json
4157 <
An example is the length of the bundle_external_key
in the analytics_subscription_transitions
table:
The issue is that we end up with data corruption in the analytics table:
java.sql.SQLDataException: Data too long for column 'bundle_external_key' at row 1 [statement:"create", located:"insert into analytics_subscription_transitions ...
Add an option to filter out specific Group(s) the user isn't interested in.
For instance, if the user doesn't care about custom fields in Analytics, ignoring Group.FIELDS
would make the AnalyticsListener
ignore all custom field events, which would speed up events processing.
For:
select *
from analytics_subscription_transitions
where bundle_external_key =2019040
order by subscription_id, record_id
subscription_id 543a0f95-2089-4a11-a1aa-9745d6583749 should not be associated with product Remove Links
See:
select * from subscription_events where subscription_id = '543a0f95-2089-4a11-a1aa-9745d6583749'
Depending on the event type, the record_id points either to blocking_states or subscription_events.
Trying to run an analytics report (the new_accounts_per_day example in the user guide) but I get an exception. It appears that minDate or maxDate is null. When I select * from the underlying view there are no null values.
2015-06-24 20:10:34,951 [catalina-exec-2] WARN o.k.b.j.mappers.ExceptionMapperBase - Internal error
java.lang.IllegalStateException: null
at org.killbill.billing.plugin.analytics.reports.ReportsUserApi.normalizeAndSortXValues(ReportsUserApi.java:316)
at org.killbill.billing.plugin.analytics.reports.ReportsUserApi.getDataForReport(ReportsUserApi.java:259)
at org.killbill.billing.plugin.analytics.http.ReportsServlet.doHandleReports(ReportsServlet.java:170)
at org.killbill.billing.plugin.analytics.http.ReportsServlet.doGet(ReportsServlet.java:92)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.killbill.billing.plugin.analytics.http.ServletRouter.forward(ServletRouter.java:101)
at org.killbill.billing.plugin.analytics.http.ServletRouter.doGet(ServletRouter.java:62)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at sun.reflect.GeneratedMethodAccessor1448.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.killbill.billing.osgi.ContextClassLoaderHelper$1$1.execute(ContextClassLoaderHelper.java:83)
at org.killbill.commons.profiling.Profiling.executeWithProfiling(Profiling.java:33)
at org.killbill.billing.osgi.ContextClassLoaderHelper$1.invoke(ContextClassLoaderHelper.java:80)
at com.sun.proxy.$Proxy133.service(Unknown Source)
at org.killbill.billing.osgi.http.OSGIServlet.serviceViaPlugin(OSGIServlet.java:82)
at org.killbill.billing.osgi.http.OSGIServlet.doGet(OSGIServlet.java:45)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at org.killbill.billing.jaxrs.resources.PluginResource.serviceViaOSGIPlugin(PluginResource.java:179)
at org.killbill.billing.jaxrs.resources.PluginResource.serviceViaOSGIPlugin(PluginResource.java:165)
at org.killbill.billing.jaxrs.resources.PluginResource.doGET(PluginResource.java:110)
at com.palominolabs.metrics.guice.TimedInterceptor.invoke(TimedInterceptor.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
at com.groupon.killbill.gkb.filters.GKBTenantFilter.doFilter(GKBTenantFilter.java:64)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.codahale.metrics.servlet.AbstractInstrumentedFilter.doFilter(AbstractInstrumentedFilter.java:104)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:74)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:516)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1015)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:652)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1575)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1533)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
analytics_payments.plugin_first_reference_id should be set to litle_responses.params_litleonelineresponse_saleresponse_id
analytics_payments.plugin_second_reference_id can be set to the litle ref ID
(if it's a lot easier to keep litle ref ID in first_reference_id it's okay to keep it there and put litle_responses.params_litleonelineresponse_saleresponse_id in second reference id but it would be better the way I propose)
Some analytics have multiple rows for the same source row when there should be only one row in the analytics table. Refreshing the associated account in analytics corrects this problem for the affected accounts.
Tablename Repeated row count
analytics_payment_auths 10
analytics_payment_captures 3
analytics_payment_refunds 3
analytics_accounts 0
analytics_bundles 0
analytics_invoice_adjustments 0
analytics_invoice_credits 0
analytics_invoice_item_adjustments 0
analytics_invoice_items 0
analytics_invoices 0
analytics_payment_credits 0
analytics_payment_chargebacks 0
analytics_payment_purchases 0
analytics_payment_voids 0
select 'analytics_accounts' as table_name, ifnull(count(1),0) from (
select account_record_id from analytics_accounts a group by 1 having count(1)>1 ) b
UNION
select 'analytics_bundles' as table_name, count(1) from (
select bundle_record_id from analytics_bundles a group by 1 having count(1)>1 ) b
UNION
select 'analytics_invoice_adjustments' as table_name, ifnull(count(1),0) from (
select invoice_item_record_id from analytics_invoice_adjustments a group by 1 having count(1)>1 ) b
UNION
select 'analytics_invoice_credits' as table_name, ifnull(count(1),0) from (
select invoice_item_record_id from analytics_invoice_credits a group by 1 having count(1)>1 ) b
UNION
select 'analytics_invoice_item_adjustments' as table_name, ifnull(count(1),0) from (
select invoice_item_record_id from analytics_invoice_item_adjustments a group by 1 having count(1)>1 ) b
UNION
select 'analytics_invoice_items' as table_name, ifnull(count(1),0) from (
select invoice_item_record_id from analytics_invoice_items a group by 1 having count(1)>1 ) b
UNION
select 'analytics_invoices' as table_name, ifnull(count(1),0) from (
select invoice_record_id from analytics_invoices a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_auths' as table_name, ifnull(count(1),0) from (
select payment_transaction_id from analytics_payment_auths a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_captures' as table_name, count(1) from (
select payment_transaction_id from analytics_payment_captures a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_credits' as table_name, count(1) from (
select payment_transaction_id from analytics_payment_credits a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_chargebacks' as table_name, count(1) from (
select payment_transaction_id from analytics_payment_chargebacks a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_purchases' as table_name, count(1) from (
select payment_transaction_id from analytics_payment_purchases a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_refunds' as table_name, count(1) from (
select payment_transaction_id from analytics_payment_refunds a group by 1 having count(1)>1 ) b
UNION
select 'analytics_payment_voids' as table_name, count(1) from (
select payment_transaction_id from analytics_payment_voids a group by 1 having count(1)>1 ) b
Maybe @kevinpostlewaite has an idea to force MySQL to use the indexes on account_record_id
and tenant_record_id, account_record_id
?
system setup:
issue:
according to the docs to create a report you have to:
curl -v \
-X POST \
-u admin:password \
-H "X-Killbill-ApiKey:bob" \
-H "X-Killbill-ApiSecret:lazar" \
-H 'Content-Type: application/json' \
-d '{"reportName": "new_accounts_per_day",
"reportPrettyName": "New accounts created (per day)",
"sourceTableName": "v_new_accounts_per_day"}' \
http://127.0.0.1:8080/plugins/killbill-analytics/reports
wich leads to the following error at my setup:
org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLIntegrityConstraintViolationException: Column 'report_type' cannot be null
after some investigation i found out that the default report_type is "TIMELINE"
wich should be choosen, if i don't set the parameter. But it isnt.
If I do the same api-call with the additional parameter "reportType": "TIMELINE"
it works.
whole working call:
curl -v \
-X POST \
-u admin:password \
-H "X-Killbill-ApiKey:bob" \
-H "X-Killbill-ApiSecret:lazar" \
-H 'Content-Type: application/json' \
-d '{"reportName": "new_accounts_per_day",
"reportPrettyName": "New accounts created (per day)",
"sourceTableName": "v_new_accounts_per_day",
"reportType": "TIMELINE"}' \
http://127.0.0.1:8080/plugins/killbill-analytics/reports
I guess this should be fixed somewhere in the code.
prev product should be null for START events but it's not always:
select * from analytics_subscription_transitions where event like 'START%' and prev_product_name is not null
next product should be null for STOP events but it's not always:
select * from analytics_subscription_transitions where event like 'STOP%' and prev_product_name is null
It seems like the field current_end_date
is never populated.
Currently, when SUM_MONTHLY is used the metric is summed and then graphed on the first day of a month. It would be better if while using SUM_MONTHLY there were no days and all data were simply graphed for the month.
The issue was reported on the mailing list, and i could easily reproduce it:
org.apache.felix.log.LogException: org.killbill.billing.catalog.api.CatalogApiException: Could not find a plan matching spec: (plan: 'MALTESTPRODUCT_MONTHLY', product: 'undefined', billing period: 'undefined', pricelist 'undefined')
at org.killbill.billing.catalog.VersionedCatalog.findCatalogPlanEntry(VersionedCatalog.java:186)
at org.killbill.billing.catalog.VersionedCatalog.findPlan(VersionedCatalog.java:301)
at org.killbill.billing.catalog.VersionedCatalog.findPhase(VersionedCatalog.java:332)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessFactoryBase.getPlanPhaseFromInvoiceItem(BusinessFactoryBase.java:381)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessContextFactory.getPlanPhaseFromInvoiceItem(BusinessContextFactory.java:401)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessInvoiceFactory.createBusinessInvoiceItem(BusinessInvoiceFactory.java:244)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessInvoiceFactory.createBusinessInvoiceItem(BusinessInvoiceFactory.java:190)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessInvoiceFactory.access$000(BusinessInvoiceFactory.java:65)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessInvoiceFactory$1.call(BusinessInvoiceFactory.java:117)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessInvoiceFactory$1.call(BusinessInvoiceFactory.java:114)
The following queries return a few thousand rows after a full refresh:
The README points to Maven yet there is no release on Maven.
Since blocking_states may be source of data for analytics_subscription_transitions it would be good to add a column to store the source record_id
select * from analytics_subscription_transitions where event = 'PAUSE_ENTITLEMENT_MISC'
and prev_service=next_service
Seen in the field (unsure how to reproduce it):
2017-02-23T10:47:00,184+0000 lvl='ERROR', log='KillbillLogWriter', th='Thread-26', xff='', rId='', aRId='14616', tRId='2125', Fatal NotificationQ dispatch error, data corruption...
org.apache.felix.log.LogException: org.killbill.notificationq.NotificationQueueException: java.lang.NullPointerException
at org.killbill.notificationq.NotificationQueueDispatcher.handleNotificationWithMetrics(NotificationQueueDispatcher.java:205)
at org.killbill.notificationq.dispatching.NotificationCallableCallback.dispatch(NotificationCallableCallback.java:51)
at org.killbill.notificationq.dispatching.NotificationCallableCallback.dispatch(NotificationCallableCallback.java:32)
at org.killbill.queue.dispatching.Dispatcher$CallableQueue.call(Dispatcher.java:103)
at org.killbill.queue.dispatching.Dispatcher$CallableQueue.call(Dispatcher.java:86)
at org.killbill.commons.concurrent.WrappedCallable.call(WrappedCallable.java:42)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.killbill.commons.concurrent.WrappedRunnable.run(WrappedRunnable.java:40)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException: null
at org.killbill.billing.plugin.analytics.dao.model.BusinessTagModelDao.<init>(BusinessTagModelDao.java:132)
at org.killbill.billing.plugin.analytics.dao.model.BusinessAccountTagModelDao.<init>(BusinessAccountTagModelDao.java:39)
at org.killbill.billing.plugin.analytics.dao.model.BusinessTagModelDao.create(BusinessTagModelDao.java:54)
at org.killbill.billing.plugin.analytics.dao.factory.BusinessTagFactory.createBusinessTags(BusinessTagFactory.java:67)
at org.killbill.billing.plugin.analytics.dao.BusinessTagDao.update(BusinessTagDao.java:45)
at org.killbill.billing.plugin.analytics.dao.AllBusinessObjectsDao.update(AllBusinessObjectsDao.java:65)
at org.killbill.billing.plugin.analytics.AnalyticsListener.handleAnalyticsJob(AnalyticsListener.java:252)
at org.killbill.billing.plugin.analytics.AnalyticsListener.access$100(AnalyticsListener.java:66)
at org.killbill.billing.plugin.analytics.AnalyticsListener$1.handleReadyNotification(AnalyticsListener.java:137)
at org.killbill.notificationq.NotificationQueueDispatcher.handleNotificationWithMetrics(NotificationQueueDispatcher.java:203)
... 10 common frames omitted
Add to analytics_payment_*:
payment_methods.id
payment_methods.external_key
We find that payment_methods.external_key is needed for reporting on payments.
We need to be able to access original bundle created date in analytics. Currently the data are available in analyticts_ning_bundle.
Possible ways to address this:
-Update the created date of bundles (I don't like this)
-populate analytics_bundles with the true created date (this is hard to do in a way that will preserve the date over multiple refreshes in a way that is amenable to open-source)
-populate a new column in analytics_bundles (same problems as #2)
The columns:
amount, converted_amount,currency,created_date
appear to be always NULL for the following tables:
analytics_payment_auths
analytics_payment_captures
analytics_payment_chargebacks
analytics_payment_credits
analytics_payment_purchases
analytics_payment_refunds
analytics_payment_voids
(source tables clearly have non-NULL values for amount, converted_amount and currency. I'm not certain of source of created_date [or if it's created_date for analytics row] but presumably it should be non-NULL as well).
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.