Integrity constraint violation: 1452 Cannot add or update a child row: foreign key constraint fails

[Sweepr]

Describe the bug

I just noticed my laravel-log is slowly filling up with error's like shown below:

Example:

NNTmux.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (nntmux_db.binaries, CONSTRAINT FK_Collections FOREIGN KEY (collections_id) REFERENCES collections (id) ON DELETE CASCADE ON UPDATE CASCADE) (SQL: INSERT INTO binaries (binaryhash, name, collections_id, totalparts, currentparts, filenumber, partsize) VALUES (UNHEX('a50a64adff094a23f7753884403c2587'), '34ddc25ff4314144b9207ef3ac29d257 [15/37] \"34ddc25ff4314144b9207ef3ac29d257.part14.rar\" yEnc', 835056, 68, 1, 15, 792755) ON DUPLICATE KEY UPDATE currentparts = currentparts + 1, partsize = partsize + 792755)

Searching table 'collections' id for collections_id = 835056 it returns empty.

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0027 seconds.)

To Reproduce

Unknown

Expected behavior

Screenshots

Installation (please complete the following information):

• OS: CentOS 6.10
• PHP v7.2.13
• NNTmux version or commit [e.g. 1.0.17.2]

Additional context

SHOW CREATE TABLE binaries;

binaries | CREATE TABLE binaries ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, name varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', collections_id int(10) unsigned NOT NULL DEFAULT 0, filenumber int(10) unsigned NOT NULL DEFAULT 0, totalparts int(10) unsigned NOT NULL DEFAULT 0, currentparts int(10) unsigned NOT NULL DEFAULT 0, partcheck tinyint(1) NOT NULL DEFAULT 0, partsize bigint(20) unsigned NOT NULL DEFAULT 0, binaryhash binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', PRIMARY KEY (id), UNIQUE KEY ix_binaries_binaryhash (binaryhash), UNIQUE KEY ux_collection_id_filenumber (collections_id,filenumber), KEY ix_binaries_collection (collections_id), KEY ix_binaries_partcheck (partcheck), CONSTRAINT FK_Collections FOREIGN KEY (collections_id) REFERENCES collections (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1556027 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

SHOW CREATE TABLE collections;

collections | CREATE TABLE collections ( id int(10) unsigned NOT NULL AUTO_INCREMENT, subject varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', fromname varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', date datetime DEFAULT NULL, xref varchar(2000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', totalfiles int(10) unsigned NOT NULL DEFAULT 0, groups_id int(10) unsigned NOT NULL DEFAULT 0, collectionhash varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', collection_regexes_id int(11) NOT NULL DEFAULT 0 COMMENT 'FK to collection_regexes.id', dateadded datetime DEFAULT NULL, added timestamp NOT NULL DEFAULT current_timestamp(), filecheck tinyint(1) NOT NULL DEFAULT 0, filesize bigint(20) unsigned NOT NULL DEFAULT 0, releases_id int(11) DEFAULT NULL, noise char(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE KEY ix_collection_collectionhash (collectionhash), KEY fromname (fromname), KEY date (date), KEY groups_id (groups_id), KEY ix_collection_dateadded (dateadded), KEY ix_collection_filecheck (filecheck), KEY ix_collection_releaseid (releases_id) ) ENGINE=InnoDB AUTO_INCREMENT=855257 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

[Sweepr]

I have now "enabled" sequential , for some reason sequential run's smoother, at least i'm not getting any error's in my log, see how this goes.

[DariusIII]

Is debug enabled on your server?

[Sweepr]

Yes it is.

[DariusIII]

Those messages will be gone when debug is disabled.

[Sweepr]

Running sequential seems better, no more complains about foreign key contraints, i also saw alot of deadlocking with sequential disabled.

[Sweepr]

Correct me if i'm mistaken. but doesn't disabling debug only hide the error's?

[DariusIII]

In this case, errors are set to be logged if debug is enabled, so production does not get cluttered log files. Most serious errors are logged even if debug is disabled. Debug should be enabled only when hunting errors, not on live server because sensitive information is being shown to everyone.

[Sweepr]

I understand, the site is not open publicly so i'm using debug to get everything running to find the best settings before going live.

I just notice there's a big difference between running with Sequential Enabled or Disabled, i'm not seeing those error's anymore running Sequential = Enabled

[DariusIII]

That is because sequential does one thing at a time, while threaded does multiple, so there is always a chance of having issues on db level.

[Sweepr]

Think that's the issue in my case, closing this one.