Open mslaby opened 2 months ago
Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder
) you are listed as a code owner for? Thanks!
(message by CodeOwnersMention)
recorder documentation recorder source (message by IssueLinks)
SELECT anon_1.metadata_id,
anon_1.state,
anon_1.last_updated_ts,
anon_1.attributes
FROM (SELECT anon_2.metadata_id AS metadata_id,
anon_2.state AS state,
anon_2.last_updated_ts AS last_updated_ts,
anon_2.attributes AS attributes
FROM (SELECT states.metadata_id AS metadata_id,
states.state AS state,
0 AS last_updated_ts,
CASE
WHEN ( state_attributes.shared_attrs IS NULL ) THEN
states.attributes
ELSE state_attributes.shared_attrs
END AS attributes
FROM states
INNER JOIN (SELECT states.metadata_id AS
max_metadata_id
,
Max(states.last_updated_ts) AS max_last_updated
FROM states
WHERE states.last_updated_ts >= 1719718797.70615e0
AND states.last_updated_ts < 1719728699.999999e0
AND states.metadata_id IN (
10987, 10988, 10989, 10990,
10991, 10992, 10993, 10994,
11472, 11475, 11474, 11473,
12336, 15511, 12337, 15510,
15512, 15513, 913, 15486,
914, 12286, 853, 876,
877, 881, 10817, 920,
11996, 11997, 11998, 11999,
12000, 12001, 12002, 12003,
12004, 12005, 12006, 12007,
12008, 12009, 12010, 13021,
13022, 13023, 13024, 13027,
13028, 13025, 13026, 20895,
20896, 20897, 20898, 20899,
20900, 20901, 20902, 21117,
21118, 13039, 13040, 13041,
13042, 13043, 13044, 13045,
13046, 13047, 13048, 11527,
11528, 11529, 19929, 19930,
19931, 12379, 12380, 12381,
12373, 12374, 12375, 12251,
12252, 12253, 12388, 12389,
12390, 12391, 12392, 12393,
20856, 20857, 20858, 13907,
13908, 13909, 14861, 14862,
14863, 12350, 12351, 12352,
12360, 12361, 12362, 12327,
12328, 12329, 11545, 11546,
11547, 11548, 11549, 11550,
11551, 11552, 11553, 11554,
11555, 11556, 11557, 11558,
11559, 11560, 11561, 11562,
11563, 11564, 11565, 12975,
12976, 12977, 12978, 12979,
12980, 12981, 12982, 12983,
12984, 12985, 12986, 12987,
12988, 12989, 12990, 12991,
12992, 12993, 12994, 12995,
12996, 12997, 12998, 12999,
13000, 13001, 13002, 13003,
13004, 13005, 13006, 13007,
13008, 13011, 13012, 13015,
13016, 13009, 13010, 13013,
13014, 13017, 13018, 11569,
11570, 11571, 12382, 12383,
12384, 19982, 19983, 19984,
12324, 12325, 12326, 20835,
20836, 20837, 12357, 12358,
12359, 13886, 13887, 13888,
12248, 12249, 12250, 12394,
12395, 12396, 12347, 12348,
12349, 12385, 12386, 12387,
12376, 12377, 12378, 14866,
14867, 14868, 11587, 11588,
11589, 11590, 11591, 11592,
11593, 11594, 11595, 11596,
11597, 11598, 11599, 11600,
11601, 11602, 11603, 11604,
13050, 13051, 13052, 13053,
13054, 13055, 13056, 13057,
21181, 21182, 21183, 21184,
21185, 21186, 21187, 21188,
21189, 21190, 21191, 21192,
21193, 21194, 21195, 21196,
21197, 21198, 21199, 21200,
12168, 12169, 12170, 13076,
13077, 13078, 13079, 13080,
13081, 13082, 13083, 13084,
13085, 13086, 13087, 13088,
13089, 13090, 13091, 13092,
13093, 13094, 13095, 13096,
13097, 13098, 13099, 13100,
13101, 13102, 13103, 13104,
13105, 13108, 13109, 13106,
13107, 13110, 13111, 821,
814, 11930, 10725, 10726,
10727, 10728, 11931, 11981,
10788, 10731, 10737, 10738,
12271, 10735, 10736, 10777,
11937, 10740, 11969, 10734,
10803, 10795, 10801, 10813,
10739, 10822, 10797, 10799,
11929, 11932, 11934, 11966,
12014, 12017, 12020, 12098,
12192, 12177, 12195, 12330,
10778, 15444, 10819, 12299,
15448, 12106, 19678, 10776,
11939, 10742, 10743, 10744,
10745, 11940, 11982, 10789,
10748, 10754, 10755, 12272,
10752, 10753, 10781, 11946,
10757, 11970, 10751, 10804,
10796, 10802, 10814, 10756,
10823, 10798, 10800, 11938,
11941, 11943, 11967, 12015,
12018, 12021, 12099, 12193,
12178, 12196, 12331, 10782,
15445, 10820, 12300, 15449,
12107, 19679, 10780, 10717,
10718, 10719, 10720, 13119,
13120, 13121, 13122, 13123,
18874, 13124, 13125, 13126,
13127, 13128, 18875, 5650,
12112, 792, 794, 605,
606, 607, 608, 609,
238, 612, 241, 771,
767, 775, 616, 617,
618, 619, 620, 621,
622, 623, 798, 800,
624, 625, 626, 627,
628, 629, 630, 631,
778, 779, 780, 598,
599, 600, 601, 759,
760, 586, 291, 292,
293, 294, 295, 331,
332, 371, 333, 372,
334, 373, 335, 296,
336, 374, 297, 337,
298, 411, 338, 303,
343, 304, 344, 305,
345, 306, 346, 307,
347, 308, 348, 309,
349, 310, 350, 315,
375, 412, 376, 413,
377, 414, 378, 415,
416, 417, 418, 383,
384, 385, 386, 423,
387, 424, 425, 388,
426, 389, 427, 428,
429, 355, 316, 390,
317, 356, 318, 357,
319, 358, 320, 359,
321, 360, 322, 361,
362, 430, 395, 396,
397, 398, 435, 399,
436, 400, 437, 401,
438, 402, 439, 440,
441, 442, 15473, 15485,
15472, 15475, 15478, 15476,
15477, 15474, 5614, 5615,
5616, 5619, 5622, 12303,
5623, 5624, 12305, 5625,
7282, 7283, 7284, 11913,
11914, 11915, 11916, 11923,
11925, 12011, 12023, 12033,
12035, 12062, 595, 596,
597, 220, 222, 10981,
10982, 10983, 10984, 5605,
5606, 5607, 5610, 5632,
12306, 5633, 5634, 12308,
5635, 14, 12, 12283,
12276, 12282, 12281, 10328,
10329, 10332, 10333, 10334,
10335, 10336, 10337, 10338,
10340, 10344, 10348, 10352,
10354, 10361, 10355, 10831,
10832, 11465, 11466, 11680,
11681, 11682, 11683, 11684,
11685, 11885, 11886, 12123,
12124, 12125, 12128, 12129,
12130, 12159, 12160, 12162,
12163, 12164, 12165, 12166,
12167, 12180, 12181, 12182,
12183, 12184, 12185, 12186,
12187, 12188, 12189, 12190,
12191, 12211, 12212, 12213,
12214, 12215, 12216, 12217,
12218, 12219, 12220, 12221,
12222, 12230, 12231, 12232,
12233, 12234, 12235, 12236,
12237, 12238, 12239, 12240,
12241, 12242, 12243, 12244,
12245, 12246, 12247, 12254,
12255, 12256, 12257, 12258,
12259, 12312, 12313, 12314,
12315, 12316, 12317, 12318,
12319, 12320, 12321, 12322,
12323, 13029, 13030, 13031,
13032, 13033, 13034, 13035,
13036, 13037, 13038, 13058,
13059, 13060, 13061, 13062,
13063, 13064, 13065, 13066,
13067, 13068, 13069, 13070,
13071, 13072, 13073, 13074,
13075, 13711, 13712, 13713,
13714, 13715, 13716, 13717,
13718, 13719, 13720, 13745,
13746, 13747, 13748, 13749,
13750, 13751, 13752, 13753,
13754, 13755, 13756, 13757,
13758, 13759, 13760, 13761,
13762, 13763, 13764, 13765,
13766, 13767, 13768, 13769,
13770, 13771, 13772, 13773,
13774, 13775, 13776, 13777,
13778, 13779, 13780, 13781,
13782, 13783, 13784, 13785,
13786, 13787, 13788, 13789,
13790, 13791, 13792, 13793,
13794, 13860, 13861, 13862,
13863, 13864, 13865, 13866,
13867, 13868, 13869, 13870,
13871, 13872, 13873, 13874,
13875, 13876, 13877, 13878,
13879, 13880, 13881, 13882,
13883, 13884, 13885, 13889,
13890, 13891, 13892, 13893,
13894, 13895, 13896, 13897,
13898, 13899, 13900, 13901,
13902, 13903, 13904, 13905,
13906, 13910, 13911, 13912,
13913, 13914, 13915, 13916,
13917, 13918, 13919, 13920,
13921, 13922, 13923, 13924,
13925, 13926, 13927, 13928,
13929, 13930, 13931, 13932,
13933, 13934, 13935, 13936,
13937, 13938, 13939, 13940,
13941, 14826, 14827, 14828,
14829, 14830, 14831, 14832,
14833, 14834, 14835, 14836,
14837, 14838, 14839, 14840,
14841, 14842, 14843, 14864,
14865, 14869, 14870, 15450,
15451, 15452, 15453, 15454,
15455, 15456, 15457, 15487,
15488, 17113, 17114, 17115,
17116, 17117, 17118, 17119,
17120, 17121, 17122, 17123,
17124, 17125, 17126, 17127,
17128, 17129, 17130, 17330,
17331, 17332, 17333, 17334,
17335, 17336, 17337, 17338,
17339, 17340, 17341, 17342,
17343, 17344, 17345, 17346,
17347, 17401, 17402, 17403,
17404, 17405, 17406, 17407,
17408, 17409, 17410, 18872,
18873, 19623, 19624, 19625,
19626, 19680, 19681, 19682,
19683, 19684, 19685, 19686,
19687, 19688, 19689, 19690,
19691, 19692, 19693, 19694,
19695, 19696, 19697, 19698,
19699, 19700, 19723, 19724,
19725, 19726, 19727, 19728,
19729, 19730, 19731, 19732,
19733, 19734, 19735, 19736,
19737, 19738, 19739, 19740,
19741, 19742, 19743, 19744,
19745, 19746, 19747, 19748,
19753, 19754, 19755, 19756,
19757, 19758, 19759, 19760,
19761, 19762, 19763, 19764,
19765, 19766, 19767, 19768,
19769, 19770, 19771, 19772,
19773, 19774, 19775, 19776,
19777, 19778, 19779, 19780,
19781, 19782, 19783, 19784,
19785, 19786, 19787, 19788,
19789, 19790, 19791, 19792,
19793, 19794, 19795, 19796,
19797, 19798, 19799, 19800,
19801, 19802, 19803, 19804,
19805, 19806, 19807, 19808,
19809, 19810, 19811, 19812,
19813, 19814, 19815, 19816,
19817, 19818, 19825, 19826,
19827, 19828, 19829, 19830,
19831, 19832, 19833, 19834,
19835, 19836, 19837, 19838,
19839, 19840, 19841, 19842,
19843, 19844, 19845, 19846,
19847, 19848, 19849, 19850,
19851, 19852, 19853, 19854,
19855, 19856, 19857, 19858,
19859, 19860, 19861, 19862,
19863, 19864, 19865, 19866,
19867, 19868, 19869, 19870,
19871, 19872, 19873, 19874,
19875, 19876, 19877, 19878,
19879, 19880, 19881, 19882,
19883, 19884, 19885, 19886,
19887, 19888, 19889, 19890,
19891, 19892, 19893, 19894,
19895, 19896, 19897, 19898,
19899, 19900, 19901, 19902,
19903, 19904, 19905, 19906,
19907, 19908, 19909, 19910,
19911, 19912, 19913, 19914,
19915, 19916, 19917, 19918,
19919, 19920, 19921, 19922,
19923, 19924, 19925, 19926,
19927, 19928, 19932, 19933,
19985, 19986, 19987, 19988,
19989, 19990, 19991, 19992,
19993, 19994, 19995, 19996,
19997, 19998, 19999, 20000,
20001, 20002, 20003, 20004,
20005, 20006, 20007, 20008,
20009, 20010, 20011, 20012,
20013, 20014, 20015, 20016,
20017, 20018, 20053, 20054,
20055, 20056, 20057, 20058,
20059, 20060, 20061, 20062,
20063, 20064, 20065, 20066,
20067, 20068, 20210, 20211,
20371, 20372, 20373, 20374,
20375, 20376, 20377, 20378,
20379, 20380, 20381, 20382,
20383, 20384, 20385, 20386,
20387, 20388, 20389, 20390,
20822, 20823, 20824, 20825,
20838, 20839, 20840, 20841,
20842, 20843, 20844, 20845,
20846, 20847, 20848, 20849,
20850, 20851, 20852, 20853,
20854, 20855, 20859, 20860,
20861, 20862, 20863, 20864,
20865, 20866, 20867, 20868,
20869, 20870, 20871, 20872,
20873, 20874, 20875, 20876,
20877, 20878, 20879, 20880,
20881, 20882, 20883, 20884,
20885, 20886, 20887, 20888,
20889, 20890, 20891, 20892,
20893, 20894, 21099, 21100,
21101, 21102, 21103, 21104,
21105, 21106, 21107, 21108,
21109, 21110, 21111, 21112,
21113, 21114, 21115, 21116,
21119, 21120, 21121, 21122,
21123, 21124, 21125, 21126,
21127, 21128, 21129, 21130,
21131, 21132, 21133, 21134,
21135, 21136, 21137, 21138,
21139, 21140, 21141, 21142,
21143, 21144, 21145, 21146,
21147, 21148, 21149, 21150,
21151, 21152, 21153, 21154,
21155, 21156, 21157, 21158,
21159, 21160, 21161, 21162,
21163, 21164, 21165, 21166,
21167, 21168, 21169, 21170,
21171, 21172, 21173, 21174,
21175, 21176, 21177, 21178,
21179, 21180 )
GROUP BY states.metadata_id) AS anon_3
ON states.metadata_id = anon_3.max_metadata_id
AND states.last_updated_ts = anon_3.max_last_updated
LEFT OUTER JOIN state_attributes
ON states.attributes_id = state_attributes.attributes_id
WHERE states.last_updated_ts >= 1719718797.70615e0
AND states.last_updated_ts < 1719728699.999999e0
AND states.metadata_id IN ( 10987, 10988, 10989, 10990,
10991, 10992, 10993, 10994,
11472, 11475, 11474, 11473,
12336, 15511, 12337, 15510,
15512, 15513, 913, 15486,
914, 12286, 853, 876,
877, 881, 10817, 920,
11996, 11997, 11998, 11999,
12000, 12001, 12002, 12003,
12004, 12005, 12006, 12007,
12008, 12009, 12010, 13021,
13022, 13023, 13024, 13027,
13028, 13025, 13026, 20895,
20896, 20897, 20898, 20899,
20900, 20901, 20902, 21117,
21118, 13039, 13040, 13041,
13042, 13043, 13044, 13045,
13046, 13047, 13048, 11527,
11528, 11529, 19929, 19930,
19931, 12379, 12380, 12381,
12373, 12374, 12375, 12251,
12252, 12253, 12388, 12389,
12390, 12391, 12392, 12393,
20856, 20857, 20858, 13907,
13908, 13909, 14861, 14862,
14863, 12350, 12351, 12352,
12360, 12361, 12362, 12327,
12328, 12329, 11545, 11546,
11547, 11548, 11549, 11550,
11551, 11552, 11553, 11554,
11555, 11556, 11557, 11558,
11559, 11560, 11561, 11562,
11563, 11564, 11565, 12975,
12976, 12977, 12978, 12979,
12980, 12981, 12982, 12983,
12984, 12985, 12986, 12987,
12988, 12989, 12990, 12991,
12992, 12993, 12994, 12995,
12996, 12997, 12998, 12999,
13000, 13001, 13002, 13003,
13004, 13005, 13006, 13007,
13008, 13011, 13012, 13015,
13016, 13009, 13010, 13013,
13014, 13017, 13018, 11569,
11570, 11571, 12382, 12383,
12384, 19982, 19983, 19984,
12324, 12325, 12326, 20835,
20836, 20837, 12357, 12358,
12359, 13886, 13887, 13888,
12248, 12249, 12250, 12394,
12395, 12396, 12347, 12348,
12349, 12385, 12386, 12387,
12376, 12377, 12378, 14866,
14867, 14868, 11587, 11588,
11589, 11590, 11591, 11592,
11593, 11594, 11595, 11596,
11597, 11598, 11599, 11600,
11601, 11602, 11603, 11604,
13050, 13051, 13052, 13053,
13054, 13055, 13056, 13057,
21181, 21182, 21183, 21184,
21185, 21186, 21187, 21188,
21189, 21190, 21191, 21192,
21193, 21194, 21195, 21196,
21197, 21198, 21199, 21200,
12168, 12169, 12170, 13076,
13077, 13078, 13079, 13080,
13081, 13082, 13083, 13084,
13085, 13086, 13087, 13088,
13089, 13090, 13091, 13092,
13093, 13094, 13095, 13096,
13097, 13098, 13099, 13100,
13101, 13102, 13103, 13104,
13105, 13108, 13109, 13106,
13107, 13110, 13111, 821,
814, 11930, 10725, 10726,
10727, 10728, 11931, 11981,
10788, 10731, 10737, 10738,
12271, 10735, 10736, 10777,
11937, 10740, 11969, 10734,
10803, 10795, 10801, 10813,
10739, 10822, 10797, 10799,
11929, 11932, 11934, 11966,
12014, 12017, 12020, 12098,
12192, 12177, 12195, 12330,
10778, 15444, 10819, 12299,
15448, 12106, 19678, 10776,
11939, 10742, 10743, 10744,
10745, 11940, 11982, 10789,
10748, 10754, 10755, 12272,
10752, 10753, 10781, 11946,
10757, 11970, 10751, 10804,
10796, 10802, 10814, 10756,
10823, 10798, 10800, 11938,
11941, 11943, 11967, 12015,
12018, 12021, 12099, 12193,
12178, 12196, 12331, 10782,
15445, 10820, 12300, 15449,
12107, 19679, 10780, 10717,
10718, 10719, 10720, 13119,
13120, 13121, 13122, 13123,
18874, 13124, 13125, 13126,
13127, 13128, 18875, 5650,
12112, 792, 794, 605,
606, 607, 608, 609,
238, 612, 241, 771,
767, 775, 616, 617,
618, 619, 620, 621,
622, 623, 798, 800,
624, 625, 626, 627,
628, 629, 630, 631,
778, 779, 780, 598,
599, 600, 601, 759,
760, 586, 291, 292,
293, 294, 295, 331,
332, 371, 333, 372,
334, 373, 335, 296,
336, 374, 297, 337,
298, 411, 338, 303,
343, 304, 344, 305,
345, 306, 346, 307,
347, 308, 348, 309,
349, 310, 350, 315,
375, 412, 376, 413,
377, 414, 378, 415,
416, 417, 418, 383,
384, 385, 386, 423,
387, 424, 425, 388,
426, 389, 427, 428,
429, 355, 316, 390,
317, 356, 318, 357,
319, 358, 320, 359,
321, 360, 322, 361,
362, 430, 395, 396,
397, 398, 435, 399,
436, 400, 437, 401,
438, 402, 439, 440,
441, 442, 15473, 15485,
15472, 15475, 15478, 15476,
15477, 15474, 5614, 5615,
5616, 5619, 5622, 12303,
5623, 5624, 12305, 5625,
7282, 7283, 7284, 11913,
11914, 11915, 11916, 11923,
11925, 12011, 12023, 12033,
12035, 12062, 595, 596,
597, 220, 222, 10981,
10982, 10983, 10984, 5605,
5606, 5607, 5610, 5632,
12306, 5633, 5634, 12308,
5635, 14, 12, 12283,
12276, 12282, 12281, 10328,
10329, 10332, 10333, 10334,
10335, 10336, 10337, 10338,
10340, 10344, 10348, 10352,
10354, 10361, 10355, 10831,
10832, 11465, 11466, 11680,
11681, 11682, 11683, 11684,
11685, 11885, 11886, 12123,
12124, 12125, 12128, 12129,
12130, 12159, 12160, 12162,
12163, 12164, 12165, 12166,
12167, 12180, 12181, 12182,
12183, 12184, 12185, 12186,
12187, 12188, 12189, 12190,
12191, 12211, 12212, 12213,
12214, 12215, 12216, 12217,
12218, 12219, 12220, 12221,
12222, 12230, 12231, 12232,
12233, 12234, 12235, 12236,
12237, 12238, 12239, 12240,
12241, 12242, 12243, 12244,
12245, 12246, 12247, 12254,
12255, 12256, 12257, 12258,
12259, 12312, 12313, 12314,
12315, 12316, 12317, 12318,
12319, 12320, 12321, 12322,
12323, 13029, 13030, 13031,
13032, 13033, 13034, 13035,
13036, 13037, 13038, 13058,
13059, 13060, 13061, 13062,
13063, 13064, 13065, 13066,
13067, 13068, 13069, 13070,
13071, 13072, 13073, 13074,
13075, 13711, 13712, 13713,
13714, 13715, 13716, 13717,
13718, 13719, 13720, 13745,
13746, 13747, 13748, 13749,
13750, 13751, 13752, 13753,
13754, 13755, 13756, 13757,
13758, 13759, 13760, 13761,
13762, 13763, 13764, 13765,
13766, 13767, 13768, 13769,
13770, 13771, 13772, 13773,
13774, 13775, 13776, 13777,
13778, 13779, 13780, 13781,
13782, 13783, 13784, 13785,
13786, 13787, 13788, 13789,
13790, 13791, 13792, 13793,
13794, 13860, 13861, 13862,
13863, 13864, 13865, 13866,
13867, 13868, 13869, 13870,
13871, 13872, 13873, 13874,
13875, 13876, 13877, 13878,
13879, 13880, 13881, 13882,
13883, 13884, 13885, 13889,
13890, 13891, 13892, 13893,
13894, 13895, 13896, 13897,
13898, 13899, 13900, 13901,
13902, 13903, 13904, 13905,
13906, 13910, 13911, 13912,
13913, 13914, 13915, 13916,
13917, 13918, 13919, 13920,
13921, 13922, 13923, 13924,
13925, 13926, 13927, 13928,
13929, 13930, 13931, 13932,
13933, 13934, 13935, 13936,
13937, 13938, 13939, 13940,
13941, 14826, 14827, 14828,
14829, 14830, 14831, 14832,
14833, 14834, 14835, 14836,
14837, 14838, 14839, 14840,
14841, 14842, 14843, 14864,
14865, 14869, 14870, 15450,
15451, 15452, 15453, 15454,
15455, 15456, 15457, 15487,
15488, 17113, 17114, 17115,
17116, 17117, 17118, 17119,
17120, 17121, 17122, 17123,
17124, 17125, 17126, 17127,
17128, 17129, 17130, 17330,
17331, 17332, 17333, 17334,
17335, 17336, 17337, 17338,
17339, 17340, 17341, 17342,
17343, 17344, 17345, 17346,
17347, 17401, 17402, 17403,
17404, 17405, 17406, 17407,
17408, 17409, 17410, 18872,
18873, 19623, 19624, 19625,
19626, 19680, 19681, 19682,
19683, 19684, 19685, 19686,
19687, 19688, 19689, 19690,
19691, 19692, 19693, 19694,
19695, 19696, 19697, 19698,
19699, 19700, 19723, 19724,
19725, 19726, 19727, 19728,
19729, 19730, 19731, 19732,
19733, 19734, 19735, 19736,
19737, 19738, 19739, 19740,
19741, 19742, 19743, 19744,
19745, 19746, 19747, 19748,
19753, 19754, 19755, 19756,
19757, 19758, 19759, 19760,
19761, 19762, 19763, 19764,
19765, 19766, 19767, 19768,
19769, 19770, 19771, 19772,
19773, 19774, 19775, 19776,
19777, 19778, 19779, 19780,
19781, 19782, 19783, 19784,
19785, 19786, 19787, 19788,
19789, 19790, 19791, 19792,
19793, 19794, 19795, 19796,
19797, 19798, 19799, 19800,
19801, 19802, 19803, 19804,
19805, 19806, 19807, 19808,
19809, 19810, 19811, 19812,
19813, 19814, 19815, 19816,
19817, 19818, 19825, 19826,
19827, 19828, 19829, 19830,
19831, 19832, 19833, 19834,
19835, 19836, 19837, 19838,
19839, 19840, 19841, 19842,
19843, 19844, 19845, 19846,
19847, 19848, 19849, 19850,
19851, 19852, 19853, 19854,
19855, 19856, 19857, 19858,
19859, 19860, 19861, 19862,
19863, 19864, 19865, 19866,
19867, 19868, 19869, 19870,
19871, 19872, 19873, 19874,
19875, 19876, 19877, 19878,
19879, 19880, 19881, 19882,
19883, 19884, 19885, 19886,
19887, 19888, 19889, 19890,
19891, 19892, 19893, 19894,
19895, 19896, 19897, 19898,
19899, 19900, 19901, 19902,
19903, 19904, 19905, 19906,
19907, 19908, 19909, 19910,
19911, 19912, 19913, 19914,
19915, 19916, 19917, 19918,
19919, 19920, 19921, 19922,
19923, 19924, 19925, 19926,
19927, 19928, 19932, 19933,
19985, 19986, 19987, 19988,
19989, 19990, 19991, 19992,
19993, 19994, 19995, 19996,
19997, 19998, 19999, 20000,
20001, 20002, 20003, 20004,
20005, 20006, 20007, 20008,
20009, 20010, 20011, 20012,
20013, 20014, 20015, 20016,
20017, 20018, 20053, 20054,
20055, 20056, 20057, 20058,
20059, 20060, 20061, 20062,
20063, 20064, 20065, 20066,
20067, 20068, 20210, 20211,
20371, 20372, 20373, 20374,
20375, 20376, 20377, 20378,
20379, 20380, 20381, 20382,
20383, 20384, 20385, 20386,
20387, 20388, 20389, 20390,
20822, 20823, 20824, 20825,
20838, 20839, 20840, 20841,
20842, 20843, 20844, 20845,
20846, 20847, 20848, 20849,
20850, 20851, 20852, 20853,
20854, 20855, 20859, 20860,
20861, 20862, 20863, 20864,
20865, 20866, 20867, 20868,
20869, 20870, 20871, 20872,
20873, 20874, 20875, 20876,
20877, 20878, 20879, 20880,
20881, 20882, 20883, 20884,
20885, 20886, 20887, 20888,
20889, 20890, 20891, 20892,
20893, 20894, 21099, 21100,
21101, 21102, 21103, 21104,
21105, 21106, 21107, 21108,
21109, 21110, 21111, 21112,
21113, 21114, 21115, 21116,
21119, 21120, 21121, 21122,
21123, 21124, 21125, 21126,
21127, 21128, 21129, 21130,
21131, 21132, 21133, 21134,
21135, 21136, 21137, 21138,
21139, 21140, 21141, 21142,
21143, 21144, 21145, 21146,
21147, 21148, 21149, 21150,
21151, 21152, 21153, 21154,
21155, 21156, 21157, 21158,
21159, 21160, 21161, 21162,
21163, 21164, 21165, 21166,
21167, 21168, 21169, 21170,
21171, 21172, 21173, 21174,
21175, 21176, 21177, 21178,
21179, 21180 )) AS anon_2
UNION ALL
SELECT anon_4.metadata_id AS metadata_id,
anon_4.state AS state,
anon_4.last_updated_ts AS last_updated_ts,
anon_4.attributes AS attributes
FROM (SELECT states.metadata_id AS metadata_id,
states.state AS state,
states.last_updated_ts AS last_updated_ts,
CASE
WHEN ( state_attributes.shared_attrs IS NULL ) THEN
states.attributes
ELSE state_attributes.shared_attrs
END AS attributes
FROM states
LEFT OUTER JOIN state_attributes
ON states.attributes_id =
state_attributes.attributes_id
WHERE ( states.last_changed_ts = states.last_updated_ts
OR states.last_changed_ts IS NULL )
AND states.metadata_id IN ( 10987, 10988, 10989, 10990,
10991, 10992, 10993, 10994,
11472, 11475, 11474, 11473,
12336, 15511, 12337, 15510,
15512, 15513, 913, 15486,
914, 12286, 853, 876,
877, 881, 10817, 920,
11996, 11997, 11998, 11999,
12000, 12001, 12002, 12003,
12004, 12005, 12006, 12007,
12008, 12009, 12010, 13021,
13022, 13023, 13024, 13027,
13028, 13025, 13026, 20895,
20896, 20897, 20898, 20899,
20900, 20901, 20902, 21117,
21118, 13039, 13040, 13041,
13042, 13043, 13044, 13045,
13046, 13047, 13048, 11527,
11528, 11529, 19929, 19930,
19931, 12379, 12380, 12381,
12373, 12374, 12375, 12251,
12252, 12253, 12388, 12389,
12390, 12391, 12392, 12393,
20856, 20857, 20858, 13907,
13908, 13909, 14861, 14862,
14863, 12350, 12351, 12352,
12360, 12361, 12362, 12327,
12328, 12329, 11545, 11546,
11547, 11548, 11549, 11550,
11551, 11552, 11553, 11554,
11555, 11556, 11557, 11558,
11559, 11560, 11561, 11562,
11563, 11564, 11565, 12975,
12976, 12977, 12978, 12979,
12980, 12981, 12982, 12983,
12984, 12985, 12986, 12987,
12988, 12989, 12990, 12991,
12992, 12993, 12994, 12995,
12996, 12997, 12998, 12999,
13000, 13001, 13002, 13003,
13004, 13005, 13006, 13007,
13008, 13011, 13012, 13015,
13016, 13009, 13010, 13013,
13014, 13017, 13018, 11569,
11570, 11571, 12382, 12383,
12384, 19982, 19983, 19984,
12324, 12325, 12326, 20835,
20836, 20837, 12357, 12358,
12359, 13886, 13887, 13888,
12248, 12249, 12250, 12394,
12395, 12396, 12347, 12348,
12349, 12385, 12386, 12387,
12376, 12377, 12378, 14866,
14867, 14868, 11587, 11588,
11589, 11590, 11591, 11592,
11593, 11594, 11595, 11596,
11597, 11598, 11599, 11600,
11601, 11602, 11603, 11604,
13050, 13051, 13052, 13053,
13054, 13055, 13056, 13057,
21181, 21182, 21183, 21184,
21185, 21186, 21187, 21188,
21189, 21190, 21191, 21192,
21193, 21194, 21195, 21196,
21197, 21198, 21199, 21200,
12168, 12169, 12170, 13076,
13077, 13078, 13079, 13080,
13081, 13082, 13083, 13084,
13085, 13086, 13087, 13088,
13089, 13090, 13091, 13092,
13093, 13094, 13095, 13096,
13097, 13098, 13099, 13100,
13101, 13102, 13103, 13104,
13105, 13108, 13109, 13106,
13107, 13110, 13111, 821,
814, 11930, 10725, 10726,
10727, 10728, 11931, 11981,
10788, 10731, 10737, 10738,
12271, 10735, 10736, 10777,
11937, 10740, 11969, 10734,
10803, 10795, 10801, 10813,
10739, 10822, 10797, 10799,
11929, 11932, 11934, 11966,
12014, 12017, 12020, 12098,
12192, 12177, 12195, 12330,
10778, 15444, 10819, 12299,
15448, 12106, 19678, 10776,
11939, 10742, 10743, 10744,
10745, 11940, 11982, 10789,
10748, 10754, 10755, 12272,
10752, 10753, 10781, 11946,
10757, 11970, 10751, 10804,
10796, 10802, 10814, 10756,
10823, 10798, 10800, 11938,
11941, 11943, 11967, 12015,
12018, 12021, 12099, 12193,
12178, 12196, 12331, 10782,
15445, 10820, 12300, 15449,
12107, 19679, 10780, 10717,
10718, 10719, 10720, 13119,
13120, 13121, 13122, 13123,
18874, 13124, 13125, 13126,
13127, 13128, 18875, 5650,
12112, 792, 794, 605,
606, 607, 608, 609,
238, 612, 241, 771,
767, 775, 616, 617,
618, 619, 620, 621,
622, 623, 798, 800,
624, 625, 626, 627,
628, 629, 630, 631,
778, 779, 780, 598,
599, 600, 601, 759,
760, 586, 291, 292,
293, 294, 295, 331,
332, 371, 333, 372,
334, 373, 335, 296,
336, 374, 297, 337,
298, 411, 338, 303,
343, 304, 344, 305,
345, 306, 346, 307,
347, 308, 348, 309,
349, 310, 350, 315,
375, 412, 376, 413,
377, 414, 378, 415,
416, 417, 418, 383,
384, 385, 386, 423,
387, 424, 425, 388,
426, 389, 427, 428,
429, 355, 316, 390,
317, 356, 318, 357,
319, 358, 320, 359,
321, 360, 322, 361,
362, 430, 395, 396,
397, 398, 435, 399,
436, 400, 437, 401,
438, 402, 439, 440,
441, 442, 15473, 15485,
15472, 15475, 15478, 15476,
15477, 15474, 5614, 5615,
5616, 5619, 5622, 12303,
5623, 5624, 12305, 5625,
7282, 7283, 7284, 11913,
11914, 11915, 11916, 11923,
11925, 12011, 12023, 12033,
12035, 12062, 595, 596,
597, 220, 222, 10981,
10982, 10983, 10984, 5605,
5606, 5607, 5610, 5632,
12306, 5633, 5634, 12308,
5635, 14, 12, 12283,
12276, 12282, 12281, 10328,
10329, 10332, 10333, 10334,
10335, 10336, 10337, 10338,
10340, 10344, 10348, 10352,
10354, 10361, 10355, 10831,
10832, 11465, 11466, 11680,
11681, 11682, 11683, 11684,
11685, 11885, 11886, 12123,
12124, 12125, 12128, 12129,
12130, 12159, 12160, 12162,
12163, 12164, 12165, 12166,
12167, 12180, 12181, 12182,
12183, 12184, 12185, 12186,
12187, 12188, 12189, 12190,
12191, 12211, 12212, 12213,
12214, 12215, 12216, 12217,
12218, 12219, 12220, 12221,
12222, 12230, 12231, 12232,
12233, 12234, 12235, 12236,
12237, 12238, 12239, 12240,
12241, 12242, 12243, 12244,
12245, 12246, 12247, 12254,
12255, 12256, 12257, 12258,
12259, 12312, 12313, 12314,
12315, 12316, 12317, 12318,
12319, 12320, 12321, 12322,
12323, 13029, 13030, 13031,
13032, 13033, 13034, 13035,
13036, 13037, 13038, 13058,
13059, 13060, 13061, 13062,
13063, 13064, 13065, 13066,
13067, 13068, 13069, 13070,
13071, 13072, 13073, 13074,
13075, 13711, 13712, 13713,
13714, 13715, 13716, 13717,
13718, 13719, 13720, 13745,
13746, 13747, 13748, 13749,
13750, 13751, 13752, 13753,
13754, 13755, 13756, 13757,
13758, 13759, 13760, 13761,
13762, 13763, 13764, 13765,
13766, 13767, 13768, 13769,
13770, 13771, 13772, 13773,
13774, 13775, 13776, 13777,
13778, 13779, 13780, 13781,
13782, 13783, 13784, 13785,
13786, 13787, 13788, 13789,
13790, 13791, 13792, 13793,
13794, 13860, 13861, 13862,
13863, 13864, 13865, 13866,
13867, 13868, 13869, 13870,
13871, 13872, 13873, 13874,
13875, 13876, 13877, 13878,
13879, 13880, 13881, 13882,
13883, 13884, 13885, 13889,
13890, 13891, 13892, 13893,
13894, 13895, 13896, 13897,
13898, 13899, 13900, 13901,
13902, 13903, 13904, 13905,
13906, 13910, 13911, 13912,
13913, 13914, 13915, 13916,
13917, 13918, 13919, 13920,
13921, 13922, 13923, 13924,
13925, 13926, 13927, 13928,
13929, 13930, 13931, 13932,
13933, 13934, 13935, 13936,
13937, 13938, 13939, 13940,
13941, 14826, 14827, 14828,
14829, 14830, 14831, 14832,
14833, 14834, 14835, 14836,
14837, 14838, 14839, 14840,
14841, 14842, 14843, 14864,
14865, 14869, 14870, 15450,
15451, 15452, 15453, 15454,
15455, 15456, 15457, 15487,
15488, 17113, 17114, 17115,
17116, 17117, 17118, 17119,
17120, 17121, 17122, 17123,
17124, 17125, 17126, 17127,
17128, 17129, 17130, 17330,
17331, 17332, 17333, 17334,
17335, 17336, 17337, 17338,
17339, 17340, 17341, 17342,
17343, 17344, 17345, 17346,
17347, 17401, 17402, 17403,
17404, 17405, 17406, 17407,
17408, 17409, 17410, 18872,
18873, 19623, 19624, 19625,
19626, 19680, 19681, 19682,
19683, 19684, 19685, 19686,
19687, 19688, 19689, 19690,
19691, 19692, 19693, 19694,
19695, 19696, 19697, 19698,
19699, 19700, 19723, 19724,
19725, 19726, 19727, 19728,
19729, 19730, 19731, 19732,
19733, 19734, 19735, 19736,
19737, 19738, 19739, 19740,
19741, 19742, 19743, 19744,
19745, 19746, 19747, 19748,
19753, 19754, 19755, 19756,
19757, 19758, 19759, 19760,
19761, 19762, 19763, 19764,
19765, 19766, 19767, 19768,
19769, 19770, 19771, 19772,
19773, 19774, 19775, 19776,
19777, 19778, 19779, 19780,
19781, 19782, 19783, 19784,
19785, 19786, 19787, 19788,
19789, 19790, 19791, 19792,
19793, 19794, 19795, 19796,
19797, 19798, 19799, 19800,
19801, 19802, 19803, 19804,
19805, 19806, 19807, 19808,
19809, 19810, 19811, 19812,
19813, 19814, 19815, 19816,
19817, 19818, 19825, 19826,
19827, 19828, 19829, 19830,
19831, 19832, 19833, 19834,
19835, 19836, 19837, 19838,
19839, 19840, 19841, 19842,
19843, 19844, 19845, 19846,
19847, 19848, 19849, 19850,
19851, 19852, 19853, 19854,
19855, 19856, 19857, 19858,
19859, 19860, 19861, 19862,
19863, 19864, 19865, 19866,
19867, 19868, 19869, 19870,
19871, 19872, 19873, 19874,
19875, 19876, 19877, 19878,
19879, 19880, 19881, 19882,
19883, 19884, 19885, 19886,
19887, 19888, 19889, 19890,
19891, 19892, 19893, 19894,
19895, 19896, 19897, 19898,
19899, 19900, 19901, 19902,
19903, 19904, 19905, 19906,
19907, 19908, 19909, 19910,
19911, 19912, 19913, 19914,
19915, 19916, 19917, 19918,
19919, 19920, 19921, 19922,
19923, 19924, 19925, 19926,
19927, 19928, 19932, 19933,
19985, 19986, 19987, 19988,
19989, 19990, 19991, 19992,
19993, 19994, 19995, 19996,
19997, 19998, 19999, 20000,
20001, 20002, 20003, 20004,
20005, 20006, 20007, 20008,
20009, 20010, 20011, 20012,
20013, 20014, 20015, 20016,
20017, 20018, 20053, 20054,
20055, 20056, 20057, 20058,
20059, 20060, 20061, 20062,
20063, 20064, 20065, 20066,
20067, 20068, 20210, 20211,
20371, 20372, 20373, 20374,
20375, 20376, 20377, 20378,
20379, 20380, 20381, 20382,
20383, 20384, 20385, 20386,
20387, 20388, 20389, 20390,
20822, 20823, 20824, 20825,
20838, 20839, 20840, 20841,
20842, 20843, 20844, 20845,
20846, 20847, 20848, 20849,
20850, 20851, 20852, 20853,
20854, 20855, 20859, 20860,
20861, 20862, 20863, 20864,
20865, 20866, 20867, 20868,
20869, 20870, 20871, 20872,
20873, 20874, 20875, 20876,
20877, 20878, 20879, 20880,
20881, 20882, 20883, 20884,
20885, 20886, 20887, 20888,
20889, 20890, 20891, 20892,
20893, 20894, 21099, 21100,
21101, 21102, 21103, 21104,
21105, 21106, 21107, 21108,
21109, 21110, 21111, 21112,
21113, 21114, 21115, 21116,
21119, 21120, 21121, 21122,
21123, 21124, 21125, 21126,
21127, 21128, 21129, 21130,
21131, 21132, 21133, 21134,
21135, 21136, 21137, 21138,
21139, 21140, 21141, 21142,
21143, 21144, 21145, 21146,
21147, 21148, 21149, 21150,
21151, 21152, 21153, 21154,
21155, 21156, 21157, 21158,
21159, 21160, 21161, 21162,
21163, 21164, 21165, 21166,
21167, 21168, 21169, 21170,
21171, 21172, 21173, 21174,
21175, 21176, 21177, 21178,
21179, 21180 )
AND states.last_updated_ts > 1719728699.999999e0
AND states.last_updated_ts < 1719729000.0e0) AS anon_4)
AS
anon_1
ORDER BY anon_1.metadata_id,
anon_1.last_updated_ts
{
"table": {
"table_name": "states",
"access_type": "ref",
"possible_keys": [
"ix_states_metadata_id_last_updated_ts",
"ix_states_last_updated_ts",
"ix_states_metadata_id"
],
"key": "ix_states_metadata_id",
"key_length": "5",
"used_key_parts": [
"metadata_id"
],
"ref": [
"tvc_0._col_1"
],
"loops": 1386,
"r_loops": 1386,
"rows": 328,
"r_rows": 9734.906205,
"cost": 550.8470486,
"r_table_time_ms": 973958.3092,
"r_other_time_ms": 2469.911632,
"r_engine_stats": {
"pages_accessed": 40494237,
"pages_read_count": 10326979,
"pages_read_time_ms": 2463462324
},
"filtered": 2.827162504,
"r_filtered": 1.510874866,
"attached_condition": "states.last_updated_ts >= 1719718797.70615e0 and states.last_updated_ts < 1719728699.999999e0 and states.last_updated_ts is not null"
}
}
the database engine is choosing to select the metadata_ids and than manually scan for values inside the time constraint.
It probably regressed in #99450
We could revert that PR but than it will make it slower for other cases.
I think this one will be nearly impossible to fix without access to an affected system to run queries / test on.
@bdraco I can't give you access to my 'production' instance of HA, but I can run/analyze any query you need.
The problem
The compilation of statistics stops the recorder. The problem sometimes appears right after restart, and sometimes after one to two days since the last restart.
As a result entity history has gaps like this:
It seems that one of the queries used during that step takes more than 15 minutes to run and requires multiple full table scans on states table (please see the sample query explanation in 'additional information').
MariaDB engine reports various performance issues:
What version of Home Assistant Core has the issue?
core-2024.6.4
What was the last working version of Home Assistant Core?
No response
What type of installation are you running?
Home Assistant Container
Integration causing the issue
Recorder
Link to integration documentation on our website
https://www.home-assistant.io/integrations/recorder/
Diagnostics information
No response
Example YAML snippet
No response
Anything in the logs that might be useful for us?
Sample Home Assistant Core log entries: home-assistant_2024-06-30T09-49-18.828Z.log
MariaDB slow query log: mariadb-slow.log
Sample query analysis: JSON: MariaDB query analysis.json CSV: MariaDB query analysis.csv
Additional information
Example of query that causes problem: