[MDEV-5724] Server crashes on SQL select containing more group by and left join statements using innodb tables Created: 2014-02-24  Updated: 2014-03-12  Resolved: 2014-03-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.35, 5.5.36, 10.0.8
Fix Version/s: 5.5.37, 10.0.10

Type: Bug Priority: Major
Reporter: Honza Horak Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux, Fedora 20


 Description   

Originally reported as (1) and please see the reproducer there as well.

It's easily reproducible on my Fedora 20 machine as well with SQL command from comment #15 (1) and a command from comment #0 (1); also crashes with a bit simplified following SQL command:

SELECT Count(*)
 
FROM   bpsocial_post AS tbl
 
       LEFT JOIN bpsocial_profile_object_option a
 
              ON a.spoo_uid = sspo_uid
 
                 AND a.spoo_option_id = 1
 
       LEFT JOIN bpsocial_profile_object_option b
 
              ON b.spoo_uid = sspo_uid
 
                 AND b.spoo_option_id = 2
 
       LEFT JOIN bpsocial_profile_object_option c
 
              ON c.spoo_uid = sspo_uid
 
                 AND c.spoo_option_id = 3
 
       LEFT JOIN bpsocial_profile_object_option d
 
              ON d.spoo_uid = sspo_uid
 
                 AND d.spoo_option_id = 5
 
       LEFT JOIN bpsocial_profile_object_option e
 
              ON e.spoo_uid = sspo_uid
 
                 AND e.spoo_option_id = 4
 
       LEFT JOIN bpsocial_profile_object_option f
 
              ON f.spoo_uid = sspo_uid
 
                 AND f.spoo_option_id = 11
 
       LEFT JOIN bpsocial_profile_object_option g
 
              ON g.spoo_uid = sspo_uid
 
                 AND g.spoo_option_id = 7
 
       LEFT JOIN bpsocial_profile_object_option h
 
              ON h.spoo_uid = sspo_uid
 
                 AND h.spoo_option_id = 10
 
       LEFT JOIN bpsocial_profile_object_option i
 
              ON i.spoo_uid = sspo_uid
 
                 AND i.spoo_option_id = 18
 
       LEFT JOIN bpsocial_profile_object_option j
 
              ON j.spoo_uid = sspo_uid
 
                 AND j.spoo_option_id = 6
 
GROUP  BY a.spoo_value,
 
          b.spoo_value,
 
          c.spoo_value,
 
          d.spoo_value,
 
          e.spoo_value,
 
          f.spoo_value,
 
          g.spoo_value,
 
          h.spoo_value,
 
          i.spoo_value,
 
          j.spoo_value;

Surprisingly, it doesn't crash with community mysql-5.5.35, so it is probably a MariaDB-only bug.

(1) https://bugzilla.redhat.com/show_bug.cgi?id=1065676

 Comments   
Comment by Elena Stepanova [ 2014-02-24 ]

Stack trace from 5.3 revno 3765:

Program terminated with signal 11, Segmentation fault.
 
#0  0x0000000000a5af77 in _ma_cmp_block_unique (info=0x361c028, def=0x3636cf8, record=0x353a130 "\375\377\b", pos=256) at ma_blockrec.c:5166
 
5166	  error= _ma_read_block_record(info, old_record, pos);
 
#0  0x0000000000a5af77 in _ma_cmp_block_unique (info=0x361c028, def=0x3636cf8, record=0x353a130 "\375\377\b", pos=256) at ma_blockrec.c:5166
 
#1  0x0000000000a3f17a in _ma_check_unique (info=0x361c028, def=0x3636cf8, record=0x353a130 "\375\377\b", unique_hash=410834519, disk_pos=18446744073709551615) at ma_unique.c:66
 
#2  0x0000000000a405f4 in maria_write (info=0x361c028, record=0x353a130 "\375\377\b") at ma_write.c:136
 
