Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535 at (follow up) #2284

Closed
3 tasks done
myxor opened this issue Jul 11, 2023 · 5 comments
Closed
3 tasks done

SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535 at (follow up) #2284

myxor opened this issue Jul 11, 2023 · 5 comments
Labels
bug

Comments

@myxor
Copy link

myxor commented Jul 11, 2023

Follow up issue from #1858

IMPORTANT

Read and tick the following checkbox after you have created the issue or place an x inside the brackets ;)

  • I have read the CONTRIBUTING.md and followed the provided tips
  • I accept that the issue will be closed without comment if I do not check here
  • I accept that the issue will be closed without comment if I do not fill out all items in the issue template.

Explain the Problem

What problem did you encounter?

News does not load any new items. The cause seems to be deeper in the readAll mechanism.

Steps to Reproduce

Explain what you did to encounter the issue

  1. I have a lot of feeds and a few (~50 right now) unread items
  2. Clicking on "unread items" and then "mark all as read" triggers the above exception
  3. The items are still unread

Sorry that i can not describe the steps with any more details but that's what is happening.

System Information

  • News app version: 21.2.0
  • Nextcloud version: 24.0.5.1
  • Cron type: system cron
  • PHP version: 8.0.21
  • Database and version: Postgresql 12.15
  • Browser and version: Firefox 105
  • OS and version: Ubuntu LTS
