Created at:

Rails notes

Troubleshooting

no such table: pragma_table_list

I saw this error when running Rails 8.0.2: 

SQLite3::SQLException: no such table: pragma_table_list: SELECT name FROM pragma_table_list WHERE schema <> 'temp' AND name NOT IN ('sqlite_sequence', 'sqlite_schema') AND name = 'schema_migrations' AND type IN ('table')

The following comment on Hacker News clarifies that this specific pragma was added in 3.37.0:

Comment in Hacker News about the "no such table: pragma table list" error

It seems my sqlite3 gem (version 2.6.0) comes with SQLite 3.46.1 so I should have no problem, but maybe it is loading an older version of the SQLite3 shared object somewhere in the system after I messed up with packages in the system?

Anyway, the solution I found was to revert the change made in the active_record gem. Specifically, this change:

Add support for SQLite3 full-text-search and other virtual tables. · rails/rails@1ecb91b · GitHub

So, I just reverted the specific SQL query that mentioned pragma_table_list. This is equivalent to apply this diff: 

--- schema_statements.rb.orig   2025-05-19 17:37:24.915386425 -0300
+++ schema_statements.rb        2025-05-19 17:38:06.506047588 -0300
@@ -182,8 +182,7 @@
             scope = quoted_scope(name, type: type)
             scope[:type] ||= "'table','view'"
 
-            sql = +"SELECT name FROM pragma_table_list WHERE schema <> 'temp'"
-            sql << " AND name NOT IN ('sqlite_sequence', 'sqlite_schema')"
+            sql = +"SELECT name FROM sqlite_master WHERE name <> 'sqlite_sequence'"
             sql << " AND name = #{scope[:name]}" if scope[:name]
             sql << " AND type IN (#{scope[:type]})"
             sql

After I reverted this change, it worked. Probably it breaks other things? Or should I debug whether or not it is using an older version of SQLite?