#3  0x00000000009a3323 in ha_maria::write_row (this=0x3539980, buf=0x353a130 "\375\377\b") at ha_maria.cc:1172
 
#4  0x00000000005d0321 in handler::ha_write_tmp_row (this=0x3539980, buf=0x353a130 "\375\377\b") at sql_class.h:3823
 
#5  0x000000000075375b in end_unique_update (join=0x32e1120, join_tab=0x33e7b90, end_of_records=false) at sql_select.cc:17678
 
#6  0x00000000006f89a3 in JOIN_CACHE::generate_full_extensions (this=0x33f2ef8, rec_ptr=0x35179b6 "\001\001") at sql_join_cache.cc:2396
 
#7  0x00000000006f86ba in JOIN_CACHE::join_matching_records (this=0x33f2ef8, skip_last=false) at sql_join_cache.cc:2288
 
#8  0x00000000006f80e6 in JOIN_CACHE::join_records (this=0x33f2ef8, skip_last=false) at sql_join_cache.cc:2085
 
#9  0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2cd8, skip_last=false) at sql_join_cache.cc:2131
 
#10 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2ab8, skip_last=false) at sql_join_cache.cc:2131
 
#11 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2898, skip_last=false) at sql_join_cache.cc:2131
 
#12 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2678, skip_last=false) at sql_join_cache.cc:2131
 
#13 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2458, skip_last=false) at sql_join_cache.cc:2131
 
#14 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2238, skip_last=false) at sql_join_cache.cc:2131
 
#15 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f2018, skip_last=false) at sql_join_cache.cc:2131
 
#16 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f1df8, skip_last=false) at sql_join_cache.cc:2131
 
#17 0x00000000006f8263 in JOIN_CACHE::join_records (this=0x33f1c50, skip_last=false) at sql_join_cache.cc:2131
 
#18 0x000000000074f45f in sub_select_cache (join=0x32e1120, join_tab=0x33e5c50, end_of_records=true) at sql_select.cc:16036
 
#19 0x000000000074f6e5 in sub_select (join=0x32e1120, join_tab=0x33e5930, end_of_records=true) at sql_select.cc:16206
 
#20 0x000000000074f14c in do_select (join=0x32e1120, fields=0x0, table=0x3537a00, procedure=0x0) at sql_select.cc:15920
 
#21 0x000000000072c844 in JOIN::exec (this=0x32e1120) at sql_select.cc:2333
 
#22 0x000000000072ed8f in mysql_select (thd=0x31afb88, rref_pointer_array=0x31b2878, tables=0x3234428, wild_num=0, fields=..., conds=0x0, og_num=10, order=0x0, group=0x32e03c0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x32e1100, unit=0x31b2118, select_lex=0x31b2620) at sql_select.cc:3007
 
#23 0x00000000007257f5 in handle_select (thd=0x31afb88, lex=0x31b2078, result=0x32e1100, setup_tables_done_option=0) at sql_select.cc:288
 
#24 0x00000000006b452c in execute_sqlcom_select (thd=0x31afb88, all_tables=0x3234428) at sql_parse.cc:5172
 
#25 0x00000000006ab6c8 in mysql_execute_command (thd=0x31afb88) at sql_parse.cc:2305
 
#26 0x00000000006b6e93 in mysql_parse (thd=0x31afb88, rawbuf=0x32338c0 "SELECT Count(*)\nFROM   bpsocial_post AS tbl\nLEFT JOIN bpsocial_profile_object_option a\nON a.spoo_uid = sspo_uid\nAND a.spoo_option_id = 1\nLEFT JOIN bpsocial_profile_object_option b\nON b.spoo_uid = sspo"..., length=1125, found_semicolon=0x7fec6b83acb8) at sql_parse.cc:6173
 
#27 0x00000000006a8ea8 in dispatch_command (command=COM_QUERY, thd=0x31afb88, packet=0x322a459 "SELECT Count(*)\nFROM   bpsocial_post AS tbl\nLEFT JOIN bpsocial_profile_object_option a\nON a.spoo_uid = sspo_uid\nAND a.spoo_option_id = 1\nLEFT JOIN bpsocial_profile_object_option b\nON b.spoo_uid = sspo"..., packet_length=1125) at sql_parse.cc:1243
 
