1

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running into a mysqld signal 11 error, which you can find at the end.

RDBMS MariaDB 5.5.42

There is an intersting point. If I excecute the view directly from mysql workbench the query properly works. But if it will be executed by php application, the mysql server crashes.

Is there a better practice?

Basicly, I have a maintable which is a collection of all measurements:

Table: measurement_sets
MeasID int(10) UN AI PK 
DateTime datetime 
EngID int(11) 

All the related data will be saved in a key-value table:

Table: md_charac_single
ID int(10) UN AI PK 
Nbr int(11)            //(Cylinder Number)
Key int(11)            //(Parameter Key)
Value double 
MeasID int(10) UN 

Last but not least, the query which is crashing the system. Note I posted a short version of the query.

SELECT 
    ms.`Type` AS 'MeasType',
    `e`.`MaxPower` AS 'RATED_POWER',
    mdPower.`Value` AS 'SHAFT_POWER',
    mdIndicatedPower.`Value` AS 'INDICATED_POWER',
    mdElectricPower.`Value` AS 'ELECTRIC_POWER',
    mdSpeed.`Value` AS 'ENGINE_SPEED',
    (CASE WHEN mdLoad.`Value` IS NOT NULL OR mdLoad.`Value` <> '' OR mdLoad.`Value` = 0 THEN mdLoad.`Value`
    ELSE (100 * mdPower.`Value`) / `e`.`MaxPower`
    END) AS 'LOAD',
    mdFuelMassFlow.`Value` AS 'FC_FLOW_HOUR',
    (SELECT FC_FLOW_HOUR * 24) / 1000 AS 'FC_FLOW_DAY',
    (SELECT FC_FLOW_HOUR * 1000) / mdIndicatedPower.`Value` AS 'SFOC_INDICATED',
    (SELECT FC_FLOW_HOUR * 1000) / mdElectricPower.`Value` * (e.EfficiencyElecGenerator / 100) AS 'SFOC_ELECTRIC',
    (CASE
        WHEN e.FuelActiveSFOC = 'SFOC_INDICATED' THEN (SELECT SFOC_INDICATED)
        WHEN e.FuelActiveSFOC = 'SFOC_ELECTRIC' THEN (SELECT SFOC_ELECTRIC)
        WHEN e.FuelActiveSFOC = 'SFOC_SHAFT' THEN (SELECT SFOC_SHAFT)
    END) AS 'SFOC_ACTIVE'
