ActiveRecord SQLite3 >= 6.0 represents boolean values as integers by default, leads to errors after upgrade

Thanks for reporting, I'll have a look.

added debian label

Unfortunately, I can't reproduce this.

@phil Do you mind to share details, which would allow me to do so? Did you do any changes to your local installation?

There was only one big change: the update to Bullseye. Other than this one nothing changed at the Schleuder setup.

As we use Schleuder only on one server I cant provide more information.

I am fine if you close this report. Thanks for your great work on Schleuder!

There is another report of someone running into this. I'll continue debugging.

added bug label

I think a found the underlying problem:

In the sqlite3 documentation it says:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

If, on our installation, I do sqlite3 db.sqlite "pragma table_info('subscriptions');" I get:

0|id|INTEGER|1||1
1|list_id|integer|0||0
2|email|varchar(255)|0||0
3|fingerprint|varchar(255)|0||0
4|admin|boolean|0|'f'|0
5|delivery_enabled|boolean|0|'t'|0
6|created_at|datetime|0||0
7|updated_at|datetime|0||0

So our boolean columns default to t&f instead of 1&0. This is probably a legacy way of storing boolean in sqlite.

and

sqlite3 db.sqlite "SELECT DISTINCT admin from subscriptions;"
t
f

After we applied the fix on one list

schleuder-cli subscriptions set foo@bar.org admin false
schleuder-cli subscriptions set foo@bar.org admin true

I get:

sqlite3 db.sqlite "SELECT DISTINCT admin from subscriptions;"
1   <---- !!!
f
t

So it looks like after the upgrade we use a newer sqlite-adapter, that is not as forgiving with BOOLEAN values stored as 't' and 'f' in a column in sqlite3.

This has been an issue with ActiveRecord since 2014: https://github.com/rails/rails/issues/17062

ActiveRecord uses 't' and 'f' constants when talking to SQLite columns for true and false, but it would probably be better if it used 1 and 0.

I was on this path of research yesterday night, because the problem rang a distant bell, but didn't come up with anything. Thank you so much for debugging this!

Great, thanks for debugging.

I assume, this then also applies to other bool columsn within the database.

Here's advice on how to do it: https://github.com/rails/rails/pull/29699/files/52e050ed00b023968fecda82f19a858876a7c435#diff-13f18e985e903996e81f573b1fd364c19bddde279bc2797181491e6c146bcdc7

(Found via the implementing pull request: https://github.com/rails/rails/pull/29699). We missed this breaking change because it is not mentioned in the Rails' Upgrading Guide, and the Rails' Changelog doesn't highlight breaking changes at all. Sorry to everyone who has to deal with these consequences.

Hi I'm working on the problem, with the legacy boolean values in the sqlite3-tables. 't' & 'f' instead of 1 & 0 .

Updating the values is as easy as:

UPDATE subscriptions SET admin = 1 WHERE admin = 't';
UPDATE subscriptions SET admin = 0 WHERE admin = 'f';
UPDATE subscriptions SET delivery_enabled = 1 WHERE delivery_enabled = 't';
UPDATE subscriptions SET delivery_enabled = 0 WHERE delivery_enabled = 'f';
...

I'm using raw SQL because I'm not sure how to use ActiveRecord-tooling with a schleuder-installation from deb-packages. E.g. where would I put rake-tasks or migrations?? Or what classes do I require from where...

This is why the instructions linked here: ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 1) are not really working for me.

But I think scripting the adjustments in SQL is a feasible way to get this over with.

Where I'm stuck now is the wrong default values for the boolean columns. First observations: in our db the subscriptions-table has default values 'f' & 't'. But the lists table with the many boolean switches for the lists already has 0 & 1 as default values. The actual values in the columns are still t&f and need to be corrected.

Since sqlite3 does NOT support changing columns default values at all, the advice is to

create NEW table with identical columns and correct default values
copy over all values
remove the OLD table with the wrong default values
rename the NEW table to the original table name

To avoid all this hassle I tried .dump-ing the db into a text file, end the edit

