Update to 12.0-U7 leads to SQL issues and NFS not starting

[IonutZ]

The first thing that I noticed was the following alert:

Code:

CRITICAL

Failed to check for alert CertificateRevoked:

Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1247,
in _execute_context self.dialect.do_execute( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 590,
in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: no such column: system_advanced.adv_syslog_tls_certificate_authority_id

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/plugins/alert.py", line 740,
in __run_source alerts = (await alert_source.check()) or [] 
File "/usr/local/lib/python3.9/site-packages/middlewared/alert/source/certificates.py", line 90, in check (await self.middleware.call('system.advanced.config'))['syslog_tls_certificate'], 
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1256, in call return await self._call( 
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1213, 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/service.py", line 373, in config return await self._get_or_insert(self._config.datastore, options) 
File "/usr/local/lib/python3.9/site-packages/middlewared/service.py", line 385, in _get_or_insert return await self.middleware.call('datastore.config', datastore, options) 
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1256, in call return await self._call( 
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1213, 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/read.py", line 186, in config return await self.query(name, [], options) 
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/read.py", line 157, in query result = await self.middleware.call("datastore.fetchall", qs) 
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1256, in call return await self._call( 
File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1213, in _call return await methodobj(*prepared_call.args) 
File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/connection.py", line 81,
in fetchall return await self.middleware.run_in_executor(self.thread_pool, self._fetchall, *args) File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1128, 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 52, in run result = self.fn(*self.args, **self.kwargs) 
File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/connection.py", line 84, in _fetchall cursor = self.connection.execute(query, params or []) 
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 984, in execute return meth(self, multiparams, params) 
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) 
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_clauseelement ret = self._execute_context( 
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1287, in _execute_context self._handle_dbapi_exception( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _handle_dbapi_exception util.raise_( 
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 178, in raise_ raise exception 
File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context self.dialect.do_execute( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 590,in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: system_advanced.adv_syslog_tls_certificate_authority_id [SQL: SELECT system_advanced.id, system_advanced.adv_consolemenu, system_advanced.adv_serialconsole, system_advanced.adv_serialport, system_advanced.adv_serialspeed, system_advanced.adv_powerdaemon, system_advanced.adv_swapondrive, system_advanced.adv_overprovision, system_advanced.adv_consolemsg, system_advanced.adv_traceback, system_advanced.adv_advancedmode, system_advanced.adv_autotune, system_advanced.adv_debugkernel, system_advanced.adv_uploadcrash, system_advanced.adv_anonstats, system_advanced.adv_anonstats_token, system_advanced.adv_motd, system_advanced.adv_boot_scrub, system_advanced.adv_fqdn_syslog, system_advanced.adv_sed_user, system_advanced.adv_sed_passwd, system_advanced.adv_sysloglevel, system_advanced.adv_syslogserver, system_advanced.adv_syslog_transport, system_advanced.adv_syslog_tls_certificate_id, system_advanced.adv_syslog_tls_certificate_authority_id, system_advanced.adv_kmip_uid, system_certificate_1.id, system_certificate_1.cert_type, system_certificate_1.cert_name, system_certificate_1.cert_certificate, system_certificate_1.cert_privatekey, system_certificate_1."cert_CSR", system_certificate_1.cert_signedby_id, system_certificate_1.cert_acme_uri, system_certificate_1.cert_domains_authenticators, system_certificate_1.cert_renew_days, system_certificate_1.cert_acme_id, system_certificate_1.cert_revoked_date, system_certificateauthority_1.id, system_certificateauthority_1.cert_type, system_certificateauthority_1.cert_name, system_certificateauthority_1.cert_certificate, system_certificateauthority_1.cert_privatekey, system_certificateauthority_1."cert_CSR", system_certificateauthority_1.cert_revoked_date, system_certificateauthority_1.cert_signedby_id, system_certificateauthority_2.id, system_certificateauthority_2.cert_type, system_certificateauthority_2.cert_name, system_certificateauthority_2.cert_certificate, system_certificateauthority_2.cert_privatekey, system_certificateauthority_2."cert_CSR", system_certificateauthority_2.cert_revoked_date, system_certificateauthority_2.cert_signedby_id, system_acmeregistration_1.id, system_acmeregistration_1.uri, system_acmeregistration_1.directory, system_acmeregistration_1.tos, system_acmeregistration_1.new_account_uri, system_acmeregistration_1.new_nonce_uri, system_acmeregistration_1.new_order_uri, system_acmeregistration_1.revoke_cert_uri, system_certificateauthority_3.id, system_certificateauthority_3.cert_type, system_certificateauthority_3.cert_name, system_certificateauthority_3.cert_certificate, system_certificateauthority_3.cert_privatekey, system_certificateauthority_3."cert_CSR", system_certificateauthority_3.cert_revoked_date, system_certificateauthority_3.cert_signedby_id, system_certificateauthority_4.id, system_certificateauthority_4.cert_type, system_certificateauthority_4.cert_name, system_certificateauthority_4.cert_certificate, system_certificateauthority_4.cert_privatekey, system_certificateauthority_4."cert_CSR", system_certificateauthority_4.cert_revoked_date, system_certificateauthority_4.cert_signedby_id FROM system_advanced LEFT OUTER JOIN system_certificate AS system_certificate_1 ON system_certificate_1.id = system_advanced.adv_syslog_tls_certificate_id LEFT OUTER JOIN system_certificateauthority AS system_certificateauthority_1 ON system_certificateauthority_1.id = system_certificate_1.cert_signedby_id LEFT OUTER JOIN system_certificateauthority AS system_certificateauthority_2 ON system_certificateauthority_2.id = system_certificateauthority_1.cert_signedby_id LEFT OUTER JOIN system_acmeregistration AS system_acmeregistration_1 ON system_acmeregistration_1.id = system_certificate_1.cert_acme_id LEFT OUTER JOIN system_certificateauthority AS system_certificateauthority_3 ON system_certificateauthority_3.id = system_advanced.adv_syslog_tls_certificate_authority_id LEFT OUTER JOIN system_certificateauthority AS system_certificateauthority_4 ON system_certificateauthority_4.id = system_certificateauthority_3.cert_signedby_id] (Background on this error at: http://sqlalche.me/e/e3q8)
2021-12-16 00:12:14 (America/Detroit)

When I attempt to start the NFS service and SAVE the configuration, I get the following error:

Code:

Error: Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: services_nfs.nfs_srv_v4_owner_major

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 138, 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 1213, in _call
    return await methodobj(*prepared_call.args)
  File "/usr/local/lib/python3.9/site-packages/middlewared/service.py", line 376, in update
    rv = await self.middleware._call(
  File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1213, 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/nfs.py", line 159, in do_update
    old = await self.config()
  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/service.py", line 404, in config
    return await self._get_or_insert(
  File "/usr/local/lib/python3.9/site-packages/middlewared/service.py", line 385, in _get_or_insert
    return await self.middleware.call('datastore.config', datastore, options)
  File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1256, in call
    return await self._call(
  File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1213, 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/read.py", line 186, in config
    return await self.query(name, [], options)
  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/read.py", line 157, in query
    result = await self.middleware.call("datastore.fetchall", qs)
  File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1256, in call
    return await self._call(
  File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1213, in _call
    return await methodobj(*prepared_call.args)
  File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/connection.py", line 81, in fetchall
    return await self.middleware.run_in_executor(self.thread_pool, self._fetchall, *args)
  File "/usr/local/lib/python3.9/site-packages/middlewared/main.py", line 1128, 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 52, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/local/lib/python3.9/site-packages/middlewared/plugins/datastore/connection.py", line 84, in _fetchall
    cursor = self.connection.execute(query, params or [])
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1287, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: services_nfs.nfs_srv_v4_owner_major
[SQL: SELECT services_nfs.id, services_nfs.nfs_srv_servers, services_nfs.nfs_srv_udp, services_nfs.nfs_srv_allow_nonroot, services_nfs.nfs_srv_v4, services_nfs.nfs_srv_v4_v3owner, services_nfs.nfs_srv_v4_krb, services_nfs.nfs_srv_bindip, services_nfs.nfs_srv_mountd_port, services_nfs.nfs_srv_rpcstatd_port, services_nfs.nfs_srv_rpclockd_port, services_nfs.nfs_srv_16, services_nfs.nfs_srv_mountd_log, services_nfs.nfs_srv_statd_lockd_log, services_nfs.nfs_srv_v4_domain, services_nfs.nfs_srv_v4_owner_major
FROM services_nfs]
(Background on this error at: http://sqlalche.me/e/e3q8)

If anyone has any ideas, it would be greatly appreciated... would hate to scrub this install and reinstall, this is on a barebones machine.

 

[Samuel Tai]

Unfortunately, the error message indicates database corruption, specifically:

no such column: system_advanced.adv_syslog_tls_certificate_authority_id

In this case you should reboot back into your previous boot environment, and reattempt the upgrade.

 

[IonutZ]

@Samuel Tai thank you very much for your response. I wasn't aware that there is a secondary boot environment as part of a generic install. How does one revert to the previous boot environment?

Thank you for your help.

 

[Samuel Tai]

Look under System->Boot for your previous boot environment. Click the 3 dots to Activate the previous boot environment, and then select Reboot from the power menu at the upper right.

If your database is too screwed up to even select the old boot environment, just reboot, and hit any key at the boot prompt to stop the count down, and you should be able to select your old boot environment then.

Once you're back in the old boot environment, create a configuration backup via System->General, and then click the "Save Config" button. If repeating the upgrade attempt doesn't work, and you still end up with database corruption, you can attempt a clean install with the new version, and then upload your config at System->General.

 

[Alecmascot]

Before attempting the upgrade again, delete the failed boot environment.