sql.Query truncated or incomplete results

Question

I've the following code:

const qInstances = `
SELECT
    i.uuid,
    i.host,
    i.hostname
FROM
    db.instances AS i
WHERE
    i.deleted_at IS NULL
GROUP BY i.uuid;
`
...
    instancesMap := make(map[string]*models.InstanceModel)
    instances := []models.Instance{}

    instancesCount := 0

    instancesRow, err := db.Query(qInstances)

    if err != nil {
        panic(err.Error())
    }
    defer instancesRow.Close()

    for instancesRow.Next() {
        i := models.Instance{}
        err = instancesRow.Scan(&i.UUID, &i.Host, &i.Hostname)
        if err != nil {
            log.Printf("[Error] - While Scanning Instances Rows, error msg: %s\n", err)
            panic(err.Error())
        } else {
            if i.UUID.String != "" {
                instancesCount++
            }

            if _, ok := instancesMap[i.UUID.String]; !ok {
                instancesMap[i.UUID.String] = &models.InstanceModel{}
                inst := instancesMap[i.UUID.String]
                inst.UUID = i.UUID.String
                inst.Host = i.Host.String
                inst.Hostname = i.Hostname.String
            } else {
                inst := instancesMap[i.UUID.String]
                inst.UUID = i.UUID.String
                inst.Host = i.Host.String
                inst.Hostname = i.Hostname.String
            }
            instances = append(instances, i)
        }
    }
    log.Printf("[Instances] - Total Count: %d\n", instancesCount)

The problem that I'm facing is that if run the SQL query directly to the database (mariadb) it returns 7150 records, but the total count inside the program outputs 5196 records. I also check the SetConnMaxLifetime parameter for the db connection and set it to 240 seconds, and it doesn't show any errors or broken connectivity between the db and the program. Also I try to do some pagination (LIMIT to 5000 records each) and issue two different queries and the first one returns the 5000 records, but the second one just 196 records. I'm using the "github.com/go-sql-driver/mysql" package. Any ideas?

Can you try finding those rows which are returned when running the query directly against the DB which are missing from the result set in your program? This might give a hint, though I cannot claim to have a specific suspicion here. — Zyl, Aug 05 '21 at 20:01
When using `db.Query` you should check [`Rows.Err`](https://pkg.go.dev/database/sql#Rows.Err) when done in case an error occurred while retrieving rows (see [the example](https://pkg.go.dev/database/sql#example-DB.Query-MultipleResultSets)); its possible that you are missing an error (I'm assuming you are taking into account that the count will not include rows where `i.UUID.String` is blank). — Brits, Aug 05 '21 at 20:12
what if you issue a COUNT(i.uuid) via the program to the DB ? my first guess was that you did not connect on the same db instance. BTW, i fond this number very odd `5196` youknow 8 16 32 64... — , Aug 05 '21 at 20:50
@Brits yes, I'm assuming that and the 7150 records from the database are not blank on the uuid, so it should return all the records. Also I implement the Rows.Err that you suggest and it doesn't display any errors, so the records in someway are processed: ```if err := instancesRow.Err(); err != nil { log.Printf("[Error] - Getting Row records, err msg: %+v", err) log.Fatal(err) }``` — happygopher, Aug 05 '21 at 20:51
@happygopher : while not overly complicated, your code is not as straightforward as it could be to identify the issue. For example : you have a `UUID != ""` check inside your go code, you add lines to a mapping (with deduplication) instead of a list, so in the end, we don't have a straight counter for the number of lines for the query. Just looking at the code, I would say : there are elements with `UUID == ""`. To actually debug the issue : can you run a simpler loop ? something like [this](https://play.golang.org/p/b4S9uMYwgcA) — LeGEC, Sep 06 '21 at 07:03
another idea is to fetch all rows WITHOUT processing, then count them all. then compared the rows fetched with direct command to cli client. — dwi kristianto, Apr 09 '22 at 06:30

sql.Query truncated or incomplete results

0 Answers0