Report doesn't work after upgrade

[Luki]

Hi,

After upgrading to version 5.9.0 or 5.9.1, the report view does not work.
Error message 500

In logs:
[2020-05-20 07:37:15] Espo.ERROR: (HY000) SQLSTATE[HY000]: General error: 2006 MySQL server has gone away; GET /api/v1/Report/act
ion/run?id=5aad8eb626d107a88&where%5B0%5D%5Btype%5D=cu rrentYear&where%5B0%5D%5Battribute%5D=invoiceDate; line: 1830, file: /var/w
ww/html/espocrm/application/Espo/Modules/Advanced/Services/Report.php [] []

[Maximus]

Hi Luki,
please provide a screenshot with your report parameters. I will try to reproduce this. Try to select edit from the Report list view (right side menu).

[Luki]

Thank you for your answer.

Screen with parameters in the attachment.

This situation applies to all reports that I have.
I checked the mariadb logs and I have something like that. This happens when I open the report:

May 20 11:45:45 [localhost] mysqld: 2020-05-20 11:45:45 0x7fc2282af700 InnoDB: Assertion failure in file /home/buildbot/buildbot
/padding_for_CPACK_RPM_BUILD_SOURCE_DIRS_PREFIX/mariadb-10.4.11/storage/innobase/row/row0sel.cc line 2551
May 20 11:45:45 [localhost] mysqld: InnoDB: Failing assertion: field->col->mtype == type
May 20 11:45:45 [localhost] mysqld: InnoDB: We intentionally generate a memory trap.
May 20 11:45:45 [localhost] mysqld: InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
May 20 11:45:45 [localhost] mysqld: InnoDB: If you get repeated assertion failures or crashes, even
May 20 11:45:45 [localhost] mysqld: InnoDB: immediately after the mysqld startup, there may be
May 20 11:45:45 [localhost] mysqld: InnoDB: corruption in the InnoDB tablespace. Please refer to
May 20 11:45:45 [localhost] mysqld: InnoDB: https://mariadb.com/kb/en/library/in...ecovery-modes/
May 20 11:45:45 [localhost] mysqld: InnoDB: about forcing recovery.
May 20 11:45:45 [localhost] mysqld: 200520 11:45:45 [ERROR] mysqld got signal 6 ;
May 20 11:45:45 [localhost] mysqld: This could be because you hit a bug. It is also possible that this binary
May 20 11:45:45 [localhost] mysqld: or one of the libraries it was linked against is corrupt, improperly built,
May 20 11:45:45 [localhost] mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
May 20 11:45:45 [localhost] mysqld: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
May 20 11:45:45 [localhost] mysqld: We will try our best to scrape up some info that will hopefully help
May 20 11:45:45 [localhost] mysqld: diagnose the problem, but since we have already crashed,
May 20 11:45:45 [localhost] mysqld: something is definitely wrong and this may fail.
May 20 11:45:45 [localhost] mysqld: Server version: 10.4.11-MariaDB
May 20 11:45:45 [localhost] mysqld: key_buffer_size=134217728
May 20 11:45:45 [localhost] mysqld: read_buffer_size=131072
May 20 11:45:45 [localhost] mysqld: max_used_connections=4
May 20 11:45:45 [localhost] mysqld: max_threads=153
May 20 11:45:45 [localhost] mysqld: thread_count=10
May 20 11:45:45 [localhost] mysqld: It is possible that mysqld could use up to
May 20 11:45:45 [localhost] mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467743 K bytes of memo
ry
May 20 11:45:45 [localhost] mysqld: Hope that's ok; if not, decrease some variables in the equation.
May 20 11:45:45 [localhost] mysqld: Thread pointer: 0x7fc1d0007b28
May 20 11:45:45 [localhost] mysqld: Attempting backtrace. You can use the following information to find out
May 20 11:45:45 [localhost] mysqld: where mysqld died. If you see no messages after this, something went
May 20 11:45:45 [localhost] mysqld: terribly wrong...
May 20 11:45:45 [localhost] mysqld: stack_bottom = 0x7fc2282aecf0 thread_stack 0x49000
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x555c0cda2ebe]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x555c0c83b4cf]
May 20 11:45:45 [localhost] mysqld: sigaction.c:0(__restore_rt)[0x7fc2300b3630]
May 20 11:45:45 [localhost] mysqld: :0(__GI_raise)[0x7fc22e384387]
May 20 11:45:45 [localhost] mysqld: :0(__GI_abort)[0x7fc22e385a78]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(+0x5a2820)[0x555c0c537820]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(+0xb31ca1)[0x555c0cac6ca1]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(+0xa475c7)[0x555c0c9dc5c7]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_ZN7handler27multi_range_read_info_constEjP 15st_range_seq_ifPvjPjS3_P13Cost_
estimate+0x25b)[0x555c0c74599b]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_ZN10DsMrr_impl16dsmrr_info_constEjP15st_ra nge_seq_ifPvjPjS3_P13Cost_estimat
e+0x49)[0x555c0c748159]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(+0x9b904e)[0x555c0c94e04e]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(+0x9bfd03)[0x555c0c954d03]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6Bi tmapILj64EEyybbbb+0xc4f)[0x555c0c
95e6af]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x28e0)[0x555c0c689fa0]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x9d)[0x555c0c68aafd]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4 ItemEPS4_jP8st_orderS9_S7_S9_yP13
select_resultP18st_select_lex_unitP13st_select_lex +0xe1)[0x555c0c68ac01]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_result m+0x1d7)[0x555c0c68b817]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(+0x5902cb)[0x555c0c5252cb]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x482e)[0x555c0c631d8e]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x 3ad)[0x555c0c63723d]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcjbb+0x217a)[0x555c0c63a5fa]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z10do_commandP3THD+0x11c)[0x555c0c63bf3c]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1fa )[0x555c0c71824a]
May 20 11:45:45 [localhost] mysqld: /usr/sbin/mysqld(handle_one_connection+0x3d)[0x555c0c71832d]
May 20 11:45:45 [localhost] mysqld: pthread_create.c:0(start_thread)[0x7fc2300abea5]
May 20 11:45:45 [localhost] mysqld: /lib64/libc.so.6(clone+0x6d)[0x7fc22e44c8dd]
May 20 11:45:45 [localhost] mysqld: Trying to get some variables.
May 20 11:45:45 [localhost] mysqld: Some pointers may be invalid and cause the dump to abort.
May 20 11:45:45 [localhost] mysqld: Query (0x7fc1d001cb90): SELECT DATE_FORMAT(opportunity.invoice_date, '%Y-%m') AS `MONTH:invoi
ceDate`, opportunity.assigned_user_id AS `assignedUserId`, SUM(opportunity.internal_income * internalIncomeCurrencyRate.rate) AS
`SUM:internalIncomeConverted` FROM `opportunity` LEFT JOIN `user` AS `assignedUser` ON opportunity.assigned_user_id = assignedUse
r.id LEFT JOIN `currency` AS `internalIncomeCurrencyRate` ON internalIncomeCurrencyRate.id = opportunity.internal_income_currency
WHERE (opportunity.stage IN ('Closed Won')) AND (opportunity.forecasting = '') AND ((opportunity.invoice_date >= '2020-01-01' AN
D opportunity.invoice_date < '2021-01-01')) AND opportunity.deleted = '0' GROUP BY DATE_FORMAT(opportunity.invoice_date, '%Y-%m')
, opportunity.assigned_user_id ORDER BY 3 DESC
May 20 11:45:45 [localhost] mysqld: Connection ID (thread ID): 93
May 20 11:45:45 [localhost] mysqld: Status: NOT_KILLED
May 20 11:45:45 [localhost] mysqld: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_so rt_union=on,index_merge_i
ntersection=on,index_merge_sort_intersection=off,e ngine_condition_pushdown=off,index_condition_pushd own=on,derived_merge=on,deriv
ed_with_keys=on,firstmatch=on,loosescan=on,materia lization=on,in_to_exists=on,semijoin=on,partial_ma tch_rowid_merge=on,partial_ma
tch_table_scan=on,subquery_cache=on,mrr=off,mrr_co st_based=off,mrr_sort_keys=off,outer_join_with_cac he=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,joi n_cache_bka=on,optimize_join_buffer_size=on,table_ elimination=on,extended_keys=
on,exists_to_in=on,orderby_uses_equalities=on,cond ition_pushdown_for_derived=on,split_materialized=o n,condition_pushdown_for_subq
uery=on,rowid_filter=on,condition_pushdown_from_ha ving=on
May 20 11:45:45 [localhost] mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
May 20 11:45:45 [localhost] mysqld: information that should help you find out what is causing the crash.
May 20 11:45:45 [localhost] mysqld: Writing a core file...
May 20 11:45:45 [localhost] mysqld: Working directory at /var/lib/mysql
May 20 11:45:45 [localhost] mysqld: Resource Limits:
May 20 11:45:45 [localhost] mysqld: Limit Soft Limit Hard Limit Units
May 20 11:45:45 [localhost] mysqld: Max cpu time unlimited unlimited seconds
May 20 11:45:45 [localhost] mysqld: Max file size unlimited unlimited bytes
May 20 11:45:45 [localhost] mysqld: Max data size unlimited unlimited bytes
May 20 11:45:45 [localhost] mysqld: Max stack size 8388608 unlimited bytes
May 20 11:45:45 [localhost] mysqld: Max core file size 0 unlimited bytes
May 20 11:45:45 [localhost] mysqld: Max resident set unlimited unlimited bytes
May 20 11:45:45 [localhost] mysqld: Max processes 15066 15066 processes
May 20 11:45:45 [localhost] mysqld: Max open files 16364 16364 files
May 20 11:45:45 [localhost] mysqld: Max locked memory 65536 65536 bytes
May 20 11:45:45 [localhost] mysqld: Max address space unlimited unlimited bytes
May 20 11:45:45 [localhost] mysqld: Max file locks unlimited unlimited locks
May 20 11:45:45 [localhost] mysqld: Max pending signals 15066 15066 signals
May 20 11:45:45 [localhost] mysqld: Max msgqueue size 819200 819200 bytes
May 20 11:45:45 [localhost] mysqld: Max nice priority 0 0
May 20 11:45:45 [localhost] mysqld: Max realtime priority 0 0
May 20 11:45:45 [localhost] mysqld: Max realtime timeout unlimited unlimited us
May 20 11:45:45 [localhost] mysqld: Core pattern: core
May 20 11:45:45 [localhost] systemd: mariadb.service: main process exited, code=killed, status=6/ABRT
May 20 11:45:45 [localhost] systemd: Unit mariadb.service entered failed state.
May 20 11:45:45 [localhost] systemd: mariadb.service failed.
May 20 11:45:50 [localhost] systemd: mariadb.service holdoff time over, scheduling restart.
May 20 11:45:50 [localhost] systemd: Stopped MariaDB 10.4.11 database server.
May 20 11:45:50 [localhost] systemd: Starting MariaDB 10.4.11 database server...

[Maximus]

What if you will delete: 'MONTH: Invoice Date' from 'Group By'?
Is the the 'Invoice Date' your custom field? How did you create it? Does it base on a custom relationship?

[Luki]

'Invoice Date' is my custom field created in Opportunity.

Backup database and restore it repair the problem, or 'ALTER TABLE opportunity ENGINE = InnoDB' also fix problem. Don't no why after upgrade is problem. I try restore old version from backup and upgrade again and the problem can be reproduce