[MDEV-5147] crash with "is an invalid pointer" in mariadb since 5.5.32 (but not in any oracle version) Created: 2013-10-17  Updated: 2013-11-06  Resolved: 2013-11-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.3.12, 5.5.33a
Fix Version/s: 10.0.5, 5.5.34, 5.3.13

Type: Bug Priority: Major
Reporter: naox Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

centos x64

Attachments: HTML File 1_create_procedure     HTML File 2_populate_db    

 Description   

I've recently upgraded from mariadb 5.5.29 to 5.5.33a on many servers. There are no problem except one server (out of ~15) that keeps crashing (every 5-20 minutes). I've tested on oracle mysql 5.5.34 (with same server) and it does not crash with it. It is server provided to hosting users with about 75GB od data on innodb and myisam. Also server stopps crashing when I switch back to mariadb 5.5.29. I use precompiled binaries of mariadb but I also compiled 5.5.33a on my system and same issue was occuring there.

131016 18:23:01 [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.33a-MariaDB
 
key_buffer_size=251658240
 
read_buffer_size=524288
 
max_used_connections=9
 
max_threads=2050
 
thread_count=10
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5530353 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7f3cb39cb000
 
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 = 0x411ec090 thread_stack 0x80000
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(my_print_stacktrace+0x2e)[0xb0bc9e]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(handle_fatal_signal+0x422)[0x6eea32]
 
/lib64/libpthread.so.0[0x7f3d358c9b10]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZNK10Item_field11result_typeEv+0xb)[0x71a0fb]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld[0x5c0d8f]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld[0x5c1aa7]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld[0x5c1e1c]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld[0x5c2356]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld[0x5c34f1]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN4JOIN8optimizeEv+0x456)[0x5d9596]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z22mysql_derived_optimizeP3THDP3LEXP10TABLE_LIST+0xfd)[0x56fc1d]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xb8)[0x56fd58]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN10TABLE_LIST14handle_derivedEP3LEXj+0x7a)[0x62663a]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x40)[0x581ac0]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN10TABLE_LIST14handle_derivedEP3LEXj+0x5a)[0x62661a]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x40)[0x581ac0]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN10TABLE_LIST14handle_derivedEP3LEXj+0x5a)[0x62661a]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x40)[0x581ac0]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN4JOIN8optimizeEv+0x134)[0x5d9274]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex
 
_unitP13st_select_lex+0xd1)[0x5e48b1]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_sele
 
ct_lexPP12multi_update+0x1cf)[0x61cd3f]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z21mysql_execute_commandP3THD+0x5a33)[0x596bd3]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x80caee]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x210)[0x80ce00]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x6a9)[0x8141b9]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x51f)[0x810dcf]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x675)[0x812635]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z21mysql_execute_commandP3THD+0x3b43)[0x594ce3]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x80caee]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x210)[0x80ce00]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x6a9)[0x8141b9]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x51f)[0x810dcf]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x675)[0x812635]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z21mysql_execute_commandP3THD+0x3b43)[0x594ce3]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1b0)[0x598490]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x172e)[0x599bce]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z10do_commandP3THD+0xdb)[0x59a13b]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x144)[0x6558d4]
 
/var/mariadb-5.5.33a-linux-x86_64/bin/mysqld(handle_one_connection+0x4c)[0x655a0c]
 
/lib64/libpthread.so.0[0x7f3d358c173d]
 
/lib64/libc.so.6(clone+0x6d)[0x7f3d34a6f4bd]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f3cb5790490): is an invalid pointer
 
Connection ID (thread ID): 2250
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersect
 
ion=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,mat
 
erialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cos
 
t_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cach
 
e_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
 
 
 
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.

