[#MDEV-8525] mariadb 10.0.20 crashing when data is read by Kodi media center (http://kodi.tv).

Comment by asavah [ 2015-07-23 ]

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:

SELECT albumview.*,albumartistview.* FROM albumview LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum WHERE albumview.idAlbum = 1 ORDER BY albumartistview.iOrder

Surprisingly removing ORDER BY succeeds.

SELECT albumview.*,albumartistview.* FROM albumview LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum WHERE albumview.idAlbum = 1

Comment by mccurly [ 2015-07-24 ]

@asavah Thanks for reporting this. I am also afflicted by this issue. Since I am a beginner in mysql and mariadb, could you please tell me how did you find what was the instruction that was crashing the server? I have some spare time now and I can help in finding out more details about this disrupting issue, if, of course, you feel that it isn't much of a bother. I could also learn along the way.
I am particularly interested in knowing if you have a debugging version of mariadb and if so, did you follow the debugging way to get to the instruction that faltered the server.
If you are like me, who doesn't have one such version installed (yet), did you use other means to pin-point the culprit?

Thanks in advance.

Comment by Elena Stepanova [ 2015-07-25 ]

Aqnaris, asavah,

Can any of you provide a dump of the involved tables? The visible ones are albumview and albumartistview, but if they are really views, there will be underlying tables, we need them as well. The dump can be uploaded to ftp.askmonty.org/private, only MariaDB developers will have access to it.
If the data in the tables is confidential and you cannot provide it, please paste the output of

SHOW CREATE TABLE albumview
SHOW TABLE STATUS LIKE 'albumview'
SHOW INDEX IN albumview
same for albumartistview and underlying tables, if any.
Please also attach your cnf file(s).

Thanks.

Comment by mccurly [ 2015-07-25 ]

Hello,

Unfortunately I have downgraded kodi to 14.2 version where this is not apparent.

But I tried to reproduce the facts and I found out that kodi v.15 errors out at this point:

09:52:28 T:140190814427136 ERROR: Unable to open database: MyMusic52 [2002](Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (111)

The /run/mysqld/mysqld.sock socket file exists and It is recognized as such.

These may be important from the mariadb my.cnf file:

The following options will be passed to all MariaDB clients
[client]
#password = your_password
port = 3306
socket = /run/mysqld/mysqld.sock

Also, this is my mysqld log

● mysqld.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sáb 2015-07-25 10:54:18 WEST; 2h 21min ago
Process: 5794 ExecStartPost=/usr/bin/mysqld-post (code=exited, status=0/SUCCESS)
Main PID: 5793 (mysqld)
CGroup: /system.slice/mysqld.service
└─5793 /usr/bin/mysqld --pid-file=/run/mysqld/mysqld.pid

Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 4303309
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] Plugin 'FEEDBACK' is disabled.
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] Recovering after a crash using mysql-bin
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] Starting crash recovery...
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] Crash recovery finished.
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] Server socket created on IP: '192.168.1.16'.
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] Event Scheduler: Loaded 0 events
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: 150725 10:54:18 [Note] /usr/bin/mysqld: ready for connections.
Jul 25 10:54:18 (cut out for privacy concerns)[5793]: Version: '10.0.20-MariaDB-log' socket: '/run/mysqld/mysqld.sock' port: 3306 MariaDB Server
Jul 25 10:54:18 (cut out for privacy concerns) systemd[1]: Started MariaDB database server.

Hope it can be of some use.

Yours

Comment by Dave M. [ 2015-07-25 ]

Uploaded ~~MDEV-8525~~-my-cnf-MyMusic52-dump.tar.gz to ftp.askmonty.org/private as requested.

Comment by asavah [ 2015-07-25 ]

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.

Comment by mccurly [ 2015-07-28 ]

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.

Comment by Elena Stepanova [ 2015-07-29 ]

Aqnaris,

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?

Comment by asavah [ 2015-07-29 ]

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

Comment by Elena Stepanova [ 2015-07-30 ]

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.

Comment by Sergei Petrunia [ 2015-10-23 ]

EXPLAIN works:

+------+-------------+--------------+--------+-----------------------------------+------------------+---------+--------------------------+------+---------------------------------+

| id   | select_type | table        | type   | possible_keys                     | key              | key_len | ref                      | rows | Extra                           |

+------+-------------+--------------+--------+-----------------------------------+------------------+---------+--------------------------+------+---------------------------------+

|    1 | PRIMARY     | album        | const  | PRIMARY                           | PRIMARY          | 4       | const                    |    1 | Using temporary; Using filesort |

|    1 | PRIMARY     | album_artist | range  | idxAlbumArtist_1,idxAlbumArtist_2 | idxAlbumArtist_1 | 5       | NULL                     |    1 | Using where                     |

