More than 1000 expressions in list are not allowed on Oracle #786

priyankub · 2024-11-21T13:20:23Z

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

Index
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

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

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

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

The text was updated successfully, but these errors were encountered:

priyankub · 2024-11-21T17:17:36Z

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

ralfbgithub · 2024-11-22T13:17:01Z

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 · 2024-11-22T14:11:27Z

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 · 2024-11-22T16:01:16Z

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 · 2024-11-22T16:20:49Z

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 · 2024-11-22T18:42:06Z

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 · 2024-11-22T18:53:46Z

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 · 2024-11-22T19:19:52Z

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 · 2024-11-23T18:17:24Z

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 · 2024-11-24T17:57:00Z

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

vwbusguy · 2024-11-26T22:16:40Z

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 · 2024-12-28T16:47:52Z

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 · 2024-12-28T16:49:46Z

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

sebaxakerhtc · 2025-02-13T08:23:16Z

Same here. Any progress?

elpraga · 2025-03-15T07:35:14Z

same here, I am not a coder, and I do not dare to edit the files.

Can we expect the fix to be released officially @matiasdelellis ? I have some 500 errors if this type in my log...

solves matiasdelellis#786

solves #786

matiasdelellis · 2025-04-22T14:06:25Z

Hi.. Sorry for the delay.

I opted for a much simpler approach, but I'm considering other changes. Please confirm if the latest version works.

priyankub · 2025-04-22T15:03:31Z

Hi.. Sorry for the delay.

I opted for a much simpler approach, but I'm considering other changes. Please confirm if the latest version works.

Thanks for looking into it. I did not see an update yet. Will test once the update is available

priyankub · 2025-04-22T15:05:13Z

I am still using NC30 though, because I have AIO. So I will likely have to wait for the latest release to be available

matiasdelellis · 2025-04-22T16:53:22Z

I am still using NC30 though, because I have AIO. So I will likely have to wait for the latest release to be available

OHh.. You're right.. I'm waiting for confirmation from nc31 and I'll send the same patch for nc30

tuxArg · 2025-04-22T20:16:52Z

Hi.. Sorry for the delay.

I opted for a much simpler approach, but I'm considering other changes. Please confirm if the latest version works.

The fix is 150acd3? Isn't it just for sqlite db?

jojo221119 · 2025-05-01T08:40:46Z

For me 150acd3 didn't fix the issue.

I'm using mariaDB. Since installation of the latest version the log entries are back again.

vwbusguy · 2025-05-01T19:29:10Z

I'm using mariaDB. Since installation of the latest version the log entries are back again.

I confirm this as well. Spamming journald hard here.

I had been using this hack before the recent update and it worked: #786 (comment) . After the update, it broke again. I then replaced that function with the one from that comment and didn't get that message again the next time the cron ran.

xkill · 2025-05-16T09:28:09Z

Same problem with latest version.

The patch proposed on #786 (comment) seems to fix the problem.

vwbusguy mentioned this issue Nov 22, 2024

More than 1000 expressions in a list are not allowed on Oracle nextcloud/tables#1061

Open

jojo221119 added a commit to jojo221119/facerecognition that referenced this issue Mar 21, 2025

Implement batch processing for findDescriptorBathed

0b34dc4

solves matiasdelellis#786

jojo221119 mentioned this issue Mar 21, 2025

Solve issue "More than 1000 expressions in list are not allowed on Oracle" #800

Closed

matiasdelellis pushed a commit that referenced this issue Apr 22, 2025

Implement batch processing for findDescriptorBathed

32dcd4b

solves #786

matiasdelellis added a commit that referenced this issue Apr 22, 2025

Fix clustering on oracle.. Issue #786

150acd3

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

More than 1000 expressions in list are not allowed on Oracle #786

More than 1000 expressions in list are not allowed on Oracle #786