Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.4.8
-
Debian GNU/Linux 10 (buster)
Description
I'm getting MariaDB mysqld signal 11 error. It has appeared from one day to another and it looks to be caused by running the below query (a day before it worked fine) - no data is returned but the query generates relatively complex explain due to nested views. The error appears randomly. Could you please advice what to do with that?
I'm enclosing the error log, the explain of the query, the query itself and source of the underlying view.
Thank you.
Martin
SELECT |
final_table.*
|
FROM
|
(SELECT |
`x588_x_bpej_ku`.`nad1rok_druhpoz2` AS `x9064_nad1rok_druhpoz2`, |
`x588_x_bpej_ku`.`trvaly_druhpoz99_rsd` AS `x9096_trvaly_druhpoz99_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz3_firma` AS `x9069_trvaly_druhpoz3_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz4_obec` AS `x9074_trvaly_druhpoz4_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz5_kraj` AS `x9079_trvaly_druhpoz5_kraj`, |
`x588_x_bpej_ku`.`trvaly_druhpoz6_rsd` AS `x9084_trvaly_druhpoz6_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz7` AS `x9089_trvaly_druhpoz7`, |
`x588_x_bpej_ku`.`trvaly_obec` AS `x9057_trvaly_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz2_obec` AS `x9062_trvaly_druhpoz2_obec`, |
`x588_x_bpej_ku`.`nad1rok_druhpoz7` AS `x9094_nad1rok_druhpoz7`, |
`x588_x_bpej_ku`.`trvaly_druhpoz3_kraj` AS `x9067_trvaly_druhpoz3_kraj`, |
`x588_x_bpej_ku`.`trvaly_druhpoz99_firma` AS `x9099_trvaly_druhpoz99_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz4_rsd` AS `x9072_trvaly_druhpoz4_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz5` AS `x9077_trvaly_druhpoz5`, |
`x588_x_bpej_ku`.`nad1rok_druhpoz5` AS `x9082_nad1rok_druhpoz5`, |
`x588_x_bpej_ku`.`trvaly_rsd` AS `x9055_trvaly_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz6_firma` AS `x9087_trvaly_druhpoz6_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz2_rsd` AS `x9060_trvaly_druhpoz2_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz7_obec` AS `x9092_trvaly_druhpoz7_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz99_kraj` AS `x9097_trvaly_druhpoz99_kraj`, |
`x588_x_bpej_ku`.`trvaly_druhpoz3` AS `x9065_trvaly_druhpoz3`, |
`x588_x_bpej_ku`.`nad1rok_druhpoz3` AS `x9070_nad1rok_druhpoz3`, |
`x588_x_bpej_ku`.`trvaly_druhpoz4_firma` AS `x9075_trvaly_druhpoz4_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz5_obec` AS `x9080_trvaly_druhpoz5_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz6_kraj` AS `x9085_trvaly_druhpoz6_kraj`, |
`x588_x_bpej_ku`.`trvaly_firma` AS `x9058_trvaly_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz7_rsd` AS `x9090_trvaly_druhpoz7_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz2_firma` AS `x9063_trvaly_druhpoz2_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz99` AS `x9095_trvaly_druhpoz99`, |
`x588_x_bpej_ku`.`nad1rok_druhpoz99` AS `x9100_nad1rok_druhpoz99`, |
`x588_x_bpej_ku`.`trvaly_druhpoz3_obec` AS `x9068_trvaly_druhpoz3_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz4_kraj` AS `x9073_trvaly_druhpoz4_kraj`, |
`x588_x_bpej_ku`.`trvaly_druhpoz5_rsd` AS `x9078_trvaly_druhpoz5_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz6` AS `x9083_trvaly_druhpoz6`, |
`x588_x_bpej_ku`.`trvaly_kraj` AS `x9056_trvaly_kraj`, |
`x588_x_bpej_ku`.`nad1rok_druhpoz6` AS `x9088_nad1rok_druhpoz6`, |
`x588_x_bpej_ku`.`trvaly_druhpoz7_firma` AS `x9093_trvaly_druhpoz7_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz2_kraj` AS `x9061_trvaly_druhpoz2_kraj`, |
`x588_x_bpej_ku`.`trvaly_druhpoz3_rsd` AS `x9066_trvaly_druhpoz3_rsd`, |
`x588_x_bpej_ku`.`trvaly_druhpoz99_obec` AS `x9098_trvaly_druhpoz99_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz4` AS `x9071_trvaly_druhpoz4`, |
`x588_x_bpej_ku`.`nad1rok_druhpoz4` AS `x9076_nad1rok_druhpoz4`, |
`x588_x_bpej_ku`.`trvaly_druhpoz5_firma` AS `x9081_trvaly_druhpoz5_firma`, |
`x588_x_bpej_ku`.`trvaly_druhpoz6_obec` AS `x9086_trvaly_druhpoz6_obec`, |
`x588_x_bpej_ku`.`trvaly_druhpoz2` AS `x9059_trvaly_druhpoz2`, |
`x588_x_bpej_ku`.`trvaly_druhpoz7_kraj` AS `x9091_trvaly_druhpoz7_kraj`, |
`x588_x_bpej_ku`.`stavba` AS `x5398_stavba`, |
`x588_x_bpej_ku`.`ku` AS `x5399_ku`, |
`x588_x_bpej_ku`.`obec` AS `x5400_obec`, |
`x588_x_bpej_ku`.`kraj` AS `x5401_kraj`, |
`x588_x_bpej_ku`.`id_etapa` AS `x5402_id_etapa`, |
`x588_x_bpej_ku`.`bpej` AS `x5403_bpej`, |
`x588_x_bpej_ku`.`trvaly` AS `x5404_trvaly`, |
`x588_x_bpej_ku`.`nad1rok` AS `x5405_nad1rok` |
FROM |
`x_bpej_ku` AS `x588_x_bpej_ku`) AS final_table |
WHERE
|
(((`x5402_id_etapa` = '643'))) |
Attachments
Activity
In the meantime we've migrated the entire database onto another server with the same MariaDB version installed. On the new instance the query is running fine with no issues. Interesting fact is that since we've dropped the problematic database, the original MariaDB instance is responding much faster. This is strange because there wasn't high traffic on that db.
Is there any possibility that the problem would be caused by some integrity problem on a storage or by similar issue connected to on how the database is stored on the disk?
The problem looks to be solved for now but we'll consider upgrade anyway.
Given the crash is the a part of the optimizer I say is reasonably less likely to be a storage problem, but its very hard to rule it out entirely.
response time - check you have either skip-name-resolve or if dns is the issue. Optimizer trace may show query differences https://mariadb.com/kb/en/optimizer-trace-overview. Hard to say. There's quite a few ways to look through causes, but its a bit beyond scope for this issue.
Best wishes for your upgrade.
It looks that the migration of the db on another server fixed the issue. Everything is running fine now withou any issues. We'll investigate on performance issues but it is another story. Let's close this.
Thanks for your insights.
Ack on query generation.
My request for decreasing nesting levels was trying to get to a repeatable test case. I don't know if it will help avoid this crash or not.
A latest test against 10.4 would at least help validate if this issue has already been solved. Upload your datadir with mariadb stopped to https://mariadb.com/kb/en/meta/mariadb-ftp-server/ if you have troubles doing this test yourself and are willing to share data with developers only to test.
If a later 10.4 fixes this issue then maybe its worth testing to see if the stability is what you expect.
Correct, about being no work arounds.