nodejs query returns at most 25000 rows in mysql2 component

Question

I'm using mysql2 component in typescript My problem is that the query returns a maximum of 25000 rows, even when it should be returning many more

This is my code:

import { type Pool } from 'mysql2/promise'

export interface RecordLayout {
  success: boolean
  message: string
  code: number
}


  public static async testSql (dbName: string, sql: string, xlsxFileName: string = '.xlsx'): Promise<void> {
    logger.debug(sql)
    const db = await (p.pools[dbName] as Pool).getConnection()
    const [rows] = await db.query(sql, []) //! returns only 25.000 rows
    // i try to add another record into array, to verify that it's not a node js problem
    const r: RecordLayout[] = rows as RecordLayout[]
    const t = r.concat({ message: 'ffffffffffff', success: true, code: 0 })
    console.log(t) // ok 25.001 row :-(
    db.release()
    logger.debug(rows)
  }

But given the precise number 25000, I suspect there is a limitation in some configuration or other

Can you help me ?

Check with `SELECT COUNT(*)` to get a count first using the same `WHERE` conditions. — tadman, May 03 '23 at 15:20
I wasted half a day looking for the bug There were 42,000 records this morning Actually doing a count now, there are only 25,000 The problem is to be found elsewhere I'm glad it's not a mysql2 bug Thank you ! — Janka, May 03 '23 at 20:58

score 0 · Answer 1 · answered Aug 26 '23 at 15:38

I think you should split data into multiple queries.

First you use one query to get the count of rows

SELECT COUNT(*) FROM table

After for each range of primary key you get the query and do something on it. For example:

const count: number = /* SELECT COUNT(*) AS cnt FROM table */;
const queryLimit: number = 10000; // For example

var currentPage: number = 0;

while (currentPage * queryLimit < count) {
    let query = /* SELECT * FROM table ORDER BY primary_key LIMIT currentPage*queryLimit, queryLimit */;

    query.forEach(row => {
        doSomething(row);
    }

    currentPage += 1;
}

Using this method you split the table into N queries that have a limited length. And you reduce the amount of data that the database has to process. The LIMIT function take in first place the start index and in second place the number of elements you want.

Attention! You must use ORDER BY, because if you don't use it a row can be repeated in different query.

Instead if you want to have an array of all rows you can do the following thing:

const count: number = /* SELECT COUNT(*) AS cnt FROM table */;
const queryLimit: number = 10000; // For example

var data: Array<any> = [];

var currentPage: number = 0;

while (currentPage * queryLimit < count) {
    let query = /* SELECT * FROM table ORDER BY primary_key LIMIT currentPage*queryLimit, queryLimit */;

    data = data.concat(query);

    currentPage += 1;
}

nodejs query returns at most 25000 rows in mysql2 component

1 Answers1