Now i compared that "Connection ID (thread ID): 2250" with general query log and server crashes always after this sequence of queries:

		 2250 Query	SET NAMES utf8
 
		 2250 Init DB	user_user
 
		 2250 Query	CALL get_round_table_09(125,1)
 
		 2250 Query	DROP TEMPORARY TABLE IF EXISTS temp_round_table, temp_positions
 
		 2250 Query	CREATE TEMPORARY TABLE temp_round_table (INDEX(league_id), INDEX(team_id)) AS
 
        SELECT l.league_id, 0 AS position, t.team_id AS team_id, t.name AS team_name, ntz(played) AS played, ntz(won) AS won, ntz(drew) AS drew, ntz(lost) AS lost, ntz(scored) AS scored, ntz(lossed) AS lossed, ntz(points) AS points, 0 AS small_points, 0 AS small_ballance, 0 AS small_scored, history, if(isnull(te.team_id),0,1) AS exclude
 
        FROM leagues l
 
          JOIN league_squads ls ON l.league_id=ls.league_id
 
          JOIN teams t ON ls.team_id=t.team_id
 
          LEFT JOIN
 
            (SELECT rt.team_id, count(*) AS played, sum(if(rt.general_result='w',1,0)) AS won, sum(if(rt.general_result='r',1,0)) AS drew, sum(if(rt.general_result='p',1,0)) AS lost, sum(rt.score) AS scored, sum(rop.score) AS lossed, sum(rt.points) AS points, group_concat(rt.general_result ORDER BY m.date, m.time SEPARATOR '') AS history
 
             FROM rounds r
 
               JOIN matches m ON m.round_id=r.round_id AND m.finished AND NOT m.cancelled
 
               JOIN results rt ON m.match_id=rt.match_id
 
               JOIN results rop ON m.match_id=rop.match_id AND rop.team_id<>rt.team_id
 
               JOIN leagues lt ON lt.round_id= NAME_CONST('var_round_id',125)
 
               JOIN league_squads lst ON lst.league_id=lt.league_id AND lst.team_id=rt.team_id
 
               JOIN leagues lop ON lop.round_id= NAME_CONST('var_round_id',125) AND lt.league_id=lop.league_id
 
             WHERE r.round_id= NAME_CONST('var_round_id',125)
 
             GROUP BY rt.team_id)
 
          m1 ON t.team_id=m1.team_id
 
          LEFT JOIN team_excludies te ON t.team_id=te.team_id AND te.competition_id= NAME_CONST('var_competition_id',87)
 
        WHERE l.round_id= NAME_CONST('var_round_id',125)
 
		 2250 Query	DROP TEMPORARY TABLE IF EXISTS temp_rx
 
		 2250 Query	CREATE TEMPORARY TABLE temp_rx AS
 
    SELECT league_id, team_id, points
 
    FROM temp_round_table
 
		 2250 Query	UPDATE temp_round_table trt,
 
         (SELECT r.team_id, sum(r.points) AS points, sum(r.score)-sum(rl.score) AS ballance, sum(r.score) AS scored
 
          FROM
 
             (SELECT mb.match_id
 
              FROM
 
               (SELECT m.match_id
 
                  FROM
 
                     (SELECT team_id
 
                      FROM temp_rx
 
                      WHERE league_id= NAME_CONST('fetched_league_id',138) AND points= NAME_CONST('fetched_points',3)) tx
 
                    JOIN matches m ON m.round_id= NAME_CONST('var_round_id',125) AND m.finished AND NOT m.cancelled
 
                    JOIN results r ON r.match_id=m.match_id AND r.team_id=tx.team_id
 
                WHERE true) mb
 
              GROUP BY mb.match_id
 
              HAVING count(*)>1) mx
 
            JOIN results r ON r.match_id=mx.match_id
 
            JOIN results rl ON rl.match_id=mx.match_id AND rl.team_id<>r.team_id
 
          GROUP BY r.team_id) stb
 
        SET trt.small_points=stb.points, trt.small_scored=stb.scored, trt.small_ballance=stb.ballance
 
        WHERE trt.team_id=stb.team_id
 
		 2250 Query	UPDATE temp_round_table trt,
 
         (SELECT r.team_id, sum(r.points) AS points, sum(r.score)-sum(rl.score) AS ballance, sum(r.score) AS scored
 
          FROM
 
             (SELECT mb.match_id
 
              FROM
 
               (SELECT m.match_id
 
                  FROM
 
                     (SELECT team_id
 
                      FROM temp_rx
 
                      WHERE league_id= NAME_CONST('fetched_league_id',138) AND points= NAME_CONST('fetched_points',4)) tx
 
                    JOIN matches m ON m.round_id= NAME_CONST('var_round_id',125) AND m.finished AND NOT m.cancelled
 
                    JOIN results r ON r.match_id=m.match_id AND r.team_id=tx.team_id
 
                WHERE true) mb
 
              GROUP BY mb.match_id
 
              HAVING count(*)>1) mx
 
            JOIN results r ON r.match_id=mx.match_id
 
            JOIN results rl ON rl.match_id=mx.match_id AND rl.team_id<>r.team_id
 
          GROUP BY r.team_id) stb
 
        SET trt.small_points=stb.points, trt.small_scored=stb.scored, trt.small_ballance=stb.ballance
 
        WHERE trt.team_id=stb.team_id



 Comments   
