More than 1000 expressions in list are not allowed on Oracle

[priyankub]

Hey, Thanks for reporting issues back to Nextcloud Face Recognition. Please, try to complete this report in detail so we can help you easier. 😄

Make sure you read all the documentation, and the FAQ, and that the issue has not been reported before. 😉

Expected behaviour

No error

Actual behaviour

Error logged saying more than 1000 expressions not allowed

Steps to reproduce

1. Index
2. Review faces found

Server configuration

• Operating system:
  Unraid. AIO install

• Pdlib version:

• How is DLib installed: Make sure it is working correctly with this tool
  Container

• How is PDlib installed: Make sure it is working correctly with this tool

• PHP version:

• Web server:

• Database: Postgres

• Nextcloud version: 30

Client configuration

• Browser:

• Operating system:

Logs

Background task log with debug.

sudo -u apache php occ -vvv face:background_job

Insert your background log here

Web server error log

Web server error log

Insert your webserver log here

Nextcloud log (data/nextcloud.log)

Nextcloud log

{"reqId":"shuHeyq4vOk4XusXdFeV","level":3,"time":"2024-11-21T13:00:18+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":"More than 1000 expressions in a list are not allowed on Oracle.","userAgent":"--","version":"30.0.2.2","exception":{"Exception":"Doctrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code":0,"Trace":[{"file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","line":284,"function":"prepareForExecute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/custom_apps/facerecognition/lib/Db/FaceMapper.php","line":61,"function":"executeQuery","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/custom_apps/facerecognition/lib/BackgroundJob/Tasks/CreateClustersTask.php","line":182,"function":"findDescriptorsBathed","class":"OCA\\FaceRecognition\\Db\\FaceMapper","type":"->","args":[[10983,10984,10985,10986,10987,"And 5420 more entries, set log level to debug to see all entries"]]},{"file":"/var/www/html/custom_apps/facerecognition/lib/BackgroundJob/Tasks/CreateClustersTask.php","line":90,"function":"createClusterIfNeeded","class":"OCA\\FaceRecognition\\BackgroundJob\\Tasks\\CreateClustersTask","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/custom_apps/facerecognition/lib/BackgroundJob/BackgroundService.php","line":150,"function":"execute","class":"OCA\\FaceRecognition\\BackgroundJob\\Tasks\\CreateClustersTask","type":"->","args":[{"__class__":"OCA\\FaceRecognition\\BackgroundJob\\FaceRecognitionContext","userManager":{"__class__":"OC\\User\\Manager"},"config":{"__class__":"OC\\AllConfig"},"logger":{"__class__":"OCA\\FaceRecognition\\BackgroundJob\\FaceRecognitionLogger"},"user":null,"verbose":false,"propertyBag":{"max_image_area":null,"run_mode":"default-mode","StaleImagesRemovalTask_staleRemovedImages":0}}]},{"file":"/var/www/html/custom_apps/facerecognition/lib/Command/BackgroundCommand.php","line":192,"function":"execute","class":"OCA\\FaceRecognition\\BackgroundJob\\BackgroundService","type":"->","args":[0,false,null,null,"default-mode"]},{"file":"/var/www/html/3rdparty/symfony/console/Command/Command.php","line":326,"function":"execute","class":"OCA\\FaceRecognition\\Command\\BackgroundCommand","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"/var/www/html/3rdparty/symfony/console/Application.php","line":1078,"function":"run","class":"Symfony\\Component\\Console\\Command\\Command","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"/var/www/html/3rdparty/symfony/console/Application.php","line":324,"function":"doRunCommand","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"OCA\\FaceRecognition\\Command\\BackgroundCommand"},{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"/var/www/html/3rdparty/symfony/console/Application.php","line":175,"function":"doRun","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"/var/www/html/lib/private/Console/Application.php","line":183,"function":"run","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"/var/www/html/console.php","line":87,"function":"run","class":"OC\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"}]},{"file":"/var/www/html/occ","line":11,"args":["/var/www/html/console.php"],"function":"require_once"}],"File":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","Line":237,"message":"More than 1000 expressions in a list are not allowed on Oracle.","query":"SELECT `id`, `descriptor` FROM `*PREFIX*facerecog_faces` `f` WHERE `id` IN (:face_ids)","exception":[],"CustomMessage":"More than 1000 expressions in a list are not allowed on Oracle."},"id":"673f320fc125b"}

Browser log

Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

[priyankub]

I guess it wasn't designed to handle > 1000 faces for an user? 😄

[ralfbgithub]

Same here:
After update to Face Recognition Version 0.9.60 this error occurs "only" 5 times in the nextcloud.log, cron runs face:background_job every 20 minutes.

QueryException: More than 1000 expressions in a list are not allowed on Oracle.

"File": "/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
"Line": 263,
"message": "More than 1000 expressions in a list are not allowed on Oracle.",
"query": "SELECT id, descriptor FROM *PREFIX*facerecog_faces f WHERE id IN (:face_ids)",
"exception": [],
"CustomMessage": "More than 1000 expressions in a list are not allowed on Oracle."

Server configuration
Operating system: Raspberry Pi 4, 8GB as host for docker
PHP version: 8.2.23
Web server: Nginx:1.27.0
Database: MariaDB 10.6.18
Nextcloud 29.0.7

[bin101]

This issue must have been here for some time.

Batches contain a minimum of 2000 faces if the hidden setting is not changed.

Confusingly, I couldn't find any code to disable batches if there are more than 25k faces, as the docs say.

Also, I'm not using Oracle as the DB, but postgresql, and still get the same message as above. afaik postgresql has no limit of values for the IN clause, which Oracle have
Edit: Nextcloud Server has some rules which will effect every DB as far as i understand.

For now you could set batch size to -1 but this would disable batches altogether.
Edit: nvm DEFAULT_CLUSTERING_BATCH_SIZE is already -1. Really don't know why this is happening...

[priyankub]

php occ config:app:get facerecognition clustering_batch_size
didn't return any value. So, I ran:
php occ config:app:set facerecognition clustering_batch_size --value='950' --type=integer

I will restart indexing now. Let's see how it goes.

[priyankub]

Did not work... Rewrote this function in lib/Db/FaceMapper.php:

	public function findDescriptorsBathed(array $faceIds): array {
		$placeholders = implode(',', array_fill(0, count($faceIds), '?'));
		$sql = "
			SELECT id, descriptor 
			FROM oc_facerecog_faces
			WHERE id IN (
				SELECT id::integer 
				FROM (VALUES ($placeholders)) AS t (id)
			)
		";
	
		$descriptors = [];
		$result = $this->db->executeQuery($sql, $faceIds);
		while ($row = $result->fetch()) { // Use fetch() instead of fetchAssociative()
			$descriptors[] = [
				'id' => $row['id'],
				'descriptor' => json_decode($row['descriptor'])
			];
		}
		$result->closeCursor();
	
		return $descriptors;
	}

Edit: Edited to the version that works after testing and debugging. I have Postgres - might not work for mariadb or sqlite. Will update again if needed

[vwbusguy]

I'm seeing this as well (on Mariadb 10.11) and found that NextCloud upstream fixed this by pagination:

https://github.com/nextcloud/server/blob/master/lib/private/Comments/Manager.php#L673

Doing it this way should work across DB platforms since it's using the Symfony backend.

[priyankub]

I'm seeing this as well (on Mariadb 10.11) and found that NextCloud upstream fixed this by pagination:

https://github.com/nextcloud/server/blob/master/lib/private/Comments/Manager.php#L673

Doing it this way should work across DB platforms since it's using the Symfony backend.

That was my initial solution as well but I thought it might reduce performance so went with the current approach instead

[priyankub]

So far so good:

I currently have the fix mentioned above, and the one here in #780 on top of the build shared here

[bin101]

I'm seeing this as well (on Mariadb 10.11) and found that NextCloud upstream fixed this by pagination:

https://github.com/nextcloud/server/blob/master/lib/private/Comments/Manager.php#L673

Doing it this way should work across DB platforms since it's using the Symfony backend.

I switched over to this chunk approach:

public function findDescriptorsBathed (array $faceIds): array {
	$qb = $this->db->getQueryBuilder();
	$qb->select('id', 'descriptor')
		->from($this->getTableName(), 'f')
		->where($qb->expr()->in('id', $qb->createParameter('face_ids')));

	$descriptors = array_fill(0, sizeof($faceIds), 0);
	$arrayindex = 0;
	foreach (array_chunk($faceIds, 1000) as $chunk) {
		$qb->setParameter('face_ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY);

		$result = $qb->executeQuery();
		while ($row = $result->fetch()) {
			$descriptors[$arrayindex] = [
				'id' => $row['id'],
				'descriptor' => json_decode($row['descriptor'])
			];
			$arrayindex++;
		}
		$result->closeCursor();
	}
	
	return $descriptors;
}

[priyankub]

After trying a few fancy stuff for better efficiency, I ended up implementing something similar to what you did

[vwbusguy]

I switched over to this chunk approach:

This seems to have worked for me as well (on MariaDB) - Thanks! I just finished a cron run without it blowing up journald.

[Rudd-O]

Can this please be fixed with the code change as a pull request? I'm getting the exact same issue (with MariaDB). Thanks.

[Rudd-O]

Does anyone need funding to deliver these fixes or restart this project? I can furnish some.