FROM
    measurement_sets ms
        LEFT JOIN
    `engines` e ON ms.EngID = e.EngID
        LEFT JOIN
    md_charac_single_desc mdLoad ON ms.MeasID = mdLoad.MeasID
        AND mdLoad.`Key` = 100004
        LEFT JOIN
    md_charac_single_desc mdSpeed ON ms.MeasID = mdSpeed.MeasID
        AND mdSpeed.`Key` = 100005
        LEFT JOIN
    md_charac_single_desc mdFuelMassFlow ON ms.MeasID = mdFuelMassFlow.MeasID
        AND mdFuelMassFlow.`Key` = 100372
        LEFT JOIN
    md_indicated_power mdIndicatedPower ON ms.MeasID = mdIndicatedPower.MeasID
        LEFT JOIN
    md_charac_single_desc mdElectricPower ON ms.MeasID = mdElectricPower.MeasID
        AND mdElectricPower.`Key` = 100007
        LEFT JOIN
    md_charac_single_desc mdFuelSupplyMeas ON ms.MeasID = mdFuelSupplyMeas.MeasID
        AND mdFuelSupplyMeas.`Key` = 100317
        LEFT JOIN
    md_charac_single_desc mdDrainMount ON ms.MeasID = mdDrainMount.MeasID
        AND mdDrainMount.`Key` = 100326
        LEFT JOIN
    md_charac_single_desc mdPower ON ms.MeasID = mdPower.MeasID
        AND mdPower.`Key` = 100006
        LEFT JOIN
    md_charac_single_desc mdFuelMeasDuration ON ms.MeasID = mdFuelMeasDuration.MeasID
        AND mdFuelMeasDuration.`Key` = 100318
        LEFT JOIN
    md_charac_single_desc mdFOTempAtFlow ON ms.MeasID = mdFOTempAtFlow.MeasID
        AND mdFOTempAtFlow.`Key` = 100060
        LEFT JOIN
    md_charac_single_desc mdFuelVolumeFlow ON ms.MeasID = mdFuelVolumeFlow.MeasID
        AND mdFuelVolumeFlow.`Key` = 100319
        LEFT JOIN
    md_charac_single_desc mdDensity ON ms.MeasID = mdDensity.MeasID
        AND mdDensity.`Key` = 100033
        LEFT JOIN
    md_charac_single_desc mdLVC ON fs.MeasID = mdLVC.MeasID
        AND mdLVC.`Key` = 100052
        LEFT JOIN
    md_charac_single_desc mdWater ON fs.MeasID = mdWater.MeasID
        AND mdWater.`Key` = 100058
        LEFT JOIN
    md_charac_single_desc mdAsh ON fs.MeasID = mdAsh.MeasID
        AND mdAsh.`Key` = 100059
        LEFT JOIN
    md_charac_single_desc mdSulphur ON fs.MeasID = mdSulphur.MeasID
        AND mdSulphur.`Key` = 100055
        LEFT JOIN
    md_charac_single_desc mdAirTempBeforeBlower ON fs.MeasID = mdAirTempBeforeBlower.MeasID
        AND mdAirTempBeforeBlower.`Key` = 100134
        LEFT JOIN
    md_engine mdBlowFactorISO ON fs.EngID = mdBlowFactorISO.EngID
        AND mdBlowFactorISO.`Key` = 100376
        LEFT JOIN
    md_engine mdBlowInTempModel ON fs.EngID = mdBlowInTempModel.EngID
        AND mdBlowInTempModel.`Key` = 100377
        LEFT JOIN
    md_charac_single_desc mdEngRoomPressure ON fs.MeasID = mdEngRoomPressure.MeasID
        AND mdEngRoomPressure.`Key` = 100021
        LEFT JOIN
    md_charac_single_desc mdPressureDropAirFilter ON fs.MeasID = mdPressureDropAirFilter.MeasID
        AND mdPressureDropAirFilter.`Key` = 100130
        LEFT JOIN
    md_engine mdEngRoomPressCorrFactor ON fs.EngID = mdEngRoomPressCorrFactor.EngID
        AND mdEngRoomPressCorrFactor.`Key` = 100378
        LEFT JOIN
    md_engine mdEngRoomPressFactor ON fs.EngID = mdEngRoomPressFactor.EngID
        AND mdEngRoomPressFactor.`Key` = 100380
        LEFT JOIN
    md_engine mdScavPowerFormFactor ON fs.EngID = mdScavPowerFormFactor.EngID
        AND mdScavPowerFormFactor.`Key` = 100379
        LEFT JOIN
    md_charac_single_desc mdScavAirTemp ON fs.MeasID = mdScavAirTemp.MeasID
        AND mdScavAirTemp.`Key` = 100137
        LEFT JOIN
    md_engine mdTrustBearingLoss ON fs.EngID = mdTrustBearingLoss.EngID
        AND mdTrustBearingLoss.`Key` = 100381
        LEFT JOIN
    md_power_dev_tot_rel totDev ON fs.MeasID = totDev.MeasID

Report from "EXPLAIN SELECT"

1   PRIMARY <derived2>  ALL                 11594880    
2   DERIVED ms  ALL                 183 
2   DERIVED e   eq_ref  PRIMARY PRIMARY 4   edc_production.ms.EngID 1   Using where
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED <derived58> ref key0    key0    5   edc_production.ms.MeasID    99  
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED mdBlowFactorISO ref fk_md_engine_engines1_idx   fk_md_engine_engines1_idx   4   edc_production.ms.EngID 2   Using where
2   DERIVED mdBlowInTempModel   ref fk_md_engine_engines1_idx   fk_md_engine_engines1_idx   4   edc_production.ms.EngID 2   Using where
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED mdEngRoomPressCorrFactor    ref fk_md_engine_engines1_idx   fk_md_engine_engines1_idx   4   edc_production.ms.EngID 2   Using where
2   DERIVED mdEngRoomPressFactor    ref fk_md_engine_engines1_idx   fk_md_engine_engines1_idx   4   edc_production.ms.EngID 2   Using where
2   DERIVED mdScavPowerFormFactor   ref fk_md_engine_engines1_idx   fk_md_engine_engines1_idx   4   edc_production.ms.EngID 2   Using where
2   DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
2   DERIVED mdTrustBearingLoss  ref fk_md_engine_engines1_idx   fk_md_engine_engines1_idx   4   edc_production.ms.EngID 2   Using where
2   DERIVED <derived75> ref key0    key0    9   edc_production.ms.MeasID    10  Using where
75  DERIVED l   ref fk_limits_engines1_idx,KEY,FK_KEY,FK_KEY_TYPE   KEY 5   const   43  Using temporary; Using filesort
75  DERIVED ms  ref PRIMARY,FK  FK  4   edc_production.l.EngID  2   Using index
75  DERIVED cs  ref FK_KEY,FK_KEY_NBR,FK    FK_KEY  8   edc_production.ms.MeasID,const  1   
75  DERIVED <derived78> ref key0    key0    4   edc_production.ms.MeasID    10  
78  DERIVED cs  index       FK  4       18271   Using where
76  DEPENDENT SUBQUERY                              No tables used
58  DERIVED md_charac_single    index       FK  4       18271   Using where
54  DEPENDENT SUBQUERY                              No tables used
53  DEPENDENT SUBQUERY                              No tables used
50  DEPENDENT SUBQUERY                              No tables used
49  DEPENDENT SUBQUERY                              No tables used
48  DEPENDENT SUBQUERY                              No tables used
47  DEPENDENT SUBQUERY                              No tables used
46  DEPENDENT SUBQUERY                              No tables used
45  DEPENDENT SUBQUERY                              No tables used
44  DEPENDENT SUBQUERY                              No tables used
43  DEPENDENT SUBQUERY                              No tables used
42  DEPENDENT SUBQUERY                              No tables used
41  DEPENDENT SUBQUERY                              No tables used
40  DEPENDENT SUBQUERY                              No tables used
39  DEPENDENT SUBQUERY                              No tables used
38  DEPENDENT SUBQUERY                              No tables used
37  DEPENDENT SUBQUERY                              No tables used
36  DEPENDENT SUBQUERY                              No tables used
35  DEPENDENT SUBQUERY                              No tables used
34  DEPENDENT SUBQUERY                              No tables used
33  DEPENDENT SUBQUERY                              No tables used
32  DEPENDENT SUBQUERY                              No tables used
31  DEPENDENT SUBQUERY                              No tables used
30  DEPENDENT SUBQUERY                              No tables used
29  DEPENDENT SUBQUERY                              No tables used
28  DEPENDENT SUBQUERY                              No tables used
27  DEPENDENT SUBQUERY                              No tables used
26  DEPENDENT SUBQUERY                              No tables used
25  DEPENDENT SUBQUERY                              No tables used
24  DEPENDENT SUBQUERY                              No tables used
21  DEPENDENT SUBQUERY                              No tables used
19  DEPENDENT SUBQUERY                              No tables used
17  DEPENDENT SUBQUERY                              No tables used
15  DEPENDENT SUBQUERY                              No tables used
14  DEPENDENT SUBQUERY                              No tables used
13  DEPENDENT SUBQUERY                              No tables used
8   DEPENDENT SUBQUERY                              No tables used
7   DEPENDENT SUBQUERY                              No tables used
6   DEPENDENT SUBQUERY                              No tables used

