6.6.25 - SQL exceptions after update from 6.5.96

[thomasgg23]

Hi,

i'm getting this error when I try to upgrade to 6.6.25 from 6.5.96.

Upgrade output will be logged into: log/upgrade/20220103_145427.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module chat because it's not available. Skipping module documenttemplates because it's not available. Skipping module gota because it's not available. Skipping module projects2 because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module pr2analyzer because it's not available. Skipping module timeregistration2 because it's not available. Skipping module assistant because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [legacy/tasks] Excuting query: update core_module set package='community', version=0 where name='tasks' legacy/tasks updated from 60 to 61

A module was refactored. Rerunning...

Skipping module chat because it's not available. Skipping module documenttemplates because it's not available. Skipping module gota because it's not available. Skipping module projects2 because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module pr2analyzer because it's not available. Skipping module timeregistration2 because it's not available. Skipping module assistant because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function Error: Exception in /usr/share/groupoffice/go/core/db/Utils.php at line 38: SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead on query (22) create table if not exists tasks_user_settings ( userId int null, defaultTasklistId int(11) unsigned null, rememberLastItems boolean not null default false, lastTasklistIds varchar(255) null, constraint tasks_user_settings_pk primary key (userId), constraint tasks_user_settings_core_user_id_fk foreign key (userId) references core_user (id) on delete cascade ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

[thomasgg23]

Second run produces this

Upgrade Group-Office Upgrade output will be logged into: log/upgrade/20220103_145523.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module chat because it's not available. Skipping module documenttemplates because it's not available. Skipping module gota because it's not available. Skipping module projects2 because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module pr2analyzer because it's not available. Skipping module timeregistration2 because it's not available. Skipping module assistant because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function Error: Exception in /usr/share/groupoffice/go/core/db/Utils.php at line 38: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-2' for key 'tasks_portlet_tasklist.PRIMARY' on query (16) INSERT INTO tasks_portlet_tasklist (createdBy, tasklistId) SELECT user_id, tasklist_id FROM ta_portlet_tasklists

[mschering]

Which Mysql version are you using?

I didn't ran into this with Mariadb.

Anyway, I made a fix for the next release.

[thomasgg23]

Ubuntu 20.04 LTS old installation with mysql-server 8.0.27-0ubuntu0.20.04.1

Thanks. I'll check it again with the next release and when my pro-license is updated to 6.6. I already opened a ticket in the helpdesk. So no rush.

[zavrazhny]

I confirm the same errors with the same config - Ubuntu 20.04 LTS + mySQL 8.0.27 with sql_mode = ''

[zavrazhny]

mschering, thanks for prompt reply, but the error still persists with patches applied: https://github.com/Intermesh/groupoffice/commit/47e688c142fac004f4dcb1251fcf9c8ecf3e7950 https://github.com/Intermesh/groupoffice/commit/969706b76ef9504c4eeaa4f0dbc851e6f5b2c0a2

this is the current log: Upgrade output will be logged into: log/upgrade/20220104_120619.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function Error: Exception in /usr/share/groupoffice/go/core/db/Utils.php at line 38: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-3' for key 'tasks_portlet_tasklist.PRIMARY' on query (16) INSERT INTO tasks_portlet_tasklist (createdBy, tasklistId) SELECT distinct user_id, tasklist_id FROM ta_portlet_tasklists

[mschering]

Can you try to change it into:

INSERT IGNORE INTO tasks_portlet_tasklist (createdBy, tasklistId) SELECT DISTINCT user_id, tasklist_id FROM ta_portlet_tasklists;

[zavrazhny]

the next table should probably needs the same tweak:

Upgrade output will be logged into: log/upgrade/20220104_124817.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function Error: Exception in /usr/share/groupoffice/go/core/db/Utils.php at line 38: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'tasks_tasklist.PRIMARY' on query (17) INSERT INTO tasks_tasklist (id, role, name, createdBy, aclId, filesFolderId, version) SELECT id, '1', name, user_id, acl_id, files_folder_id, version FROM ta_tasklists

[zavrazhny]