Contents of nextcloud/data/nextcloud.log 
{
	"reqId": "HT2AfDfntitknS8ZtFZP",
	"level": 3,
	"time": "2022-09-20T08:30:32+00:00",
	"remoteAddr": "x.x.x.x",
	"user": "username",
	"app": "index",
	"method": "POST",
	"url": "/apps/news/items/read",
	"message": "An exception occurred while executing a query: SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535",
	"userAgent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:105.0) Gecko/20100101 Firefox/105.0",
	"version": "24.0.5.1",
	"exception": {
		"Exception": "OC\\DB\\Exceptions\\DbalException",
		"Message": "An exception occurred while executing a query: SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535",
		"Code": 7,
		"Trace": [{
			"file": "/var/www/html/lib/private/DB/ConnectionAdapter.php",
			"line": 85,
			"function": "wrap",
			"class": "OC\\DB\\Exceptions\\DbalException",
			"type": "::",
			"args": [{
				"__class__": "Doctrine\\DBAL\\Exception\\DriverException"
			}]
		}, {
			"file": "/var/www/html/custom_apps/news/lib/Db/ItemMapperV2.php",
			"line": 297,
			"function": "executeStatement",
			"class": "OC\\DB\\ConnectionAdapter",
			"type": "->",
			"args": ["UPDATE `*PREFIX*news_items` SET `unread` = :unread WHERE (id IN (:idList)) AND (unread != :unread)", [false, [54998, 37793, 43476, 46174, 45933, "And 69573 more entries, set log level to debug to see all entries"]],
				[5, 101]
			]
		}, {
			"file": "/var/www/html/custom_apps/news/lib/Service/ItemServiceV2.php",
			"line": 224,
			"function": "readAll",
			"class": "OCA\\News\\Db\\ItemMapperV2",
			"type": "->",
			"args": ["username", 104218]
		}, {
			"file": "/var/www/html/custom_apps/news/lib/Controller/ItemController.php",
			"line": 308,
			"function": "readAll",
			"class": "OCA\\News\\Service\\ItemServiceV2",
			"type": "->",
			"args": ["username", 104218]
		}, {
			"file": "/var/www/html/lib/private/AppFramework/Http/Dispatcher.php",
			"line": 225,
			"function": "readAll",
			"class": "OCA\\News\\Controller\\ItemController",
			"type": "->",
			"args": [104218]
		}, {
			"file": "/var/www/html/lib/private/AppFramework/Http/Dispatcher.php",
			"line": 133,
			"function": "executeController",
			"class": "OC\\AppFramework\\Http\\Dispatcher",
			"type": "->",
			"args": [{
				"__class__": "OCA\\News\\Controller\\ItemController"
			}, "readAll"]
		}, {
			"file": "/var/www/html/lib/private/AppFramework/App.php",
			"line": 172,
			"function": "dispatch",
			"class": "OC\\AppFramework\\Http\\Dispatcher",
			"type": "->",
			"args": [{
				"__class__": "OCA\\News\\Controller\\ItemController"
			}, "readAll"]
		}, {
			"file": "/var/www/html/lib/private/Route/Router.php",
			"line": 298,
			"function": "main",
			"class": "OC\\AppFramework\\App",
			"type": "::",
			"args": ["OCA\\News\\Controller\\ItemController", "readAll", {
					"__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer"
				},
				["news.item.readAll"]
			]
		}, {
			"file": "/var/www/html/lib/base.php",
			"line": 1030,
			"function": "match",
			"class": "OC\\Route\\Router",
			"type": "->",
			"args": ["/apps/news/items/read"]
		}, {
			"file": "/var/www/html/index.php",
			"line": 36,
			"function": "handleRequest",
			"class": "OC",
			"type": "::",
			"args": []
		}],
		"File": "/var/www/html/lib/private/DB/Exceptions/DbalException.php",
		"Line": 71,
		"Previous": {
			"Exception": "Doctrine\\DBAL\\Exception\\DriverException",
			"Message": "An exception occurred while executing a query: SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535",
			"Code": 7,
			"Trace": [{
					"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
					"line": 1780,
					"function": "convert",
					"class": "Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter",
					"type": "->",
					"args": [{
						"__class__": "Doctrine\\DBAL\\Driver\\PDO\\Exception"
					}, {
						"__class__": "Doctrine\\DBAL\\Query"
					}]
				}, {
					"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
					"line": 1719,
					"function": "handleDriverException",
					"class": "Doctrine\\DBAL\\Connection",
					"type": "->",
					"args": [{
						"__class__": "Doctrine\\DBAL\\Driver\\PDO\\Exception"
					}, {
						"__class__": "Doctrine\\DBAL\\Query"
					}]
				}, {
					"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
					"line": 1173,
					"function": "convertExceptionDuringQuery",
					"class": "Doctrine\\DBAL\\Connection",
					"type": "->",
					"args": [{
							"__class__": "Doctrine\\DBAL\\Driver\\PDO\\Exception"
						}, "UPDATE \"oc_news_items\" SET \"unread\" = ? WHERE (id IN (?, ?, ?, ?,
						...lots of question marks...
						?
						, ? , ? , ? , ? , ? )) AND(unread != ? )
				",[false,54998,37793,43476,46174,"
				And 69575 more entries, set log level to debug to see all entries "],[5,1,1,1,1,"
				And 69575 more entries, set log level to debug to see all entries "]]},{"
				file ":" /
				var / www / html / lib / private / DB / ConnectionAdapter.php ","
				line ":83,"
				function ":"
				executeStatement ","
				class ":"
				OC\\ DB\\ Connection ","
				type ":" - > ","
				args ":["
				UPDATE\ "oc_news_items\" SET \"unread\" = :unread WHERE (id IN (:idList)) AND (unread != :unread)", [false, [54998, 37793, 43476, 46174, 45933, "And 69573 more entries, set log level to debug to see all entries"]],
				[5, 101]
			]
		},
		{
			"file": "/var/www/html/custom_apps/news/lib/Db/ItemMapperV2.php",
			"line": 297,
			"function": "executeStatement",
			"class": "OC\\DB\\ConnectionAdapter",
			"type": "->",
			"args": ["UPDATE `*PREFIX*news_items` SET `unread` = :unread WHERE (id IN (:idList)) AND (unread != :unread)", [false, [54998, 37793, 43476, 46174, 45933, "And 69573 more entries, set log level to debug to see all entries"]],
				[5, 101]
			]
		},
		{
			"file": "/var/www/html/custom_apps/news/lib/Service/ItemServiceV2.php",
			"line": 224,
			"function": "readAll",
			"class": "OCA\\News\\Db\\ItemMapperV2",
			"type": "->",
			"args": ["username", 104218]
		},
		{
			"file": "/var/www/html/custom_apps/news/lib/Controller/ItemController.php",
			"line": 308,
			"function": "readAll",
			"class": "OCA\\News\\Service\\ItemServiceV2",
			"type": "->",
			"args": ["username", 104218]
		},
		{
			"file": "/var/www/html/lib/private/AppFramework/Http/Dispatcher.php",
			"line": 225,
			"function": "readAll",
			"class": "OCA\\News\\Controller\\ItemController",
			"type": "->",
			"args": [104218]
		},
		{
			"file": "/var/www/html/lib/private/AppFramework/Http/Dispatcher.php",
			"line": 133,
			"function": "executeController",
			"class": "OC\\AppFramework\\Http\\Dispatcher",
			"type": "->",
			"args": [{
				"__class__": "OCA\\News\\Controller\\ItemController"
			}, "readAll"]
		},
		{
			"file": "/var/www/html/lib/private/AppFramework/App.php",
			"line": 172,
			"function": "dispatch",
			"class": "OC\\AppFramework\\Http\\Dispatcher",
			"type": "->",
			"args": [{
				"__class__": "OCA\\News\\Controller\\ItemController"
			}, "readAll"]
		},
		{
			"file": "/var/www/html/lib/private/Route/Router.php",
			"line": 298,
			"function": "main",
			"class": "OC\\AppFramework\\App",
			"type": "::",
			"args": ["OCA\\News\\Controller\\ItemController", "readAll", {
					"__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer"
				},
				["news.item.readAll"]
			]
		},
		{
			"file": "/var/www/html/lib/base.php",
			"line": 1030,
			"function": "match",
			"class": "OC\\Route\\Router",
			"type": "->",
			"args": ["/apps/news/items/read"]
		},
		{
			"file": "/var/www/html/index.php",
			"line": 36,
			"function": "handleRequest",
			"class": "OC",
			"type": "::",
			"args": []
		}],
	"File": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
	"Line": 92
}
}
}, "CustomMessage": "--"
}
}
@myxor myxor added the bug label Jul 11, 2023
@myxor
Copy link
Author

