Urgent - MySQL crashing since updated to 5.5.5

[khopper]

Here is an example of the query that the CRM is shooting over to mysql with and without the teams field in the report filter.

[Maximus]

Hello khopper,
could you give more information to help us reproduce the error? What is your php version? Also, very helpful would be to take a look on your log files (Espo, web server, php), check one more time if there any activated BPM or Workflows that have an influence on this report. Also, could you archive and give us your Custom folder.

[khopper]

Maximus
Here is everything you asked for in link.
I have confirmed its not caused by BPM or Workflows and modified the reports by removing field "teams" from the filter and it has not occurred since.

https://file.io/oPUo4E

[Maximus]

Error 404.

[khopper]

Try this instead, the other link must have timed out!

https://1drv.ms/f/s!AspO7u2OV-uzp3g7c8INIjSriB4I

[Maximus]

When you have launched a report it caused “2006 MySQL server has gone away” Error. This error may appear when max_allowed_packet is too small. According to your version of MySQL, it can be 1MB by default. Try to increase this value to 32MB. The max size can be 1GB so fill free to keep increasing it if 32MB will not work.
This will help you to do this: https://dev.mysql.com/doc/refman/5.6...too-large.html

Note: you should restart MySQL service after upgrade max_allowed_packet value

[khopper]

Error “2006 MySQL server has gone away” was caused directly be me killing the query in MySQL console and is how I found the issue relating to the "teams" filed.

[Maximus]

Hi, khooper
please make the screenshots of all parameters that you have used when tried to create the report. It will give more clarity on some error process that could appear on sub level. Also, enter to your database via phpMyAdmin and take a look on all fields. Are there any inactive fields with disabled options like browse, structure, search, etc. Also, very helpful would be if you will give the access for our team to the portal with access to try to create the report, etc.

[khopper]

Maximus

Here is the screen shot of the report that used to work perfectly. I recently upgrade to 5.5.6 and still have the same issue.
I will have to create a portal/user and get back on that part,

[khopper]

Maximus

I was following the documentation at https://www.espocrm.com/documentatio...ration/portal/ however it seems outdated.

I created the portal, a user and a role however nothing is showing up! (tabs)

What am I missing?

[Maximus]

Your portal settings are ok. You just need to add tabs into the tab list when you are customizing your portal. Also, please add access to the "Report" entity and give the User Name of the test user account. It would be great if you tell us when we can run the report via the portal (after work time) to prevent 100% core usage at work time.

[khopper]

The Tabs are already listed in the first screen shot but nothing shows. (Portal02.jpg)
I even added a dashboard for 1 of the trouble reports and it shows access denied. The report should be wide open and readable to everyone in the system.

[2019-02-06 16:51:22] Espo.ERROR: API [GET]:/:portalId/:controller/action/:action, Params:Array (     [portalId] => 5c59bf7a68f317e1a     [controller] => Report     [action] => run ) , InputData:  -  [] []
[2019-02-06 16:51:22] Espo.ERROR: Display Error: , Code: 403 URL: /espocrm/api/v1/portal-access/5c59bf7a68f317e1a/Report/action/run?id=5b1089915cd005644&where= [] []

[khopper]

I don't know what happened but it just randomly just now started working. I will PM you the information.

[Maximus]

Please change your report logic without "NOT" block like on the attachments, add a team to the filter and run it.

[khopper]

I removed as suggested and report timed out. I will PM screen shot and GIF to show.

[yuri]

Hi Kim,

I tried to run a sum report with a filter by teams. 100,000 records in database, each record has from 1 to 4 assigned teams. Report executed in 3 seconds. Windows environment (which is much slower than linux).

SUM function with a filter through many-to-many relationship (which Teams is) generates SQL with a sub-query. That sub-query seems to cause a crash of your MySQL. It's supposed that MySQL optimizes the query w/o creating temporary table but on your environment it seems it's not the case.

[khopper]

I only have about 16,000 records. each record has from 1 to 3 max assigned teams.
Running a Linux environment here.

[yuri]

Hi Kim,

MySQL 5.7 has a better sub-query optimizer.There's a big chance that the report will execute very quickly there.

[khopper]

Last time I tried upgrading to MySQL 5.7 it crashed the entire espo system, I had to restore from a backup..... I will try again on the upcoming weekend.