View Issue Details

IDProjectCategoryView StatusLast Update
0004442SOGoBackend Address Bookpublic2018-04-27 09:23
Reporterbassist144 Assigned Toludovic  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
PlatformLinuxOSUbuntuOS Version16.04.4 LTS
Product Version4.0.0 
Fixed in Version4.0.1 
Summary0004442: Unable to add new Contact to MySQL-Backend.
Description

Apr 09 10:35:50 sogod [26795]: [ERROR] <0x0x55b6b9aa1b20[GCSFolder]> -[GCSFolder _generateInsertStatementForRow:adaptor:tableName:]: no type found for column name c_hascertificate
Apr 09 10:35:50 sogod [26795]: [ERROR] <0x55b6ba088680[SOGoContactGCSEntry]:95580663-117a-4ae0-8dfb-3488aac8e17e.vcf> write failed: <MySQL4Exception: 0x55b6bc007e90> NAME:ExecutionFailed REASON:Column count doesn't match value count at row 1

SOGo seems been unable to get column-type from new c_hashcertificate on MariaDB.

Mail / Calender works just fine.
Contacts sync was also working until dropping the database. (Read yes / Write no)

Steps To Reproduce

Stop SOGo;
Drop Old Database;
Create new SOGo Database with mysql-utf8mb4.sql; (From GitRepo)
Change Privileges on new DB;
Start SOGo;

Additional Information

ii libsope-appserver4.9 4.9.r1664.20180320
ii libsope-core4.9 4.9.r1664.20180320
ii libsope-gdl1-4.9 4.9.r1664.20180320
ii libsope-ldap4.9 4.9.r1664.20180320
ii libsope-mime4.9 4.9.r1664.20180320
ii libsope-xml4.9 4.9.r1664.20180320
ii sope4.9-gdl1-mysql 4.9.r1664.20180320
ii sope4.9-libxmlsaxdriver 4.9.r1664.20180320
ii sogo:amd64 4.0.0.20180406-1
ii sogo-activesync 4.0.0.20180406-1
ii libmariadbclient18 10.1.32+maria-1~xenial
ii libmysqlclient18 10.1.32+maria-1~xenial
ii mariadb-client-10.1 10.1.32+maria-1~xenial
ii mariadb-client-core-10.1 10.1.32+maria-1~xenial
ii mariadb-common 10.1.32+maria-1~xenial
ii mariadb-server 10.1.32+maria-1~xenial
ii mariadb-server-10.1 10.1.32+maria-1~xenial
ii mariadb-server-core-10.1 10.1.32+maria-1~xenial
ii mysql-common 10.1.32+maria-1~xenial

Database is Clusted with Galera.
Nightly-Builds

