sqlite Problem after Update

PScherrer80

Hi folks,

I've done some udpates and now I can't connect to webmail (internal server error) nor imap-connection is working.
On gromox.http Logs I have following errors:

[2023-01-14 07:23:41.097246]: dbop_sqlite: /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3: current schema EV-5; upgrading to EV-10.
[2023-01-14 07:23:41.097732]: dbop_sqlite: upgrading /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3 to schema EV-6
[2023-01-14 07:23:41.097784]: sqlite3_exec "ALTER TABLE `attachment_properties` RENAME TO `u0`": there is already another table or index with this name: u0
[2023-01-14 07:23:41.097816]: dbop_sqlite upgrade /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3: Input/output error

After deleting attachment_properties and rename u0 to attachment_properties I receive now following messages:

[2023-01-14 08:12:51.463652]: sqlite3_exec "INSERT INTO attachment_properties SELECT attachment_id, proptag, propval FROM u0": UNIQUE constraint failed: attachment_properties.attachment_id, attachment_properties.proptag
[2023-01-14 08:12:51.463895]: dbop_sqlite upgrade /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3: Input/output error
[2023-01-14 08:14:04.399483]: dbop_sqlite: /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3: current schema EV-5; upgrading to EV-10.
[2023-01-14 08:14:04.399892]: dbop_sqlite: upgrading /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3 to schema EV-6
[2023-01-14 08:14:04.399953]: sqlite3_exec "ALTER TABLE `attachment_properties` RENAME TO `u0`": there is already another table or index with this name: u0
[2023-01-14 08:14:04.399979]: dbop_sqlite upgrade /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3: Input/output error

I have:
Name : gromox
Version : 2.1.3.cb51757
Release : lp153.54.1

What can I do now? Any ideas?

Thanks

crpb

PScherrer80 After deleting attachment_properties and rename u0 to attachment_properties I receive now following messages:

Do you have an Backup of this Users exchange.sqlite3?
I'm just guessing here but i think the already present "u0" was from an previous Upgrade and that should have been renamed/dropped instead of deleting your original attachment_properties and renaming this temporary table.
sqlite3 /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3 "ALTER TABLE RENAME u0 to u0BAKWHATEVER;"

PScherrer80

crpb
the table attachment_properties was empty (no records inside) and u0 had data. so I renamed u0 to attachment_properties

WalterH

Is the DB schema current?
Run gromox-dbop -U, should report: dbop: Current database schema: gx-111

PScherrer80

Hi Walter,

schema is gx-111

crpb

PScherrer80

Ahh, then this makes more sense 😛.
Maybe try rebuild ?!

PScherrer80

crpb
I can try it with a rebuild. How is the exact command to run the rebuild? do you know?

crpb

PScherrer80

~~The only optional parameter is another folder for the SQLite schemas which you don't want to use.~~

So a basic /usr/libexec/gromox/rebuild exchange.sqlite3 will do.
You can try it on any file wherever it resides doesn't matter to the program as far as i can make out.

After you ran on your actual File in /var/lib/gromox/user/N/N/exmdb/exchange.sqlite3 and it seemed to work don't forget to use gromox-mbop ... unload either with -d /var/lib/gromox/user/N/N or -u user@domain.tld.
This would result in e.g. gromox-mbop -d /var/lib/gromox/user/N/N unload.

~~Just FYI, it also accepts multiple exchange.sqlite's if anyone has the need to run it on multiple Mailboxes.~~

I hope this was all correct @jengelh ? 👋

Removed

https://github.com/grommunio/gromox/commit/ca27ffeb8f487dada312e7a516e4c0c286b79d5d

WalterH

This are the tables in my exchange.sqlite3 database:

sqlite> .tables
allocated_eids         message_changes        recipients
attachment_properties  message_properties     recipients_properties
attachments            messages               rules
configurations         named_properties       search_result
folder_properties      permissions            search_scopes
folders                receive_table          store_properties

PScherrer80

crpb

Thanks, I've done all with no luck.

As I have seen it is like that:
the schemaupgrade renames the table attachment_properties to u0.
then creates an empty table attachment_properties
then insert data from u0 to attachment_properties where it fails with "UNIQUE constraint failed: attachment_properties.attachment_id, attachment_properties.proptag (19)"

I've checked the table u0 about duplicate entries in combination attachment_id and proptag but did not find any duplicate values.

I don't get it why the insert fails. For me is no problem to delete duplicate values but I don't no which values.

I've searched like this with no results:
select attachment_id, count() as countd from u0 group by attachment_id, proptag having count() > 1;

PScherrer80

I've deleted all attachments and attachment_properties
now migration was sucessful and I can use my instance again.

Thanks for your help