Closed BronzeHeron closed 2 years ago
@arvidn - can you take a look at this?
this suggests your sqlite version is quite old, and doesn't support indices with conditions. Does this just happen with chia db upgrade
? I would expect it to happen just starting the node as well, does it?
adding that constraint to the index is a very small optimization. I think we're still good removing it.
@esaung I take it Centos 7 is a reasonably modern version of that distro, yes?
and you don't have a libsqlite3
that's out of date by any chance?
support for this was added in sqlite version 3.9.0 (2015-10-14)
if you have everything up to date and it's still an issue, we should consider landing https://github.com/Chia-Network/chia-blockchain/pull/10199
Yes, the same issue occurs with attempting to start the node.
sqlite is the system installed version (which is old):
[chia@ACEPChiaFarmer ~]$ rpm -q sqlite
sqlite-3.7.17-8.el7_7.1.x86_64
[chia@ACEPChiaFarmer ~]$ rpm -ql sqlite
/usr/bin/sqlite3
/usr/lib64/libsqlite3.so.0
/usr/lib64/libsqlite3.so.0.8.6
/usr/share/doc/sqlite-3.7.17
/usr/share/doc/sqlite-3.7.17/README
/usr/share/man/man1/sqlite3.1.gz
[chia@ACEPChiaFarmer ~]$
Ok, does my patch solve the issue?
Indications are promising:
(venv) [chia@ACEPChiaFarmer ~]$ chia db upgrade opening file for reading: /opt/chia/.chia/mainnet/db/blockchain_v1_mainnet.sqlite opening file for writing: /opt/chia/.chia/mainnet/db/blockchain_v2_mainnet.sqlite initializing v2 version initializing v2 block store peak: 30295b9cdcc9d12c569f726a2c3215b0bc8c447556b6b49cc27ae27ce7e5119d height: 1547872 [1/5] converting full_blocks 1547000 0.06% 1.0 blocks/s ETA: 1547000.0 s
@BronzeHeron did you run sh install.sh
in the root of the repository as well? There's a chance it will install a newer version of sqlite
You installed the RPM, but you are also using git clone?
Since you installed the RPM, you should launch chia from /usr/lib/chia-blockchain/resources/app.asar.unpacked/daemon/
cd /usr/lib/chia-blockchain/resources/app.asar.unpacked/daemon/
./chia version
Originally I attempted the upgrade via source (git checkout main ; sh install.sh ; . ./chia-blockchain activate ; chia init) as that's how I installed in the first place. After encountering the sqlite error I installed the RPM. I have not attempted to run sh install.sh
post RPM install, and it didn't even occur to me to check where the RPM installed files to.
Ah, so long story:
By default Centos 7 has a rather old version of sqlite library. This is dynamically linked and used by python, even if you have a fairly newer version of python, like 3.9 - you still get this old sqlite library since that is the default installed lib. There is a similar story with openssl (the default centos7 openssl doesn't support TLS 1.3)
The RPM bundles up a newer version of both the sqlite library and the openssl library and puts them somewhere in the /usr/lib/chia-blockchain directory. So if you run chia from that long directory, the embedded python version will use those newer libs and you get both newer sqlite and python.
Your alternative is to compile up yourself newer version of sqlite and I also recommend openssl 1.1.1
Understood. For what it's worth, with the patch, the DB upgrade completed, but encountered another error. The chia executable supplied by the RPM seems happy.
[chia@ACEPChiaFarmer ~]$ . ./chia-blockchain/activate
(venv) [chia@ACEPChiaFarmer ~]$ chia db upgrade
opening file for reading: /opt/chia/.chia/mainnet/db/blockchain_v1_mainnet.sqlite
opening file for writing: /opt/chia/.chia/mainnet/db/blockchain_v2_mainnet.sqlite
initializing v2 version
initializing v2 block store
peak: 30295b9cdcc9d12c569f726a2c3215b0bc8c447556b6b49cc27ae27ce7e5119d height: 1547872
[1/5] converting full_blocks
4302.16 seconds
[2/5] converting sub_epoch_segments_v3
43.91 seconds
[3/5] converting hint_store
83.11 seconds
[4/5] converting coin_store
13097.52 seconds
[5/5] build indices
block store
Traceback (most recent call last):
File "/opt/chia/chia-blockchain/venv/bin/chia", line 33, in
ah, right. This feature is required in the v2 db. I think it makes sense to give up on trying to support an old sqlite.
Right-o. Make a note in the release notes and mark this issue closed.
[chia@ACEPChiaFarmer ~]$ rpm -q chia-blockchain chia-blockchain-1.2.12.dev269-1.el7.x86_64 [chia@ACEPChiaFarmer ~]$ cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core) [chia@ACEPChiaFarmer ~]$ . ./chia-blockchain/activate (venv) [chia@ACEPChiaFarmer ~]$ python -V Python 3.7.7 (venv) [chia@ACEPChiaFarmer ~]$ chia db upgrade opening file for reading: /opt/chia/.chia/mainnet/db/blockchain_v1_mainnet.sqlite Traceback (most recent call last): File "/opt/chia/chia-blockchain/venv/bin/chia", line 33, in
sys.exit(load_entry_point('chia-blockchain', 'console_scripts', 'chia')())
File "/opt/chia/chia-blockchain/chia/cmds/chia.py", line 148, in main
cli() # pylint: disable=no-value-for-parameter
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/core.py", line 829, in call
return self.main(args, kwargs)
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, ctx.params)
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/core.py", line 610, in invoke
return callback(args, *kwargs)
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/click/decorators.py", line 21, in new_func
return f(get_current_context(), args, **kwargs)
File "/opt/chia/chia-blockchain/chia/cmds/db.py", line 30, in db_upgrade_cmd
no_update_config,
File "/opt/chia/chia-blockchain/chia/cmds/db_upgrade_func.py", line 48, in db_upgrade_func
asyncio.run(convert_v1_to_v2(in_db_path, out_db_path))
File "/usr/local/lib/python3.7/asyncio/runners.py", line 43, in run
return loop.run_until_complete(main)
File "/usr/local/lib/python3.7/asyncio/base_events.py", line 587, in run_until_complete
return future.result()
File "/opt/chia/chia-blockchain/chia/cmds/db_upgrade_func.py", line 86, in convert_v1_to_v2
store_v1 = await BlockStore.create(DBWrapper(in_db, db_version=1))
File "/opt/chia/chia-blockchain/chia/full_node/block_store.py", line 100, in create
await self.db.execute("CREATE INDEX IF NOT EXISTS peak on block_records(is_peak) where is_peak = 1")
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/aiosqlite/core.py", line 184, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/aiosqlite/core.py", line 129, in _execute
return await future
File "/opt/chia/chia-blockchain/venv/lib/python3.7/site-packages/aiosqlite/core.py", line 102, in run
result = function()
sqlite3.OperationalError: near "where": syntax error
(venv) [chia@ACEPChiaFarmer ~]$