{
/* ***** Main SOGo configuration file **

  • *
  • Since the content of this file is a dictionary in OpenStep plist format, *
  • the curly braces enclosing the body of the configuration are mandatory. *
  • See the Installation Guide for details on the format. *
  • *
  • C and C++ style comments are supported. *
  • *
  • This example configuration contains only a subset of all available *
  • configuration parameters. Please see the installation guide more details. *
  • *
  • ~sogo/GNUstep/Defaults/.GNUstepDefaults has precedence over this file, *
  • make sure to move it away to avoid unwanted parameter overrides. *
  • *
  • **/

    / Database configuration (mysql:// or postgresql://) /
    SOGoProfileURL = "mysql://sogo:@localhost:3306/sogo/sogo_user_profile";
    OCSFolderInfoURL = "mysql://sogo:
    @localhost:3306/sogo/sogo_folder_info";
    OCSStoreURL = "mysql://sogo:@localhost:3306/sogo/sogo_store";
    OCSAclURL = "mysql://sogo:
    @localhost:3306/sogo/sogo_acl";
    OCSCacheFolderURL = "mysql://sogo:@localhost:3306/sogo/sogo_cache_folder";
    OCSSessionsFolderURL = "mysql://sogo:
    @localhost:3306/sogo/sogo_sessions_folder";

    / Mail /
    SOGoDraftsFolderName = Drafts;
    SOGoSentFolderName = Sent;
    SOGoTrashFolderName = Trash;
    SOGoIMAPServer = localhost;
    SOGoSieveServer = sieve://127.0.0.1:4190;
    SOGoSMTPServer = 127.0.0.1;
    //SOGoMailDomain = acme.com;
    SOGoMailingMechanism = smtp;
    //SOGoForceExternalLoginWithEmail = NO;
    //SOGoMailSpoolPath = /var/spool/sogo;
    //NGImap4ConnectionStringSeparator = "/";

    / Notifications /
    //SOGoAppointmentSendEMailNotifications = NO;
    //SOGoACLsSendEMailNotifications = NO;
    //SOGoFoldersSendEMailNotifications = NO;

    / Authentication /
    SOGoPasswordChangeEnabled = YES;

    / LDAP authentication example /
    //SOGoUserSources = (
    // {
    // type = ldap;
    // CNFieldName = cn;
    // UIDFieldName = uid;
    // IDFieldName = uid; // first field of the DN for direct binds
    // bindFields = (uid, mail); // array of fields to use for indirect binds
    // baseDN = "ou=users,dc=acme,dc=com";
    // bindDN = "uid=sogo,ou=users,dc=acme,dc=com";
    // bindPassword = qwerty;
    // canAuthenticate = YES;
    // displayName = "Shared Addresses";
    // hostname = ldap://127.0.0.1:389;
    // id = public;
    // isAddressBook = YES;
    // }
    //);

    / LDAP AD/Samba4 example /
    //SOGoUserSources = (
    // {
    // type = ldap;
    // CNFieldName = cn;
    // UIDFieldName = sAMAccountName;
    // baseDN = "CN=users,dc=domain,dc=tld";
    // bindDN = "CN=sogo,CN=users,DC=domain,DC=tld";
    // bindFields = (sAMAccountName, mail);
    // bindPassword = password;
    // canAuthenticate = YES;
    // displayName = "Public";
    // hostname = ldap://127.0.0.1:389;
    // filter = "mail = '*'";
    // id = directory;
    // isAddressBook = YES;
    // }
    //);

    / SQL authentication example /
    /* These database columns MUST be present in the view/table:

  • c_uid - will be used for authentication - it's the username or username@domain.tld)
  • c_name - which can be identical to c_uid - will be used to uniquely identify entries
  • c_password - password of the user, plain-text, md5 or sha encoded for now
  • c_cn - the user's common name - such as "John Doe"
  • mail - the user's mail address
  • See the installation guide for more details
    */
    SOGoUserSources = (
    {
    type = sql;
    id = vmail_mailbox;
    viewURL = "mysql://sogo:****@127.0.0.1:3306/sogo/users";
    canAuthenticate = YES;

        // Default algorithm used when changing passwords.
        userPasswordAlgorithm = ssha;
        prependPasswordScheme = YES;
    
        // Use vmail.mailbox as global address book.
        // WARNING: This will search all user accounts, not just accounts
        // under same domain as login user.
        //isAddressBook = YES;
        //displayName = &quot;Global Address Book&quot;;
    }

    );

    / Web Interface /
    SOGoPageTitle = SOGo;
    SOGoVacationEnabled = YES;
    SOGoForwardEnabled = YES;
    SOGoSieveScriptsEnabled = YES;
    SOGoMailAuxiliaryUserAccountsEnabled = YES;
    SOGoTrustProxyAuthentication = NO;
    SOGoXSRFValidationEnabled = YES;

    / General - SOGoTimeZone MUST be defined /
    SOGoLanguage = German;
    SOGoTimeZone = Europa/Berlin;
    SOGoCalendarDefaultRoles = (
    PublicDAndTViewer,
    ConfidentialDAndTViewer
    );
    //SOGoSuperUsernames = (sogo1, sogo2); // This is an array - keep the parens!
    SxVMemLimit = 8096 ;
    WOPidFile = "/var/run/sogo/sogo.pid";
    SOGoMemcachedHost = "/var/run/memcached.sock";

    / Debug /
    //SOGoDebugRequests = YES;
    //SoDebugBaseURL = YES;
    //ImapDebugEnabled = YES;
    //LDAPDebugEnabled = YES;
    //PGDebugEnabled = YES;
    //MySQL4DebugEnabled = YES;
    //SOGoUIxDebugEnabled = YES;
    //WODontZipResponse = YES;
    WOLogFile = /var/log/sogo/sogo.log;
    }

TagsNo tags attached.

Activities

francis

francis

2018-04-09 08:55

administrator   ~0012819

Please run the sql-update-3.2.10_to_4.0.0-mysql.sh script.

bassist144

bassist144

2018-04-10 03:33

reporter   ~0012822

Sorry for reopening it, but as you might know.
The Github Version of mysql-utf8mb4.sql, adds the c_hascertificate column.

Database changed
MariaDB [sogo]> explain sogo_quick_contact;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| c_folder_id | int(11) | NO | PRI | NULL | |
| c_name | varchar(255) | NO | PRI | NULL | |
| c_givenname | varchar(255) | YES | | NULL | |
| c_cn | varchar(255) | YES | | NULL | |
| c_sn | varchar(255) | YES | | NULL | |
| c_screenname | varchar(255) | YES | | NULL | |
| c_l | varchar(255) | YES | | NULL | |
| c_mail | text | YES | | NULL | |
| c_o | varchar(255) | YES | | NULL | |
| c_ou | varchar(255) | YES | | NULL | |
| c_telephonenumber | varchar(255) | YES | | NULL | |
| c_categories | varchar(255) | YES | | NULL | |
| c_component | varchar(10) | NO | | NULL | |
| c_hascertificate | int(11) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

In my opinion there is a problem with mariadb/innodb and the column type.

As there is a try to get the type, which throws an error and then leaves out the c_hascertificate in the INSERT.

Apr 09 21:16:01 sogod [30697]: [ERROR] <0x0x558aa8518740[GCSFolder]> -[GCSFolder _generateInsertStatementForRow:adaptor:tableName:]: no type found for column name c_hascertificate
2018-04-09 21:16:01.809 sogod[30697] <MySQL4Channel[0x0x558aa784b3c0] connection=0x0x558aa772eee0> SQL: INSERT INTO sogo_quick_contact (c_folder_id, c_cn, c_component, c_screenname, c_sn, c_name, c_hascertificate, c_telephonenumber, c_categories, c_ou, c_o, c_mail, c_givenName) VALUES (2, '', 'vcard', '', 'Barczyk', '77E9-5ACBBC00-1-5E1ED400.vcf', '1212121212', null, '', '', 'svenbarczyk@gmx.de', 'Sven');
2018-04-09 21:16:01.809 sogod[30697] <MySQL4Channel[0x0x558aa784b3c0] connection=0x0x558aa772eee0> ERROR: Column count doesn't match value count at row 1

so an insert with an missing value throws the: ERROR: Column count doesn't match value count at row 1

ludovic

ludovic

2018-04-27 09:23

administrator   ~0012856

Replaced default NULL with default 0 in the mysql-utf8mb4.sql

Alter your database schema and you should be all good.

Related Changesets

sogo: master 64055678

2018-04-27 09:20:42

ludovic

Details Diff
(fix) fixed default data value for c_hascertificate (fixes 0004442) Affected Issues
0004442
mod - NEWS Diff File
mod - Scripts/mysql-utf8mb4.sql Diff File

Issue History

Date Modified Username Field Change
2018-04-09 05:03 bassist144 New Issue
2018-04-09 08:55 francis Note Added: 0012819
2018-04-09 08:55 francis Status new => closed
2018-04-09 08:55 francis Assigned To => francis
2018-04-09 08:55 francis Resolution open => no change required
2018-04-10 03:33 bassist144 Note Added: 0012822
2018-04-10 03:33 bassist144 Status closed => feedback
2018-04-10 03:33 bassist144 Resolution no change required => reopened
2018-04-27 09:22 ludovic Changeset attached => sogo master 64055678
2018-04-27 09:22 ludovic Assigned To francis => ludovic
2018-04-27 09:23 ludovic Note Added: 0012856
2018-04-27 09:23 ludovic Status feedback => resolved
2018-04-27 09:23 ludovic Fixed in Version => 4.0.1
2018-04-27 09:23 ludovic Resolution reopened => fixed