Comment by Elena Stepanova [ 2013-10-17 ]

Hi,

Would you be able to provide a database dump? If you cannot include data due to privacy concerns, then at least the table/procedure structures (if you can give us a full dump with the data, and if it's not too big, it would be even better).

Also, please note that the 5th line in your log fragment seems to be wrong – it's the one that starts with ' )' – was it really a synax error on the client side, or was it a copy-paste problem?

Thanks

Comment by naox [ 2013-10-18 ]

It seems is was an error in copy/paste. I've pasted again those queries (in original post).
I've been running now oracle mysql 5.5.34 for day without crash. I've switched back now to mariadb 5.5.33a and of course crashing is back. I've corelated apache logs with crash of mariadb and got query to my customer website that causes mysql crash (every time. I can now crash mysql anytime I want by requesting this url).

Comment by naox [ 2013-10-18 ]

I've tested mariadb 5.5.29-5.5.33a. Bug was introduced in mariadb version 5.5.32 (until 5.5.31 is fine, every version after it is crashing). Someone might want to review changes in 5.5.32

For reference I've tested oracle mysql 5.5.32, 5.5.33, 5.5.34 and it is not crashing. That means that bug was not backported from 5.5.32 and fixed in .33 by oracle. It seems that this is something mariadb specific introduced at 5.5.32

Comment by Elena Stepanova [ 2013-10-18 ]

Hi,

I understand that it's reproducible on your side, but we still need the structures of participating tables and stored procedures to reproduce it locally, If you can't do a dump of the structures, output of separate SHOW commands will also do.

Thanks.

Comment by naox [ 2013-10-18 ]

Your MariaDB connection id is 3
Server version: 5.5.33a-MariaDB-log MariaDB Server

MariaDB [user_futsal]> CALL get_round_table_09(128,1);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: user_futsal

ERROR 2013 (HY000): Lost connection to MySQL server during query

MariaDB [user_futsal]> CALL get_round_table_09(120,1);
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Your MySQL connection id is 643115
Server version: 5.5.34 Source distribution

MySQL [user_futsal]> CALL get_round_table_09(128,1);
....
8 rows in set (0.02 sec)

When I truncate all tables in database result of this procedure is also obviously empty so no crash. This is connected somehow to data in database.
unfortunetly I can't disclose any data of my customer. Also it is beyond me to simplyfy/trace work of this procedure as it gathers data from numerous tables and calls 2 other functions and code of procedure has about 5 pages as I see

Comment by Elena Stepanova [ 2013-10-19 ]

>> When I truncate all tables in database result of this procedure is also obviously empty so no crash. This is connected somehow to data in database.
unfortunetly I can't disclose any data of my customer.

It's understandable. Please still provide the description of the structures if you can, and whatever you can share about the volume of data, index distribution etc. (SHOW CREATE TABLE, SHOW INDEX, SHOW TABLE STATUS, SHOW CREATE PROCEDURE). We will try to populate the structures with the artificial data. You can obfuscate column/index names if even they are private.

Comment by naox [ 2013-10-19 ]

1) create procedure

