Re: [SOGo] SOGo database schema
Cédric Archambeau quatschte am Wed, Dec 29, 2021 at 12:22:34PM +0100: > Hi Andre, > > Lots of disagrees with the form in your message. > > Remember you benefit from a FOSS. > Not OK with some feature ? Report a bug, maybe pull a request (*you* could > hire a database administrator to redesign database schema), or discuss (not > assault !) it here. > > SOGo nightly is *free*, but *not a due*. > > Sorry if i feed the trolls, but you may have in mind that some of us > (community users) want to keep this list as sane as possible, and keep SOGo > devs and users away from agressive contents. > > EOT for me. Hi Cédric, completely agree with you. This was such a rude post. F/LOSS is open to everyone to contribute, so do it instead of harassing developers for doing a great job. Regards and keep up the great work SOGo Team ! Peter -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database schema
Hi Andre, Lots of disagrees with the form in your message. Remember you benefit from a FOSS. Not OK with some feature ? Report a bug, maybe pull a request (*you* could hire a database administrator to redesign database schema), or discuss (not assault !) it here. SOGo nightly is *free*, but *not a due*. Sorry if i feed the trolls, but you may have in mind that some of us (community users) want to keep this list as sane as possible, and keep SOGo devs and users away from agressive contents. EOT for me. Regards, Cédric Archambeau Secrétariat National Solidaires Finances Publiques Pôle Communication - Informatique Le 23/12/2021 à 10:33, André Rodier (an...@rodier.me) a écrit : Good morning, all. OK, SOGo is the only working CalDAV / CardDAV server with an acceptable level of quality, at least to me, in the standard Debian repository. The two absolute requirements for me are LDAP authentication and a working user interface over https. So I have to use it, fair enough. By the way, if right now, when reading this, your mind comes up with nice repositories on GitHub or somewhere else, where I can download xxx or yyy, just stop and read again. I want to stick to a standard Debian compatible repository, this is not negotiable. Unfortunately, every time I have a look into the database schema, it is a shame, and I don't see any improvements over the years. First, the tables prefixes - seriously, what is the point of this, remove it. The tables should not be named sogo_xxx ? We are not any more in the 80s, when you were only able to use one database on a hosting provider. At least, make this an option, and not the default case. Second, the column prefixes - Why all the columns are called c_xxx, there is no point behind this except it is annoying and reduce queries readability. Do you have a dinosaur programmer in your team that advertise this as a poor way to avoid errors ? For these two "features", trust unit test, static code analysis and continuous integration to detect errors, not programming practices from the 70s. Third, why the table sogo_folder_info replicates the credentials of the database in each record ? (columns c_location, c_quick_location, c_acl_location) Even if you wanted to have multiple database, the credentials should not be stored in clear text in the database. This is both a security issue and the worst database design I have seen so far...just remove this feature. Fourth, in the same table, what are the columns c_path1, c_path2, c_path3 ??? If your database was properly designed, you wouldn't need these artefacts. For 2022, can you hire a database administrator to redesign your database schema ? Otherwise, I wish the whole team a happy new year. Andre Rodier. -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database schema
Hi, Only to say: YES, specially on #3: the credentials in clear text inside the database. It's terrible. And additionally makes it so much more difficult to change the database credentials than it really should be. MJ Op 23-12-2021 om 10:33 schreef André Rodier (an...@rodier.me): Good morning, all. OK, SOGo is the only working CalDAV / CardDAV server with an acceptable level of quality, at least to me, in the standard Debian repository. The two absolute requirements for me are LDAP authentication and a working user interface over https. So I have to use it, fair enough. By the way, if right now, when reading this, your mind comes up with nice repositories on GitHub or somewhere else, where I can download xxx or yyy, just stop and read again. I want to stick to a standard Debian compatible repository, this is not negotiable. Unfortunately, every time I have a look into the database schema, it is a shame, and I don't see any improvements over the years. First, the tables prefixes - seriously, what is the point of this, remove it. The tables should not be named sogo_xxx ? We are not any more in the 80s, when you were only able to use one database on a hosting provider. At least, make this an option, and not the default case. Second, the column prefixes - Why all the columns are called c_xxx, there is no point behind this except it is annoying and reduce queries readability. Do you have a dinosaur programmer in your team that advertise this as a poor way to avoid errors ? For these two "features", trust unit test, static code analysis and continuous integration to detect errors, not programming practices from the 70s. Third, why the table sogo_folder_info replicates the credentials of the database in each record ? (columns c_location, c_quick_location, c_acl_location) Even if you wanted to have multiple database, the credentials should not be stored in clear text in the database. This is both a security issue and the worst database design I have seen so far...just remove this feature. Fourth, in the same table, what are the columns c_path1, c_path2, c_path3 ??? If your database was properly designed, you wouldn't need these artefacts. For 2022, can you hire a database administrator to redesign your database schema ? Otherwise, I wish the whole team a happy new year. Andre Rodier. -- users@sogo.nu https://inverse.ca/sogo/lists
[SOGo] SOGo database schema
Good morning, all. OK, SOGo is the only working CalDAV / CardDAV server with an acceptable level of quality, at least to me, in the standard Debian repository. The two absolute requirements for me are LDAP authentication and a working user interface over https. So I have to use it, fair enough. By the way, if right now, when reading this, your mind comes up with nice repositories on GitHub or somewhere else, where I can download xxx or yyy, just stop and read again. I want to stick to a standard Debian compatible repository, this is not negotiable. Unfortunately, every time I have a look into the database schema, it is a shame, and I don't see any improvements over the years. First, the tables prefixes - seriously, what is the point of this, remove it. The tables should not be named sogo_xxx ? We are not any more in the 80s, when you were only able to use one database on a hosting provider. At least, make this an option, and not the default case. Second, the column prefixes - Why all the columns are called c_xxx, there is no point behind this except it is annoying and reduce queries readability. Do you have a dinosaur programmer in your team that advertise this as a poor way to avoid errors ? For these two "features", trust unit test, static code analysis and continuous integration to detect errors, not programming practices from the 70s. Third, why the table sogo_folder_info replicates the credentials of the database in each record ? (columns c_location, c_quick_location, c_acl_location) Even if you wanted to have multiple database, the credentials should not be stored in clear text in the database. This is both a security issue and the worst database design I have seen so far...just remove this feature. Fourth, in the same table, what are the columns c_path1, c_path2, c_path3 ??? If your database was properly designed, you wouldn't need these artefacts. For 2022, can you hire a database administrator to redesign your database schema ? Otherwise, I wish the whole team a happy new year. Andre Rodier. -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database move - unsuccessfull
hi, I had to do pg_dump all and restore then also disabled the ipv6 on the machine. This all worked. thanks KH On Thu, Jan 24, 2013 at 5:19 PM, Christian Mack christian.m...@uni-konstanz.de wrote: Hello Khapare Joshi Am 2013-01-24 18:08, schrieb Khapare Joshi: I have been running sogo for now two years in virtual server. since users increased I wanted to move to another physical server. I am having issue with sogo database. I dumped the sogo database with pg_dump and tried to restore with pg_restore. At the end of the pg_restore i get following error: ALTER TABLE ALTER TABLE WARNING: no privileges could be revoked for public REVOKE WARNING: no privileges could be revoked for public REVOKE WARNING: no privileges were granted for public GRANT WARNING: no privileges were granted for public GRANT Anyway, then I tried to login to interface - i do not see any calendars, contacts etc. then tried with sogo-tool, I still do not see the calendars, contacts etc. I see lots of meesage in sogo.log 0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'test+attributes': WRITE FAILURE Jan 24 16:59:22 sogod [9038]: |SOGo| starting method 'POST' on uri '/SOGo/so/test/Contacts/personal/canAccessContent' Jan 24 16:59:22 sogod [9033]: |SOGo| starting method 'POST' on uri '/SOGo/so/test/Contacts/test.com/canAccessContent http://test.com/canAccessContent' Jan 24 16:59:22 sogod [9038]: 0x0x7f8d3df1bad8[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9033]: 0x0x7f8d3df2a358[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'test+attributes': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 't...@test.com mailto:t...@test.com+attributes': WRITE FAILURE my sogo version in old server is : 2.0.2 and in new server 2.0.3 postgres version on old server is : 8.4 and in new server 9.2 Obviously your user public does not get the required privileges on your tables. So every write operation will not work. This applies to session information also. So you will not get any response from SOGo. Fix this and it will work. Kind regards, Christian Mack -- Christian Mack Gruppe Informationsdienste Rechenzentrum Universität Konstanz -- users@sogo.nu https://inverse.ca/sogo/lists -- users@sogo.nu https://inverse.ca/sogo/lists
[SOGo] SOGo database move - unsuccessfull
Hello all, I have been running sogo for now two years in virtual server. since users increased I wanted to move to another physical server. I am having issue with sogo database. I dumped the sogo database with pg_dump and tried to restore with pg_restore. At the end of the pg_restore i get following error: ALTER TABLE ALTER TABLE WARNING: no privileges could be revoked for public REVOKE WARNING: no privileges could be revoked for public REVOKE WARNING: no privileges were granted for public GRANT WARNING: no privileges were granted for public GRANT Anyway, then I tried to login to interface - i do not see any calendars, contacts etc. then tried with sogo-tool, I still do not see the calendars, contacts etc. I see lots of meesage in sogo.log 0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'test+attributes': WRITE FAILURE Jan 24 16:59:22 sogod [9038]: |SOGo| starting method 'POST' on uri '/SOGo/so/test/Contacts/personal/canAccessContent' Jan 24 16:59:22 sogod [9033]: |SOGo| starting method 'POST' on uri '/SOGo/so/test/Contacts/test.com/canAccessContent' Jan 24 16:59:22 sogod [9038]: 0x0x7f8d3df1bad8[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9033]: 0x0x7f8d3df2a358[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'test+attributes': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 't...@test.com+attributes': WRITE FAILURE my sogo version in old server is : 2.0.2 and in new server 2.0.3 postgres version on old server is : 8.4 and in new server 9.2 Any help would be great. KH -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database move - unsuccessfull
Hello Khapare Joshi Am 2013-01-24 18:08, schrieb Khapare Joshi: I have been running sogo for now two years in virtual server. since users increased I wanted to move to another physical server. I am having issue with sogo database. I dumped the sogo database with pg_dump and tried to restore with pg_restore. At the end of the pg_restore i get following error: ALTER TABLE ALTER TABLE WARNING: no privileges could be revoked for public REVOKE WARNING: no privileges could be revoked for public REVOKE WARNING: no privileges were granted for public GRANT WARNING: no privileges were granted for public GRANT Anyway, then I tried to login to interface - i do not see any calendars, contacts etc. then tried with sogo-tool, I still do not see the calendars, contacts etc. I see lots of meesage in sogo.log 0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'test+attributes': WRITE FAILURE Jan 24 16:59:22 sogod [9038]: |SOGo| starting method 'POST' on uri '/SOGo/so/test/Contacts/personal/canAccessContent' Jan 24 16:59:22 sogod [9033]: |SOGo| starting method 'POST' on uri '/SOGo/so/test/Contacts/test.com/canAccessContent http://test.com/canAccessContent' Jan 24 16:59:22 sogod [9038]: 0x0x7f8d3df1bad8[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9033]: 0x0x7f8d3df2a358[SOGoCache] an error occurred when caching value for key 'session:wg1iycSZeH9KHietXslAkQ==': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 'test+attributes': WRITE FAILURE Jan 24 16:59:22 sogod [9037]: 0x0x7f8d3df2a368[SOGoCache] an error occurred when caching value for key 't...@test.com mailto:t...@test.com+attributes': WRITE FAILURE my sogo version in old server is : 2.0.2 and in new server 2.0.3 postgres version on old server is : 8.4 and in new server 9.2 Obviously your user public does not get the required privileges on your tables. So every write operation will not work. This applies to session information also. So you will not get any response from SOGo. Fix this and it will work. Kind regards, Christian Mack -- Christian Mack Gruppe Informationsdienste Rechenzentrum Universität Konstanz -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo Database Cleanup
On Thu, 1 Dec 2011, Luca Olivetti wrote: More than accents, I had problems with newlines (i.e., when a note is in multiple lines), at least I see funambol complaints about encountered \r\n and stops the synchronization. look at http://www.sogo.nu/bugs/view.php?id=904 Regards MW -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo Database Cleanup
Al 29/11/2011 21:42, En/na Dominique ha escrit: So now I have a working calendar, but with entries that need to be cleanup. Does anyone have any idea/tools/scripts how to massively cleanup the database: massive change in times depending on the time zone selected, accent cleanups, More than accents, I had problems with newlines (i.e., when a note is in multiple lines), at least I see funambol complaints about encountered \r\n and stops the synchronization. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es Tel. +34 935883004 (Ext.133) Fax +34 935883007 -- users@sogo.nu https://inverse.ca/sogo/lists
[SOGo] SOGo Database Cleanup
Hi, I have been trying to sync Sogo with Funanbol to Mobile devices for a long time now. If it basically works, I discovered through trial and error that where two major flows in those processes (probably to my own fault): 1. Time synchronization (GMT+xxx) 2. Languages in use - Mostly accent related (something the English language is not much accustomed to). Both elements have created havoc by duplicating entries, movies them to different expected times, refused to synchronize some entry (mostly accent related problems). Note to all starters with this process: make sure your time zone are correctly everywhere (from SOGo to your phones) or you are in route for a cleanup nightmare. For language, I am still not sure how to get everyone aligned on the same options, and how to make everyone happy with accents. So now I have a working calendar, but with entries that need to be cleanup. Does anyone have any idea/tools/scripts how to massively cleanup the database: massive change in times depending on the time zone selected, accent cleanups, Thanks, Dominique -- users@sogo.nu https://inverse.ca/sogo/lists
[SOGo] SOGo Database Cleanup
Hi, I have been trying to sync Sogo with Funanbol to Mobile devices for a long time now. If it basically works, I discovered through trial and error that where two major flows in those processes (probably to my own fault): 1. Time synchronization (GMT+xxx) 2. Languages in use - Mostly accent related (something the English language is not much accustomed to). Both elements have created havoc by duplicating entries, movies them to different expected times, refused to synchronize some entry (mostly accent related problems). Note to all starters with this process: make sure your time zone are correctly everywhere (from SOGo to your phones) or you are in route for a cleanup nightmare. For language, I am still not sure how to get everyone aligned on the same options, and how to make everyone happy with accents. So now I have a working calendar, but with entries that need to be cleanup. Does anyone have any idea/tools/scripts how to massively cleanup the database: massive change in times depending on the time zone selected, duplicate entries, accent cleanups, Thanks, Dominique -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
On Tue, 26 Apr 2011, Pascal Gienger wrote: Hello Pascal, What's the size/value of shared_buffers = ... and max_locks_per_transaction = ... in your postgresql.conf? shared_buffers = 24MB [default value] max_locks_per_transaction = 256 [default value = 64] thank, Milos -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
Am 26.04.2011 08:05, schrieb Milos Wimmer: On Tue, 26 Apr 2011, Pascal Gienger wrote: Hello Pascal, What's the size/value of shared_buffers = ... and max_locks_per_transaction = ... in your postgresql.conf? shared_buffers = 24MB [default value] max_locks_per_transaction = 256 [default value = 64] thank, Milos Hallo Milos, you should increase the kernels maximum size of shared memory segment. To do this. edit your /etc/sysctl.conf. For example: kernel.shmmax = 134217728 (sets the value to 128 MB). You can also find a explanation in http://www.postgresql.org/docs/8.4/interactive/kernel-resources.html cu Sven -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
On Tue, 26 Apr 2011, Sven Marth wrote: Hi Sven, you should increase the kernels maximum size of shared memory segment. To do this. edit your /etc/sysctl.conf. For example: kernel.shmmax = 134217728 (sets the value to 128 MB). You can also find a explanation in http://www.postgresql.org/docs/8.4/interactive/kernel-resources.html I use 1 GB (it corresponeds to /proc/sys/kernel/shmmax on Linux) which is the same value which uses Pascal. cat /proc/sys/kernel/shmmax 1342177280 I tried to set more, without success. Thanks, Milos -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
On Thu, 21 Apr 2011, Pascal Gienger wrote: We have 60552 tables now, and the WAL backup (hot backup) is still running without any problem. PostgreSQL 8.4.7 on Ubuntu 10.04 LTS Server with different volumes for DB files and for DB log (WAL) files, 1G shared memory. I have 125406 tables now, PostgreSQL 8.4.7. SOGo works without problem with so many tables. But I cannot backup database with pg_dump command. It writes: pg_dump: WARNING: out of shared memory pg_dump: SQL command failed pg_dump: Error message from server: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. pg_dump: The command was: LOCK TABLE public.sogodrendr0013165525c_quick IN ACCESS SHARE MODE My system has 32 GB RAM, here are my relevant system settings: cat /proc/sys/kernel/shmall 1342177280 cat /proc/sys/kernel/shmmax 1342177280 cat /proc/sys/kernel/sem 250 32000 32 128 Does somebody have any experience how to backup database including thousands of tables with pg_dump command? I'm using sogo-tools for backup now, works nice. Nevertheless - to have complete dump of the database would be fine... Thanks, Milos -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
Am 26.04.11 00:17, schrieb Milos Wimmer: On Thu, 21 Apr 2011, Pascal Gienger wrote: We have 60552 tables now, and the WAL backup (hot backup) is still running without any problem. PostgreSQL 8.4.7 on Ubuntu 10.04 LTS Server with different volumes for DB files and for DB log (WAL) files, 1G shared memory. I have 125406 tables now, PostgreSQL 8.4.7. SOGo works without problem with so many tables. But I cannot backup database with pg_dump command. It writes: pg_dump: WARNING: out of shared memory pg_dump: SQL command failed pg_dump: Error message from server: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. pg_dump: The command was: LOCK TABLE public.sogodrendr0013165525c_quick IN ACCESS SHARE MODE What's the size/value of shared_buffers = ... and max_locks_per_transaction = ... in your postgresql.conf? -- Pascal Gienger Jabber/XMPP/Mail: pascal.gien...@uni-konstanz.de University of Konstanz, IT Services Department (Rechenzentrum) Electronic Communications and Web Services Building V, Room V404, Phone +49 7531 88 5048, Fax +49 7531 88 3739 -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
Am 21.04.11 07:40, schrieb Martin Rabl: @Pascal Gienger: Pascal, you work with a large SOGo-Installation, do you have any problems with the many tables? We use two Postgres instances: One locally on the SOGo system for the session table, and one in a central Postgres Server infrastructure to store all users' folders. We have 60552 tables now, and the WAL backup (hot backup) is still running without any problem. PostgreSQL 8.4.7 on Ubuntu 10.04 LTS Server with different volumes for DB files and for DB log (WAL) files, 1G shared memory. -- Pascal Gienger Jabber/XMPP/Mail: pascal.gien...@uni-konstanz.de University of Konstanz, IT Services Department (Rechenzentrum) Electronic Communications and Web Services Building V, Room V404, Phone +49 7531 88 5048, Fax +49 7531 88 3739 -- users@sogo.nu https://inverse.ca/sogo/lists
Re: [SOGo] SOGo database
Hi, I was wondering, too. It is a little bit ... strange, in terms of common database design ;-) But it is easy to understand: Every table is a folder of an user. Contacts are folders, calendars are folders ... The current used folder for contacts or calendars you will find in the folder_info-Table. The amount of tables will grow with every user und every folder (=calendar, contacts) he will use. @Pascal Gienger: Pascal, you work with a large SOGo-Installation, do you have any problems with the many tables? Greetings, Martin Am 21.04.2011 um 02:26 schrieb dkil...@hwy.com.au: I was wondering about the structure of SOGo's database. I am using MySQL, not that it is probably all that relevant to this question. Each user appears to have many tables generated for it. I was wondering if there is a finite set of tables that a single user is likely to have, or are these generated tables going to continue to grow in number? At the moment the database looks like it might become large and unwieldy if there were many users with many tables. Cheers, David. -- users@sogo.nu https://inverse.ca/sogo/lists -- users@sogo.nu https://inverse.ca/sogo/lists
