SOLVED TrueNAS 13 - can't delete cloud credential

[jim99487]

Updated the other day to 13, and noticed that cloud sync task to Backblaze B2 failing.

I realised that the credential was not verifying. Getting the following error:

Error: 2023/04/14 08:03:40 Failed to create file system for "remote:": failed to authorize account: failed to authenticate: Get "https://api.backblazeb2.com/b2api/v1/b2_authorize_account": dial tcp: lookup api.backblazeb2.com on [::1]:53: read udp [::1]:43700->[::1]:53: read: connection refused

Contacted B2 support, but no fix forthcoming, so I disabled the cloudsync tasks and then attempted to delete the existing credentials and create another, but I can't delete the existing credential, instead I'm getting the following error:

Error: Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

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

Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 139, in call_method
result = await self.middleware._call(message['method'], serviceobj, methodobj, params, app=self)
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1236, in _call
return await methodobj(*prepared_call.args)
File "/usr/local/lib/python3.9/site-packages/middlewared/service.py", line 518, in delete
rv = await self.middleware._call(
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1236, in _call
return await methodobj(*prepared_call.args)
File "/usr/local/lib/python3.9/site-packages/middlewared/schema.py", line 975, in nf
return await f(*args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/cloud_sync.py", line 640, in do_delete
await self.middleware.call(
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1279, in call
return await self._call(
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1236, in _call
return await methodobj(*prepared_call.args)
File "/usr/local/lib/python3.9/site-packages/middlewared/schema.py", line 975, in nf
return await f(*args, **kwargs)
File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/write.py", line 192, in delete
await self.middleware.call(
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1279, in call
return await self._call(
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1236, in _call
return await methodobj(*prepared_call.args)
File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/connection.py", line 70, in execute_write
return await self.middleware.run_in_executor(self.thread_pool, self._execute_write, sql, binds, options)
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1152, in run_in_executor
return await loop.run_in_executor(pool, functools.partial(method, *args, **kwargs))
File "/usr/local/lib/python3.9/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/connection.py", line 73, in _execute_write
result = self.connection.execute(sql, binds)
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1274, in execute
return self._exec_driver_sql(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1578, in _exec_driver_sql
ret = self._execute_context(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: DELETE FROM system_cloudcredentials WHERE system_cloudcredentials.id = ?]
[parameters: (1,)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

Would be grateful if someone could point me in the direction of a fix.

 

[Samuel Tai]

Have you created a credential with embedded spaces? That's the usual cause of SQL parse errors. What's the output of midclt call cloudsync.credentials.query | jq? (Remember to blur out credential secrets before pasting the output here.)

 

[jim99487]

Have you created a credential with embedded spaces? That's the usual cause of SQL parse errors. What's the output of midclt call cloudsync.credentials.query | jq? (Remember to blur out credential secrets before pasting the output here.)

The service was working absolutely fine until upgrading to TrueNAS 13.

I did create a new B2 key to see if that would work, here's the output of your suggestion:

Can't see any embedded spaces.

 

[Dice]

What about recreating the bucket key on b2, and setting up a new cloud credential?

 

[jim99487]

What about recreating the bucket key on b2, and setting up a new cloud credential?

I tried that first off, but didn't work. I created the new key on B2 and went back to my server and tried to add a new credential, but it wouldn't verify.

 

[Dice]

I tried that first off, but didn't work. I created the new key on B2 and went back to my server and tried to add a new credential, but it wouldn't verify.

That was unfortunate.
There might be more powerful wizards with ideas on how to fix the issue under the hood, but those occasions are slim on the forum.
The middleware creates a lot of complexities.

Had it be me, I'd proceed like:
1. Assume there is a middleware glitch/database problem. Reinstall TN, and restore from backup configuration file.
Does it fix it? good. if not;
2. Reinstalling TN, without restoring from backup to not introduce the errors/problems during restore. Manually setup all configurations again.

 

[jim99487]

Thanks, I'll give that a go. I can't get any of my plugins up at the moment either. Something fishy going on!

Will report back.

 

[Samuel Tai]

The middleware won't let you delete credentials in use. Try deleting your cloud sync task, then deleting the cloud credential.

 

[jim99487]

As I was having issues with both this and starting my plugins. I thought it might be a dodgy upgrade to 13, but poking around on the forums noted that it could have been an interface issue.

So I reset my interfaces from the console (I'd recently managed to set up link aggregation for the first time) and that has solved the problem.

I'm now back to two separate interfaces, but seeing that I can't use LACP, I'm not missing much.

Many thanks for your help everyone.