Here you get the MariaDB error log:

Version: '5.5.42-MariaDB-1~wheezy-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
151208 19:43:32 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 5.5.42-MariaDB-1~wheezy-log
key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=1
max_threads=102
thread_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 759563 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x7f50c8806000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f50fc235e50 thread_stack 0x48000
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f50fcb3382b]
/usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7f50fc76bae2]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x7f50fbe630a0]
/usr/sbin/mysqld(_ZN17Dependency_marker11visit_fieldEP10Item_field+0x37)[0x7f50fc78f337]
/usr/sbin/mysqld(_ZN10Item_field30enumerate_field_refs_processorEPh+0x12)[0x7f50fc775d72]
/usr/sbin/mysqld(_ZN9Item_func4walkEM4ItemFbPhEbS1_+0x7c)[0x7f50fc7b6a6c]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f50c681d018): is an invalid pointer
Connection ID (thread ID): 52
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=o$

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
151208 19:43:32 mysqld_safe Number of processes running now: 0
151208 19:43:32 mysqld_safe mysqld restarted
151208 19:43:32 InnoDB: The InnoDB memory heap is disabled
151208 19:43:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151208 19:43:32 InnoDB: Compressed tables use zlib 1.2.7
151208 19:43:32 InnoDB: Using Linux native AIO
151208 19:43:32 InnoDB: Initializing buffer pool, size = 256.0M
151208 19:43:32 InnoDB: Completed initialization of buffer pool
151208 19:43:32 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 695575502
151208 19:43:32  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 695576415
151208 19:43:32  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89$
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 809, file name /var/log/mysql/mariadb-bin.000331
151208 19:43:33  InnoDB: Waiting for the background threads to start
151208 19:43:34 Percona XtraDB (http://www.percona.com) 5.5.41-MariaDB-37.0 started; log sequence number 695576415
151208 19:43:34 [Note] Plugin 'FEEDBACK' is disabled.
151208 19:43:34 [Note] Recovering after a crash using /var/log/mysql/mariadb-bin
151208 19:43:34 [Note] Starting crash recovery...
151208 19:43:34 [Note] Crash recovery finished.
151208 19:43:34 [Note] Server socket created on IP: '127.0.0.1'.
151208 19:43:34 [Note] Event Scheduler: Loaded 0 events
151208 19:43:34 [Note] /usr/sbin/mysqld: ready for connections.
  • Interesting that the php script fails out, but not workbench. Is the PHP server running on the same as the MySQL server? If not, are the mariadb client libs installed instead of mysql client libs? – Derek Downey Dec 8 '15 at 21:48
  • Yes the apache server runs on the same sever as mariadb. I'm using laravel 4.2 php framework. – Raphael Fuchs Dec 8 '15 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.