[MDEV-4643] MariaDB crashes consistently when trying a SELECT on VIEW with a UNION and an additional JOIN in SELECT Created: 2013-06-12  Updated: 2013-07-08  Due: 2013-06-28  Resolved: 2013-07-08

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.3, 5.5.31, 5.3.12
Fix Version/s: 10.0.4, 5.5.32, 5.3.13

Type: Bug Priority: Major
Reporter: Jens Tautenhahn Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:
  1. cat /etc/lsb-release
    DISTRIB_ID=Ubuntu
    DISTRIB_RELEASE=12.04
    DISTRIB_CODENAME=praecise
    DISTRIB_DESCRIPTION="Ubuntu 12.04.2 LTS"
  1. uname -a
    Linux prepaid1 3.2.0-45-generic #70-Ubuntu SMP Wed May 29 20:12:06 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
  1. free
    total used free shared buffers cached
    Mem: 1019508 439568 579940 0 68308 166068
    -/+ buffers/cache: 205192 814316
    Swap: 2097148 0 2097148

Issue Links:
Duplicate
duplicates MDEV-4610 SQL query crashes MariaDB with derive... Closed

 Description   

MariaDB crashes consistently with signal 11 when trying a SELECT on VIEW with a UNION and an additional JOIN in SELECT.

/var/log/syslog:

Jun 12 14:23:13 prepaid1 mysqld: 130612 14:23:13 [ERROR] mysqld got signal 11 ;
 
Jun 12 14:23:13 prepaid1 mysqld: This could be because you hit a bug. It is also possible that this binary
 
Jun 12 14:23:13 prepaid1 mysqld: or one of the libraries it was linked against is corrupt, improperly built,
 
Jun 12 14:23:13 prepaid1 mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: To report this bug, see http://kb.askmonty.org/en/reporting-bugs
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: We will try our best to scrape up some info that will hopefully help
 
Jun 12 14:23:13 prepaid1 mysqld: diagnose the problem, but since we have already crashed,
 
Jun 12 14:23:13 prepaid1 mysqld: something is definitely wrong and this may fail.
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: Server version: 5.5.29-MariaDB-mariadb1~precise-log
 
Jun 12 14:23:13 prepaid1 mysqld: key_buffer_size=67108864
 
Jun 12 14:23:13 prepaid1 mysqld: read_buffer_size=2097152
 
Jun 12 14:23:13 prepaid1 mysqld: max_used_connections=5
 
Jun 12 14:23:13 prepaid1 mysqld: max_threads=102
 
Jun 12 14:23:13 prepaid1 mysqld: thread_count=5
 
Jun 12 14:23:13 prepaid1 mysqld: It is possible that mysqld could use up to
 
Jun 12 14:23:13 prepaid1 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 693689 K  bytes of memory
 
Jun 12 14:23:13 prepaid1 mysqld: Hope that's ok; if not, decrease some variables in the equation.
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: Thread pointer: 0x0x7f664b5e9a30
 
Jun 12 14:23:13 prepaid1 mysqld: Attempting backtrace. You can use the following information to find out
 
Jun 12 14:23:13 prepaid1 mysqld: where mysqld died. If you see no messages after this, something went
 
Jun 12 14:23:13 prepaid1 mysqld: terribly wrong...
 
Jun 12 14:23:13 prepaid1 mysqld: stack_bottom = 0x7f6630302e40 thread_stack 0x48000
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648f0d2fb]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648b32aa1]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6647393cb0]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648acdaa4]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648ace2f7]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648c26436]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648a226d5]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648a2d25f]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648a2f1d0]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648a33a23]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f66489d7bc2]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f66489e1a83]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f66489e8cf7]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f66489e8fdf]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f66489eb04f]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f66489eb6c3]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648aa7e63]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6648aa7f61]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f664738be9a]
 
Jun 12 14:23:13 prepaid1 mysqld: ??:0(??)[0x7f6646abcccd]
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: Trying to get some variables.
 
Jun 12 14:23:13 prepaid1 mysqld: Some pointers may be invalid and cause the dump to abort.
 
Jun 12 14:23:13 prepaid1 mysqld: Query (0x7f664b5f5478): select beschreibung, betrag, kontoauszug.datum, menge, seriennummer,
 
