getting error: "no such column: transaction_inputs.witnesses"

[krupan]

I have some wallets I created back in 2021 and I recently updated my laptop with the python 3.11 and bitcoinlib 0.6.14 and now when I do this:

clw <wallet-name>

I see some wallet info and then a traceback which seems to indicate that my database probably needs a schema upgrade? Is there an easy safe way to do that?

Traceback (most recent call last):
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1970, in _exec_single_context
    self.dialect.do_execute(
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: transaction_inputs.witnesses

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/bryan/.direnv/python-3.11/bin/clw", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/bitcoinlib/tools/clw.py", line 405, in main
    wlt.info()
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/bitcoinlib/wallets.py", line 4219, in info
    txs = self.transactions(include_new=include_new, account_id=account_id, network=nw.name,
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/bitcoinlib/wallets.py", line 3201, in transactions
    txs = qr.all()
          ^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2673, in all
    return self._iter().all()  # type: ignore
           ^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
                                                  ^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1421, in execute
    return meth(
           ^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1643, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1849, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1989, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1970, in _exec_single_context
    self.dialect.do_execute(
  File "/home/bryan/.direnv/python-3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: transaction_inputs.witnesses
[SQL: SELECT transaction_inputs.transaction_id AS transaction_inputs_transaction_id, transaction_inputs.index_n AS transaction_inputs_index_n, transaction_inputs.key_id AS transaction_inputs_key_id, transaction_inputs.address AS transaction_inputs_address, transaction_inputs.witnesses AS transaction_inputs_witnesses, transaction_inputs.witness_type AS transaction_inputs_witness_type, transaction_inputs.prev_txid AS transaction_inputs_prev_txid, transaction_inputs.output_n AS transaction_inputs_output_n, transaction_inputs.script AS transaction_inputs_script, transaction_inputs.script_type AS transaction_inputs_script_type, transaction_inputs.sequence AS transaction_inputs_sequence, transaction_inputs.value AS transaction_inputs_value, transaction_inputs.double_spend AS transaction_inputs_double_spend, transaction_inputs.address AS transaction_inputs_address__1, transactions.confirmations AS transactions_confirmations, transactions.txid AS transactions_txid, transactions.network_name AS transactions_network_name, transactions.status AS transactions_status 
FROM transaction_inputs JOIN transactions ON transactions.id = transaction_inputs.transaction_id JOIN keys ON keys.id = transaction_inputs.key_id 
WHERE transactions.account_id = ? AND transactions.wallet_id = ? AND keys.wallet_id = ? AND transactions.network_name = ? AND (transactions.status = ? OR transactions.status = ?)]
[parameters: (0, 18, 18, 'bitcoin', 'confirmed', 'unconfirmed')]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[mccwdev]

Between major versions the database is not automatically updated unfortunately.

What you can do is copy the private keys and recreate the wallets. In tools/import_database.py you can find an example of a very basic automated script to import a bunch of wallets.

Make sure you backup the database before you run any scripts, but you already knew that...

[krupan]

I don't see an import_database.py anywhere in this repo

[krupan]

I think I can figure this out by looking at the source for clw though

[krupan]

Here's a quick and dirty script I wrote to migrate my wallets from an old database to a new one:

#!/usr/bin/env python3
"""Pretty hacky, but it works.  Move the database from
~/.bitcoinlib/database/bitcoinlib.sqlite to
database-bitcoinlib.sqlite, then run this script

"""
import subprocess

from bitcoinlib.config.config import DEFAULT_DATABASE
from bitcoinlib.wallets import Wallet, wallets_list

print(f"database: {DEFAULT_DATABASE}")

# learned how to do this from clw
for w in wallets_list(db_uri='database-backup.sqlite'):
    if 'parent_id' in w and w['parent_id']:
        continue
    wlt = Wallet(w['name'], db_uri='database-backup.sqlite')
    print(f"[{w['id']}] {w['name']} ({w['network']}) {w['owner']}")
    subprocess.run(['clw', '-c', wlt.main_key.wif, w['name']], check=True)
    try:
        subprocess.run(['clw', '--update-transactions', w['name']], check=True)
    except subprocess.CalledProcessError:
        pass # we'll just try again later

[mccwdev]

I'm sorry, I saw the tools/import_database.py was only available in the latest version and not in the current branch. https://github.com/1200wd/bitcoinlib/blob/release-v07/bitcoinlib/tools/import_database.py

But your script seems to do the same...