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