CREATE TABLE IF NOT EXISTS "subscriptions" 
  ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    "list_id" integer, 
    "email" varchar(255), 
    "fingerprint" varchar(255), 
    "admin" boolean DEFAULT 'f', 
    "delivery_enabled" boolean DEFAULT 't', 
    "created_at" datetime, 
    "updated_at" datetime);

To:

...
"admin" boolean DEFAULT 0,
"delivery_enabled" boolean DEFAULT 1,
...

After reading the text-dump back in the subscription table now looks like this:

sqlite3 db2.sqlite "pragma table_info('subscriptions');"
0|id|INTEGER|1||1
1|list_id|integer|0||0
2|email|varchar(255)|0||0
3|fingerprint|varchar(255)|0||0
4|admin|boolean|0|0|0
5|delivery_enabled|boolean|0|1|0
6|created_at|datetime|0||0
7|updated_at|datetime|0||0

Do you think this is a feasible way to change the default values for the two boolean columns?

To fix the problem interactively it might suffice to run this in rake console:

Subscription.connection.change_column_default('subscriptions', 'admin', true)
Subscription.connection.change_column_default('subscriptions', 'admin', false)

I don't know how they do it (didn't find the code), but on my machine™ it works (Archlinux with gems ActiveRecord v6.1.3, sqlite v1.4.2). Could you try this in your setup?

To change the values you could still use Ruby code with a temporary class in order to avoid problems like we did here. A migration should be runnable on Debian like on any other setup (rake db:migrate). If that isn't the case, our Debian specialist maybe can help but they are afk for some days.

But I guess using SQL should be ok in this simple case, too.

Thanks for the suggestions. But I am still stuck on how to access schleuder console on a debian install. I figured out, that I probably have to move to /usr/lib/ruby/vendor_ruby/schleuder fist.

But rake -T only gives me all the db:tasks. Looking at /usr/lib/ruby/vendor_ruby/schleuder/Rakefile I kinda understand, why they are available there. But there is not rake colsole available. Should it?

I created a small ruby-script, that adjusts the values and column-defaults for our case. Maybe it is useful for anyone else with this problem:

SOURCE_DB_NAME = 'db_work.sqlite'
TARGET_DB_NAME = 'db_new.sqlite'
TEMP_FILE_NAME = 'SQL_temp_file.sql'

puts "changing the default values via sql-dump"

