SOGo | BTS

View Issue Details Jump to Notes ] Related Changesets ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0004442SOGoBackend Address Bookpublic2018-04-09 05:032018-04-27 09:23
Reporterbassist144 
Assigned Toludovic 
PrioritynormalSeverityminorReproducibilityalways
StatusresolvedResolutionfixed 
PlatformLinuxOSUbuntuOS Version16.04.4 LTS
Product Version4.0.0 
Target VersionFixed in Version4.0.1 
Summary0004442: Unable to add new Contact to MySQL-Backend.
DescriptionApr 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 ReproduceStop SOGo;
Drop Old Database;
Create new SOGo Database with mysql-utf8mb4.sql; (From GitRepo)
Change Privileges on new DB;
Start SOGo;



Additional Informationii 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 = "Global Address Book";
        }
    );

  /* 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.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
User avatar (0012819)
francis (administrator)
2018-04-09 08:55

Please run the sql-update-3.2.10_to_4.0.0-mysql.sh script.
(0012822)
bassist144 (reporter)
2018-04-10 03:33

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
User avatar (0012856)
ludovic (administrator)
2018-04-27 09:23

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
Timestamp: 2018-04-27 09:20:42
Author: ludovic
Details ] Diff ]
(fix) fixed default data value for c_hascertificate (fixes 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


Copyright © 2000 - 2018 MantisBT Team
Powered by Mantis Bugtracker