Details
Critical
Description
Reported the problem here: http://trac.kodi.tv/ticket/16126 and here: http://forum.kodi.tv/showthread.php?tid=232834
Mariadb seems to crash when data is read?! by Kodi
Attached is journalctl output of the crash plus a logfile of Kodi.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Uploaded full dump rpi2_music53.sql.gz as requested.
It only has one entry because while trying to troubleshoot the issue I tried dropping the database and letting Kodi recreate it.
The crash can be reproduced with this dump.
Hello. Unfortunately, I don't have much/any feedback to give as of yet. I am currently using mysql from oracle. With their version of sql this problem doesn't occur. I am sure this problem affects others. Were any changes made to mariadb and/or any of it's dependencies? If so I am willing to try them out again.
Yours.
Thanks a lot, with the provided dump I can reproduce the issue. Do I understand correctly that the table structures (column names, indexes and such) come from the media center and thus are public, and only the data should be kept private?
Yes.
In the settings file we only provide mysql connection data to mediacenter.
Everything (db,tables,views,indexes & etc) is created and populated by Kodi application logic.
Data is populated by scanning the library using tags embedded in the media files and/or online scrapers.
Kodi mysql logic is here: https://github.com/xbmc/xbmc/blob/4b122b1eaf97de975ee199eb702123be408ee86e/xbmc/dbwrappers/mysqldataset.cpp
Stack trace from 10.0 83ba48b7c670f6dba465325cafd808c91f551544
#3 <signal handler called> #4 0x0000000000883f06 in Item_field::Item_field (this=0x7feafed704d0, f=0x0) at 10.0/sql/item.cc:2291 #5 0x000000000090a74e in Item_subselect::get_tmp_table_item (this=0x7feafedf63c0, thd_arg=0x7feb08903070) at 10.0/sql/item_subselect.cc:877 #6 0x0000000000891b5d in Item_ref::get_tmp_table_item (this=0x7feafee2a858, thd=0x7feb08903070) at 10.0/sql/item.cc:7450 #7 0x000000000089b9a3 in Item_direct_view_ref::get_tmp_table_item (this=0x7feafee2a858, thd=0x7feb08903070) at 10.0/sql/item.h:3773 #8 0x00000000006e8ef9 in change_refs_to_tmp_fields (thd=0x7feb08903070, ref_pointer_array=0x7feafee22520, res_selected_fields=..., res_all_fields=..., elements=24, all_fields=...) at 10.0/sql/sql_select.cc:22475 #9 0x00000000006b8a84 in JOIN::exec_inner (this=0x7feafec1a340) at 10.0/sql/sql_select.cc:2652 #10 0x00000000006b7946 in JOIN::exec (this=0x7feafec1a340) at 10.0/sql/sql_select.cc:2368 #11 0x00000000006bacdb in mysql_select (thd=0x7feb08903070, rref_pointer_array=0x7feb08907330, tables=0x7feafec144f8, wild_num=2, fields=..., conds=0x7feafec15d58, og_num=1, order=0x7feafec15fe0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7feafec1a320, unit=0x7feb089069c8, select_lex=0x7feb089070b8) at 10.0/sql/sql_select.cc:3308 #12 0x00000000006b12c5 in handle_select (thd=0x7feb08903070, lex=0x7feb08906900, result=0x7feafec1a320, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:373 #13 0x0000000000685a13 in execute_sqlcom_select (thd=0x7feb08903070, all_tables=0x7feafec144f8) at 10.0/sql/sql_parse.cc:5274 #14 0x000000000067dd44 in mysql_execute_command (thd=0x7feb08903070) at 10.0/sql/sql_parse.cc:2562 #15 0x000000000068856b in mysql_parse (thd=0x7feb08903070, rawbuf=0x7feafec14088 "SELECT albumview.*,albumartistview.* FROM albumview LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum WHERE albumview.idAlbum = 1 ORDER BY albumartistview.iOrder", length=184, parser_state=0x7feb13fd6600) at 10.0/sql/sql_parse.cc:6529 #16 0x000000000067af57 in dispatch_command (command=COM_QUERY, thd=0x7feb08903070, packet=0x7feb08679071 "SELECT albumview.*,albumartistview.* FROM albumview LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum WHERE albumview.idAlbum = 1 ORDER BY albumartistview.iOrder", packet_length=184) at 10.0/sql/sql_parse.cc:1308 #17 0x000000000067a23d in do_command (thd=0x7feb08903070) at 10.0/sql/sql_parse.cc:999 #18 0x000000000079825a in do_handle_one_connection (thd_arg=0x7feb08903070) at 10.0/sql/sql_connect.cc:1378 #19 0x0000000000797fb9 in handle_one_connection (arg=0x7feb08903070) at 10.0/sql/sql_connect.cc:1293 #20 0x0000000000cd7fcf in pfs_spawn_thread (arg=0x7feb08bf13f0) at 10.0/storage/perfschema/pfs.cc:1860 #21 0x00007feb13c10b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #22 0x00007feb11ec695d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Test case
--source include/have_xtradb.inc DROP TABLE IF EXISTS `song`; CREATE TABLE `song` ( `idSong` int(11) NOT NULL AUTO_INCREMENT, `idAlbum` int(11) DEFAULT NULL, `idPath` int(11) DEFAULT NULL, `strArtists` text, `strGenres` text, `strTitle` varchar(512) DEFAULT NULL, `iTrack` int(11) DEFAULT NULL, `iDuration` int(11) DEFAULT NULL, `iYear` int(11) DEFAULT NULL, `dwFileNameCRC` text, `strFileName` text, `strMusicBrainzTrackID` text, `iTimesPlayed` int(11) DEFAULT NULL, `iStartOffset` int(11) DEFAULT NULL, `iEndOffset` int(11) DEFAULT NULL, `idThumb` int(11) DEFAULT NULL, `lastplayed` varchar(20) DEFAULT NULL, `rating` char(1) DEFAULT '0', `comment` text, `mood` text, PRIMARY KEY (`idSong`), UNIQUE KEY `idxSong7` (`idAlbum`,`strMusicBrainzTrackID`(36)), KEY `idxSong` (`strTitle`(255)), KEY `idxSong1` (`iTimesPlayed`), KEY `idxSong2` (`lastplayed`), KEY `idxSong3` (`idAlbum`), KEY `idxSong6` (`idPath`,`strFileName`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `song` VALUES (1,1,1,'strArtists1','strGenres1','strTitle1',1,100,2000,NULL,'strFileName1','strMusicBrainzTrackID1',0,0,0,NULL,NULL,'0','',''),(2,2,2,'strArtists2','strGenres2','strTitle2',2,200,2001,NULL,'strFileName2','strMusicBrainzTrackID2',0,0,0,NULL,NULL,'0','',''); DROP TABLE IF EXISTS `album`; CREATE TABLE `album` ( `idAlbum` int(11) NOT NULL AUTO_INCREMENT, `strAlbum` varchar(256) DEFAULT NULL, `strMusicBrainzAlbumID` text, `strArtists` text, `strGenres` text, `iYear` int(11) DEFAULT NULL, `idThumb` int(11) DEFAULT NULL, `bCompilation` int(11) NOT NULL DEFAULT '0', `strMoods` text, `strStyles` text, `strThemes` text, `strReview` text, `strImage` text, `strLabel` text, `strType` text, `iRating` int(11) DEFAULT NULL, `lastScraped` varchar(20) DEFAULT NULL, `dateAdded` varchar(20) DEFAULT NULL, `strReleaseType` text, PRIMARY KEY (`idAlbum`), UNIQUE KEY `idxAlbum_2` (`strMusicBrainzAlbumID`(36)), KEY `idxAlbum` (`strAlbum`(255)), KEY `idxAlbum_1` (`bCompilation`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `album` VALUES (1,'strAlbum1','strMusicBrainzAlbumID1','strArtists1','strGenres1',2000,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'album'); DROP TABLE IF EXISTS `album_artist`; CREATE TABLE `album_artist` ( `idArtist` int(11) DEFAULT NULL, `idAlbum` int(11) DEFAULT NULL, `strJoinPhrase` text, `boolFeatured` int(11) DEFAULT NULL, `iOrder` int(11) DEFAULT NULL, `strArtist` text, UNIQUE KEY `idxAlbumArtist_1` (`idAlbum`,`idArtist`), UNIQUE KEY `idxAlbumArtist_2` (`idArtist`,`idAlbum`), KEY `idxAlbumArtist_3` (`boolFeatured`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `album_artist` VALUES (1,1,'',0,0,'strArtist1'); DROP TABLE IF EXISTS `artist`; CREATE TABLE `artist` ( `idArtist` int(11) NOT NULL AUTO_INCREMENT, `strArtist` varchar(256) DEFAULT NULL, `strMusicBrainzArtistID` text, `strBorn` text, `strFormed` text, `strGenres` text, `strMoods` text, `strStyles` text, `strInstruments` text, `strBiography` text, `strDied` text, `strDisbanded` text, `strYearsActive` text, `strImage` text, `strFanart` text, `lastScraped` varchar(20) DEFAULT NULL, `dateAdded` varchar(20) DEFAULT NULL, PRIMARY KEY (`idArtist`), UNIQUE KEY `idxArtist1` (`strMusicBrainzArtistID`(36)), KEY `idxArtist` (`strArtist`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `artist` VALUES (1,'strArtist1','strMusicBrainzArtistID',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); CREATE VIEW `albumview` AS select `album`.`idAlbum` AS `idAlbum`,`album`.`strAlbum` AS `strAlbum`,`album`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`album`.`strArtists` AS `strArtists`,`album`.`strGenres` AS `strGenres`,`album`.`iYear` AS `iYear`,`album`.`strMoods` AS `strMoods`,`album`.`strStyles` AS `strStyles`,`album`.`strThemes` AS `strThemes`,`album`.`strReview` AS `strReview`,`album`.`strLabel` AS `strLabel`,`album`.`strType` AS `strType`,`album`.`strImage` AS `strImage`,`album`.`iRating` AS `iRating`,`album`.`bCompilation` AS `bCompilation`,(select min(`song`.`iTimesPlayed`) from `song` where (`song`.`idAlbum` = `album`.`idAlbum`)) AS `iTimesPlayed`,`album`.`strReleaseType` AS `strReleaseType` from `album`; CREATE VIEW `albumartistview` AS select `album_artist`.`idAlbum` AS `idAlbum`,`album_artist`.`idArtist` AS `idArtist`,`artist`.`strArtist` AS `strArtist`,`artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,`album_artist`.`boolFeatured` AS `boolFeatured`,`album_artist`.`strJoinPhrase` AS `strJoinPhrase`,`album_artist`.`iOrder` AS `iOrder` from (`album_artist` join `artist` on((`album_artist`.`idArtist` = `artist`.`idArtist`))); SELECT albumview.*,albumartistview.* FROM albumview LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum WHERE albumview.idAlbum = 1 ORDER BY albumartistview.iOrder;
Note: it's exactly the same data structures and query as provided, I only obfuscated the data. The views at least can be simplified, but I intentionally leave them as is to be sure that the initial problem gets fixed rather than some variation of it.
The problem appeared in 10.0 tree some time between 10.0.17 and 10.0.18 releases. I could not find the exact revision that caused it, my search led to the merge 5.5=>10.0, but it's not reproducible on 5.5, so the further search got stuck. Still, I set 'fix version' to 5.5 in case it originates from there. Please adjust if needed.
Just stumbled into this issue.
It's not Kodi fault, the same query crashes the server if executed from shell (mysql-client) or phpmyadmin.
This issue could be used for DoS.
The query thats crashes mariadb server:
Surprisingly removing ORDER BY succeeds.