I then changed line 286 to: INSERT IGNORE INTO tasks_tasklist (id, role, name, createdBy, aclId, filesFolderId, version)

and got: Upgrade output will be logged into: log/upgrade/20220104_125410.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function Error: Exception in /usr/share/groupoffice/go/core/db/Utils.php at line 38: SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead on query (22) create table if not exists tasks_user_settings ( userId int null, defaultTasklistId int(11) unsigned null, rememberLastItems boolean not null default false, lastTasklistIds varchar(255) null, constraint tasks_user_settings_pk primary key (userId), constraint tasks_user_settings_core_user_id_fk foreign key (userId) references core_user (id) on delete cascade ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

[mschering]

That shouldn't be necessary. I think the problem is it's running for the second time. Can you restart the upgrade from scratch?

[zavrazhny]

while my restore task in progress (I preferred to restore from snapshot rather from mysqldump < sql), would you please confirm my steps:

1. I am on my way back to 6.5.95 and then do upgrade to 6.5.96 (it was OK in running configuration)
2. after upgrade I change sixfive to sixsix in /etc/apt/sources.list.d/groupoffice.list
3. sudo apt-get update && upgrade
4. I do changes in sql scripts as above suggested except mine from here https://github.com/Intermesh/groupoffice/issues/770#issuecomment-1004667238
5. start upgrade

[mschering]

Hi,

Step 4 is no longer necessary as 6.6.26 has been released with those fixes.

[zavrazhny]

Did a restore to 6.5.95 with next upgrade to 6.5.96 then to 6.6.26 and finally the same:

Upgrade output will be logged into: log/upgrade/20220104_194744.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function Error: Exception in /usr/share/groupoffice/go/core/db/Utils.php at line 38: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'tasks_tasklist.PRIMARY' on query (17) INSERT INTO tasks_tasklist (id, role, name, createdBy, aclId, filesFolderId, version) SELECT id, '1', name, user_id, acl_id, files_folder_id, version FROM ta_tasklists

[mschering]

This should only happen if for some reason the query is executed for a second time. Are you sure there wasn't a table 'tasks_tasklist' left over from the previous attempt?

[zavrazhny]

Yes, unfortunately I didn’t save the first output, but it also was an error.

Do you test releases against mysql?

[thomasgg23]

Seems to be the same problem with one of my installations. Fixed it with clearing the tables tasks_tasklist and tasks_category and running upgrade again.

Works fine now

[zavrazhny]

Attempt to upgrade from 6.5.96 to 6.6.28 failed:

Upgrade output will be logged into: log/upgrade/20220107_124545.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [legacy/tasks] Excuting query: update core_module set package='community', version=0 where name='tasks' legacy/tasks updated from 60 to 61

A module was refactored. Rerunning...

Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [community/tasks] Running callable function community/tasks updated from 0 to 1 [community/tasks] Running callable function community/tasks updated from 1 to 2 [community/tasks] Running callable function community/tasks updated from 2 to 3 [community/tasks] Excuting query: ALTER TABLE tasks_task CHANGE COLUMN description description TEXT NULL DEFAULT ''; community/tasks updated from 3 to 4 [community/notes] Excuting query: ALTER TABLE notes_user_settings ADD (rememberLastItems TINYINT(1) DEFAULT 0, lastNoteBookIds VARCHAR(255) DEFAULT ''); community/notes updated from 57 to 58 [community/tasks] Running callable function Migrating project jobs to tasks

Done migrating project jobs to taskscommunity/tasks updated from 4 to 5 [community/tasks] Excuting query: ALTER TABLE tasks_task ADD progressChange TINYINT(2) NULL community/tasks updated from 5 to 6 [community/tasks] Excuting query: ALTER TABLE tasks_task ADD COLUMN IF NOT EXISTS startTime TIME NULL DEFAULT NULL IGNORE: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS startTime TIME NULL DEFAULT NULL' at line 1 community/tasks updated from 6 to 7 [legacy/sync] Excuting query: legacy/sync updated from 50 to 51 [legacy/sync] Excuting query: alter table sync_tasklist_user change tasklist_id tasklistId int(11) unsigned auto_increment; legacy/sync updated from 51 to 52 [legacy/sync] Excuting query: alter table sync_tasklist_user change user_id userId int(11) default 0 not null; legacy/sync updated from 52 to 53 [legacy/sync] Excuting query: alter table sync_tasklist_user change default_tasklist isDefault boolean default 0 not null; legacy/sync updated from 53 to 54 [legacy/sync] Excuting query: DELETE FROM sync_tasklist_user WHERE userId NOT IN(SELECT id FROM core_user); legacy/sync updated from 54 to 55 [legacy/sync] Excuting query: alter table sync_tasklist_user add constraint sync_tasklist_user_core_user_id_fk foreign key (userId) references core_user (id) on delete cascade; legacy/sync updated from 55 to 56 [legacy/sync] Excuting query: DELETE FROM sync_tasklist_user WHERE tasklistId NOT IN(SELECT id FROM tasks_tasklist); legacy/sync updated from 56 to 57 [legacy/sync] Excuting query: alter table sync_tasklist_user add constraint sync_tasklist_user_tasks_tasklist_id_fk foreign key (tasklistId) references tasks_tasklist (id) on delete cascade; legacy/sync updated from 57 to 58 [community/tasks] Excuting query: alter table tasks_tasklist add projectId int null; IGNORE: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'projectId' community/tasks updated from 7 to 8 [community/addressbook] Excuting query: ALTER TABLE addressbook_user_settings ADD lastAddressBookId INT(11) null; community/addressbook updated from 67 to 68 [community/addressbook] Excuting query: alter table addressbook_user_settings add startIn enum('allcontacts', 'starred', 'default', 'remember') default 'allcontacts' not null; community/addressbook updated from 68 to 69 [community/tasks] Excuting query: create table if not exists tasks_user_settings ( userId int not null, defaultTasklistId int(11) unsigned null, rememberLastItems boolean not null default false, lastTasklistIds varchar(255) null, constraint tasks_user_settings_pk primary key (userId), constraint tasks_user_settings_core_user_id_fk foreign key (userId) references core_user (id) on delete cascade ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

community/tasks updated from 8 to 9 [community/tasks] Excuting query: alter table tasks_task drop foreign key tasks_task_ibfk_1; community/tasks updated from 9 to 10 [community/tasks] Excuting query: alter table tasks_task add constraint tasks_task_ibfk_1 foreign key (tasklistId) references tasks_tasklist (id) on DELETE cascade; community/tasks updated from 10 to 11 [community/tasks] Excuting query: alter table tasks_user_settings add constraint tasks_user_settings_tasks_tasklist_id_fk foreign key (defaultTasklistId) references tasks_tasklist (id) on delete set null; community/tasks updated from 11 to 12 [community/tasks] Excuting query: create index tasks_task_progress_index on tasks_task (progress); community/tasks updated from 12 to 13 [community/tasks] Excuting query: ALTER TABLE tasks_category DROP FOREIGN KEY tasks_category_ibfk_1; community/tasks updated from 13 to 14 [community/tasks] Excuting query: ALTER TABLE tasks_category CHANGE COLUMN createdBy ownerId INT(11) NULL ; community/tasks updated from 14 to 15 [community/tasks] Excuting query: ALTER TABLE tasks_category ADD CONSTRAINT tasks_category_ibfk_1 FOREIGN KEY (ownerId) REFERENCES core_user (id); community/tasks updated from 15 to 16 [community/tasks] Excuting query: ALTER TABLE tasks_task ADD COLUMN IF NOT EXISTS location TEXT NULL; IGNORE: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS location TEXT NULL' at line 1 community/tasks updated from 16 to 17 [community/tasks] Excuting query: ALTER TABLE tasks_category ADD COLUMN tasklistId INT(11) NULL DEFAULT NULL AFTER ownerId, ADD INDEX tasks_category_tasklist_ibfk_9_idx (tasklistId ASC); community/tasks updated from 17 to 18 [community/tasks] Excuting query: ALTER TABLE tasks_category ADD CONSTRAINT tasks_category_tasklist_ibfk_9 FOREIGN KEY (tasklistId) REFERENCES tasks_tasklist (createdBy) ON DELETE CASCADE; community/tasks updated from 18 to 19 [community/comments] Excuting query: drop table comments_attachment community/comments updated from 27 to 28 [community/comments] Excuting query: create table comments_comment_attachment ( id int unsigned auto_increment primary key, commentId int not null, blobId binary(40) null, name varchar(190) not null, constraint comments_comment_attachment_comments_comment_id_fk foreign key (commentId) references comments_comment (id) on update cascade, constraint comments_comment_attachment_core_blob_id_fk foreign key (blobId) references core_blob (id) ); community/comments updated from 28 to 29 [community/tasks] Excuting query: alter table tasks_category drop foreign key tasks_category_ibfk_1; community/tasks updated from 19 to 20 [community/tasks] Excuting query: alter table tasks_category add constraint tasks_category_ibfk_1 foreign key (ownerId) references core_user (id) on delete cascade; community/tasks updated from 20 to 21 [core/core] Excuting query: UPDATE core_user SET theme='Paper' WHERE theme NOT IN ('Paper', 'Dark', 'Compact'); core/core updated from 246 to 247 [core/core] Excuting query: alter table core_alert add data text null; core/core updated from 247 to 248 [core/core] Excuting query: CREATE TABLE core_pdf_block ( id bigint(20) UNSIGNED NOT NULL, pdfTemplateId bigint(20) UNSIGNED NOT NULL, x int(11) DEFAULT NULL, y int(11) DEFAULT NULL, width int(11) DEFAULT NULL, height int(11) DEFAULT NULL, align enum('L','C','R','J') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'L', content text COLLATE utf8mb4_unicode_ci NOT NULL, type varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'text' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; IGNORE: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'core_pdf_block' already exists core/core updated from 248 to 249 [core/core] Excuting query: CREATE TABLE core_pdf_template ( id bigint(20) UNSIGNED NOT NULL, moduleId int(11) NOT NULL, key varchar(20) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, language varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en', name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, stationaryBlobId binary(40) DEFAULT NULL, landscape tinyint(1) NOT NULL DEFAULT 0, pageSize varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A4', measureUnit enum('mm','pt','cm','in') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'mm', marginTop decimal(19,4) NOT NULL DEFAULT 10.0000, marginRight decimal(19,4) NOT NULL DEFAULT 10.0000, marginBottom decimal(19,4) NOT NULL DEFAULT 10.0000, marginLeft decimal(19,4) NOT NULL DEFAULT 10.0000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; IGNORE: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'core_pdf_template' already exists core/core updated from 249 to 250 [core/core] Excuting query: ALTER TABLE core_pdf_block ADD PRIMARY KEY (id) USING BTREE, ADD KEY pdfTemplateId (pdfTemplateId); IGNORE: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'pdfTemplateId' core/core updated from 250 to 251 [core/core] Excuting query: ALTER TABLE core_pdf_template ADD PRIMARY KEY (id), ADD KEY moduleId (moduleId), ADD KEY stationaryBlobId (stationaryBlobId); IGNORE: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'moduleId' core/core updated from 251 to 252 [core/core] Excuting query: ALTER TABLE core_pdf_block MODIFY id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; core/core updated from 252 to 253 [core/core] Excuting query: ALTER TABLE core_pdf_template MODIFY id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; core/core updated from 253 to 254 [core/core] Excuting query: ALTER TABLE core_pdf_block ADD CONSTRAINT core_pdf_block_ibfk_1 FOREIGN KEY (pdfTemplateId) REFERENCES core_pdf_template (id) ON DELETE CASCADE; IGNORE: SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'core_pdf_block_ibfk_1' core/core updated from 254 to 255 [core/core] Excuting query: ALTER TABLE core_pdf_template ADD CONSTRAINT core_pdf_template_ibfk_1 FOREIGN KEY (moduleId) REFERENCES core_module (id) ON DELETE CASCADE, ADD CONSTRAINT core_pdf_template_ibfk_2 FOREIGN KEY (stationaryBlobId) REFERENCES core_blob (id); IGNORE: SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'core_pdf_template_ibfk_1' core/core updated from 255 to 256 [core/core] Excuting query: ALTER TABLE core_email_template ADD key VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NULL DEFAULT NULL AFTER aclId, ADD language VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'en' AFTER key; core/core updated from 256 to 257 [core/core] Excuting query: alter table core_alert change alertId tag varchar(50) null; core/core updated from 257 to 258 [core/core] Excuting query: create unique index core_alert_entityTypeId_entityId_tag_userId_uindex on core_alert (entityTypeId, entityId, tag, userId);

core/core updated from 258 to 259 [core/core] Excuting query: create table core_auth_remember_me ( id int auto_increment, token varchar(190) collate ascii_bin null, series varchar(190) collate ascii_bin null, userId int not null, expiresAt datetime null, constraint core_auth_remember_me_pk primary key (id) ); core/core updated from 259 to 260 [core/core] Excuting query: create index core_auth_remember_me_series_index on core_auth_remember_me (series); core/core updated from 260 to 261 [core/core] Excuting query: alter table core_auth_remember_me add constraint core_auth_remember_me_core_user_id_fk foreign key (userId) references core_user (id); core/core updated from 261 to 262 [core/core] Excuting query: alter table core_auth_remember_me add remoteIpAddress varchar(100) CHARACTER SET ascii COLLATE ascii_bin NOT NULL; core/core updated from 262 to 263 [core/core] Excuting query: alter table core_auth_remember_me add userAgent varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL core/core updated from 263 to 264 [core/core] Excuting query: alter table core_auth_remember_me add platform varchar(190) COLLATE utf8mb4_unicode_ci null after userAgent; core/core updated from 264 to 265 [core/core] Excuting query: alter table core_auth_remember_me add browser varchar(190) COLLATE utf8mb4_unicode_ci null after platform; core/core updated from 265 to 266 [core/core] Excuting query: alter table core_alert drop foreign key fk_alert_user; core/core updated from 266 to 267 [core/core] Excuting query: alter table core_alert add constraint fk_alert_user foreign key (userId) references core_user (id) on delete cascade; core/core updated from 267 to 268 [core/core] Excuting query: CREATE TABLE core_permission ( moduleId INT NOT NULL, groupId INT NOT NULL, rights BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (moduleId, groupId), INDEX fk_permission_group_idx (groupId ASC), CONSTRAINT fk_permission_module FOREIGN KEY (moduleId) REFERENCES core_module (id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT fk_permission_group FOREIGN KEY (groupId) REFERENCES core_group (id) ON DELETE CASCADE ON UPDATE NO ACTION); core/core updated from 268 to 269 [core/core] Excuting query: INSERT IGNORE INTO core_permission (groupId, rights, moduleId) SELECT ag.groupId, IF(ag.level > 10, 1,0), m.id FROM core_acl_group ag join core_module m on ag.aclId = m.aclId; core/core updated from 269 to 270 [core/core] Excuting query: UPDATE core_permission p join core_acl_group ag on ag.groupId = p.groupId join core_module m on ag.aclId = m.aclId SET rights = IF(ag.level=10,0,IF(ag.level=40,1,3)) WHERE m.id = p.moduleId AND m.name = 'projects2'; core/core updated from 270 to 271 [core/core] Excuting query: alter table core_module drop foreign key acl; core/core updated from 271 to 272 [core/core] Excuting query: alter table core_module drop column aclId; core/core updated from 272 to 273 [core/core] Excuting query: alter table core_alert add sendMail boolean default false not null; core/core updated from 273 to 274 [core/core] Excuting query: insert ignore into core_setting values((select id from core_module where name='core'), 'demoDataAsked', 1) core/core updated from 274 to 275 [business/business] Excuting query: alter table business_activity add sortOrder int(11) unsigned default 0 null; Error: Exception in /usr/share/groupoffice/go/core/Installer.php at line 726: 42S02: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'groupoffice.business_activity' doesn't exist Query: alter table business_activity add sortOrder int(11) unsigned default 0 null; Package: business Module: business Module installed version: 31 Module source version: 32 ABORTING: Please contact support

[zavrazhny]

second run of upgrader:

Upgrade output will be logged into: log/upgrade/20220107_131410.log

Failed to disable 'innodb_strict_mode': SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation Skipping module assistant because it's not available. Skipping module billing because it's not available. Skipping module projects2 because it's not available. Skipping module timeregistration2 because it's not available. Skipping module hoursapproval2 because it's not available. Skipping module leavedays because it's not available. Skipping module savemailas because it's not available. Skipping module tickets because it's not available. Skipping module newsletters because it's not available. Skipping module onlyoffice because it's not available. Skipping module wopi because it's not available. [business/business] Excuting query: alter table business_activity add sortOrder int(11) unsigned default 0 null; Error: Exception in /usr/share/groupoffice/go/core/Installer.php at line 726: 42S02: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'groupoffice.business_activity' doesn't exist Query: alter table business_activity add sortOrder int(11) unsigned default 0 null; Package: business Module: business Module installed version: 31 Module source version: 32 ABORTING: Please contact support

[mschering]

Strange, is the module "business" installed in 6.5?

[zavrazhny]

I used a trial key, then switched off the related modules and finally deleted it. So I am on free license

[mschering]

Ok that should be fine, it still updates "Disabled" modules if not removed completely. It's still strange that the 'business_activity' is not there. did you remove tables manually?

[zavrazhny]

I don’t know the tables names responsible for business modules - so I didn't do any changes to DB structure

[zavrazhny]

mschering, I See there is no progress on this issue for almost a month, as well as no answer for questions asked, so I think it's not a matter for me to use further. I hope you could involve more people inside the project except you. It has attracted me by simple and fast interface and very reasonable functionality, but I see the progress and issues arrived are far from the responses and patches they must have.

[michalcharvat]

@mschering Maybe I missed something but it almost looks like there is no check if module is enabled or disabled. Based on that it does not look like zavrazhny removed business modules from GO and because there is no check if module is enabled, it will simply goes trough all modules in GO. Installer only check if modules are available (and they probably are). Update script is also available so installer will process queries from update file.

[mschering]

Yes, it's just strange that the table business_activity doesn't exist. The module seems to be installed but the tables are not.

You could try to run the install file manually:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema go_master
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `business_business`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `business_business` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `hourlyRevenue` FLOAT NULL,
  `contactId` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_business_business_addressbook_contact_idx` (`contactId` ASC),
  CONSTRAINT `fk_business_business_addressbook_contact`
    FOREIGN KEY (`contactId`)
    REFERENCES `addressbook_contact` (`id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `business_employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `business_employee` (
  `id` INT NOT NULL, -- cant be unsigned is referenced to user
  `businessId` INT UNSIGNED NOT NULL,
  `timeClosedUntil` DATE,
  `quitAt` DATE NULL,
  `hourlyRevenue` FLOAT NOT NULL DEFAULT 0,
  `hourlyCosts` FLOAT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `fk_business_employee_business_business1_idx` (`businessId` ASC),
  CONSTRAINT `fk_business_employee_core_user1`
    FOREIGN KEY (`id`)
    REFERENCES `core_user` (`id`)
    ON DELETE cascade
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_business_employee_business_business1`
    FOREIGN KEY (`businessId`)
    REFERENCES `business_business` (`id`)
    ON DELETE cascade
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `business_agreement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `business_agreement` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `employeeId` INT NOT NULL,
  `start` DATE NOT NULL,
  `finish` DATE DEFAULT NULL,
--  `hourlyCost` FLOAT NOT NULL DEFAULT 0,
--  `yearlyHolidayTime` SMALLINT NOT NULL DEFAULT 0,
--  `holidayValidityPeriod` VARCHAR(10) NULL,
  `mo` SMALLINT UNSIGNED NOT NULL,
  `tu` SMALLINT UNSIGNED NOT NULL,
  `we` SMALLINT UNSIGNED NOT NULL,
  `th` SMALLINT UNSIGNED NOT NULL,
  `fr` SMALLINT UNSIGNED NOT NULL,
  `sa` SMALLINT UNSIGNED NOT NULL,
  `su` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_business_agreement_business_employee1_idx` (`employeeId` ASC),
  CONSTRAINT `fk_business_agreement_business_employee1`
    FOREIGN KEY (`employeeId`)
    REFERENCES `core_user` (`id`)
    ON DELETE cascade
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `business_manager`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `business_manager` (
  `subjectId` INT NOT NULL,
  `managerId` INT NOT NULL,
  `notified` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`subjectId`, `managerId`),
  INDEX `fk_business_employeebusiness_employee_business_employee2_idx` (`managerId` ASC),
  INDEX `fk_business_employeebusiness_employee_business_employee1_idx` (`subjectId` ASC),
  CONSTRAINT `fk_business_employeecore_user_core_user1`
    FOREIGN KEY (`subjectId`)
    REFERENCES `core_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_business_employeecore_user_core_user2`
    FOREIGN KEY (`managerId`)
    REFERENCES `core_user` (`id`)
    ON DELETE cascade
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `business_activity` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `type` SMALLINT NOT NULL DEFAULT 1,
   `code` VARCHAR(11) NOT NULL,
   `name` VARCHAR(50) NOT NULL,
   `units` DOUBLE NOT NULL,
   `description` TEXT NULL,
   `disabled` TINYINT(1) NULL DEFAULT 0,
   `billable` TINYINT(4) NULL DEFAULT 0,
   `budgetable` TINYINT(4) NULL DEFAULT 0,
   `budgetExpires` TINYINT(1) NULL DEFAULT 0,
   `budgetTransferable` TINYINT(1) NULL DEFAULT 0,
   `sortOrder` INT(11) UNSIGNED NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `business_activity_budget` (
  `agreementId` INT UNSIGNED NOT NULL,
  `activityId` INT UNSIGNED NOT NULL,
  `budget` FLOAT NOT NULL DEFAULT 0, -- only if activity is budgetable
  PRIMARY KEY (`agreementId`, `activityId`),
  INDEX `fk_business_activity_budget_business_activity1_idx` (`activityId` ASC),
  INDEX `fk_business_activity_budget_business_employee_agreement1_idx` (`agreementId` ASC),
  CONSTRAINT `fk_business_activity_budget_business_employee_agreement1`
      FOREIGN KEY (`agreementId`)
          REFERENCES `business_agreement` (`id`)
          ON DELETE CASCADE
          ON UPDATE NO ACTION,
  CONSTRAINT `fk_business_activity_budget_business_activity1`
      FOREIGN KEY (`activityId`)
          REFERENCES `business_activity` (`id`)
          ON DELETE cascade
          ON UPDATE NO ACTION
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `business_activity_rate` (
    `employeeId` INT NOT NULL,
    `activityId` INT UNSIGNED NOT NULL,
    `externalRate` FLOAT NOT NULL,
    PRIMARY KEY (`employeeId`, `activityId`),
    INDEX `fk_business_employee_activity_business_activity1_idx` (`activityId` ASC),
    INDEX `fk_business_employee_activity_business_employee1_idx` (`employeeId` ASC),
    CONSTRAINT `fk_business_employee_activity_business_employee1`
        FOREIGN KEY (`employeeId`)
        REFERENCES `business_employee` (`id`)
        ON DELETE cascade
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_business_employee_activity_business_activity1`
        FOREIGN KEY (`activityId`)
        REFERENCES `business_activity` (`id`)
        ON DELETE cascade
        ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS  `business_business_custom_fields` (
    `id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `business_business_custom_fields_ibfk_1`
        FOREIGN KEY (`id`)
        REFERENCES `business_business` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

PS: @zavrazhny sorry for not replying sooner. With covid lockdowns and kids home from school often, we're having some workload issues. This is not an issue that affects other people so I lost track of it.

[derjoachim]

Closing this ticket due to long inactivity.