dump_string = %x{sqlite3 #{SOURCE_DB_NAME} ".dump"}

new_sql_string = dump_string
  .sub(/"admin" boolean DEFAULT 'f'/, '"admin" boolean DEFAULT 0')
  .sub(/"delivery_enabled" boolean DEFAULT 't'/, '"delivery_enabled" boolean DEFAULT 1')

puts "puting adjusted sql into temp-file: #{TEMP_FILE_NAME}"
File.write(TEMP_FILE_NAME, new_sql_string)

puts "creat new empty db"
%x{ sqlite3 #{TARGET_DB_NAME} "VACUUM;" }

puts "restoring the data back into the new db"
%x{ sqlite3 #{TARGET_DB_NAME} < #{TEMP_FILE_NAME} }



all_columns = {
  subscriptions: %w[
    admin
    delivery_enabled
  ],
  lists: %w[
    send_encrypted_only
    receive_encrypted_only
    receive_signed_only
    receive_authenticated_only
    receive_from_subscribed_emailaddresses_only
    receive_admin_only
    keep_msgid
    bounces_drop_all
    bounces_notify_admins
    include_list_headers
    include_openpgp_header
    forward_all_incoming_to_admins
    deliver_selfsent
    include_autocrypt_header
    set_reply_to_to_sender
    munge_from
  ]
}

puts "### updating values in all boolean columns"
all_columns.each do |table, columns|
  columns.each do |column|
    sql_01 = "UPDATE #{table} SET #{column} = 1 WHERE #{column} = 't';"
    sql_02 = "UPDATE #{table} SET #{column} = 0 WHERE #{column} = 'f';"

    cmd_01 = "sqlite3 #{TARGET_DB_NAME} \"#{sql_01}\""
    cmd_02 = "sqlite3 #{TARGET_DB_NAME} \"#{sql_02}\""

    puts "cmd: #{cmd_01}"
    puts "cmd: #{cmd_02}"

    %x{ #{cmd_01} }
    %x{ #{cmd_02} }
  end
end

FTR: I aim to fix this in Debian via the upcoming point release 11.2. There is no ETA yet for this. I'll keep you posted.

@Segaja This issue might be relevant in the ArchLinux context as well.

After having a deeper look into this, I'm wondering if, instead of handling database migrations, which are a sensible way to fix this issue, but also more complex and error-prone, way more easy, but also hacky (?), would be to monkey patch the actual ActiveRecord connection adapter to handle SQLite3 to tell it what it needs to do, for example, via:

require 'active_record/connection_adapters/sqlite_adapter3'

module ActiveRecord::ConnectionAdapters::SQLite3Adapter
  QUOTED_TRUE, QUOTED_FALSE = 't'.freeze, 'f'.freeze

  def quoted_true; QUOTED_TRUE end
  def quoted_false; QUOTED_FALSE end
end

To me, this issue seems database-specific, because, AFAIK, PostgreSQL is able to handle the values as is. I'm aware of people considering moving their Schleuder databases off SQLite3 towards PostgreSQL. So what about staying with database values which are still accepted, and handled correctly, in the wider ecosystem?

We're monkey patching other parts of dependencies already, which might not be a good argument in favor of doing this per se, just to point out that we made such decisions in the past in other cases, in case these were sensible in our environment, see https://0xacab.org/schleuder/schleuder-cli/-/raw/main/lib/schleuder-cli/openssl_ssl_patch.rb for an example.

May I ask how these monkey patches are applied?

On a site note, do I understand this correctly that schleuder supports postgresql? i only saw sqlite dependencies in there and i don't remember the docu saying that postgresql is a thing

May I ask how these monkey patches are applied?

Similar to other files, via 'require', see https://0xacab.org/schleuder/schleuder-cli/-/blob/main/lib/schleuder-cli.rb#L23 for an example.

On a site note, do I understand this correctly that schleuder supports postgresql? i only saw sqlite dependencies in there and i don't remember the docu saying that postgresql is a thing

I never did this personally, but I believe this should work. One would need to install https://github.com/ged/ruby-pg, and change the database settings to tell Schleuder what to do: https://0xacab.org/schleuder/schleuder/-/blob/main/etc/schleuder.yml#L55-58

There is room for improvement, wrt our docs. :)

Hm ok, I think for now I will stay with sqlite setup.

#492 relates to your question wrt PostgreSQL.

There is also #179 (closed) which relates to PostgreSQL.

it works so far without any hickups

added upstream label

changed title from "List has no admin configured" - can't send mails after upgrade to 3.6 to ActiveRecord >= 6.0 represents boolean values as integers by default, breaks upgrades 3.x -> 4.x

changed title from ActiveRecord >= 6.0 represents boolean values as integers by default, breaks upgrades 3.x -> 4.x to ActiveRecord >= 6.0 represents boolean values as integers by default, leads to errors after upgrade

changed title from ActiveRecord >= 6.0 represents boolean values as integers by default, leads to errors after upgrade to ActiveRecord SQLite3 >= 6.0 represents boolean values as integers by default, leads to errors after upgrade

Is this only an issue during upgrading or also for clean schleuder 4 installations?

Is this only an issue during upgrading or also for clean schleuder 4 installations?

This only applies to upgrades which involve ActiveRecord < 6.0 to >= 6.0. In case you're asking wrt the ArchLinux context, if there only ever was the later in place, since Schleuder is (again?) available, this shouldn't be of concern.

For the archlinux context (used by the arch team) it will be a new setup. So no issue for us. Thanks for the warning tho.

added CI label

ActiveRecord SQLite3 >= 6.0 represents boolean values as integers by default, leads to errors after upgrade

Expected Behavior

Actual Behavior

Steps to Reproduce the Problem

Specifications

Other information

Child items ...

Activity