Jun 12 14:23:13 prepaid1 mysqld: #011buchungen_id, buchungen_storno_id, buchungen_lizenzarten_art
 
Jun 12 14:23:13 prepaid1 mysqld: from kontoauszug
 
Jun 12 14:23:13 prepaid1 mysqld: join kunden on kunden.nr = kontoauszug.nr
 
Jun 12 14:23:13 prepaid1 mysqld: where kontoauszug.nr = '1342624662'
 
Jun 12 14:23:13 prepaid1 mysqld:   and kostenlos = (kunden.immer_kostenlos or (kunden.kostenlos_bis > now()))
 
Jun 12 14:23:13 prepaid1 mysqld: order by datum desc, sort desc
 
Jun 12 14:23:13 prepaid1 mysqld: limit 0, 10
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: Connection ID (thread ID): 34
 
Jun 12 14:23:13 prepaid1 mysqld: Status: NOT_KILLED
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: 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=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
 
Jun 12 14:23:13 prepaid1 mysqld:
 
Jun 12 14:23:13 prepaid1 mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
Jun 12 14:23:13 prepaid1 mysqld: information that should help you find out what is causing the crash.
 
Jun 12 14:23:13 prepaid1 mysqld_safe: Number of processes running now: 0
 
Jun 12 14:23:13 prepaid1 mysqld_safe: WSREP: not restarting wsrep node automatically
 
Jun 12 14:23:13 prepaid1 mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended

To reproduce the crash create tables and a view like this:

CREATE TABLE `kunden` (
 
  `nr` char(10) NOT NULL,
 
  `datum` datetime NOT NULL,
 
  `kostenlos_bis` datetime DEFAULT NULL,
 
  `immer_kostenlos` tinyint(1) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`nr`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `installationen` (
 
  `seriennummer` char(16) NOT NULL,
 
  `kdnr` char(10) NOT NULL,
 
  `orig_kdnr` char(10) DEFAULT NULL,
 
  PRIMARY KEY (`seriennummer`),
 
  KEY `fk_kunden` (`kdnr`),
 
  KEY `fk_installationen_kunden_orig_kdnr` (`orig_kdnr`),
 
  CONSTRAINT `fk_installationen_kunden_orig_kdnr` FOREIGN KEY (`orig_kdnr`) REFERENCES `kunden` (`nr`) ON DELETE SET NULL ON UPDATE CASCADE,
 
  CONSTRAINT `fk_kunden` FOREIGN KEY (`kdnr`) REFERENCES `kunden` (`nr`) ON DELETE CASCADE ON UPDATE CASCADE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `einzahlungen` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `datum` datetime NOT NULL,
 
  `betrag` decimal(9,2) NOT NULL,
 
  `kunden_nr` char(10) DEFAULT NULL,
 
  `installationen_seriennummer` char(16) DEFAULT NULL,
 
  `kostenlos` tinyint(1) NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `einzahlungen_kunden_installationen_datum` (`kunden_nr`,`installationen_seriennummer`,`datum`),
 
  KEY `fk_einzahlungen_kunden` (`kunden_nr`),
 
  KEY `fk_einzahlungen_installationen` (`installationen_seriennummer`),
 
  CONSTRAINT `fk_einzahlungen_installationen` FOREIGN KEY (`installationen_seriennummer`) REFERENCES `installationen` (`seriennummer`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `fk_einzahlungen_kunden` FOREIGN KEY (`kunden_nr`) REFERENCES `kunden` (`nr`) ON DELETE CASCADE ON UPDATE CASCADE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `buchungen` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `datum` datetime NOT NULL,
 
  `menge` int(11) DEFAULT NULL,
 
  `installationen_seriennummer` char(16) DEFAULT NULL,
 
  `tarife_nr` int(10) unsigned DEFAULT NULL,
 
  `lizenzarten_art` int(10) unsigned DEFAULT NULL,
 
  `kunden_nr` char(10) DEFAULT NULL,
 
  `kostenlos` tinyint(1) NOT NULL DEFAULT '0',
 
  `beschreibung` text NOT NULL,
 
  `tarif_preis` decimal(9,2) DEFAULT NULL,
 
  `tarif_menge` int(11) DEFAULT NULL,
 
  `storno_id` int(10) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `buchungen_kunden_tarife_datum` (`kunden_nr`,`installationen_seriennummer`,`tarife_nr`,`datum`),
 
  KEY `fk_buchungen_kunden` (`kunden_nr`),
 
  KEY `fk_buchungen_lizenzarten` (`lizenzarten_art`),
 
  KEY `fk_buchungen_installationen` (`installationen_seriennummer`) USING BTREE,
 
  KEY `fk_buchungen_tarife` (`tarife_nr`) USING BTREE,
 
  KEY `buchungen_storno_id` (`storno_id`),
 
  CONSTRAINT `fk_buchungen_installationen` FOREIGN KEY (`installationen_seriennummer`) REFERENCES `installationen` (`seriennummer`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `fk_buchungen_kunden` FOREIGN KEY (`kunden_nr`) REFERENCES `kunden` (`nr`) ON DELETE CASCADE ON UPDATE CASCADE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE VIEW `kontoauszug` AS
 
select `kunden`.`nr` AS `nr`,
 
	`einzahlungen`.`datum` AS `datum`,
 
	`einzahlungen`.`betrag` AS `betrag`,
 
	'Einzahlung' AS `beschreibung`,
 
	NULL AS `menge`,
 
	1 AS `sort`,
 
	`installationen`.`seriennummer` AS `seriennummer`,
 
	`einzahlungen`.`kostenlos` AS `kostenlos`,
 
	NULL AS `buchungen_id`,
 
	NULL AS `buchungen_storno_id`,
 
	NULL AS `buchungen_lizenzarten_art`
 
from ((`kunden`
 
	join `einzahlungen` on((`einzahlungen`.`kunden_nr` = `kunden`.`nr`)))
 
	left join `installationen` on((`installationen`.`seriennummer` = `einzahlungen`.`installationen_seriennummer`)))
 
 
 
union
 
 
 
select `kunden`.`nr` AS `nr`,
 
	`buchungen`.`datum` AS `datum`,
 
	((`buchungen`.`menge` * `buchungen`.`tarif_preis`) * -(1)) AS `betrag`,
 
	if(isnull(`buchungen`.`tarif_menge`),
 
	concat(if((`buchungen`.`menge` > 0),'Storno ',''),'Verbrauch: ',`buchungen`.`beschreibung`),concat('Verwendung: ',if((`buchungen`.`menge` > 1),concat(`buchungen`.`menge`,_utf8' x '),_utf8''),`buchungen`.`beschreibung`)) AS `beschreibung`,
 
	ifnull((`buchungen`.`menge` * `buchungen`.`tarif_menge`),`buchungen`.`menge`) AS `menge`,
 
	if(isnull(`buchungen`.`tarif_menge`),3,2) AS `sort`,
 
	`installationen`.`seriennummer` AS `seriennummer`,
 
	`buchungen`.`kostenlos` AS `kostenlos`,
 
	`buchungen`.`id` AS `buchungen_id`,
 
	`buchungen`.`storno_id` AS `buchungen_storno_id`,
 
	`buchungen`.`lizenzarten_art` AS `buchungen_lizenzarten_art`
 
from ((`kunden`
 
	join `buchungen` on((`buchungen`.`kunden_nr` = `kunden`.`nr`)))
 
	left join `installationen` on((`installationen`.`seriennummer` = `buchungen`.`installationen_seriennummer`)));
 
 
 
insert into kunden (nr, datum) values ('1342624662', now());

When submitting this SELECT MariaDB crashes:

– ****CRASH****

select beschreibung, betrag, kontoauszug.datum, menge, seriennummer,
 
	buchungen_id, buchungen_storno_id, buchungen_lizenzarten_art
 
from kontoauszug
 
join kunden on kunden.nr = kontoauszug.nr
 
where kontoauszug.nr = '1342624662'
 
  and kostenlos = (kunden.immer_kostenlos or (kunden.kostenlos_bis > now()))
 
order by datum desc, sort desc
 
limit 0, 10;



 Comments   
Comment by Patryk Pomykalski [ 2013-06-12 ]

Backtrace looks similar to MDEV-4610

Comment by Elena Stepanova [ 2013-06-12 ]

Indeed, it's most likely the same problem. I'll keep it open to re-check after MDEV-4610 is fixed.

#3 <signal handler called>
#4 0x0000000000758618 in DsMrr_impl::choose_mrr_impl (this=0x7f3e040a84c0, keyno=0, rows=2, flags=0x7f3e040b3f98, bufsz=0x7f3e040b3f9c, cost=0x7f3e0d8561f0) at /5.5/sql/multi_range_read.cc:1562
#5 0x00000000007582fd in DsMrr_impl::dsmrr_info (this=0x7f3e040a84c0, keyno=0, n_ranges=1, rows=2, key_parts=4294967295, bufsz=0x7f3e040b3f9c, flags=0x7f3e040b3f98, cost=0x7f3e0d8561f0) at /5.5/sql/multi_range_read.cc:1419
#6 0x0000000000b00843 in ha_maria::multi_range_read_info (this=0x7f3e040a8078, keyno=0, n_ranges=1, keys=2, key_parts=4294967295, bufsz=0x7f3e040b3f9c, flags=0x7f3e040b3f98, cost=0x7f3e0d8561f0) at /5.5/storage/maria/ha_maria.cc:3789
#7 0x00000000008ef739 in get_quick_select_for_ref (thd=0x20e7230, table=0x7f3e040a5dc8, ref=0x7f3e040b2268, records=2) at /5.5/sql/opt_range.cc:10545
#8 0x0000000000672f9b in create_sort_index (thd=0x20e7230, join=0x7f3e04097148, order=0x7f3e0403c398, filesort_limit=10, select_limit=10, is_order_by=false) at /5.5/sql/sql_select.cc:19223
#9 0x000000000064b047 in JOIN::exec (this=0x7f3e04097148) at /5.5/sql/sql_select.cc:2804
#10 0x000000000064ba85 in mysql_select (thd=0x20e7230, rref_pointer_array=0x20eaee8, tables=0x7f3e04014998, wild_num=0, fields=..., conds=0x7f3e0403c078, og_num=2, order=0x7f3e0403c398, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f3e04018838, unit=0x20ea548, select_lex=0x20eac40) at /5.5/sql/sql_select.cc:3054
#11 0x00000000006424a8 in handle_select (thd=0x20e7230, lex=0x20ea498, result=0x7f3e04018838, setup_tables_done_option=0) at /5.5/sql/sql_select.cc:318
#12 0x000000000061a504 in execute_sqlcom_select (thd=0x20e7230, all_tables=0x7f3e04014998) at /5.5/sql/sql_parse.cc:4642
#13 0x0000000000612f4a in mysql_execute_command (thd=0x20e7230) at /5.5/sql/sql_parse.cc:2196
#14 0x000000000061cda4 in mysql_parse (thd=0x20e7230, rawbuf=0x7f3e040537e8 "select beschreibung, betrag, kontoauszug.datum, menge, seriennummer,\nbuchungen_id, buchungen_storno_id, buchungen_lizenzarten_art\nfrom kontoauszug\njoin kunden on kunden.nr = kontoauszug.nr\nwhere konto"..., length=344, parser_state=0x7f3e0d8574f0) at /5.5/sql/sql_parse.cc:5760
#15 0x000000000061048a in dispatch_command (command=COM_QUERY, thd=0x20e7230, packet=0x214bff1 "select beschreibung, betrag, kontoauszug.datum, menge, seriennummer,\nbuchungen_id, buchungen_storno_id, buchungen_lizenzarten_art\nfrom kontoauszug\njoin kunden on kunden.nr = kontoauszug.nr\nwhere konto"..., packet_length=344) at /5.5/sql/sql_parse.cc:1068
#16 0x000000000060f6cb in do_command (thd=0x20e7230) at /5.5/sql/sql_parse.cc:794
#17 0x000000000071b343 in do_handle_one_connection (thd_arg=0x20e7230) at /5.5/sql/sql_connect.cc:1266
#18 0x000000000071ad2a in handle_one_connection (arg=0x20e7230) at /5.5/sql/sql_connect.cc:1181
#19 0x0000000000977ea6 in pfs_spawn_thread (arg=0x2107690) at /5.5/storage/perfschema/pfs.cc:1015
#20 0x00007f3e11743e9a in start_thread (arg=0x7f3e0d858700) at pthread_create.c:308
#21 0x00007f3e10a37cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Comment by Sergei Golubchik [ 2013-07-05 ]

I'll take it, I've already tested it

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