#28 0x00000000006a8194 in do_command (thd=0x31afb88) at sql_parse.cc:923
 
#29 0x00000000006a5025 in handle_one_connection (arg=0x31afb88) at sql_connect.cc:1231
 
#30 0x00007fec75029b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#31 0x00007fec743cca7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Test case:

CREATE TABLE `bpsocial_post` (
 
  `sspo_id` int(11) NOT NULL AUTO_INCREMENT,
 
  `sspo_uid` int(11) NOT NULL DEFAULT '0',
 
  `sspo_type` varchar(1) NOT NULL DEFAULT 'P',
 
  `sspo_text` longtext NOT NULL,
 
  `sspo_image` varchar(255) NOT NULL,
 
  `sspo_source` int(11) NOT NULL DEFAULT '0',
 
  `sspo_event_name` varchar(255) NOT NULL DEFAULT '',
 
  `sspo_event_location` varchar(255) NOT NULL DEFAULT '',
 
  `sspo_event_date` datetime DEFAULT NULL,
 
  `sspo_remote_title` varchar(255) NOT NULL,
 
  `sspo_remote_url` varchar(255) NOT NULL,
 
  `sspo_remote_desc` text NOT NULL,
 
  `sspo_remote_image` varchar(255) NOT NULL,
 
  `sspo_obj_status` varchar(1) NOT NULL DEFAULT 'A',
 
  `sspo_cr_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 
  `sspo_cr_uid` int(11) NOT NULL DEFAULT '0',
 
  `sspo_lu_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 
  `sspo_lu_uid` int(11) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`sspo_id`),
 
  KEY `post_uid` (`sspo_uid`,`sspo_cr_date`)
 
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
 
INSERT INTO `bpsocial_post` VALUES (1,2,'P','test1','',0,'','',NULL,'','','','','A','2013-09-30 00:19:32',2,'2013-09-30 00:19:32',2),(2,2,'P','bbb','',0,'','',NULL,'','','','','A','2013-10-02 15:06:35',2,'2013-10-02 15:06:35',2);
 
 
 
CREATE TABLE `bpsocial_profile_object_option` (
 
  `spoo_id` int(11) NOT NULL AUTO_INCREMENT,
 
  `spoo_user_type_id` int(11) NOT NULL DEFAULT '0',
 
  `spoo_uid` int(11) NOT NULL DEFAULT '0',
 
  `spoo_option_id` int(11) NOT NULL DEFAULT '0',
 
  `spoo_value` varchar(10000) NOT NULL,
 
  `spoo_obj_status` varchar(1) NOT NULL DEFAULT 'A',
 
  `spoo_cr_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 
  `spoo_cr_uid` int(11) NOT NULL DEFAULT '0',
 
  `spoo_lu_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 
  `spoo_lu_uid` int(11) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`spoo_id`),
 
  KEY `object_option_main_idx` (`spoo_user_type_id`,`spoo_uid`,`spoo_option_id`,`spoo_value`(255))
 
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;
 
INSERT INTO `bpsocial_profile_object_option` VALUES (19,1,2,6,'Dortmund','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2),(20,1,2,8,'49','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2);
 
 
 
SELECT Count(*)
 
