Database connection lost - Cannot delete or update a parent row: a foreign key constraint fails

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.

https://www.home-assistant.io

Apache License 2.0

73.67k stars 30.8k forks source link

Database connection lost - Cannot delete or update a parent row: a foreign key constraint fails #14271

Closed danodemano closed 5 years ago

danodemano commented 6 years ago

Home Assistant release with the issue: 0.68.1

Last working Home Assistant release (if known): Unsure

Operating environment (Hass.io/Docker/Windows/etc.): Debian/pip3

Component/platform: ~~Graphs/history~~ Database

Description of problem: Graphs flatline after some time. Though data is still current. Database errors seem to cause the connection to fail.

Additional information: It's not one thing in particular. ALL the graphs stop and won't start again until I bounce Home Assistant. There is no consistency. Sometimes they will display fine for days on end. Sometimes they quit every 20 minutes.

It's the database errors that cause a loss of connectivity to the database.

Data still appears to be there as my Grafana still operates as expected:

cgarwood commented 6 years ago

If you restart HomeAssistant do the graphs that were previously a flat line update to show the correct data, or will that time period always be a flat line?

danodemano commented 6 years ago

@cgarwood - I was going to say they updated but I am wrong. They stay flatlined:

So maybe there is a bigger issue with the history recording. I have it set to keep 2 days:

purge_interval: 1
purge_keep_days: 2
db_url: !secret recorder_db_url

cgarwood commented 6 years ago

YEah, it sounds like the recorder is losing connection to the database and not reconnecting until HomeAssistant is restarted.

Which database engine are you using, and is it on the same system as HomeAssistant or a different system? If you check the home-assistant.log or the error log in the frontend are there any errors related to the recorder or database connection?

danodemano commented 6 years ago

Of course it's been running correctly since I submitted this issue earlier today. :/

I'll check the logs as soon as it acts up. I'm using MariaDB/MySQL and yes it's on the same system.

danodemano commented 6 years ago

@cgarwood After running solid for almost 3 days I finally had it happen. All graphs are now flatlined starting around 9AM. At 9:10AM I see this in the logs. There isn't anything else that appears to be related.