|    1 | PRIMARY     | artist       | eq_ref | PRIMARY                           | PRIMARY          | 4       | j2.album_artist.idArtist |    1 |                                 |

|    3 | SUBQUERY    | song         | ref    | idxSong7,idxSong3                 | idxSong7         | 5       | const                    |    1 |                                 |

+------+-------------+--------------+--------+-----------------------------------+------------------+---------+--------------------------+------+---------------------------------+

The query itself crashes in top-level select, when trying to switch to reading from the temporary table. It searches for the temp. table item for

  $48 = 0x1852a80 "(select min(j2.song.iTimesPlayed) from j2.song where (j2.song.idAlbum = 1))"

and the item returns NULL.

Comment by Sergei Petrunia [ 2015-10-23 ]

The temporary table actually has an item for the subquery:

(gdb) p dbug_print_item(fields.elem(15))

  $77 = 0x1852a80 "(select min(`j2`.`song`.`iTimesPlayed`) from `j2`.`song` where (`j2`.`song`.`idAlbum` = 1))"

(gdb) p fields.elem(15)

  $78 = (Item_direct_view_ref *) 0x7fffa803fe30

...

(gdb) p fields.elem(15)->ref[0]

  $80 = (Item_singlerow_subselect *) 0x7fffa8011d38

Comment by Sergei Petrunia [ 2015-10-23 ]

... However, both Item_direct_view_ref and Item_singlerow_subselect have result_field=NULL.

result_field is set in create_tmp_table.
Looking at that function.
Note that EXPLAIN shows that table album has type= const.

create_tmp_table has this code:

  while ((item=li++))

    Item::Type type=item->type();

    if (not_all_columns)

...

      if (item->const_item() && (int) hidden_field_count <= 0)

        continue; // We don't have to store this

We arrive to "We don't have to store this" for all items in albumview.*, including the iTimesPlayed subquery.

Comment by Sergei Petrunia [ 2015-10-23 ]

Items in the other table are also Item_direct_view objects, and create_tmp_table will call set_result_field for them:

(gdb) wher 4

  #0  Item_ref::set_result_field (this=0x7fff9c0400b0, field=0x7fff9c06c028) at /home/psergey/dev-git/10.0/sql/item.h:3439

  #1  0x00000000006cb079 in create_tmp_field (thd=0x2ed0960, table=0x7fff9c073bd8, item=0x7fff9c014740, type=Item::FIELD_ITEM, copy_func=0x7ffff7f80d80, from_field=0x7fff9c074d40, default_field=0x7fff9c074c18, group=false, modify_item=false, table_cant_handle_bit_fields=false, make_copy_field=false, convert_blob_length=0) at /home/psergey/dev-git/10.0/sql/sql_select.cc:15656

  #2  0x00000000006cc67d in create_tmp_table (thd=0x2ed0960, param=0x7fff9c03b9b0, fields=..., group=0x0, distinct=false, save_sum_fields=false, select_options=2147748608, rows_limit=18446744073709551615

Comment by Sergei Petrunia [ 2015-10-23 ]

.. however, change_refs_to_tmp_fields() seems to ignore the above difference. It has a loop that just goes through all items.

Need to discuss this with sanja.

Comment by Oleksandr Byelkin [ 2015-10-28 ]

revision-id: ad9ed479ab2cb711ce2b0de01f91eb03dfd0c4d6 (mariadb-10.0.21-59-gad9ed47)
parent(s): a9b5a8d5056af6c8f9b98a231ffa2de9ea20f215
committer: Oleksandr Byelkin
timestamp: 2015-10-28 08:34:08 +0100
message:

~~MDEV-8525~~ mariadb 10.0.20 crashing when data is read by Kodi media center (http://kodi.tv).

Item_direct_view_ref maintains its own item_const() method so should use it when asked of temporary table field to be in sync with it.

—

Comment by Sergei Petrunia [ 2015-10-28 ]

Ok to push.

I think we should ask elenst to make a test pass with RQG and relevant test load: queries with ORDER BY/GROUP BY/DISTINCT .

[MDEV-8525] mariadb 10.0.20 crashing when data is read by Kodi media center (http://kodi.tv). Created: 2015-07-22 Updated: 2018-05-30 Resolved: 2015-10-28
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Manipulation - Subquery
Affects Version/s:	10.0, 10.1
Fix Version/s:	10.0.22, 10.1.9

[MDEV-8525] mariadb 10.0.20 crashing when data is read by Kodi media center (http://kodi.tv). Created: 2015-07-22 Updated: 2018-05-30 Resolved: 2015-10-28