[MDEV-24152] mysqld segfault - signal 11 - optimizer get_best_combination Created: 2020-11-05  Updated: 2020-11-23  Resolved: 2020-11-23

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Martin Roman Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: crash, need_feedback
Environment:

Debian GNU/Linux 10 (buster)

Attachments: Text File error.log     Text File explain.txt     File view.sql     File x_bpej.sql    

 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')))



 Comments   
Comment by Daniel Black [ 2020-11-06 ]

can you include `show create table gisa_mpe_unis.x_bpej`?

Comment by Daniel Black [ 2020-11-06 ]

Can you try a later 10.4 release? A new release will be out sometime next week because of a major regression in 10.4.16.

Comment by Martin Roman [ 2020-11-06 ]

Attached x_bpej.sql . Alright, I'll check the possibility of upgrade on the new version

Comment by Martin Roman [ 2020-11-06 ]

I'd forgot to include one information - the db server is virtual running on vmware.

Comment by Martin Roman [ 2020-11-06 ]

MariaDB upgrade is not an option for the moment.

Comment by Daniel Black [ 2020-11-06 ]

With x_bpej I was trying to get to a base table, however looking at the explain I can assume this is several layers deep.

Generally a VM scenario isn't a major consideration. Is it possible to however clone the VM, upgrade the clone and verify its not fixed in the latest 10.4?

If its not fixed it might be worth trying to come up with a simpler query with less layers of views.

Comment by Martin Roman [ 2020-11-06 ]

Ok, understand. I'll verify the options with my colleagues and come back to you with the decision.

With regard to queries with many layers of views. Is there any parameter / variable in MariaDB configuration that can control explain complexity and prevent the database server from crashing when such a kind of query is invoked by some user?

Comment by Daniel Black [ 2020-11-07 ]

> Is there any parameter / variable in MariaDB configuration that can control explain complexity and prevent the database server from crashing when such a kind of query is invoked by some user?

Not that I could easily identify.

Making whatever `x5402_id_etapa` = '643' equates to at the low level being an index lookup might help.

I'm curious as to where the patten of making a subquery in the FROM clause and then doing a `SELECT *` comes form. I don't think changing it will help, but it does look like an overly verbose way to write the query.

Comment by Martin Roman [ 2020-11-09 ]

The pattern you're referring to is coming from our PHP framework:

  • internal logic of the sub-query is given by the user
  • the 'select * from (sub-query)' is then generated by the framework

Let me summarize your recommendations:

  • decrease view nesting levels
  • upgrade to the latest 10.4 version might bring us better stability
  • there is not any specific parameter that prevent db server crash potentially caused by high level of query complexity
Comment by Daniel Black [ 2020-11-16 ]

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.

Comment by Martin Roman [ 2020-11-16 ]

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.

Comment by Daniel Black [ 2020-11-16 ]

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.

Comment by Martin Roman [ 2020-11-21 ]

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.

Comment by Daniel Black [ 2020-11-23 ]

Thanks for the feedback.

Generated at Thu Feb 08 09:27:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.