Tinebase.purgeDeletedRecords raises sql exception

[mokraemer]

php /mnt/www/tine/tine20.php --username "XXX" --password "YYYY" --method Tinebase.purgeDeletedRecords -- date=$(date -d -30days +%Y-%m-%d)

Purging obsolete data from tables... Cleaning relations...PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tine.tine20_sales_contracts' doesn't exist in /mnt/www/tine/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php:228 Stack trace:

0 /mnt/www/tine/vendor/zendframework/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute()

[github-actions[bot]]

This issue is stale because it has been open 30 days with no activity. Comment or remove the stale label. Otherwise, it will be closed in 7 days.

[pschuele]

I think, something is wrong with you db structure (also see #42 ). you might try to recreate the table via:

CREATE TABLE `tine20_sales_contracts` (
  `id` varchar(40) NOT NULL,
  `parent_id` varchar(255) DEFAULT NULL,
  `number` varchar(64) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` longtext DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `billing_address_id` varchar(255) DEFAULT NULL,
  `container_id` varchar(40) DEFAULT NULL,
  `created_by` varchar(40) DEFAULT NULL,
  `creation_time` datetime DEFAULT NULL,
  `last_modified_by` varchar(40) DEFAULT NULL,
  `last_modified_time` datetime DEFAULT NULL,
  `seq` int(10) unsigned NOT NULL DEFAULT 0,
  `deleted_by` varchar(40) DEFAULT NULL,
  `deleted_time` datetime DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `eval_dim_cost_center` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_11FA3506BC21F742` (`container_id`),
  KEY `eval_dim_cost_center` (`eval_dim_cost_center`),
  FULLTEXT KEY `description` (`description`),
  CONSTRAINT `FK_11FA35068726CCC9` FOREIGN KEY (`eval_dim_cost_center`) REFERENCES `tine20_evaluation_dimension_item` (`id`),
  CONSTRAINT `FK_11FA3506BC21F742` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

[mokraemer]

Nope this does not WORK

[mokraemer]

Old table is defined as

CREATE TABLE `tine20_sales_contracts` (
  `id` varchar(40) NOT NULL,
  `container_id` varchar(40) NOT NULL,
  `parent_id` varchar(40) DEFAULT NULL,
  `number` varchar(64) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `billing_address_id` varchar(40) DEFAULT NULL,
  `created_by` varchar(40) DEFAULT NULL,
  `creation_time` datetime DEFAULT NULL,
  `last_modified_by` varchar(40) DEFAULT NULL,
  `last_modified_time` datetime DEFAULT NULL,
  `is_deleted` tinyint(1) unsigned DEFAULT 0,
  `deleted_by` varchar(40) DEFAULT NULL,
  `deleted_time` datetime DEFAULT NULL,
  `seq` int(11) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `container_id` (`container_id`),
  FULLTEXT KEY `description` (`description`),
  CONSTRAINT `tine20_sales_contracts::container_id--container::id` FOREIGN KEY (`container_id`) REFERENCES `tine20_container` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci ROW_FORMAT=DYNAMIC

Trying to drop and recreate the table via your code gives: ERROR 1005 (HY000): Can't create table tine.tine20_sales_contracts (errno: 150 "Foreign key constraint is incorrectly formed")

It would be nice, if the reports did not get closed directly, if there is no option to reopen them

[mokraemer]

Please note, this installation was originally created ~2015, and is updated regulary. I've issued this bug a few times since 2015 - but was never resolved.

I've created the table without constraints, run update afterwards, constraints were not added by the update.

At least Tinebase.purgeDeletedRecords runs without errors, but is very slow and runs a lot of sql queries!

[pschuele]

is this still an issue? you said that it runs without errors now ...

[mokraemer]

it can be closed. It runs - but is quite slow. Looks like every record is loaded into php, and then deleted instead of a direct database operation.