Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
attached files with above code (in case utf8 encoding on one record is relevant)
Thanks a lot for the test case.
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;
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