Row Count Exceeded 100000 when creating a chart using query

Feature(s) impacted

Chart Creation

Observed behavior

I’m getting the following error:

target: whydonate-production.-.primary: vttablet: rpc error: code = Aborted desc = Row count exceeded 100000 (CallerID: pkal3jupyrr3r2fl5lr3)

Expected behavior

I am trying to create a chart where I sum up the data of two table’s columns.

This is the query that I wrote:

(SELECT SUM(tip_amount) FROM donation_order WHERE status = ‘paid’ AND EXTRACT(YEAR_MONTH FROM created_at) = EXTRACT(YEAR_MONTH FROM created_at)) AS ‘value’
FROM accounting_transaction
WHERE type IN (‘cost’, ‘commission’)
ORDER BY ‘key’;

Any idea how to solve this?

Failure Logs


  • Project name: …
  • Team name: …
  • Environment name: …
  • Agent (forest package) name & version: …
  • Database type: …
  • Recent changes made on your end if any: …

Hello @nielswhydonate,

We need a bit more context to help you. :pray:

  • Can you confirm that you are using the agent (forest package) forest-express-sequelize?
  • What database are you using? MySQL?
  • Are you trying to achieve a time-based chart? Can you explain your use case, cause your query doesn’t seem right?

Thanks in advance for your answers.

Kind regards,

Hi @morganperre ,

Thanks for your quick reply. Let me answer your questions:

  1. Yes, that’s correct.
  2. Indeed, MYSQL.
  3. Yes, it’s a time-based chart. I’m trying to do the following:
    Creat a SQL query that sums up 2 columns from two different tables.

Column 1: Sum up the tip_amount column from the donation_order table where the status is ‘paid’.
Column 2: Sum up the amount from the accounting_transaction table where the type is ‘cost’ or ‘commission’.

Please let me know if you need any other information from my side.

Thanks for this clear response.

So there is not any relations between accounting_transaction and donation_order?

So here is your first mistake, your looking twice at the same created_at you probably want to check that the donation_order.created_at is equal to accounting_transaction.created_at. So a more correct way to do this is:

EXTRACT(YEAR_MONTH FROM accounting_transaction.created_at) = EXTRACT(YEAR_MONTH FROM created_at)) AS 'value'

Then you don’t have any GROUP BY statement, which is probably required in your case if I get the idea.

I’m betting on this query at the end:

(SELECT SUM(tip_amount) FROM donation_order WHERE status = 'paid' AND EXTRACT(YEAR_MONTH FROM accounting_transaction.created_at) = EXTRACT(YEAR_MONTH FROM created_at)) AS 'value'
FROM accounting_transaction
WHERE type IN ('cost', 'commission')
GROUP BY 'key'
ORDER BY 'key';

Let me know if you still encounter the inital issue. :pray:

Kind regards,

Thanks for the help again @morganperre .

There is indeed no relationship between accounting_transaction and donation_order. The only thing I want to do is sumup the two columns and show them on a monthly basis in a graph :slight_smile:

I’m getting the following error when adding that query:

It seems there is an issue with the group by line. Any idea what is wrong?

You are missing another GROUP BY clause BUT…

I don’t think that you can even get some valid data in this case.

If you sum all tip_amount for a month then sum them on a condition WHERE type IN ('cost', 'commission') on another table accounting_transaction without any relationship you won’t get anything that makes much sense? You will probably sum multiple times the same tip_amount because there is no concrete link between checking accounting_transaction.type and the dotations. :confused:

That being said you probably need to use WITH statement to split your query into two parts to understand the structural issue of what you are trying to achieve.

WITH periods AS (
   SELECT SUM(tip_amount) AS "sum", EXTRACT(YEAR_MONTH FROM created_at) as "month"
   FROM donation_order
   WHERE status = 'paid'
   GROUP BY "month"
  periods.month AS key,
  periods.sum AS value
FROM accounting_transaction
INNER JOIN periods
      ON periods.month = EXTRACT(YEAR_MONTH FROM accounting_transaction.created_at)
WHERE type IN ('cost', 'commission')
GROUP BY periods.month, 

This thread also talks about joining 2 tables without relations.

You should probably try to create a proper query in MySQL Workbench
or this kind of tool and then copy it into Forest Admin once fully working. :slight_smile:

Kind regards,

Hi @morganperre,

It is saying I can only use SELECT statement, so no WITH.

Any other suggestions to make this work?

Tried to use UNION now, but still no luck.

Ok ok, my bad for the WITH statement.

You need something to create a jointure between the two tables if not:

Your method is based on the matching of the created_at date (but you used the YEAR_MONTH from the date so even if records don’t match type IN ('cost', 'commission') and status = 'paid' they will be sum at the end…).

This might be a better approach if and only if the dates are exactly the same…

  EXTRACT(YEAR_MONTH FROM DO.created_at) AS key,
  SUM(DO.tip_amount) AS value
FROM accounting_transaction AS AT
LEFT JOIN donation_order AS DO
   ON DO.created_at = AT.created_at 
WHERE AT.type IN ('cost', 'commission') AND DO.status = 'paid'
GROUP BY key, 

Not sure we can help you more than that at this point.

Kind regards,