myxor commented Jul 11, 2023

Maybe this helps finding the problem:

nextcloud=> SELECT COUNT(*) FROM oc_news_items;
 count 
-------
 95022
(1 row)

nextcloud=> SELECT COUNT(*) FROM oc_news_items WHERE unread = 'f';
 count 
-------
 95022
(1 row)

@Grotax
Copy link
Member

Grotax commented Jul 11, 2023

Are you the only user of news on that server?

@Grotax
Copy link
Member

Grotax commented Jul 11, 2023

Maybe this helps finding the problem:

nextcloud=> SELECT COUNT(*) FROM oc_news_items;
 count 
-------
 95022
(1 row)

nextcloud=> SELECT COUNT(*) FROM oc_news_items WHERE unread = 'f';
 count 
-------
 95022
(1 row)

According to this your total amount of items is 95022.
And you have also 95022 read items, therefore no unread items.

Unread items would be:

SELECT COUNT(*) FROM oc_news_items WHERE unread = 'true';

@Grotax
Copy link
Member

Grotax commented Jul 11, 2023

News does not load any new items. The cause seems to be deeper in the readAll mechanism.

This sounds strange for me. "no new items" but you complain about the mark all as read function maybe based on the log entry?

But talking about the log.
"time": "2022-09-20T08:30:32+00:00",

That is long ago, you don't have anything recent?

If you don't get new items that is probably due to other issues. If you happen to have reddit feeds for example, #2236

@Grotax
Copy link
Member

Grotax commented Aug 9, 2023

Closing this as the data is outdated, by now also the versions are outdated.

If you are still facing issues feel free to open a new issue with recent information and also check out the hints I provided.
We cannot reproduce your issue.

@Grotax Grotax closed this as completed Aug 9, 2023
@anoymouserver anoymouserver mentioned this issue Jan 20, 2024
Closed
3 tasks
@IgorA100 IgorA100 mentioned this issue Jan 20, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@myxor @Grotax