Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4643

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.3, 5.5.31, 5.3.12
    • Fix Version/s: 10.0.4, 5.5.32, 5.3.13
    • Component/s: None
    • Labels:
      None
    • Environment:

      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;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                sesom42 Jens Tautenhahn
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: