ERROR: relation "system" already exists

[roughnecks]

Hello,
not sure when this started but I just found out.

I made some mistakes with a carddav and a caldav plugins and I had to restore a backup (pgsql and files). Then I run the upgrade process even if it said I already was at the latest release (1.6.7) and at the end I got:

`ERROR: Error in DDL upgrade 2013011000: [7] ERROR:  relation "system" already exists`

When using the webmail all seemed to be working fine but I found out that there were sql errors for the personal address book (not sure if related to the message above) and I found a way to disable that with:
`$config['address_book_type'] = '';` since CardDAV is working and I can use those address books instead.

I remember a few days ago, before finding the dav plugin, that I tried repeated times to import a .vcf but it always said to me "0 contacts imported". So now I realized it could be because of the sql issue and I have no idea when it started.

Any hint/suggestions on what to do?
Thanks

[JohnDoh]

Please could you post the full output from the upgrade script? Also what is the result of `SELECT * FROM `system` ;`?

[roughnecks]

Can't do the upgrade now, but here's the query:

Code Select Expand

roundcube=# select * from system;
ERROR:  relation "system" does not exist
LINE 1: select * from system;
                      ^
roundcube=#

[JohnDoh]

huh? in your first post the error was "ERROR:  relation "system" already exists`" now its "ERROR:  relation "system" does not exist". how is that possible?

[roughnecks]

ahah not sure but I copied the first error from my terminal when I was still trying to fix up things, while I run the select before posting my reply here.

Can I run the upgrade again now, even if it's already latest?

[JohnDoh]

yes, it will automatically stop is there is nothing to do. You need to figure out what's up with your db though. It might be fastest to just compare your db with what is in the initial sql file that shipped with the target version

[roughnecks]

at the moment I ran the installto script again and..

Code Select Expand

Target installation already in version 1.6.7. Do you want to update again? (y/N)
y
Copying files to target location..../
password/
.htaccess.new
done.

NOTICE: New .htaccess file saved as .htaccess.new.

Running update script at target...
Executing database schema update.
Updating database schema (2013011000)... [FAILED]
ERROR: Error in DDL upgrade 2013011000: [7] ERROR:  relation "system" already exists
All done.

[JohnDoh]

If the system table in your db had the correct entry for version 1.6.7 or really any recent version the upgrade script would not try and run 2013011000 which is from version (I think) version 1.0. I think the easiest way forwards is still to manual check the state of your database.

[roughnecks]

IIRC I had Mariadbd configured at first, then converted it to PostGRES, but I upgraded roundcube a few times since then and this is the first one I noticed the error.

Anyway, can you point me to what should I check/fix?

Thanks

[roughnecks]

Should I try running this?

`INSERT INTO "system" (name, value) VALUES ('roundcube-version', '2022100100');`

Here's a couple screenshots of DBeaver:

https://i.imgur.com/EH3t77l.jpeg
https://i.imgur.com/HFnHrTY.jpeg

I'm also reading this in postgres.initial.sql

Code Select Expand

