Periodic failure of KReporter displaying reports

#1

I have a recurring issue with KReporter which I can only solve by conducting a repair/rebuild of the database.

Every few days when you run a KReport the screen does not load the report data. I cannot work out what might be causing this and I wondered if anyone else had reported the same issue. Please see the screenshot.

Any assistance / direction is greatly appreciated.

Thank you.

#2

Jonnie,
Suite CRM 7.7.9?
Can you extract the report entry in kreports and make it an INSERT sql so that I can have a look at your report?
Val

#3

Hi Val,

Thank you for your reply. I’m sorry I don’t quite understand what you would like me to do. Please can you provide further instruction?

Regards,

Jonnie

#4

I would like to see the settings of your report. They are stored in kreports table in the row with the corresponding report ID. If you have a tool like phpmyadmin, myself workbench … you could extract data row and post it.
Val

#6

– phpMyAdmin SQL Dump
– version 4.8.3
https://www.phpmyadmin.net/

– Host: localhost:3306
– Generation Time: Nov 20, 2018 at 05:19 PM
– Server version: 5.6.41
– PHP Version: 7.2.7

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = “+00:00”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/
!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/
!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/
!40101 SET NAMES utf8mb4 */;


– Database: DATABASENAME



– Table structure for table kreports

CREATE TABLE kreports (
id char(36) NOT NULL,
name varchar(255) DEFAULT NULL,
date_entered datetime DEFAULT NULL,
date_modified datetime DEFAULT NULL,
modified_user_id char(36) DEFAULT NULL,
created_by char(36) DEFAULT NULL,
description text,
deleted tinyint(1) DEFAULT ‘0’,
assigned_user_id char(36) DEFAULT NULL,
report_module varchar(45) DEFAULT NULL,
report_status varchar(1) DEFAULT NULL,
union_modules text,
reportoptions text,
listtype varchar(10) DEFAULT NULL,
listtypeproperties text,
selectionlimit varchar(25) DEFAULT NULL,
presentation_params text,
visualization_params text,
integration_params text,
wheregroups text,
whereconditions text,
listfields text,
unionlistfields text,
advancedoptions text,
category_id char(36) DEFAULT NULL,
category_priority int(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


– Dumping data for table kreports

INSERT INTO kreports (id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, report_module, report_status, union_modules, reportoptions, listtype, listtypeproperties, selectionlimit, presentation_params, visualization_params, integration_params, wheregroups, whereconditions, listfields, unionlistfields, advancedoptions, category_id, category_priority) VALUES
(‘861d99a8-3dd9-40fc-8cee-1945553e9722’, ‘Financial | Invoice Detail by Month’, ‘2018-11-17 13:55:29’, ‘2018-11-17 14:25:32’, ‘1’, ‘1’, NULL, 0, ‘1’, ‘AOS_Invoices’, NULL, NULL, NULL, ‘grouped’, NULL, NULL, ‘{“plugin”:“grouped”,“pluginData”:{“groupedViewProperties”:{“groupById”:“kee70b5579032a0a7e72a472348c8”,“startcollapsed”:false}}}’, NULL, ‘{“activePlugins”:{“kcsvexport”:0,“kpdfexport”:0,“kexcelexport”:1},“kpdfexport”:{“pdf_layout”:“default”,“pdf_format”:“A4”,“pdf_orientation”:“L”,“pdf_palignment”:“L”,“pdf_exportwhere”:false,“pdf_multicell”:false,“pdf_newpagepergroup”:false,“pdf_headerperpage”:true,“pdf_chartpage”:false}}’, ‘[{“unionid”:“root”,“id”:“root”,“groupid”:“root”,“type”:“AND”,“parent”:"-",“notexists”:""}]’, ‘[{“unionid”:“root”,“fieldid”:“k5f251fa8234cdede7ded6402aafa”,“path”:“root:AOS_Invoices::field:invoice_date”,“name”:“Invoice Date”,“type”:“date”,“displaypath”:“AOS_Invoices”,“groupid”:“root”,“referencefieldid”:"",“operator”:“ignore”,“jointype”:“required”,“usereditable”:“yes”,“dashleteditable”:“no”,“exportpdf”:“no”,“value”:"",“valueto”:"",“valuekey”:"",“valuetokey”:"",“id”:“SpiceCRM.KReporter.Designer.model.whereclause-1”}]’, ‘[{“fieldid”:“kee70b5579032a0a7e72a472348c8”,“path”:“root:AOS_Invoices::relate:AOS_Invoices:billing_account::field:name”,“displaypath”:“AOS_Invoices->Account”,“fieldname”:“name”,“name”:“Account:”,“display”:“yes”,“sequence”:“01”,“width”:190,“sort”:"-",“sortpriority”:"",“jointype”:“required”,“sqlfunction”:"-",“summaryfunction”:"",“groupby”:“no”,“link”:“no”,“fixedvalue”:"",“id”:“SpiceCRM.KReporter.Designer.model.listFields-1”},{“fieldid”:“ked7a89ad9661a960abb54aad7ba2”,“path”:“root:AOS_Invoices::field:invoice_date”,“displaypath”:“AOS_Invoices”,“fieldname”:“invoice_date”,“name”:“Invoice Date:”,“display”:“yes”,“sequence”:“02”,“width”:108,“sort”:"-",“sortpriority”:"",“jointype”:“required”,“sqlfunction”:"-",“summaryfunction”:"",“groupby”:“no”,“link”:“no”,“fixedvalue”:"",“id”:“SpiceCRM.KReporter.Designer.model.listFields-2”},{“fieldid”:“k63a5666e451936ef4f84552ef689”,“path”:“root:AOS_Invoices::link:AOS_Invoices:accounts::link:Accounts:opportunities::field:name”,“displaypath”:“AOS_Invoices->Accounts->Opportunity”,“fieldname”:“name”,“name”:“Opportunity:”,“display”:“yes”,“sequence”:“03”,“width”:311,“sort”:"-",“sortpriority”:"",“jointype”:“required”,“sqlfunction”:"-",“summaryfunction”:"",“groupby”:“no”,“link”:“no”,“fixedvalue”:"",“id”:“SpiceCRM.KReporter.Designer.model.listFields-3”},{“fieldid”:“k7b5eba99e0cccb432b1cbfbf5739”,“path”:“root:AOS_Invoices::link:AOS_Invoices:rls_payments_aos_invoices_1::link:RLS_Payments:rls_payments_aos_contracts::field:name”,“displaypath”:“AOS_Invoices->Payment->Contracts”,“fieldname”:“name”,“name”:“Contract Title:”,“display”:“yes”,“sequence”:“04”,“width”:382,“sort”:"-",“sortpriority”:"",“jointype”:“required”,“sqlfunction”:"-",“summaryfunction”:"",“groupby”:“no”,“link”:“no”,“fixedvalue”:"",“id”:“SpiceCRM.KReporter.Designer.model.listFields-4”},{“fieldid”:“k17cffc4a85a424bd8a05b17786bd”,“path”:“root:AOS_Invoices::link:AOS_Invoices:rls_payments_aos_invoices_1::field:name”,“displaypath”:“AOS_Invoices->Payment”,“fieldname”:“name”,“name”:“Payment:”,“display”:“yes”,“sequence”:“05”,“width”:300,“sort”:"-",“sortpriority”:"",“jointype”:“required”,“sqlfunction”:"-",“summaryfunction”:"",“groupby”:“no”,“link”:“no”,“fixedvalue”:"",“id”:“SpiceCRM.KReporter.Designer.model.listFields-5”},{“fieldid”:“ka7b02b43ffb486207ceed9478bd6”,“path”:“root:AOS_Invoices::field:total_amount”,“displaypath”:“AOS_Invoices”,“fieldname”:“total_amount”,“name”:“Grand Total:”,“display”:“yes”,“sequence”:“06”,“width”:130,“sort”:"-",“sortpriority”:"",“jointype”:“required”,“sqlfunction”:"-",“summaryfunction”:“sum”,“groupby”:“no”,“link”:“no”,“fixedvalue”:"",“id”:“SpiceCRM.KReporter.Designer.model.listFields-6”}]’, ‘[]’, NULL, ‘kfa72ad327ee0b4a2a8468db9eb15’, NULL);


– Indexes for dumped tables


– Indexes for table kreports

ALTER TABLE kreports
ADD PRIMARY KEY (id),
ADD KEY idx_reminder_name (name),
ADD KEY idx_cat (category_id);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/
!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/
!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

#7

Hi Val,

Is this what you needed?

Regards,

Jonnie

#8

By the way we are running SuiteCRM v. 7.8.8

Many thanks,

Jonnie

#9

Thanks for data, Jonnie. Just what I needed.
Val

#10

Your report settings look good.
Next time yiu have the error please check php error und sugarcrn.log.

#11

Hi Val,

This error is happening several times a day but it is impossible to determine a time. I cannot see anything in the sugarcrm.log, the suitecrm.log or the error log. Rebuilding the database (quick repair) solves the issue but only temporarily.

It is a very strange error. What can you suggest?

Many thanks,

Jonnie

#12

Very strange indeed. Reloading the report is not enough?
If onky repair/rebuild helps it might be an issue with cached variables. Would you check next time it happens:

  • Javascript console error messages and post them
  • then delete browser cache (no repair/rebuild) ans see if you can load report.
    Val
#13

Hi Val,

Here are the entries from the console errors.

Deleting the browser cache has no effect unfortunately

#14

oh, a 503 error. Service unavailable.
I am wondering how a repair/rebuild may correct this.
What’s the max_connections allowed in your database?

#15

Hi Val,

Currently max_connections appears to be set at 151.

Any ideas?

Many thanks,

Jonnie

#16

Jonnie,
You might try checking max_user_connections.
503 points to an overloading problem like server is too busy. Might be database or webserver. Do you have any special server architecture?

Val

#17

Hi Val,

Unfortunately I still have not got to the bottom of this issue. It appears to be happening more frequently now. We have checked the max connections and increased the limit, (which it is not reaching), so we do not think this is the problem.

In the console it makes reference to 'Failed to load resource.The server responded with a status of 503. Start @ ext-all.js:22

I would be grateful if you could help with this.

Many thanks,

Jonnie

#18

Jonnie,

that’s a difficult one.
Can you tell if happens on specific complex reports? In that case I suggest to have a look at the query using “query analyzer”, check indices on queried tables and add missing indices.

Val

#19

Hi val,

No, it can be working fine and then you come back some time later, (could be a few hours but not a specific amount) and it does not work. I do not believe it is report specific.

I’ve no idea what to do with this.

Many thanks

Jonnie

#20

Can you log slow queries?
Might give us a hint.