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.
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
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
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
– 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 */;
DATABASENAME
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;
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:
AOS_Invoices:accounts: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:AOS_Invoices:rls_payments_aos_invoices_1: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: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);
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 */;
Hi Val,
Is this what you needed?
Regards,
Jonnie
By the way we are running SuiteCRM v. 7.8.8
Many thanks,
Jonnie
Thanks for data, Jonnie. Just what I needed.
Val
Your report settings look good.
Next time yiu have the error please check php error und sugarcrn.log.
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
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:
Hi Val,
Here are the entries from the console errors.
Deleting the browser cache has no effect unfortunately
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?
Hi Val,
Currently max_connections appears to be set at 151.
Any ideas?
Many thanks,
Jonnie
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
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
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
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
Can you log slow queries?
Might give us a hint.
Hi Val,
I’m afraid I have never got to the bottom of this issue whereby Kreports fails to load the results unless you repair the database or repair roles.
This did not happen with the previous versions of Kreport. We have confirmed that the number of connections is sufficient. I have attached a screen shot of the error messages within developer tools. I hope this may assist you.
Any ideas what could be causing this?
Many thanks,