REPLACE INTO proc VALUES ('user_user','fill_smallpts_09','PROCEDURE','fill_smallpts_09','SQL','CONTAINS_SQL','NO','DEFINER','','','BEGIN
 
 
 
  DECLARE fetched_league_id INT UNSIGNED;
 
  DECLARE fetched_points INT;
 
 
 
  DROP TEMPORARY TABLE IF EXISTS temp_rx;
 
  CREATE TEMPORARY TABLE temp_rx AS
 
    SELECT league_id, team_id, points
 
    FROM temp_round_table;
 
 
 
        UPDATE temp_round_table trt,
 
         (SELECT r.team_id, sum(r.points) AS points, sum(r.score)-sum(rl.score) AS ballance, sum(r.score) AS scored
 
          FROM
 
             (SELECT mb.match_id
 
              FROM
 
               (SELECT m.match_id
 
                  FROM
 
                     (SELECT team_id
 
                      FROM temp_rx
 
                      WHERE league_id=fetched_league_id AND points=fetched_points) tx
 
                WHERE true) mb
 
              GROUP BY mb.match_id
 
              HAVING count(*)>1) mx
 
          GROUP BY r.team_id) stb
 
        SET trt.small_points=stb.points, trt.small_scored=stb.scored, trt.small_ballance=stb.ballance
 
        WHERE trt.team_id=stb.team_id;
 
 
 
END','user_user@mysql5','2010-09-15 18:56:16','2010-09-15 18:56:16','','','utf8','utf8_polish_ci','utf8_polish_ci','
 
');

2) populate temporary table and run procedure twice

SET NAMES utf8;
 
 
 
DROP TEMPORARY TABLE IF EXISTS `temp_round_table`;
 
CREATE TEMPORARY TABLE `temp_round_table` (
 
  `league_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `position` int(1) NOT NULL DEFAULT '0',
 
  `team_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `team_name` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  `played` double DEFAULT NULL,
 
  `won` double DEFAULT NULL,
 
  `drew` double DEFAULT NULL,
 
  `lost` double DEFAULT NULL,
 
  `scored` double DEFAULT NULL,
 
  `lossed` double DEFAULT NULL,
 
  `points` double DEFAULT NULL,
 
  `small_points` int(1) NOT NULL DEFAULT '0',
 
  `small_ballance` int(1) NOT NULL DEFAULT '0',
 
  `small_scored` int(1) NOT NULL DEFAULT '0',
 
  `history` text CHARACTER SET utf8,
 
  `exclude` int(1) NOT NULL DEFAULT '0',
 
  KEY `league_id` (`league_id`),
 
  KEY `team_id` (`team_id`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
 
 
 
 
 
INSERT INTO `temp_round_table` VALUES (141,0,975,'GCZM Zarys Zabrze',1,0,0,1,2,5,0,0,0,0,'p',0),(141,0,976,'Vlassenroot',1,1,0,0,6,2,3,0,0,0,'w',0),(141,0,977,'FC Roma',1,0,1,0,2,2,1,0,0,0,'r',0),(141,0,978,'Fortuna',1,0,0,1,1,11,0,0,0,0,'p',0),(141,0,979,'ZZRG Bielszowice',1,1,0,0,11,1,3,0,0,0,'w',0),(141,0,980,'Mostostal KP',1,0,1,0,2,2,1,0,0,0,'r',0),(141,0,981,'Promil',1,0,0,1,2,6,0,0,0,0,'p',0),(141,0,982,'Walczymy z Nadwagą',1,1,0,0,5,2,3,0,0,0,'w',0);
 
 
 
CALL fill_smallpts_09();
 
CALL fill_smallpts_09();
 

MariaDB [user_user]> CALL fill_smallpts_09();
 
ERROR 2013 (HY000): Lost connection to MySQL server during query

Comment by naox [ 2013-10-19 ]

attached files with above code (in case utf8 encoding on one record is relevant)

Comment by Elena Stepanova [ 2013-10-21 ]

Thanks a lot for the test case.

Comment by Elena Stepanova [ 2013-10-21 ]

Here is a somewhat smaller test case based on the original one (but please also check the original one after fixing):

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 ( points double ) ENGINE=MyISAM;

INSERT INTO t1 VALUES (0),(3);

CREATE TABLE t2 AS SELECT points FROM t1;

UPDATE t1 trt,
(SELECT sum(r.points) AS points FROM
(SELECT * FROM
(SELECT match_id FROM t2) mb
) mx
) stb
SET trt.small_points=stb.points;

Comment by Elena Stepanova [ 2013-11-06 ]

The bug was fixed along with MDEV-5143 by revno 3710 on maria/5.3 and merged up into 5.5 and 10.0.
I checked both the original test case and the simplified one from my comment, they cause a crash before the fix for MDEV-5143 and the expected ER_BAD_FIELD_ERROR after the fix.

Generated at Thu Feb 08 07:02:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.