2018-05-07 09:10:27 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 5, 5, 13, 3, 13, 394966, tzinfo=<UTC>), 16152091, 17388179, 17449653, 17455307, 17469362, 17483858, 17483859, 18531201, 18531203, 18531209, 18531211, 18531213, 18531224, 20203077, 20203079, 20206556, 23344680, 23344681, 23344687, 23344688, 23344691, 23344693, 24615081, 24615994, 24618004, 24691199, 24691216, 24691218, 24691219, 24691220, 24691222, 24726408, 24726409, 24726410, 24726411, 24742238, 24742239, 24752148, 24752158, 24752164, 24752165, 24752166, 24753508, 24753509, 24753510, 24753511, 28255856, 28255857, 28255923, 28255924, 28259636, 28294328, 28300555, 28306339, 28306343, 28323386, 30290943, 30290958, 30290961, 30290964, 32562019, 32562020, 32574671, 36506059, 36506061, 36510730, 36515854, 36515855, 36515856, 36517536, 36517537, 36517538, 36518228, 36518229, 36518230, 36518231, 36518232, 36518233, 36518234, 36518235, 36518236, 36518237, 36518238, 36518239, 36518240, 36518258, 36518259, 36518260, 36518261, 36518262, 36518263, 36518264, 36518265, 36518266, 36518267, 36518268, 36518269, 36518270, 36933210, 36933232, 36933261, 36933262, 36933263, 36933264, 36933265, 36933266, 36933270, 36933290, 36933305, 36933328, 36933329, 36933375, 36933376, 36933377, 36933378, 36933379, 36933380, 36933381, 36933382, 36933383, 36933384, 36933385, 36933386, 36933387, 36933388, 36933389, 36933390, 36933391, 36933392, 36933393, 36933394, 36933395, 36933397, 36933399, 36933400, 36933401, 36933402, 36933404, 36933406, 36933407, 36933408, 36933409, 36933410, 36933411, 36933412, 36933425, 36933426, 36933428, 36933447, 36933456, 36933457, 36933477, 36933505, 36933507, 36933550, 36933551, 36933552, 36933553, 36933556, 36933557, 36933575, 36933576, 36933578, 36933579, 36933580, 36933586, 36933614, 36933616, 36933620, 36933625, 36933626, 36933637, 36933641, 36933642, 36933644, 36933649, 36933675, 36933695, 36933696, 36933697, 36933699, 36933700, 36933701, 36933704, 36933710, 36933713, 36933714, 36933716, 36933724, 36933727, 36933732, 36933733, 36933734, 36933735, 36933736, 36933741, 36933743, 36933744, 36933749, 36933750, 36933751, 36933752, 36933753, 36933754, 36933758, 36933760, 36933762, 36933763, 36933767, 36933790, 36933829, 36933830, 36933832, 36933833, 36933835, 36933837, 36933838, 36933841, 36933843, 36933844, 36933846, 36933848, 36933850, 36933851, 36933852, 36933853, 36933854, 36933855, 36933856, 36933864, 36933866, 36933867, 36933868, 36933869, 36933876, 36933914, 36933933, 36933951, 36933952, 36933973, 36933974, 36933975, 36933976, 36933977, 36933978, 36933982, 36933983, 36933989, 36933993, 36933996, 36934000, 36934002, 36934003, 36934004, 36934006, 36934007, 36934009, 36934010, 36934011, 36934012, 36934015, 36934016, 36934017, 36934018, 36934020, 36934026, 36934029, 36934032, 36934033, 36934035, 36934036, 36934038, 36934039, 36934040, 36934042, 36934045, 36934047, 36934049, 36934052, 36934056, 36934057, 36934059, 36934062, 36934064, 36934066, 36934069, 36934071, 36934072, 36934073, 36934076, 36934077, 36934078, 36934081, 36934083, 36934085, 36934089, 36934092, 36934094, 36934096, 36934099, 36934100, 36934102, 36934110, 36934115, 36934119, 36934124, 36934128, 36934129, 36934130, 36934131, 36934132, 36934133, 36934136, 36934137, 36934138, 36934141, 36934143, 36934146, 36934147, 36934151, 36934152, 36934153, 36934160, 36934162, 36934164, 36934167, 36934172, 36934176, 36934177, 36934180, 36934181, 36934183, 36934185, 36934186, 36934188, 36934189, 36934190, 36934191, 36934193, 36934194, 36934196, 36934197, 36934200, 36934201, 36934203, 36934205, 36934206, 36934208, 36934212, 36934213, 36934216, 36934219, 36934220, 36934223, 36934224, 36934228, 36934229, 36934230, 36934232, 36934233, 36934234, 36934235, 36934236, 36934238, 36934281, 36934284, 36934285, 36934288, 36934289, 36934291, 36934293, 36934295, 36934297, 36934299, 36934308, 36934314, 36934371, 36934421, 36934422, 36934433, 36934435, 36934437, 36934439, 36934440, 36934444, 36934454, 36934464, 36934468, 36934492, 36934498, 36934501, 36934502, 36934504, 36934506, 36934508, 36934510, 36934512, 36934514, 36934516, 36934518, 36934520, 36934522, 36934532, 36936266, 36936267, 36936324, 36936325, 36936346, 36936347, 36936376, 36938447, 36938448, 36938449, 36938450, 36940307, 36964945, 36964946, 36964947, 36964948, 37034867, 37048336, 37048337, 37048338, 37048339, 37107964, 37107965, 37107966, 37107967, 37195247, 37225993, 37226002, 37277421, 37481615, 37481616, 37481618, 37528889, 37563791, 37613015, 37846216, 37846229, 37860668, 37878906, 37878907, 37878913, 37878918, 37878919, 37878920, 37878921, 37878922, 37878923, 37882443, 37954009, 38022109, 38165222, 38253448, 38272625, 38293196, 38379379, 38401967, 38404044, 38404045, 38404046, 38404666, 38404667, 38404668, 38404669, 38404813, 38404814, 38404815, 38416758, 38431547, 38431548, 38444499, 38510271, 38510273, 38510353, 38510354, 38510355, 38510406, 38514221, 38556857, 38608385, 38608387, 38633394, 38641589, 38642762, 38651173, 38674265, 38680947, 38680948, 38680955, 38680957, 38681131, 38681353, 38682941, 38685963, 38687220, 38708770, 38708771, 38708772, 38708775, 38710160, 38710697, 38716697, 38716698, 38716700, 38716701, 38717821, 38719184, 38719185, 38719186, 38719441, 38720884, 38722432, 38724261, 38724262, 38724263, 38726098, 38727460, 38727461, 38727467, 38727475, 38727476, 38727481, 38727560, 38727561, 38727563, 38727624, 38731832, 38732487, 38732488, 38733539, 38733540, 38734245, 38734665, 38740768, 38742811, 38742842, 38743892, 38743893, 38743894, 38744001, 38744019, 38744621, 38744622, 38744624, 38744724, 38744751, 38744752, 38744753, 38744783, 38744864, 38744943, 38748070, 38748115, 38748116, 38748117, 38748118, 38749901, 38750047, 38750048, 38751968, 38751969, 38751970, 38752080, 38753177, 38753178, 38753179, 38753180, 38754280, 38756852, 38756853, 38756854, 38756855, 38756856, 38757615, 38759059, 38759935, 38761419, 38761421, 38761422, 38761430, 38761439, 38761440, 38761441, 38761443, 38761444, 38761445, 38761446, 38761447, 38762078, 38763028, 38765093, 38765658, 38766419, 38766647, 38766648, 38766649, 38767808, 38768078, 38769375, 38769481, 38771567, 38771571, 38771773, 38771775, 38771776, 38771777, 38771778, 38771779, 38771780, 38771781, 38771782, 38771783, 38771784, 38771785, 38771786, 38771787, 38771788, 38771789, 38771790, 38771791, 38771792, 38771793, 38771794, 38771795, 38771796, 38771797, 38771798, 38771799, 38771800, 38771801, 38771802, 38771909, 38772106, 38772107, 38772109, 38772111, 38772453, 38772463, 38772477, 38772480, 38772481, 38772618, 38772620, 38772625, 38772632, 38772702, 38772892, 38772893, 38772894, 38773178, 38773179, 38773180, 38773181, 38773212, 38773213, 38773214, 38773215, 38773216, 38773217, 38773218, 38773219, 38773220, 38773221, 38773222, 38773223, 38773224, 38773225, 38773226, 38773298, 38773299, 38773309, 38773312, 38773328, 38773359, 38773422, 38773423, 38773475, 38773704, 38773705, 38773706, 38773707, 38773759, 38773768, 38773769, 38773770, 38773771, 38773772, 38773773, 38773787, 38773788, 38773789, 38773790, 38773791, 38773792, 38773793, 38773794, 38773795, 38773796, 38773797, 38773798, 38773799, 38773800, 38773801, 38773802, 38773803, 38773804, 38773805, 38773806, 38773807, 38773808, 38773809, 38773810, 38773811, 38773821, 38773837, 38773838, 38773839, 38773840, 38773841, 38773842, 38773843, 38773844, 38773845, 38773848, 38773849, 38773850, 38773851, 38773852, 38773853, 38773854, 38773855, 38773856, 38773857, 38773858, 38773860, 38773864, 38773865, 38773866, 38773867, 38773868, 38773869, 38773870, 38773871, 38773872, 38773873)] (Background on this error at: http://sqlalche.me/e/gkpj)

cgarwood commented 6 years ago

If you're purging every 2 days I'm guessing your not super worried about old state history? I'd say try emptying your states and events tables (TRUNCATE TABLE `states`) It looks like somehow the relationships/foreign keys got out of whack and is causing issues when it tries to purge. I'm not sure why it starts working for a few days after restarting though.

danodemano commented 6 years ago

Nope I'm not worried about the history - I keep everything long-term in InfluxDB. Should I purge the whole DB instead?

cgarwood commented 6 years ago

Wouldn't hurt to just blow away the entire DB and let HomeAssistant recreate it. That would definitely make sure there were no foreign key issues

danodemano commented 6 years ago

Database nuked. It did re-build and is starting to populate again. I'll keep an eye on things.

danodemano commented 6 years ago

@cgarwood - just popped up again after the database nuke. Similar error to before and all graphs now flatlined again:

2018-05-09 21:25:43 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 5, 8, 1, 21, 53, 159117, tzinfo=<UTC>), 817636, 817637, 817642, 817662, 817693, 817694, 817695, 817696, 817704, 817725, 817744, 817790, 817791, 817792, 817793, 817794, 817795, 817796, 817797, 817798, 817799, 817800, 817801, 817802, 817803, 817804, 817805, 817806, 817807, 817808, 817809, 817810, 817811, 817812, 817813, 817814, 817815, 817816, 817817, 817818, 817819, 817820, 817821, 817822, 817823, 817824, 817825, 817826, 817827, 817835, 817836, 817837, 817838, 817839, 817846, 817847, 817848, 817849, 817879, 817880, 817901, 817906, 817907, 817908, 817925, 817935, 817937, 817940, 817941, 817946, 817991, 817992, 817993, 817994, 817996, 817998, 818002, 818015, 818017, 818019, 818023, 818024, 818026, 818037, 818042, 818044, 818045, 818047, 818051, 818054, 818058, 818101, 818102, 818110, 818112, 818113, 818114, 818123, 818124, 818125, 818126, 818127, 818128, 818129, 818135, 818151, 818153, 818155, 818156, 818158, 818159, 818160, 818161, 818162, 818164, 818166, 818167, 818168, 818169, 818173, 818177, 818178, 818179, 818182, 818184, 818186, 818187, 818188, 818189, 818190, 818191, 818192, 818197, 818232, 818257, 818258, 818259, 818260, 818262, 818264, 818272, 818273, 818274, 818277, 818279, 818280, 818282, 818283, 818284, 818286, 818288, 818290, 818292, 818294, 818296, 818299, 818301, 818302, 818303, 818304, 818311, 818359, 818374, 818376, 818377, 818378, 818379, 818380, 818385, 818398, 818406, 818407, 818417, 818426, 818428, 818429, 818430, 818435, 818438, 818440, 818441, 818443, 818444, 818445, 818446, 818447, 818448, 818451, 818459, 818460, 818461, 818464, 818465, 818466, 818467, 818471, 818472, 818473, 818477, 818478, 818480, 818481, 818484, 818485, 818486, 818496, 818497, 818499, 818500, 818501, 818502, 818503, 818504, 818505, 818511, 818512, 818514, 818518, 818519, 818524, 818527, 818528, 818538, 818539, 818540, 818541, 818542, 818543, 818544, 818545, 818549, 818552, 818554, 818555, 818556, 818557, 818558, 818564, 818565, 818567, 818568, 818569, 818570, 818571, 818575, 818576, 818577, 818579, 818580, 818581, 818582, 818583, 818592, 818594, 818595, 818596, 818600, 818601, 818602, 818607, 818608, 818609, 818615, 818616, 818617, 818618, 818619, 818623, 818628, 818629, 818630, 818633, 818634, 818635, 818636, 818637, 818638, 818639, 818640, 818641, 818642, 818644, 818648, 818649, 818652, 818653, 818654, 818656, 818659, 818660, 818661, 818662, 818663, 818668, 818712, 818718, 818720, 818721, 818722, 818724, 818726, 818728, 818730, 818732, 818743, 818754, 818795, 818813, 818823, 818825, 818827, 818829, 818832, 818844, 818845, 818855, 818857, 818859, 818861, 818863, 818867, 818892, 818902, 818906, 818916, 818920, 818922, 818924, 818926, 818928, 818930, 818933, 818935, 818937, 818939, 818941, 818943, 818945, 818949, 818966, 821412, 821425, 821444, 821451, 821482, 821484, 825534, 842405, 842406, 842407, 842408, 842500, 881706, 902583, 902584, 902585, 902586, 953296, 953298, 960222, 960225, 1024795, 1038102, 1047310, 1068116, 1073803, 1088592, 1106604, 1106657, 1106658, 1106659, 1106661, 1106662, 1106663, 1106664, 1106665, 1106668, 1128402, 1128403, 1128405, 1188302, 1209487, 1209492, 1209502, 1209607, 1246386, 1251764, 1258606, 1258607, 1258609, 1258610, 1263619, 1284590, 1284591, 1284592, 1290827, 1300796, 1300797, 1300798, 1300877, 1306996, 1313640, 1315626, 1320880, 1328490, 1337749, 1337762, 1342594, 1342595, 1342596, 1342601, 1342602, 1342603, 1342604, 1342605, 1342606, 1343390, 1343391, 1366919, 1366920, 1366921, 1367213, 1369785, 1380378, 1380783, 1383043, 1390523, 1390895, 1396369, 1396992, 1400947, 1400948, 1400950, 1400951, 1402389, 1403364, 1403365, 1403366, 1403584, 1403606, 1406716, 1406807, 1406859, 1406860, 1406862, 1406864, 1406984, 1407029, 1407075, 1407111, 1407112, 1407113, 1407125, 1408044, 1408302, 1408379, 1408386, 1408542, 1408602, 1408603, 1408604, 1408719, 1408720, 1408721, 1408727, 1408729, 1408730, 1408808, 1408809, 1408810, 1408999, 1409000, 1409001, 1409002, 1409003, 1409094, 1409476, 1409477, 1409478, 1409571, 1409572, 1409984, 1410947, 1411125, 1411750, 1411751, 1412324, 1412325, 1413638, 1413653, 1414025, 1414026, 1414027, 1415014, 1415019, 1415271, 1415473, 1415728, 1415729, 1415730, 1417363, 1418109, 1418158, 1419069, 1419673, 1419970, 1420441, 1420680, 1420682, 1420684, 1420685, 1422448, 1422449, 1422651, 1422652, 1422653, 1422654, 1422655, 1422656, 1422657, 1422658, 1422659, 1422660, 1422661, 1422662, 1422663, 1422664, 1422665, 1422666, 1422667, 1422668, 1422669, 1422670, 1422671, 1422672, 1422673, 1422674, 1422675, 1422676, 1422937, 1423616, 1423619, 1423631, 1423777, 1423780, 1423783, 1423791, 1423796, 1423797, 1423897, 1423940, 1423942, 1423943, 1423944, 1423945, 1423946, 1423947, 1423948, 1423949, 1424032, 1424039, 1424041, 1424042, 1424043, 1424057, 1424067, 1424112, 1424140, 1424145, 1424146, 1424147, 1424148, 1424149, 1424150, 1424151, 1424152, 1424153, 1424154, 1424155, 1424252, 1424253, 1424254, 1424255, 1424279, 1424280, 1424281, 1424282, 1424283, 1424319, 1424340, 1424341, 1424342, 1424343, 1424344, 1424345, 1424360, 1424361, 1424362, 1424363, 1424364, 1424365, 1424366, 1424367, 1424368, 1424384, 1424385, 1424387, 1424389, 1424394, 1424395, 1424396, 1424397, 1424398, 1424413, 1424414, 1424415, 1424416, 1424417, 1424418, 1424419, 1424420, 1424421, 1424422, 1424423, 1424424, 1424425, 1424426, 1424427, 1424433, 1424434, 1424436, 1424437, 1424438, 1424446, 1424447, 1424449, 1424450, 1424455, 1424456, 1424457, 1424458, 1424459, 1424460, 1424461, 1424462, 1424463, 1424464, 1424465, 1424466, 1424467)] (Background on this error at: http://sqlalche.me/e/gkpj)

cgarwood commented 6 years ago

Boo. I'm not sure what would be causing that then. I know @OverloadUT and @amelchio have done a lot of work on the recorder component, I wonder if they would have any ideas.

OverloadUT commented 6 years ago

Can you try disabling the auto database purge and see if the problem goes away? It would be nice to narrow it down to that as the cause if it's the case. 2 days is a pretty short purge interval and it may be that there are some bugs that are cloaked when using a longer interval

danodemano commented 6 years ago

@OverloadUT - I will give it a shot. I have it so short since it grows but about 1GB per day and gets out of hand after a week or so cause massive lag trying to load the graphs.

I'll up it to 14 days and see what happens. I'll go ahead and nuke the DB again too just so we are starting fresh.

cgarwood commented 6 years ago

@danodemano do you have any excludes set up for recorder? If not, I'd add sun.sun and the entire zwave domain (if you have zwave devices) to the exclude list. That should help slim down the size a bit

danodemano commented 6 years ago

@cgarwood - I have a pretty good exclude list going already. I had sun in there but had some issues with the sun state so took it back out so that I could track the state closer. Exclude:

db_url: !secret recorder_db_url
exclude:
  domains:
    - automation
    - weblink
    - updater
    - calendar
    - camera
    - zwave
    - group
    - input_number
    - scene
    - zone
  entities:
    - sensor.time
    - sensor.last_boot
    - sensor.date
    - sensor.pws_alerts
    - sensor.pws_dewpoint_f
    - sensor.pws_feelslike_f
    - sensor.pws_heat_index_f
    - sensor.pws_precip_today_in
    - sensor.pws_pressure_in
    - sensor.pws_station_id
    - sensor.pws_uv
    - sensor.pws_weather
    - sensor.pws_wind_dir
    - sensor.pws_wind_mph
    - sensor.dark_sky_daily_summary
    - sensor.dark_sky_hourly_summary
    - sensor.dark_sky_minutely_summary
    - sensor.dark_sky_nearest_storm_distance
    - sensor.dark_sky_summary
    - sensor.dark_sky_temperature
    - sensor.dark_sky_cloud_coverage_1
    - sensor.dark_sky_cloud_coverage_2
    - sensor.dark_sky_cloud_coverage_3
    - sensor.dark_sky_cloud_coverage_4
    - sensor.dark_sky_cloud_coverage_5
    - sensor.dark_sky_cloud_coverage_6
    - sensor.dark_sky_cloud_coverage_7
    - sensor.dark_sky_daily_high_apparent_temperature
    - sensor.dark_sky_daily_high_apparent_temperature_1
    - sensor.dark_sky_daily_high_apparent_temperature_2
    - sensor.dark_sky_daily_high_apparent_temperature_3
    - sensor.dark_sky_daily_high_apparent_temperature_4
    - sensor.dark_sky_daily_high_apparent_temperature_5
    - sensor.dark_sky_daily_high_apparent_temperature_6
    - sensor.dark_sky_daily_high_apparent_temperature_7
    - sensor.dark_sky_daily_high_temperature
    - sensor.dark_sky_daily_high_temperature_1
    - sensor.dark_sky_daily_high_temperature_2
    - sensor.dark_sky_daily_high_temperature_3
    - sensor.dark_sky_daily_high_temperature_4
    - sensor.dark_sky_daily_high_temperature_5
    - sensor.dark_sky_daily_high_temperature_6
    - sensor.dark_sky_daily_high_temperature_7
    - sensor.dark_sky_daily_low_apparent_temperature
    - sensor.dark_sky_daily_low_apparent_temperature_1
    - sensor.dark_sky_daily_low_apparent_temperature_2
    - sensor.dark_sky_daily_low_apparent_temperature_3
    - sensor.dark_sky_daily_low_apparent_temperature_4
    - sensor.dark_sky_daily_low_apparent_temperature_5
    - sensor.dark_sky_daily_low_apparent_temperature_6
    - sensor.dark_sky_daily_low_apparent_temperature_7
    - sensor.dark_sky_daily_low_temperature
    - sensor.dark_sky_daily_low_temperature_1
    - sensor.dark_sky_daily_low_temperature_2
    - sensor.dark_sky_daily_low_temperature_3
    - sensor.dark_sky_daily_low_temperature_4
    - sensor.dark_sky_daily_low_temperature_5
    - sensor.dark_sky_daily_low_temperature_6
    - sensor.dark_sky_daily_low_temperature_7
    - sensor.dark_sky_daily_max_precip_intensity
    - sensor.dark_sky_daily_max_precip_intensity_1
    - sensor.dark_sky_daily_max_precip_intensity_2
    - sensor.dark_sky_daily_max_precip_intensity_3
    - sensor.dark_sky_daily_max_precip_intensity_4
    - sensor.dark_sky_daily_max_precip_intensity_5
    - sensor.dark_sky_daily_max_precip_intensity_6
    - sensor.dark_sky_daily_max_precip_intensity_7
    - sensor.dark_sky_dew_point
    - sensor.dark_sky_dew_point_1
    - sensor.dark_sky_dew_point_2
    - sensor.dark_sky_dew_point_3
    - sensor.dark_sky_dew_point_4
    - sensor.dark_sky_dew_point_5
    - sensor.dark_sky_dew_point_6
    - sensor.dark_sky_dew_point_7
    - sensor.dark_sky_humidity
    - sensor.dark_sky_humidity_1
    - sensor.dark_sky_humidity_2
    - sensor.dark_sky_humidity_3
    - sensor.dark_sky_humidity_4
    - sensor.dark_sky_humidity_5
    - sensor.dark_sky_humidity_6
    - sensor.dark_sky_humidity_7
    - sensor.dark_sky_icon
    - sensor.dark_sky_icon_1
    - sensor.dark_sky_icon_2
    - sensor.dark_sky_icon_3
    - sensor.dark_sky_icon_4
    - sensor.dark_sky_icon_5
    - sensor.dark_sky_icon_6
    - sensor.dark_sky_icon_7
    - sensor.dark_sky_ozone
    - sensor.dark_sky_ozone_1
    - sensor.dark_sky_ozone_2
    - sensor.dark_sky_ozone_3
    - sensor.dark_sky_ozone_4
    - sensor.dark_sky_ozone_5
    - sensor.dark_sky_ozone_6
    - sensor.dark_sky_ozone_7
    - sensor.dark_sky_precip
    - sensor.dark_sky_precip_1
    - sensor.dark_sky_precip_2
    - sensor.dark_sky_precip_3
    - sensor.dark_sky_precip_4
    - sensor.dark_sky_precip_5
    - sensor.dark_sky_precip_6
    - sensor.dark_sky_precip_7
    - sensor.dark_sky_precip_intensity
    - sensor.dark_sky_precip_intensity_1
    - sensor.dark_sky_precip_intensity_2
    - sensor.dark_sky_precip_intensity_3
    - sensor.dark_sky_precip_intensity_4
    - sensor.dark_sky_precip_intensity_5
    - sensor.dark_sky_precip_intensity_6
    - sensor.dark_sky_precip_intensity_7
    - sensor.dark_sky_precip_probability
    - sensor.dark_sky_precip_probability_1
    - sensor.dark_sky_precip_probability_2
    - sensor.dark_sky_precip_probability_3
    - sensor.dark_sky_precip_probability_4
    - sensor.dark_sky_precip_probability_5
    - sensor.dark_sky_precip_probability_6
    - sensor.dark_sky_precip_probability_7
    - sensor.dark_sky_pressure
    - sensor.dark_sky_pressure_1
    - sensor.dark_sky_pressure_2
    - sensor.dark_sky_pressure_3
    - sensor.dark_sky_pressure_4
    - sensor.dark_sky_pressure_5
    - sensor.dark_sky_pressure_6
    - sensor.dark_sky_pressure_7
    - sensor.dark_sky_visibility
    - sensor.dark_sky_visibility_1
    - sensor.dark_sky_visibility_2
    - sensor.dark_sky_visibility_3
    - sensor.dark_sky_visibility_4
    - sensor.dark_sky_visibility_5
    - sensor.dark_sky_visibility_6
    - sensor.dark_sky_visibility_7
    - sensor.dark_sky_wind_bearing
    - sensor.dark_sky_wind_bearing_1
    - sensor.dark_sky_wind_bearing_2
    - sensor.dark_sky_wind_bearing_3
    - sensor.dark_sky_wind_bearing_4
    - sensor.dark_sky_wind_bearing_5
    - sensor.dark_sky_wind_bearing_6
    - sensor.dark_sky_wind_bearing_7
    - sensor.dark_sky_wind_speed_1
    - sensor.dark_sky_wind_speed_2
    - sensor.dark_sky_wind_speed_3
    - sensor.dark_sky_wind_speed_4
    - sensor.dark_sky_wind_speed_5
    - sensor.dark_sky_wind_speed_6
    - sensor.dark_sky_wind_speed_7

danodemano commented 6 years ago

@OverloadUT - I'm pretty convinced you hit the nail on the head and this is related to the purge process. No issues now ~6 days later (other than my database size). And none of those foreign key errors in the logs. So where does that leave us?

amelchio commented 6 years ago

So we must have a race in the purge task. Do you have SQL experience so you can find the event that is causing the problem with some manual SQL?

danodemano commented 6 years ago

@amelchio - I know enough to be dangerous. :D I'm good at following directions and can run whatever SQL needed to track down the issue.

danodemano commented 6 years ago

Had it again with the default purge settings which I guess is 10 days according to the docs. Looks to be the same thing:

2018-05-24 19:04:02 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 5, 14, 23, 0, 32, 176398, tzinfo=<UTC>), 1311349, 6111570, 6111571, 6111572, 6111573, 6111611, 6111626, 6111629, 6111663, 6111680, 6111687, 6111691, 6111694, 6111695, 6111744, 6111745, 6111746, 6111747, 6111748, 6111749, 6111750, 6111751, 6111752, 6111753, 6111754, 6111755, 6111756, 6111758, 6111759, 6111760, 6111761, 6111762, 6111763, 6111764, 6111767, 6111768, 6111769, 6111770, 6111771, 6111774, 6111775, 6111776, 6111777, 6111778, 6111779, 6111780, 6111781, 6111799, 6111800, 6111801, 6111811, 6111825, 6111831, 6111834, 6111844, 6111910, 6111911, 6111912, 6111913, 6111924, 6111926, 6111929, 6111930, 6111932, 6111933, 6111937, 6111956, 6111958, 6111959, 6111969, 6111970, 6112011, 6112014, 6112025, 6112026, 6112029, 6112030, 6112034, 6112037, 6112043, 6112046, 6112048, 6112049, 6112054, 6112066, 6112068, 6112076, 6112077, 6112078, 6112101, 6112103, 6112104, 6112105, 6112106, 6112107, 6112108, 6112109, 6112110, 6112111, 6112112, 6112114, 6112116, 6112117, 6112118, 6112119, 6112120, 6112123, 6112124, 6112126, 6112127, 6112128, 6112129, 6112130, 6112132, 6112133, 6112134, 6112139, 6112179, 6112181, 6112182, 6112183, 6112184, 6112185, 6112186, 6112187, 6112188, 6112189, 6112190, 6112191, 6112193, 6112195, 6112197, 6112199, 6112201, 6112204, 6112206, 6112208, 6112210, 6112211, 6112212, 6112213, 6112214, 6112216, 6112218, 6112220, 6112229, 6112282, 6112298, 6112299, 6112301, 6112317, 6112318, 6112319, 6112323, 6112324, 6112327, 6112328, 6112329, 6112330, 6112332, 6112333, 6112335, 6112336, 6112340, 6112343, 6112346, 6112347, 6112351, 6112353, 6112354, 6112358, 6112360, 6112368, 6112369, 6112370, 6112376, 6112379, 6112382, 6112387, 6112388, 6112390, 6112391, 6112392, 6112393, 6112394, 6112395, 6112396, 6112400, 6112401, 6112402, 6112403, 6112404, 6112406, 6112411, 6112412, 6112413, 6112415, 6112420, 6112421, 6112422, 6112424, 6112429, 6112431, 6112433, 6112438, 6112439, 6112440, 6112441, 6112443, 6112446, 6112447, 6112448, 6112449, 6112450, 6112451, 6112454, 6112456, 6112457, 6112458, 6112461, 6112465, 6112468, 6112469, 6112470, 6112471, 6112476, 6112477, 6112478, 6112484, 6112485, 6112488, 6112490, 6112491, 6112496, 6112497, 6112498, 6112499, 6112501, 6112504, 6112505, 6112506, 6112507, 6112509, 6112513, 6112514, 6112515, 6112516, 6112519, 6112522, 6112523, 6112526, 6112527, 6112529, 6112530, 6112532, 6112534, 6112535, 6112539, 6112542, 6112543, 6112544, 6112546, 6112547, 6112549, 6112550, 6112553, 6112555, 6112556, 6112558, 6112560, 6112561, 6112562, 6112565, 6112568, 6112569, 6112617, 6112619, 6112621, 6112623, 6112625, 6112627, 6112629, 6112633, 6112634, 6112635, 6112644, 6112650, 6112690, 6112715, 6112760, 6112762, 6112764, 6112766, 6112774, 6112775, 6112786, 6112787, 6112790, 6112791, 6112793, 6112797, 6112807, 6112809, 6112811, 6112813, 6112815, 6112817, 6112837, 6112847, 6112851, 6112853, 6112855, 6112857, 6112859, 6112861, 6112863, 6112865, 6112867, 6112869, 6112872, 6112874, 6112875, 6112885, 6115108, 6115109, 6115196, 6115200, 6115201, 6115203, 6115205, 6115207, 6115208, 6115209, 6115210, 6115231, 6115237, 6115238, 6115277, 6116346, 6116347, 6116348, 6116349, 6119531, 6121530, 6147302, 6147303, 6147304, 6163180, 6163181, 6163182, 6163183, 6181913, 6181914, 6181915, 6181916, 6320493, 6326717, 6326718, 6390609, 6407848, 6407850, 6411922, 6412513, 6422981, 6422982, 6422986, 6901780, 6963520, 6963521, 6963564, 6963565, 6964648, 6964649, 6964650, 6965124, 6965125, 6965126, 6980903, 6981143, 7047429, 7178729, 7223324, 7223348, 7316693, 7316694, 7316695, 7447021, 7478461, 7478462, 7478464, 7524901, 7557409, 7584763, 7655163, 7655167, 7680033, 7680973, 7733666, 7733871, 7737803, 7740380, 7740381, 7740383, 7740384, 7745472, 7749158, 7754977, 7756171, 7756176, 7766632, 7766717, 7778598, 7799175, 7812226, 7812228, 7812229, 7812230, 7812231, 7812232, 7812233, 7812234, 7812235, 7812551, 7815376, 7815377, 7815381, 7866374, 7875775, 7881873, 7889991, 7889993, 7959049, 7959497, 7959501, 7959516, 7959518, 7959521, 7959722, 7959723, 7959724, 7960217, 7960218, 7960220, 7960221, 7960679, 7960680, 7960691, 7961326, 7961327, 7962718, 7962725, 7962731, 7962733, 7982045, 7982435, 7982436, 7984263, 7992505, 7992522, 7992883, 7992885, 7996177, 7999059, 7999677, 7999678, 8001519, 8002033, 8002280, 8002282, 8002288, 8002289, 8002292, 8002297, 8002301, 8002302, 8002304, 8003762, 8004769, 8006999, 8007064, 8007534, 8007720, 8007863, 8008361, 8008445, 8013221, 8013222, 8013223, 8013224, 8014136, 8014858, 8014866, 8015889, 8015892, 8016066, 8016067, 8016068, 8016164, 8016165, 8016166, 8016420, 8016990, 8016998, 8017163, 8019042, 8019970, 8019971, 8019972, 8021604, 8022810, 8022830, 8022989, 8023393, 8023654, 8023655, 8023656, 8023657, 8024047, 8027162, 8028477, 8028495, 8028532, 8028533, 8028534, 8028623, 8029939, 8030589, 8030631, 8030926, 8030927, 8030928, 8030929, 8030930, 8031536, 8031537, 8031538, 8031539, 8031540, 8031541, 8031542, 8031543, 8031544, 8031545, 8031546, 8031547, 8031548, 8031549, 8031550, 8031551, 8032032, 8032059, 8032105, 8032152, 8032262, 8032267, 8032292, 8032383, 8032384, 8032385, 8032386, 8032387, 8032470, 8032471, 8032472, 8032719, 8032720, 8032721, 8032778, 8032799, 8032800, 8032801, 8032802, 8032803, 8032804, 8032805, 8032806, 8032807, 8032808, 8032809, 8032810, 8032811, 8032812, 8032813, 8032814, 8032815, 8032922, 8032923, 8032924, 8032925, 8032926, 8032927, 8032928, 8032975, 8032976, 8032977, 8032978, 8032979, 8032980, 8032981, 8032982, 8032983, 8032985, 8033004, 8033021, 8033022, 8033024, 8033026, 8033058, 8033064, 8033065, 8033067, 8033068, 8033069, 8033071, 8033072, 8033074, 8033075, 8033076, 8033077, 8033078, 8033081, 8033082, 8033083, 8033084, 8033085, 8033086, 8033087, 8033088, 8033089, 8033090, 8033091, 8033092, 8033093, 8033094, 8033095, 8033096, 8033097, 8033098, 8033099, 8033100, 8033101, 8033102, 8033103, 8033104, 8033105, 8033106, 8033107, 8033108, 8033109, 8033110, 8033111, 8033112, 8033113, 8033114, 8033115, 8033116, 8033117, 8033118, 8033119)] (Background on this error at: http://sqlalche.me/e/gkpj)

danodemano commented 6 years ago

There isn't any good rhyme or reason to it running. It was only up for ~40 minutes this morning after I posted that before it stopped again.

2018-05-25 10:20:07 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 5, 15, 14, 8, 53, 186491, tzinfo=<UTC>), 1311349, 7982045, 7984263, 8016420, 8016990, 8016998, 8017163, 8019042, 8022810, 8022830, 8033183, 8033205, 8033234, 8033236, 8033239, 8033240, 8033241, 8033242, 8033243, 8033244, 8033245, 8033246, 8033251, 8033266, 8033312, 8033313, 8033314, 8033315, 8033316, 8033317, 8033318, 8033319, 8033320, 8033321, 8033322, 8033323, 8033324, 8033325, 8033326, 8033327, 8033328, 8033329, 8033330, 8033331, 8033332, 8033333, 8033334, 8033335, 8033336, 8033337, 8033338, 8033339, 8033340, 8033341, 8033342, 8033343, 8033344, 8033345, 8033346, 8033347, 8033348, 8033349, 8033357, 8033358, 8033359, 8033360, 8033365, 8033368, 8033387, 8033394, 8033397, 8033418, 8033419, 8033420, 8033421, 8033432, 8033433, 8033442, 8033443, 8033444, 8033445, 8033446, 8033449, 8033450, 8033451, 8033453, 8033454, 8033455, 8033462, 8033487, 8033488, 8033491, 8033500, 8033524, 8033525, 8033526, 8033527, 8033528, 8033529, 8033530, 8033531, 8033532, 8033533, 8033534, 8033535, 8033536, 8033537, 8033538, 8033548, 8033549, 8033550, 8033554, 8033556, 8033557, 8033558, 8033559, 8033569, 8033571, 8033572, 8033573, 8033574, 8033575, 8033576, 8033577, 8033578, 8033581, 8033582, 8033583, 8033586, 8033587, 8033625, 8033626, 8033630, 8033636, 8033637, 8033638, 8033639, 8033640, 8033642, 8033643, 8033644, 8033645, 8033646, 8033647, 8033648, 8033652, 8033653, 8033654, 8033658, 8033661, 8033669, 8033670, 8033671, 8033674, 8033675, 8033676, 8033677, 8033680, 8033681, 8033682, 8033683, 8033684, 8033685, 8033686, 8033687, 8033688, 8033689, 8033690, 8033695, 8033696, 8033697, 8033698, 8033702, 8033703, 8033704, 8033705, 8033706, 8033707, 8033708, 8033710, 8033711, 8033713, 8033715, 8033717, 8033718, 8033719, 8033720, 8033721, 8033722, 8033723, 8033724, 8033729, 8033732, 8033733, 8033734, 8033735, 8033738, 8033739, 8033740, 8033741, 8033747, 8033748, 8033749, 8033771, 8033780, 8033810, 8033811, 8033812, 8033813, 8033814, 8033816, 8033818, 8033820, 8033822, 8033823, 8033824, 8033826, 8033828, 8033829, 8033830, 8033831, 8033832, 8033833, 8033834, 8033836, 8033837, 8033838, 8033839, 8033840, 8033841, 8033842, 8033844, 8033847, 8033848, 8033849, 8033850, 8033851, 8033852, 8033853, 8033854, 8033856, 8033857, 8033858, 8033863, 8033864, 8033865, 8033870, 8033871, 8033874, 8033884, 8033888, 8033890, 8033896, 8033912, 8033913, 8033915, 8033919, 8033920, 8033921, 8033922, 8033923, 8033925, 8033927, 8033932, 8033933, 8033934, 8033935, 8033936, 8033952, 8033953, 8033954, 8033955, 8033956, 8033957, 8033958, 8033965, 8033966, 8033969, 8033970, 8033972, 8033973, 8033976, 8033978, 8033979, 8033983, 8033984, 8033987, 8033991, 8033994, 8033996, 8033997, 8033999, 8034000, 8034002, 8034003, 8034004, 8034006, 8034011, 8034013, 8034017, 8034020, 8034024, 8034025, 8034026, 8034027, 8034028, 8034033, 8034035, 8034038, 8034039, 8034042, 8034043, 8034045, 8034046, 8034047, 8034050, 8034051, 8034052, 8034053, 8034054, 8034055, 8034058, 8034059, 8034060, 8034064, 8034068, 8034073, 8034078, 8034079, 8034081, 8034082, 8034084, 8034085, 8034087, 8034090, 8034091, 8034092, 8034094, 8034099, 8034100, 8034101, 8034107, 8034111, 8034117, 8034121, 8034122, 8034123, 8034124, 8034126, 8034127, 8034130, 8034131, 8034132, 8034134, 8034136, 8034139, 8034142, 8034143, 8034144, 8034146, 8034149, 8034150, 8034151, 8034154, 8034158, 8034163, 8034165, 8034167, 8034168, 8034171, 8034173, 8034176, 8034177, 8034180, 8034182, 8034183, 8034185, 8034186, 8034187, 8034192, 8034194, 8034195, 8034199, 8034200, 8034205, 8034206, 8034207, 8034208, 8034209, 8034211, 8034218, 8034219, 8034220, 8034261, 8034264, 8034265, 8034268, 8034269, 8034271, 8034273, 8034276, 8034277, 8034279, 8034292, 8034300, 8034353, 8034354, 8034355, 8034356, 8034361, 8034363, 8034365, 8034368, 8034369, 8034371, 8034375, 8034381, 8034386, 8034388, 8034390, 8034393, 8034394, 8034396, 8034400, 8034403, 8034404, 8034405, 8034406, 8034408, 8034411, 8034413, 8034414, 8034416, 8034418, 8034420, 8034422, 8034424, 8034426, 8034428, 8034430, 8034432, 8034436, 8034465, 8034467, 8034469, 8034471, 8034473, 8034475, 8034479, 8034489, 8034494, 8034497, 8034499, 8034500, 8034502, 8034504, 8034506, 8034509, 8034510, 8034513, 8034515, 8034517, 8034518, 8034520, 8034525, 8034529, 8034534, 8034539, 8034540, 8035992, 8036834, 8036836, 8036837, 8036838, 8036931, 8037004, 8037009, 8037010, 8037016, 8037022, 8037023, 8037024, 8037025, 8037027, 8037067, 8037121, 8037179, 8037181, 8037182, 8037209, 8037480, 8037486, 8039198, 8040805, 8040943, 8040944, 8040945, 8040946, 8041128, 8042980, 8043154, 8043464, 8043947, 8044015, 8044269, 8044310, 8044311, 8044908, 8045055, 8045408, 8045409, 8045410, 8045935, 8045936, 8045937, 8045938, 8045999, 8046060, 8046064, 8046177, 8046178, 8046179, 8046180, 8046181, 8046182, 8046183, 8046184, 8046185, 8046186, 8046187, 8046188, 8046189, 8046190, 8046191, 8046192, 8046193, 8046194, 8046195, 8046196, 8046197, 8046198, 8046199, 8046200, 8046313, 8047129, 8047130, 8047131, 8047248, 8047367, 8047414, 8047500, 8047501, 8047502, 8047503, 8047505, 8047628, 8047732, 8048038, 8048072, 8048086, 8048092, 8048181, 8048182, 8048183, 8048184, 8048227, 8048239, 8048241, 8048242, 8048244, 8048268, 8048271, 8048272, 8048273, 8048274, 8048275, 8048276, 8048277, 8048283, 8048284, 8048285, 8048286, 8048287, 8048288, 8048289, 8048295, 8048296, 8048298, 8048299, 8048308, 8048309, 8048311, 8048313, 8048314, 8048317, 8048318, 8048320, 8048322, 8048323, 8048324, 8048325, 8048326, 8048327, 8048332, 8048333, 8048334, 8048335, 8048336, 8048337, 8048338, 8048339, 8048340, 8048341, 8048342, 8048343, 8048344, 8048345, 8048346, 8048347, 8048348, 8048349, 8048350, 8048351, 8048352, 8048353, 8048354, 8048355, 8048356, 8048357, 8048371, 8048372, 8048373, 8048374, 8048375, 8048376, 8048377, 8048378, 8048379, 8048380, 8048381, 8048382, 8048383, 8048384, 8048385, 8048386, 8048387, 8048392, 8048393, 8048394, 8048395, 8048396, 8048397, 8048398, 8048399, 8048400, 8048401, 8048402, 8048403, 8048404)] (Background on this error at: http://sqlalche.me/e/gkpj)

danodemano commented 6 years ago

And 45 minutes:

2018-05-25 15:13:15 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 5, 15, 19, 1, 25, 529473, tzinfo=<UTC>), 1311349, 7982045, 8017163, 8019042, 8048470, 8048471, 8048472, 8048473, 8048474, 8048475, 8048476, 8048478, 8048501, 8048531, 8048538, 8048551, 8048566, 8048569, 8048613, 8048614, 8048615, 8048616, 8048617, 8048618, 8048619, 8048620, 8048621, 8048622, 8048623, 8048624, 8048625, 8048626, 8048627, 8048628, 8048629, 8048630, 8048631, 8048632, 8048633, 8048634, 8048635, 8048636, 8048637, 8048638, 8048639, 8048640, 8048641, 8048642, 8048643, 8048644, 8048645, 8048646, 8048647, 8048648, 8048649, 8048650, 8048657, 8048658, 8048660, 8048661, 8048666, 8048677, 8048682, 8048686, 8048687, 8048688, 8048689, 8048691, 8048693, 8048694, 8048695, 8048696, 8048729, 8048730, 8048732, 8048735, 8048738, 8048739, 8048740, 8048741, 8048777, 8048778, 8048794, 8048802, 8048805, 8048806, 8048807, 8048808, 8048809, 8048810, 8048811, 8048812, 8048813, 8048814, 8048815, 8048816, 8048826, 8048830, 8048831, 8048832, 8048834, 8048835, 8048837, 8048841, 8048842, 8048843, 8048845, 8048848, 8048849, 8048850, 8048852, 8048854, 8048855, 8048856, 8048858, 8048860, 8048863, 8048866, 8048867, 8048868, 8048870, 8048877, 8048880, 8048881, 8048882, 8048883, 8048885, 8048886, 8048905, 8048906, 8048907, 8048908, 8048909, 8048910, 8048911, 8048912, 8048913, 8048914, 8048915, 8048916, 8048917, 8048919, 8048922, 8048925, 8048926, 8048934, 8048935, 8048936, 8048937, 8048938, 8048939, 8048940, 8048942, 8048943, 8048944, 8048946, 8048947, 8048948, 8048949, 8048950, 8048951, 8048952, 8048953, 8048954, 8048955, 8048960, 8048961, 8048962, 8048963, 8048965, 8048966, 8048967, 8048968, 8048971, 8048973, 8048974, 8048975, 8048976, 8048977, 8048978, 8048979, 8048981, 8048982, 8048983, 8048984, 8048985, 8048986, 8048987, 8048989, 8048990, 8048991, 8048993, 8048994, 8048999, 8049002, 8049003, 8049008, 8049010, 8049011, 8049013, 8049015, 8049049, 8049057, 8049072, 8049093, 8049095, 8049097, 8049100, 8049102, 8049104, 8049105, 8049106, 8049107, 8049108, 8049109, 8049110, 8049111, 8049113, 8049114, 8049115, 8049116, 8049118, 8049119, 8049120, 8049121, 8049122, 8049123, 8049124, 8049125, 8049126, 8049127, 8049128, 8049129, 8049130, 8049131, 8049132, 8049133, 8049134, 8049136, 8049138, 8049140, 8049141, 8049146, 8049148, 8049163, 8049167, 8049168, 8049176, 8049183, 8049193, 8049195, 8049196, 8049197, 8049202, 8049203, 8049204, 8049205, 8049206, 8049217, 8049218, 8049236, 8049240, 8049241, 8049244, 8049251, 8049254, 8049255, 8049256, 8049257, 8049258, 8049259, 8049260, 8049261, 8049268, 8049269, 8049275, 8049276, 8049278, 8049282, 8049283, 8049285, 8049287, 8049288, 8049296, 8049297, 8049298, 8049299, 8049300, 8049308, 8049311, 8049312, 8049316, 8049317, 8049318, 8049322, 8049323, 8049324, 8049325, 8049331, 8049332, 8049335, 8049336, 8049337, 8049344, 8049345, 8049350, 8049351, 8049352, 8049354, 8049355, 8049356, 8049357, 8049362, 8049365, 8049366, 8049368, 8049369, 8049370, 8049379, 8049380, 8049382, 8049384, 8049385, 8049391, 8049392, 8049394, 8049396, 8049401, 8049402, 8049403, 8049404, 8049405, 8049406, 8049408, 8049409, 8049410, 8049415, 8049416, 8049417, 8049418, 8049419, 8049421, 8049422, 8049432, 8049434, 8049435, 8049436, 8049437, 8049438, 8049439, 8049440, 8049441, 8049448, 8049453, 8049454, 8049455, 8049456, 8049457, 8049459, 8049462, 8049468, 8049470, 8049471, 8049472, 8049476, 8049477, 8049479, 8049480, 8049483, 8049488, 8049489, 8049490, 8049491, 8049492, 8049493, 8049494, 8049495, 8049497, 8049501, 8049502, 8049503, 8049504, 8049505, 8049506, 8049507, 8049509, 8049541, 8049547, 8049548, 8049549, 8049550, 8049552, 8049558, 8049562, 8049563, 8049564, 8049575, 8049577, 8049578, 8049580, 8049630, 8049633, 8049636, 8049640, 8049641, 8049642, 8049643, 8049644, 8049645, 8049653, 8049657, 8049664, 8049667, 8049669, 8049671, 8049673, 8049676, 8049682, 8049686, 8049692, 8049693, 8049695, 8049697, 8049699, 8049702, 8049703, 8049706, 8049707, 8049710, 8049711, 8049715, 8049724, 8049725, 8049726, 8049727, 8049729, 8049746, 8049748, 8049750, 8049752, 8049754, 8049756, 8049762, 8049764, 8049767, 8049768, 8049770, 8049772, 8049776, 8049779, 8049780, 8049782, 8049784, 8049789, 8049792, 8049793, 8049794, 8049795, 8049798, 8049799, 8049800, 8049802, 8049807, 8049811, 8049816, 8049820, 8049823, 8049926, 8051686, 8051687, 8051788, 8051792, 8051793, 8051795, 8051799, 8051800, 8051801, 8051802, 8051803, 8051981, 8052066, 8052081, 8052082, 8052083, 8052120, 8052609, 8053625, 8054271, 8054483, 8055108, 8055392, 8057086, 8057145, 8057146, 8057147, 8057148, 8057275, 8057467, 8057468, 8058855, 8058868, 8059024, 8059534, 8059535, 8059536, 8059622, 8059623, 8059655, 8059678, 8059679, 8059727, 8059728, 8059729, 8059730, 8059731, 8059732, 8059733, 8059734, 8059735, 8059736, 8059737, 8059738, 8059739, 8059740, 8059741, 8059742, 8059743, 8059744, 8059745, 8059746, 8059747, 8059748, 8059749, 8059750, 8059751, 8059752, 8059753, 8059754, 8059755, 8060220, 8060221, 8060510, 8060657, 8060753, 8060754, 8060767, 8060789, 8060802, 8060829, 8060863, 8060864, 8060865, 8060866, 8060867, 8060868, 8060869, 8060870, 8060871, 8060872, 8060873, 8060875, 8060876, 8060888, 8060909, 8060910, 8060913, 8060916, 8060974, 8060979, 8060980, 8060981, 8060982, 8061006, 8061014, 8061019, 8061025, 8061053, 8061054, 8061055, 8061056, 8061087, 8061088, 8061090, 8061092, 8061108, 8061228, 8061229, 8061231, 8061259, 8061260, 8061261, 8061262, 8061263, 8061264, 8061265, 8061266, 8061267, 8061268, 8061269, 8061270, 8061271, 8061272, 8061273, 8061274, 8061275, 8061276, 8061277, 8061278, 8061303, 8061306, 8061309, 8061313, 8061315, 8061320, 8061355, 8061360, 8061361, 8061363, 8061365, 8061366, 8061371, 8061374, 8061375, 8061377, 8061378, 8061379, 8061380, 8061381, 8061382, 8061383, 8061384, 8061385, 8061386, 8061387, 8061388, 8061389, 8061390, 8061391, 8061392, 8061393, 8061394, 8061395, 8061396, 8061397, 8061398, 8061399, 8061400, 8061401, 8061402, 8061403, 8061404, 8061405, 8061406, 8061407, 8061408, 8061409, 8061410, 8061411, 8061412)] (Background on this error at: http://sqlalche.me/e/gkpj)

amelchio commented 6 years ago

The purge action will start 30 minutes after startup so this is not so strange. If you are willing to share the MySQL datadir, I could have a look (it probably has sensitive data, so don't publish it publicly).

danodemano commented 6 years ago

@amelchio that makes sense then. But it doesn't always do it which is what's odd. I have the DB, what's the best way to get it to you? Compressed it's ~275MB.

amelchio commented 6 years ago

I currently do not have the time to investigate this after all, sorry.

danodemano commented 6 years ago

No worries. If anyone could at least point me in the right direction I could at least make an attempt to track it down.

balloobbot commented 6 years ago

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.

Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment :+1:

danodemano commented 6 years ago

This is still an issue as of 0.77.3:

Still the same error in the logs too:

2018-09-06 11:18:52 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s AND events.event_id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (datetime.datetime(2018, 9, 4, 15, 18, 30, 693024, tzinfo=<UTC>), 1127750, 1185224, 1185366, 1193841, 1819138, 2862361, 2862362, 2862366, 3180436, 3196103, 3329056, 3477839, 4410266, 5004888, 5492891, 5492904, 5547162, 5672108, 5724998, 5725213, 5725214, 5725240, 5725254, 5725272, 5725273, 5725274, 5725275, 5725276, 5725278, 5725283, 5725292, 5725302, 5725327, 5725330, 5725331, 5725376, 5725377, 5725378, 5725379, 5725380, 5725381, 5725382, 5725383, 5725384, 5725385, 5725386, 5725387, 5725389, 5725390, 5725391, 5725392, 5725393, 5725394, 5725395, 5725396, 5725397, 5725398, 5725399, 5725400, 5725401, 5725402, 5725403, 5725404, 5725405, 5725406, 5725407, 5725408, 5725409, 5725410, 5725411, 5725412, 5725413, 5725414, 5725420, 5725421, 5725422, 5725423, 5725424, 5725429, 5725430, 5725431, 5725447, 5725466, 5725471, 5725472, 5725476, 5725477, 5725478, 5725483, 5725498, 5725500, 5725501, 5725502, 5725503, 5725505, 5725507, 5725510, 5725511, 5725512, 5725515, 5725516, 5725518, 5725520, 5725524, 5725543, 5725544, 5725545, 5725551, 5725553, 5725560, 5725562, 5725566, 5725568, 5725570, 5725571, 5725572, 5725573, 5725574, 5725575, 5725586, 5725589, 5725591, 5725592, 5725594, 5725597, 5725603, 5725615, 5725617, 5725618, 5725620, 5725622, 5725626, 5725630, 5725643, 5725650, 5725654, 5725657, 5725665, 5725668, 5725669, 5725670, 5725672, 5725673, 5725676, 5725677, 5725681, 5725683, 5725691, 5725697, 5725699, 5725700, 5725701, 5725702, 5725706, 5725707, 5725708, 5725709, 5725712, 5725713, 5725714, 5725715, 5725716, 5725717, 5725722, 5725723, 5725725, 5725727, 5725729, 5725730, 5725732, 5725734, 5725735, 5725736, 5725737, 5725738, 5725739, 5725740, 5725741, 5725742, 5725744, 5725746, 5725750, 5725751, 5725752, 5725753, 5725754, 5725756, 5725757, 5725758, 5725762, 5725766, 5725767, 5725769, 5725771, 5725780, 5725786, 5725790, 5725791, 5725792, 5725793, 5725794, 5725795, 5725796, 5725797, 5725798, 5725799, 5725813, 5725814, 5725816, 5725817, 5725818, 5725819, 5725820, 5725821, 5725822, 5725823, 5725824, 5725825, 5725826, 5725828, 5725829, 5725830, 5725831, 5725839, 5725841, 5725842, 5725843, 5725844, 5725845, 5725846, 5725867, 5725878, 5725880, 5725881, 5725882, 5725884, 5725885, 5725886, 5725887, 5725888, 5725889, 5725890, 5725891, 5725893, 5725895, 5725897, 5725898, 5725900, 5725902, 5725905, 5725908, 5725910, 5725911, 5725913, 5725914, 5725915, 5725916, 5725917, 5725918, 5725919, 5725920, 5725921, 5725923, 5725925, 5725932, 5725933, 5725934, 5725939, 5725944, 5725955, 5725956, 5725957, 5725958, 5725967, 5725983, 5725984, 5725986, 5725993, 5726003, 5726005, 5726066, 5726068, 5726070, 5726072, 5726074, 5726076, 5726078, 5726080, 5726082, 5726084, 5726086, 5726096, 5726115, 5726151, 5726170, 5726181, 5726183, 5726185, 5726187, 5726189, 5726202, 5726203, 5726205, 5726207, 5726210, 5726211, 5726213, 5726215, 5726217, 5726219, 5726221, 5726225, 5726237, 5726239, 5726241, 5726243, 5726245, 5726250, 5726254, 5726264, 5726268, 5726270, 5726272, 5726274, 5726276, 5726278, 5726280, 5726282, 5726284, 5726286, 5726288, 5726290, 5726292, 5726343, 5726344, 5726345, 5726346, 5726347, 5726348, 5726349, 5726350, 5726351, 5726352, 5726353, 5726354, 5726355, 5726356, 5726357, 5726358, 5726359, 5726360, 5726361, 5726362, 5726363, 5726364, 5726365, 5726366, 5726367, 5726368, 5726369, 5726370, 5726371, 5726372, 5726373, 5726374, 5726375, 5726376, 5726381, 5726382, 5726383, 5726384, 5726385, 5726386, 5726387, 5726388, 5726389, 5726390, 5726391, 5726414, 5726415, 5726416, 5726417, 5726418, 5726426, 5726427, 5726428, 5726429, 5726443, 5726444, 5726445, 5726446, 5726447, 5726448, 5726449, 5726450, 5726451, 5726452, 5726453, 5726454, 5726455, 5726456, 5726457, 5726458, 5726459, 5726460, 5726461, 5726462, 5726463, 5726464, 5726466, 5726467, 5726468, 5726469, 5726470, 5726471, 5726472, 5726477, 5726478, 5726479, 5726480, 5726481, 5726482, 5726483, 5726484, 5726485, 5726486, 5726487, 5726488, 5726489, 5726514, 5726515, 5726516, 5726517, 5726518, 5726519, 5726520, 5726521, 5726522, 5726523, 5726525, 5726526, 5726527, 5726528, 5726529, 5726530, 5726531, 5726532, 5726533, 5726534, 5726535, 5726537, 5726538, 5726539, 5726540, 5726541, 5726542, 5726545, 5726546, 5726552, 5726563, 5726566, 5726568, 5726570, 5726572, 5726574, 5726577, 5726580, 5726585, 5726587, 5726589, 5726591, 5726648, 5726689, 5726691, 5727753, 5727756, 5728684, 5728685, 5728691, 5728695, 5728696, 5728697, 5728698, 5728700, 5728702, 5728703, 5728704, 5728724, 5728745, 5728755, 5728765, 5729930, 5729942, 5730762, 5733678, 5733679, 5733680, 5733681, 5733682, 5734795, 5734796, 5734797, 5734798, 5746746, 5748069, 5748070, 5748071, 5748072, 5749983, 5750003, 5751356, 5763696, 5766805, 5766806, 5766808, 5766810, 5770525, 5770526, 5770527, 5770907, 5771137, 5771402, 5771403, 5771881, 5773461, 5773578, 5773579, 5775174, 5775181, 5775182, 5775184, 5775186, 5775187, 5775188, 5775190, 5775194, 5775195, 5775196, 5775197, 5775198, 5775199, 5775200, 5775201, 5775202, 5775206, 5775207, 5775211, 5775221, 5775222, 5777147, 5777540, 5777661, 5778138, 5778784, 5778994, 5778995, 5778996, 5778997, 5778998, 5779278, 5779279, 5779771, 5779772, 5779773, 5780106, 5780112, 5780421, 5780422, 5780423, 5780638, 5780639, 5780640, 5780641, 5780642, 5780659, 5780660, 5780662, 5780663, 5782440, 5783690, 5783916, 5783919, 5783939, 5783940, 5784066, 5784067, 5784068, 5784195, 5784196, 5784421, 5784428, 5784477, 5784478, 5784479, 5784786, 5784977, 5784985, 5784986, 5785032, 5785033, 5785034, 5785035, 5786089, 5786257, 5786421, 5787272, 5787273, 5787274, 5789103, 5789104, 5789105, 5790317, 5790856, 5790883, 5790884, 5790885, 5790886, 5790887, 5790888, 5790889, 5790892, 5790893, 5790894, 5793351, 5794652, 5794654, 5795194, 5797744, 5798384, 5798760, 5798761, 5798813, 5799066, 5799755, 5800048, 5800651, 5800652, 5800662, 5800985, 5801145, 5801861, 5801864, 5801906, 5801908, 5801909, 5801910, 5802037, 5802101, 5802102, 5802103, 5802104, 5802105, 5802106, 5802113, 5802114, 5802115, 5802116, 5802118, 5802119, 5802120, 5802121, 5802122, 5802123, 5802124, 5802148, 5802149, 5802150, 5802165, 5802166, 5802167, 5802168, 5802169, 5802170, 5802171, 5802178, 5802179, 5802180, 5802181, 5802183, 5802184, 5802185, 5802186, 5802187, 5802188, 5802189, 5802215, 5802216, 5802234, 5802235, 5802237, 5802263, 5802264, 5802265, 5802266, 5802267, 5802268, 5802269, 5802270, 5802271, 5802272, 5802273, 5802274, 5802275, 5802276, 5802277, 5802278, 5802279, 5802280, 5802281, 5802305, 5802315, 5802326, 5802336, 5802371, 5802372, 5802373, 5802374, 5802375, 5802376, 5802377, 5802378, 5802379, 5802380, 5802381, 5802382, 5802383, 5802390, 5802391, 5802392, 5802393, 5802394, 5802395, 5802396, 5802397, 5802398, 5802399, 5802400, 5802401, 5802402, 5802403, 5802404, 5802405, 5802406, 5802407, 5802408, 5802409, 5802410, 5802411, 5802412, 5802413, 5802414, 5802415, 5802416, 5802417, 5802418, 5802419, 5802420)] (Background on this error at: http://sqlalche.me/e/gkpj)

timgiwo commented 5 years ago

I have exact same error on 0.84.6

amelchio commented 5 years ago

This could be fixed by #19358 or at least it will fail differently. So please try again with 0.85 when it is released.

danodemano commented 5 years ago

@amelchio - I have yet to see this issue surface on 0.85.1. I changed my database back to MySQL after that release. Hard to say for sure though if it's resolved as it was always sporadic.

@timgiwo - Have you seen any issues since 0.85?

danodemano commented 5 years ago

I spoke too soon, the issue still exists in 0.85.1. Different error though:

2019-01-24 01:16:10 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (hass.states, CONSTRAINTstates_ibfk_1FOREIGN KEY (event_id) REFERENCESevents(event_id))') [SQL: 'DELETE FROM events WHERE events.time_fired < %s'] [parameters: (datetime.datetime(2019, 1, 19, 6, 9, 39, 13614, tzinfo=<UTC>),)] (Background on this error at: http://sqlalche.me/e/gkpj)

amelchio commented 5 years ago

Okay, I see what could be happening. Each state change causes an event to fire and we record both the state and the event. If the purge cut-off is right between the state change and the corresponding event, an inconsistency can happen when deleting the event.

However, that window is tiny so I wonder why you hit it so often.

danodemano commented 5 years ago

I've been fighting with what I suspect to be IO issues in my VM cluster so perhaps that's related? I've got either a network or HDD bottleneck. I'm working on getting some direct-attached SSDs in the mix but my one ESXi server keeps crashing when I use it.

Anyway, sounds like a plausible reason for the query to fail. Any thoughts on a fix then?

amelchio commented 5 years ago

Fixing that is somewhat complicated but I guess the bigger issue here is actually the flatlining and I now made a PR that should fix that: #20424

timgiwo commented 5 years ago

@danodemano I actually increased my purge interval as that seemed to be what caused the error. I don’t think I’ve had to run a purge yet

amelchio commented 5 years ago

The fix is in the 0.87 beta. If you notice the warning after that, please report whether the flatlining is gone.

danodemano commented 5 years ago

Will do, thanks! On another note, this issue also occurs with PostgreSQL though this is the first time I've seen it on that platform:

2019-01-31 11:39:53 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (psycopg2.IntegrityError) update or delete on table "events" violates foreign key constraint "states_event_id_fkey" on table "states"
DETAIL:  Key (event_id)=(8762171) is still referenced from table "states".
 [SQL: 'DELETE FROM events WHERE events.time_fired < %(time_fired_1)s'] [parameters: {'time_fired_1': datetime.datetime(2019, 1, 26, 16, 37, 24, 7336, tzinfo=<UTC>)}] (Background on this error at: http://sqlalche.me/e/gkpj)