Can't delete old datasets

Get Help
1

EDITED TO ADD: I’m on version 1.8.2

When I try to delete a dataset that I believe is no longer in use, I get “Unexpected Error, please contact support.” The log entry says:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails ('cms'.'datasetdata', CONSTRAINT 'datasetdata_ibfk_1' FOREIGN KEY ('DataSetColumnID') REFERENCES 'datasetcolumn' ('DataSetColumnID')) Exception Type: PDOException

Any ideas?

2

Is that still on 1.8.0 CMS or after upgrade to 1.8.2?

3

Ah, thank you for asking. It’s 1.8.2.

4

I can’t recreate it here locally on my dev CMS or on my test release docker toolbox CMS (both updated to 1.8.2 from earlier 1.8 versions).

Does it affect datasets created after upgrade to 1.8.2 or only those that were in the CMS before?

Is there anything else in the logs related to this action?

5

I was able to create a new dataset, add column, upload a CSV, and delete both the dataset and the data. So it appears to only affect older datasets.

Yes, I think I’ve just found another noteworthy log entry as I was looking through log.csv:

115753,972dcd0,“2017-06-28 10:09:42”,WEB,/dataset/5,DELETE,"SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘cms.dataset_5’ doesn’t exist#0 /var/www/cms/lib/Storage/PdoStorageService.php(163): PDOStatement->execute(Array)

Indeed, the older dataset_ tables don’t seem to exist:

$ grep -n "INSERT INTO .dataset_" db-2017-06-26_18-31-09.sql | cut -d' ' -f3
`dataset_10`
`dataset_11`
`dataset_12`
`dataset_8`
`dataset_9`

But:

  • datasetdata has the data,
  • datasetcolumn has the columns referred to by datasetdata, and
  • dataset has the datasets.

It’s just that the table dataset_5, for example, doesn’t exist.

6

Interesting - that table was removed in the 1.7 → 1.8 upgrade process, in favour of having realised dataset_<id> tables. Did this CMS come from a 1.7 series upgrade?

7

Yes, it did. And the upgrade was more or less simultaneous with converting it to Docker running on a Linux VM, then moving it to Docker running on the Windows Server 2012 R2 VM that had originally been hosting the 1.7 version. So it was a messier process than it needed to be, I’m sure.

8

I’m willing to try connecting to the sql server within Docker and disabling constraints or running other commands.

Fortunately, it would be fairly simple to recreate the datasets we actually want to keep; datasets 8-11 have 7 rows each (library hours at various library branches) and dataset 12 just needs to be recreated and re-uploaded from a CSV file. If we had to delete all datasets at once, the worst part of that would be going into all our layouts and fixing/recreating the timeline items that were pointing to a dataset that was deleted.

But I’m hoping there’s a way to get rid of the datasetdata table, and the old datasets 1-7 on the list, without having to lose the realised dataset_<id> tables. Our database backups are larger than they need to be and it’s an annoyance to look past the old datasets.

9

Do you have dataset_ tables for all datasets that you want to keep? With all relevant data inside them? If the answer is yes, then you can just delete the entire datasetdata table, it is not used anymore.

One time:

DROP TABLE `datasetdata`;

For each dataset to delete:

DELETE FROM `datasetcolumn` WHERE dataSetId = X;
DELETE FROM `dataset` WHERE dataSetId = X;

On the other hand, if there is data you want to get out of that, it gets more complicated. This is what it would have tried to do on upgrade: https://github.com/xibosignage/xibo-cms/blob/develop/lib/Upgrade/DataSetConvertStep.php#L50

Basically, it gets a list of all DataSets, rebuilds the realised tables based on the column definition and then queries datasetdata to transfer the data between tables. The last thing it does is remove the old datasetdata table.

This is complicated to do outside of a programming language, as the old table wasn’t trivial to query. If you needed this we’d have to look at writing a task to do it.

1 Like
10

Thanks, that worked a treat once I changed DELETE TABLE to DROP TABLE.

1 Like
11

Incidentally, does copying a dataset really not copy the rows?

I thought something might have been wrong on my system when I tried to copy a dataset that I didn’t remove, but the documentation for datasets doesn’t mention copying them, so maybe it’s only intended to create a new dataset with the same column names/types.

12

It doesn’t copy all rows, although we could include a checkbox for that.

You may also “hack” it in if you wanted

INSERT INTO `dataset_X` SELECT * FROM `dataset_y`;

Assuming the columns are still the same.

1 Like