-- Table "system"
-- Name: system; Type: TABLE; Schema: public; Owner: postgres
```

Owner in my db is the same "roundcube" user. Not sure if it means anything.

Code Select Expand

$ psql roundcube roundcube
Password for user roundcube:
psql (15.7 (Debian 15.7-0+deb12u1))
Type "help" for help.

roundcube=> select * from system;
           name            |   value
---------------------------+------------
 calendar-database-version | 2021102600
(1 row)

roundcube=> \dt
                  List of relations
  Schema   |         Name         | Type  |   Owner
-----------+----------------------+-------+-----------
 roundcube | attachments          | table | roundcube
 roundcube | cache                | table | roundcube
 roundcube | cache_index          | table | roundcube
 roundcube | cache_messages       | table | roundcube
 roundcube | cache_shared         | table | roundcube
 roundcube | cache_thread         | table | roundcube
 roundcube | calendars            | table | roundcube
 roundcube | carddav_accounts     | table | roundcube
 roundcube | carddav_addressbooks | table | roundcube
 roundcube | carddav_contacts     | table | roundcube
 roundcube | carddav_group_user   | table | roundcube
 roundcube | carddav_groups       | table | roundcube
 roundcube | carddav_migrations   | table | roundcube
 roundcube | carddav_xsubtypes    | table | roundcube
 roundcube | collected_addresses  | table | roundcube
 roundcube | contactgroupmembers  | table | roundcube
 roundcube | contactgroups        | table | roundcube
 roundcube | contacts             | table | roundcube
 roundcube | dictionary           | table | roundcube
 roundcube | events               | table | roundcube
 roundcube | filestore            | table | roundcube
 roundcube | identities           | table | roundcube
 roundcube | itipinvitations      | table | roundcube
 roundcube | responses            | table | roundcube
 roundcube | searches             | table | roundcube
 roundcube | session              | table | roundcube
 roundcube | system               | table | roundcube
 roundcube | users                | table | roundcube
(28 rows)


[JohnDoh]

You system table is missing the entry for `roundcube-version` this is a core part of the system and identifies the version of the database schema. I guess your options are to manually check your database schema to make sure its up to date and then fix the `roundcube-version` entry so you don't have the same problem next time or backup the contents of your tables, use the initial file to recreate the database and then import you data.

[roughnecks]

Sorry for the delay but I'm not being email-notified anymore by the forum.
Think I fixed it, thanks!

[roughnecks]

Hello

I have more errors in logs.
What I did was:

Code Select Expand

pg_dump -U roundcube -d roundcube -n roundcube -f roundcube_dump.sql

drop database roundcube;
create database roundcube;
grant all privileges on database roundcube to roundcube;

./bin/initdb.sh --dir=SQL

psql --username=roundcube roundcube < roundcube_dump.sql

I lost all carddav data and settings, but those I could manually restore
What it's not working is adding custom identities and custom replies.

Code Select Expand

NOTA: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL Query: SELECT "response_id", "name" FROM "responses" WHERE "user_id" = '10' AND "del" = 0 ORDER BY "name") in /var/www/webmail/program/lib/Roundcube/rcube_db.php on line 577 (GET /?_task=mail&_action=compose&_id=148488133666965e7bcf782)
[16-Jul-2024 11:50:21 UTC] PHP Deprecated:  Creation of dynamic property account_details::$rc is deprecated in /var/www/webmail/plugins/account_details/account_details.php on line 7
[16-Jul-2024 11:50:21 +0000]: <2ja2m4a5> DB Error: [7] ERROR:  operator does not exist: boolean <> integer
RIGA 1: ...ROM "identities" WHERE "user_id" = '10' AND "del" <> 1 ORDER...
                                                            ^
NOTA: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL Query: SELECT "identity_id", "name", "email" FROM "identities" WHERE "user_id" = '10' AND "del" <> 1 ORDER BY "standard" DESC, "name" ASC, "email" ASC, "identity_id" ASC) in /var/www/webmail/program/lib/Roundcube/rcube_db.php on line 577 (GET /?_task=settings&_action=identities)
[16-Jul-2024 11:50:26 UTC] PHP Deprecated:  Creation of dynamic property account_details::$rc is deprecated in /var/www/webmail/plugins/account_details/account_details.php on line 7
[16-Jul-2024 11:50:26 +0000]: <2ja2m4a5> DB Error: [7] ERROR:  operator does not exist: boolean = integer
RIGA 1: ...FROM "responses" WHERE "user_id" = '10' AND "del" = 0 ORDER ...
                                                            ^
NOTA: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL Query: SELECT "response_id", "name" FROM "responses" WHERE "user_id" = '10' AND "del" = 0 ORDER BY "name") in /var/www/webmail/program/lib/Roundcube/rcube_db.php on line 577 (GET /?_task=settings&_action=responses)
[16-Jul-2024 11:50:28 UTC] PHP Deprecated:  Creation of dynamic property account_details::$rc is deprecated in /var/www/webmail/plugins/account_details/account_details.php on line 7
[16-Jul-2024 11:50:36 UTC] PHP Deprecated:  Creation of dynamic property account_details::$rc is deprecated in /var/www/webmail/plugins/account_details/account_details.php on line 7

Is it everything screwed up? 

[roughnecks]

Ok, nevermind, I just reset everything.
You can ignore my previous post.

Greetings