{"id":14259,"date":"2026-06-21T15:57:03","date_gmt":"2026-06-21T15:57:03","guid":{"rendered":"https:\/\/www.rjt.org.uk\/home\/?post_type=home_assistant_tip&#038;p=14259"},"modified":"2026-06-21T16:00:30","modified_gmt":"2026-06-21T16:00:30","slug":"migrate-recorder-from-mariadb-to-sqlite","status":"publish","type":"home_assistant_tip","link":"https:\/\/www.rjt.org.uk\/home\/archives\/home-assistant-tip\/migrate-recorder-from-mariadb-to-sqlite\/","title":{"rendered":"Migrate Recorder from MariaDB to SQLite"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\">Tested<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">This guide was live-tested on a real HA installation in June 2026 on x86 hardware running Home Assistant OS. All lessons learned are incorporated.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Background &amp; Why This Is a Good Idea<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQLite is now the\u00a0<strong>default and officially recommended<\/strong>\u00a0database engine for Home Assistant.<\/li>\n\n\n\n<li>HA has been heavily optimised for SQLite over recent years \u2014 it is now considered as fast as MariaDB for typical home use, and more stable.<\/li>\n\n\n\n<li>Removing MariaDB simplifies your stack if you&#8217;re only using it for the recorder.<\/li>\n\n\n\n<li>The HA dev team no longer recommends MariaDB for typical installations.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Decision: Migrate History or Start Fresh?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Choose before starting<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If history doesn&#8217;t matter to you,&nbsp;<a href=\"#Option B \u2013 Fresh Start (No History Preserved)\">Option B \u2013 Fresh Start (No History Preserved)<\/a>&nbsp;is much simpler.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Option<\/th><th>Pros<\/th><th>Cons<\/th><\/tr><\/thead><tbody><tr><td><strong>A \u2013 Migrate with history<\/strong><\/td><td>Keeps long-term statistics (Energy dashboard, etc.)<\/td><td>Requires SSH and command-line tools<\/td><\/tr><tr><td><strong>B \u2013 Fresh start<\/strong><\/td><td>Simple \u2014 10 minutes total<\/td><td>Lose all history and long-term statistics<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Option A \u2013 Full Migration (Preserve Long-Term Statistics)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Prerequisites<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Terminal &amp; SSH add-on installed and running<\/li>\n\n\n\n<li>SSH client on your computer (PuTTY on Windows, built-in terminal on Mac\/Linux)<\/li>\n\n\n\n<li>Free disk space: at least\u00a0<strong>1.5\u00d7 the size of your current MariaDB database<\/strong><\/li>\n\n\n\n<li>~30\u201360 minutes (x86 hardware is much faster than Raspberry Pi)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 1: Take a Full Backup<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Do not skip this<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If anything goes wrong, this is your only safety net.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Go to\u00a0<strong>Settings ? System ? Backups<\/strong><\/li>\n\n\n\n<li>Click\u00a0<strong>Create backup ? Full backup<\/strong><\/li>\n\n\n\n<li>Wait for completion before continuing<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 2: Record Your MariaDB Credentials<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Go to\u00a0<strong>Settings ? Add-ons ? MariaDB ? Configuration tab<\/strong><\/li>\n\n\n\n<li>Note down:\n<ul class=\"wp-block-list\">\n<li><code>database<\/code>\u00a0name<\/li>\n\n\n\n<li><code>username<\/code><\/li>\n\n\n\n<li><code>password<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Consider creating a dedicated migration user<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The default MariaDB password is very long and hard to type. You can create a simpler user via phpMyAdmin. If you do, make sure to grant with&nbsp;<code>'username'@'%'<\/code>&nbsp;(wildcard host) not&nbsp;<code>'username'@'localhost'<\/code>, or you will get&nbsp;<code>ERROR 1045 Access denied<\/code>. See&nbsp;<a href=\"#Troubleshooting\">Troubleshooting<\/a>&nbsp;for details.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 3: Expose MariaDB on a Network Port<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Go to\u00a0<strong>Settings ? Add-ons ? MariaDB ? Configuration ? Network<\/strong><\/li>\n\n\n\n<li>Enable\u00a0<strong>Show disabled ports<\/strong><\/li>\n\n\n\n<li>Set the database engine port to\u00a0<code>3306<\/code><\/li>\n\n\n\n<li>Save and restart the add-on<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Test the connection via SSH<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Before proceeding, verify you can connect:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">mysql -h &lt;HA-IP-ADDRESS> -P 3306 -u &lt;username> -p'yourpassword' &lt;database>\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Password flag syntax<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Always use&nbsp;<code>-p'yourpassword'<\/code>&nbsp;with&nbsp;<strong>no space<\/strong>&nbsp;between&nbsp;<code>-p<\/code>&nbsp;and the password, and&nbsp;<strong>single quotes<\/strong>&nbsp;around it. If you type the password at the prompt, shell special characters can cause silent failures. If the password starts with a letter that matches a CLI flag (e.g.&nbsp;<code>c<\/code>), use&nbsp;<code>--mysql-password 'yourpassword'<\/code>&nbsp;instead \u2014 see&nbsp;<a href=\"#Phase 9 Run the Migration\">Phase 9 Run the Migration<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If connected successfully you&#8217;ll see the MariaDB prompt. Run a quick size check:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT table_schema AS \"Database\",\n  ROUND(SUM(data_length + index_length) \/ 1024 \/ 1024, 1) AS \"Size (MB)\"\nFROM information_schema.tables\nWHERE table_schema = 'homeassistant'\nGROUP BY table_schema;\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Type&nbsp;<code>exit<\/code>&nbsp;to leave.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 4: Shrink the Database<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Important<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This removes short-term history but&nbsp;<strong>preserves long-term statistics<\/strong>. It&#8217;s essential \u2014 migrating a large database without purging first will fail with a&nbsp;<code>Killed<\/code>&nbsp;error.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Go to\u00a0<strong>Developer Tools ? Actions<\/strong><\/li>\n\n\n\n<li>Search for\u00a0<strong><code>recorder.purge<\/code><\/strong>\u00a0(not\u00a0<code>recorder.purge_entities<\/code>\u00a0\u2014 that&#8217;s a different action requiring entity IDs)<\/li>\n\n\n\n<li>Set:\n<ul class=\"wp-block-list\">\n<li><strong>Keep days:<\/strong>\u00a0<code>1<\/code><\/li>\n\n\n\n<li><strong>Repack:<\/strong>\u00a0?<\/li>\n\n\n\n<li><strong>Apply filter:<\/strong>\u00a0?<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Click\u00a0<strong>Perform Action<\/strong><\/li>\n<\/ol>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># YAML mode equivalent\naction: recorder.purge\ndata:\n  keep_days: 1\n  repack: true\n  apply_filter: true\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Wait for completion<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The action returns immediately but runs in the background. The&nbsp;<strong>repack<\/strong>&nbsp;is the slow part. Monitor progress by re-running the size query in phpMyAdmin every 20\u201330 minutes. Only proceed when the size has&nbsp;<strong>stopped dropping<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Check row count to gauge progress:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT COUNT(*) FROM states;\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Check free disk space in SSH:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">df -h \/config\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">You need at least&nbsp;<strong>1.5\u00d7 database size<\/strong>&nbsp;free. 14 GB free for a ~900 MB database is comfortable.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 5: Prepare the SSH Add-on<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Go to\u00a0<strong>Settings ? Add-ons ? Terminal &amp; SSH ? Configuration<\/strong><\/li>\n\n\n\n<li>Set a password under\u00a0<strong>Options<\/strong><\/li>\n\n\n\n<li>Under\u00a0<strong>Network<\/strong>, set SSH port to\u00a0<code>22<\/code><\/li>\n\n\n\n<li>Save and restart<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 6: Connect via SSH<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ssh root@&lt;HA-IP-ADDRESS> -p 22\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 7: Install the Migration Tool<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Virtual environment required<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Recent Alpine Linux versions used by HA OS enforce PEP 668 \u2014&nbsp;<code>pip install<\/code>&nbsp;directly will fail with&nbsp;<code>externally-managed-environment<\/code>. Use a venv instead.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">apk add python3\npython3 -m venv \/root\/migrate-venv\nsource \/root\/migrate-venv\/bin\/activate\npip install mysql-to-sqlite3\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Your prompt will change to&nbsp;<code>(migrate-venv)<\/code>&nbsp;confirming you&#8217;re inside the venv.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you reconnect SSH later<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Re-activate the venv before running the migration:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">source \/root\/migrate-venv\/bin\/activate\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 8: Stop Home Assistant Core<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ha core stop\n<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">HA itself stops, but the OS and add-ons (including MariaDB) remain running. This is correct.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">Verify the SQLite file doesn&#8217;t already exist (important if retrying):<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ls -lh \/config\/home-assistant_v2.db\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If it exists from a previous failed attempt, delete it before re-running:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">rm \/config\/home-assistant_v2.db\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 9: Run the Migration<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Password flag gotcha<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If your password&nbsp;<strong>starts with a letter<\/strong>&nbsp;that matches a&nbsp;<code>mysql2sqlite<\/code>&nbsp;flag (e.g.&nbsp;<code>c<\/code>,&nbsp;<code>h<\/code>,&nbsp;<code>u<\/code>,&nbsp;<code>d<\/code>), using&nbsp;<code>-p'password'<\/code>&nbsp;will cause a parsing error like:<br><code>Error: Invalid value for '-c' \/ '--chunk': '92' is not a valid integer.<\/code><br>Use&nbsp;<code>--mysql-password 'yourpassword'<\/code>&nbsp;instead to avoid this entirely.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">mysql2sqlite \\\n  -f \/config\/home-assistant_v2.db \\\n  -d homeassistant \\\n  -u &lt;username> \\\n  -h &lt;HA-IP-ADDRESS> \\\n  -P 3306 \\\n  --mysql-password 'yourpassword' \\\n  -V \\\n  --use-buffered-cursors\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Monitor progress in a second SSH session:<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">watch -n 5 'ls -lh \/config\/home-assistant_v2.db'\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The file should appear and grow. On x86 hardware a ~900 MB database takes around&nbsp;<strong>2 minutes<\/strong>. On Raspberry Pi expect 20\u201330 minutes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Expected output on completion:<\/strong><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INFO     Vacuuming created SQLite database file.\nThis might take a while.\nINFO     Done!\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Troubleshooting<\/h4>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Error<\/th><th>Cause<\/th><th>Fix<\/th><\/tr><\/thead><tbody><tr><td><code>Killed<\/code>&nbsp;during&nbsp;<code>states<\/code>&nbsp;table<\/td><td>Database still too large<\/td><td>Run another purge (Phase 4) and wait longer<\/td><\/tr><tr><td><code>table states has no column named domain<\/code><\/td><td>Stale partial migration file<\/td><td><code>rm \/config\/home-assistant_v2.db<\/code>&nbsp;and re-run<\/td><\/tr><tr><td><code>Invalid value for '-c'<\/code><\/td><td>Password starts with a flag letter<\/td><td>Use&nbsp;<code>--mysql-password 'yourpassword'<\/code>&nbsp;instead of&nbsp;<code>-p<\/code><\/td><\/tr><tr><td><code>ERROR 1045 Access denied<\/code><\/td><td>User host mismatch<\/td><td>Ensure grant uses&nbsp;<code>'user'@'%'<\/code>&nbsp;not&nbsp;<code>'user'@'localhost'<\/code><\/td><\/tr><tr><td>Out of memory<\/td><td>Insufficient RAM on HA hardware<\/td><td>Run migration on a PC\/VM, copy&nbsp;<code>.db<\/code>&nbsp;to&nbsp;<code>\/config\/<\/code>, continue from Phase 10<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 10: Update configuration.yaml<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">nano \/config\/configuration.yaml\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Comment out the MariaDB&nbsp;<code>db_url<\/code>&nbsp;line:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"yaml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">recorder:\n  # db_url: mysql:\/\/user:password@core-mariadb\/homeassistant?charset=utf8mb4\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Save with&nbsp;<code>Ctrl+O<\/code>, exit with&nbsp;<code>Ctrl+X<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 11: Start Home Assistant Core<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bash\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ha core start\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 12: Verify<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open HA in your browser<\/li>\n\n\n\n<li><strong>Settings ? System ? Logs<\/strong>\u00a0\u2014 check for database errors<\/li>\n\n\n\n<li><strong>Energy Dashboard<\/strong>\u00a0\u2014 scroll back several months, long-term statistics should be intact<\/li>\n\n\n\n<li><strong>History<\/strong>\u00a0\u2014 will only go back ~1 day (expected, due to Phase 4 purge)<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Post-startup log errors to expect and fix<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">SQL sensor errors after migration<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you had a sensor querying MariaDB&#8217;s&nbsp;<code>information_schema.tables<\/code>&nbsp;for database size, it will error immediately on SQLite:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ERROR (DbWorker_0) [homeassistant.components.sql.sensor] \nno such table: information_schema.tables\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Replace the query in your&nbsp;<code>configuration.yaml<\/code>&nbsp;with the SQLite equivalent:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT ROUND(page_count * page_size \/ 1024 \/ 1024, 1) AS size\nFROM pragma_page_count(), pragma_page_size();\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Use&nbsp;<code>size<\/code>&nbsp;as the column value. Restart HA after updating.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">MariaDB stats error<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You may also see:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Failed to call \/addons\/core_mariadb\/stats - Add-on core_mariadb is not running\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is harmless and will stop once you uninstall MariaDB in Phase 14.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 13: Rollback (if needed)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If anything isn&#8217;t right, reverting to MariaDB is instant:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Edit\u00a0<code>\/config\/configuration.yaml<\/code>\u00a0and\u00a0<strong>uncomment<\/strong>\u00a0the\u00a0<code>db_url<\/code>\u00a0line<\/li>\n\n\n\n<li>Restart Home Assistant<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Your MariaDB data is completely untouched.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Phase 14: Clean Up<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Wait a few days before uninstalling<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Leave MariaDB installed as a safety net while you confirm everything is working correctly \u2014 history loads, Energy dashboard is intact, no recurring errors.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When satisfied:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Settings ? Add-ons ? MariaDB ? Uninstall<\/strong><\/li>\n<\/ol>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">This permanently deletes all MariaDB data. Only do this when you&#8217;re confident.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Option B \u2013 Fresh Start (No History Preserved)<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Take a full backup<\/strong>\u00a0\u2014 Settings ? System ? Backups ? Create backup<\/li>\n\n\n\n<li>Open\u00a0<code>\/config\/configuration.yaml<\/code>\u00a0(File Editor add-on or SSH)<\/li>\n\n\n\n<li>Comment out the\u00a0<code>db_url<\/code>\u00a0line:<code>recorder: # db_url: mysql:\/\/...<\/code><\/li>\n\n\n\n<li>Save<\/li>\n\n\n\n<li><strong>Settings ? Add-ons ? MariaDB ? Stop ? Uninstall<\/strong><\/li>\n\n\n\n<li><strong>Settings ? System ? Restart<\/strong><\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">HA will create a fresh SQLite database at&nbsp;<code>\/config\/home-assistant_v2.db<\/code>&nbsp;automatically.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Post-Migration: Optional Recorder Tuning<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"yaml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">recorder:\n  # db_url omitted \u2014 SQLite is used by default\n  purge_keep_days: 30       # Days of history to retain\n  auto_purge: true          # Nightly purge at 04:12 local time\n  auto_repack: true         # Repack every second Sunday\n  commit_interval: 5        # Seconds between DB commits; increase to reduce SD card wear\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Disk space requirement (official docs)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Always keep at least&nbsp;<strong>1.5\u00d7 your database size<\/strong>&nbsp;free on disk at all times. During schema upgrades or repacks HA may need a temporary full copy. If disk fills up, HA will move the database aside and start fresh as a last resort.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">References<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.home-assistant.io\/integrations\/recorder\/\" target=\"_blank\" rel=\"noreferrer noopener\">HA Official Recorder Documentation<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/community.home-assistant.io\/t\/migrate-back-from-mariadb-to-the-default-sqlite\/604278\" target=\"_blank\" rel=\"noreferrer noopener\">Community Migration Guide (May 2025)<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/community.home-assistant.io\/t\/migrating-from-mysql-mariadb-back-to-sqlite\/545837\" target=\"_blank\" rel=\"noreferrer noopener\">Original Forum Thread<\/a><\/li>\n<\/ul>\n","protected":false},"template":"","class_list":["post-14259","home_assistant_tip","type-home_assistant_tip","status-publish","hentry","comments-off"],"_links":{"self":[{"href":"https:\/\/www.rjt.org.uk\/home\/wp-json\/wp\/v2\/home_assistant_tip\/14259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rjt.org.uk\/home\/wp-json\/wp\/v2\/home_assistant_tip"}],"about":[{"href":"https:\/\/www.rjt.org.uk\/home\/wp-json\/wp\/v2\/types\/home_assistant_tip"}],"wp:attachment":[{"href":"https:\/\/www.rjt.org.uk\/home\/wp-json\/wp\/v2\/media?parent=14259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}