Topic: SOGo: can't sync contacts client=>server

[Arnaud]

Hi,
~ since an update of SOGo3 by the beginning of this year, I can't sync the contacts in direction client machine to server.
The opposite direction (server => client) works well.
The clients are Davdroid and Evolution and both give this error message at the sync client => server:

Code: [Select]

https://www.domain.tld/SOGo/dav/arnaud/Contacts/personal/9ac2f04e97cd9f3c95d059fdc978e4db4b4e9e4a.vcf
There are following logs into the server:

Code: [Select]

Jan 14 20:46:20 sogod [3119]: <0x7fea44b8a418[SOGoContactGCSEntry]:878e6283a9e354ffb70496891f42263236642249.vcf> TODO: implement if-none-match for etag: '*'
Jan 14 20:46:20 sogod [3119]: <0x0x7fea44ba4908[GCSFolder]> ERROR(-[GCSFolder writeContent:fromComponent:container:toName:baseVersion:]): cannot insert content : <MySQL4Exception: 0x7fea44a97a38> NAME:ExecutionFailed REASON:Unknown column 'c_hascertificate' in 'field list'
Jan 14 20:46:20 sogod [3119]: [ERROR] <0x7fea44b8a418[SOGoContactGCSEntry]:878e6283a9e354ffb70496891f42263236642249.vcf> write failed: <MySQL4Exception: 0x7fea44a97a38> NAME:ExecutionFailed REASON:Unknown column 'c_hascertificate' in 'field list'
Jan 14 20:46:20 sogod [3119]: 192.168.28.133 "PUT /SOGo/dav/arnaud/Contacts/personal/878e6283a9e354ffb70496891f42263236642249.vcf HTTP/1.1" 500 217/336 0.014 - - 0
Searching the www with "SOGo Unknown column 'c_hascertificate' in 'field list'" I found this discussion https://www.mail-archive.com/users@sogo.nu/msg27143.html => it appears (post 2) that a new column must be added into the db.

=> how to do it?
=> does somebody already have got "positive" experience in this case with a SME9?

Thanks in advance.
Arnaud

[Arnaud]

on the 4th post https://www.mail-archive.com/users@sogo.nu/msg27156.html there are some details about a script "sql-update-3.2.10_to_4.0.0.sh".
I found it by "/usr/share/doc/sogo-3.2.10.20180113".

Code: [Select]

# db configuration  show sogod gives the db-password

Procedure: stop sogo, backup the db, run the script, pray&start sogo ??

=> may be next WE.....

[mmccarn]

I can confirm that I get the same error ("... <MySQL4Exception: 0x7f169bedf148> NAME:ExecutionFailed REASON:Unknown column 'c_hascertificate' in 'field list'...") in /var/log/sogo.log if I click on Address Book in the sogo web interface.

I'm currently running sogo-3.2.10.20180111-1.centos6.x86_64

[Stefano]

when you execute the script, don't use 127.0.0.1 as server but localhost

[mmccarn]

I fixed the problem on my system as follows:

Code: [Select]

# stop sogod
/etc/init.d/sogod stop
#
# backup the sogo db
mkdir -p ~/sogodump
cd ~/sogodump
mysqldump sogo > sogo.mysqldump
#
# Display the DbPassword for the 'sogo' database for the 'sogo' user (so you can put it in your clipboard)
config getprop sogod DbPassword
#
# Run the update script
# The exact path to the script will vary depending on your installed sogo version
#
# IMPORTANT: the database host on my system defaulted to "127.0.0.1" and had to be changed to "localhost"
sudo -u sogo /usr/share/doc/sogo-3.2.10.20180111/sql-update-3.2.10_to_4.0.0-mysql.sh
#
# Username (sogo): [press enter]
# Hostname (127.0.0.1): [localhost]
# Database (sogo): [press enter]
# Enter password: [paste the db password obtained using "config getprop sogod DbPassword"]
# Enter password: [paste it again]
#
# Restart sogod
/etc/init.d/sogod start


After doing the update as described above:
- Inbox loads OK
- Address Book loads OK
- NO OTHER TESTS have been performed

[mmccarn]

Another note: It looks like the script is adding the missing field (c_hascertificate) in a separate table for each connected user.

On my system, the new field was added to these tables:
 sogoadmin0015f9ea953_quick
 sogommccarn00203a16790_quick

But *not* to these tables:
 sogoadmin00112b40482_quick
 sogommccarn0012042b8a0_quick

(My conclusion was - "don't try to do this without using the script")

[Stefano]

the script does some other things too..

[mmccarn]

the script does some other things too..

Yeah; that's why I specified that I had not tested anything behond Inbox and Address Book...


I've created a bug report at sogo.nu:
https://sogo.nu/bugs/view.php?id=4374

[Arnaud]

Hi,
many thanks for your replies.

Another note: It looks like the script is adding the missing field (c_hascertificate) in a separate table for each connected user.

How should this be understood? Is it a good thing or a bad thing, looking into the future?
Will the result of the script be supported by the future updates of sogo or will the modified db become unusable in the future?

Arnaud

[mmccarn]

Hi,
many thanks for your replies.

How should this be understood? Is it a good thing or a bad thing, looking into the future?
Will the result of the script be supported by the future updates of sogo or will the modified db become unusable in the future?

Arnaud

My intention was to point out that there isn't a single table you can add the field to (manually) and be done with it -- that you need to use the script.  I spent 30-45 minutes trying to figure out which table needed the new field, and wanted to save others this wasted time.

The script is an official Sogo script; there is no reason that the new data fields should introduce future compatibility problems.  If they do, I'm sure the folks at sogo will create another script to fix things.

[Arnaud]

Hi,
the script has worked perfectly: I can sync the contacts again.

@mmccarn: many thanks for the detailed way to use the script! 

I was not aware that the script comes from SOGo, but it is in fact evident, due to its storage place into the SOGo folder........

Bye
Arnaud

[mmccarn]

Glad it worked.

Here is the official response from Sogo to my bug report:

You always have to run the new/additional database update script after updating SOGo.
That is also hinted on the command line, when updating.

Therefore this is no error on the SOGo side.

The only thing you could argue is, that the documentation for the released version is not yet updated.
But you are using a nightly build, therefore that change didn't happen in the release yet.

Which, as far as I can tell, is shorthand for "you're on your own with the nightly builds, we don't give you any easy way to figure out that there's a script you need to run, and if we issue a security patch to the supported version of 3.2.x our paid users will have this problem and we'll deal with it then."

[Stefano]

Glad it worked.

Here is the official response from Sogo to my bug report:
Which, as far as I can tell, is shorthand for "you're on your own
with the nightly builds, we don't give you any easy way to figure out that there's a script you need to run, and if we issue a security patch to the supported version of 3.2.x our paid users will have this problem and we'll deal with it then."

well.. they're right.. you're (we're) using nightly build, so it's up to us to check everything is working as expected..

BTW, I don't remember any kind of advice about using script during the yum upgrade process..