frappe.exceptions.ValidationError: Too many writes in one request. Please send smaller requests

RWEMA_Aimable · September 16, 2019, 12:57pm

Bench update give this error:

Any solution around that error?
These tables have grown to big: tabVersion, tabDeleted Document, tabCommunication (2Million records) and tabEmail Queue.
Can I safely empty those table maybe to reduce the size of the DB if that’s the reason
If all the above fails, is there a way to reverse back to the state before bench update? So far the site is stuck at System Updating

Many Thanks

RWEMA_Aimable · September 16, 2019, 2:52pm

Digging into source code I found this:

github.com

frappe/frappe/blob/a72a9e1946311d39c6bab9a8c3b90813a704773b/frappe/database/database.py#L260


      
          
          
		if query and query.strip().lower() in ('commit', 'rollback'):
          			self.transaction_writes = 0
          
          
		if query[:6].lower() in ('update', 'insert', 'delete'):
          			self.transaction_writes += 1
          			if self.transaction_writes > 200000:
          				if self.auto_commit_on_many_writes:
          					self.commit()
          				else:
          					frappe.throw(_("Too many writes in one request. Please send smaller requests"), frappe.ValidationError)
          
          
	def fetch_as_dict(self, formatted=0, as_utf8=0):
          		"""Internal. Converts results to dict."""
          		result = self._cursor.fetchall()
          		ret = []
          		if result:
          			keys = [column[0] for column in self._cursor.description]
          
          
		for r in result:
          			values = []

And setting this self.auto_commit_on_many_writes = True can potentially solve the problem.

Next tasks is 1) to know how to initialize that variable globally or 2) Do witch patch/file I need to put that line into

Any lead will be appreciated

RWEMA_Aimable · September 16, 2019, 8:52pm

I added the code into the file itself and then bench update --patch did it

wale · June 3, 2020, 11:19pm

There’s actually already a line in the file like this: self.auto_commit_on_many_writes = 0

All you need to do is change the 0 to 1

Cheers

aakvatech · June 18, 2021, 3:03pm

We are expecting to fix this by sets of PR to stock related transcations and optimizing them to reduce the number of pending commits or merging multiple commis into a single commit.

We will sponsor the PR to optimize teh stock transaction that requires 1,000’s to 10,000’s of parts in Delivery Note, Purchase Receipt, Landed Cost Vouchers.

Having more than 1,000 items in stock transactions is pretty common in our part of the world where businesses don’t focus on what they want to sell, but want to sell anything and everything.

Please comment or like this post so that we know who else is interested or affected by this problem.

aakvatech · June 18, 2021, 3:15pm

github.com/frappe/erpnext

LCV: Too many writes in one request. Please send smaller requests

opened 02:57PM - 18 Jun 21 UTC

aakvatech

bug

## Description of the issue The error "Too many writes in one request. Please s…end smaller requests" comes in long running transactions that are not explicitely allowed to commit mid way. It does make sense to have explicity definition but the problem created by very large transactions ## Context information (for bug reports) ERPNExt Landed Cost Voucher with 1,000's of parts Submitting such LCVs on front end fails with Timeout. On enqueue of such transactions they fail with "Too many writes in one request. Please send smaller requests" **Output of `bench version`** ``` erpnext 13.5.0 frappe 13.5.0 ``` ## Steps to reproduce the issue 1. Create an LCV in ERPNext with 1,000's of transactions 2. Submit using enqueue as front end submit will definitely timeout 3. Wait for the error to show up on the comments ### Observed result Error comes in the comments indicating ![image](https://user-images.githubusercontent.com/35020381/122580002-2cad7300-d05e-11eb-80f0-429b5d5b8b99.png) ### Expected result No comments should show, but it should be automatically committed. ### Stacktrace / full error message ``` No stack trace, only error on Commit ``` ## Additional information It affects all stock transactions, even Purchase Receipts with large number of records in single receipts fail. Only way to bypass is to change frappe code at line https://github.com/frappe/frappe/blob/7c5624dd56a4ce4e22b547df6e5de2eef7a568f9/frappe/database/database.py#L242 ``` if self.transaction_writes > 200000: ``` should be allowed 10 fold to allow muliple writes for such large data set. Or else do not allow large data set in a single Document (child table) ```if self.transaction_writes > 2000000: ``` OS version / distribution, `Frappe` install method, etc. FrappeCloud version 13.5.0

Tufan_Kaynak2 · January 25, 2022, 8:48am

Why don’t we put it as a global Frappe parameter in the interface for administrators to be set according to the platform size?