FROM   bpsocial_post AS tbl
 
       LEFT JOIN bpsocial_profile_object_option a
 
              ON a.spoo_uid = sspo_uid
 
                 AND a.spoo_option_id = 1
 
       LEFT JOIN bpsocial_profile_object_option b
 
              ON b.spoo_uid = sspo_uid
 
                 AND b.spoo_option_id = 2
 
       LEFT JOIN bpsocial_profile_object_option c
 
              ON c.spoo_uid = sspo_uid
 
                 AND c.spoo_option_id = 3
 
       LEFT JOIN bpsocial_profile_object_option d
 
              ON d.spoo_uid = sspo_uid
 
                 AND d.spoo_option_id = 5
 
       LEFT JOIN bpsocial_profile_object_option e
 
              ON e.spoo_uid = sspo_uid
 
                 AND e.spoo_option_id = 4
 
       LEFT JOIN bpsocial_profile_object_option f
 
              ON f.spoo_uid = sspo_uid
 
                 AND f.spoo_option_id = 11
 
       LEFT JOIN bpsocial_profile_object_option g
 
              ON g.spoo_uid = sspo_uid
 
                 AND g.spoo_option_id = 7
 
       LEFT JOIN bpsocial_profile_object_option h
 
              ON h.spoo_uid = sspo_uid
 
                 AND h.spoo_option_id = 10
 
       LEFT JOIN bpsocial_profile_object_option i
 
              ON i.spoo_uid = sspo_uid
 
                 AND i.spoo_option_id = 18
 
       LEFT JOIN bpsocial_profile_object_option j
 
              ON j.spoo_uid = sspo_uid
 
                 AND j.spoo_option_id = 6
 
GROUP  BY a.spoo_value,
 
          b.spoo_value,
 
          c.spoo_value,
 
          d.spoo_value,
 
          e.spoo_value,
 
          f.spoo_value,
 
          g.spoo_value,
 
          h.spoo_value,
 
          i.spoo_value,
 
          j.spoo_value;

Comment by Michael Widenius [ 2014-03-10 ]

The problem is that the temporary table we have to create to store the interim result has a row length of 300K. We try to allocate on row on the stack (which works) but next call causes a segmentation fault.

I will fix this by not allocating bigger rows than 16K on the stack for rows.

To avoid this bug, do one of the following:

  • Increase size of the thread-stack startup options (288K by default). 300K was needed for the above case.
  • Change some of the big VARCHAR to BLOB as BLOB only takes 16 bytes in the record buffer
Comment by Michael Widenius [ 2014-03-10 ]

Fixed by using malloc() instead of alloca() for big records
Fixed for 10.0. will do a separate fix for 5.5

Comment by Michael Widenius [ 2014-03-10 ]

reopen to mark that not fixed in 5.3

Comment by Michael Widenius [ 2014-03-10 ]

State updated

Comment by Honza Horak [ 2014-03-11 ]

Thanks for the quick fix. However, when applying for mariadb-5.5.36, the test didn't pass. Couldn't the output change in 5.6, so the expected result should be different in 5.5?

main.stack-crash                         [ fail ]
 
        Test ended at 2014-03-11 19:44:53
 
CURRENT_TEST: main.stack-crash
 
--- /builddir/build/BUILD/mariadb-5.5.36/mysql-test/r/stack-crash.result	2014-03-11 17:38:41.768424086 +0000
 
+++ /builddir/build/BUILD/mariadb-5.5.36/mysql-test/r/stack-crash.reject	2014-03-11 19:44:53.001623144 +0000
 
@@ -1,7 +1,7 @@
 
 drop table if exists t1,t2;
 
 Warnings:
 
-Note	1051	Unknown table 'test.t1'
 
-Note	1051	Unknown table 'test.t2'
 
+Note	1051	Unknown table 't1'
 
+Note	1051	Unknown table 't2'
 
 CREATE TABLE t1 (
 
 `sspo_id` int(11) NOT NULL AUTO_INCREMENT,
 
 `sspo_uid` int(11) NOT NULL DEFAULT '0',
 
mysqltest: Result length mismatch
 
 - saving '/builddir/build/BUILD/mariadb-5.5.36/mysql-test/var/log/main.stack-crash/' to '/builddir/build/BUILD/mariadb-5.5.36/mysql-test/var/log/main.stack-crash/'

Comment by Sergei Golubchik [ 2014-03-12 ]

Yes, the note message text is slightly different there. Simply update the test result.

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