Same query returns different number of rows

Hello!

Queries do not return the same number of rows when:

  • New > SQL Query
  • Download CSV

Metabase Details

Metabase Cache is off.

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.109 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.14.1+1",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.14.1",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.14.1+1",
    "os.name": "Linux",
    "os.version": "5.4.0-1041-aws",
    "user.language": "en",
    "user.timezone": "UTC"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "googleanalytics"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MySQL",
        "version": "8.0.23"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.6.2"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-02-28",
      "tag": "v0.42.2",
      "branch": "release-x.42.x",
      "hash": "d6ff494"
    },
    "settings": {
      "report-timezone": "UTC"
    }
  }
}

We run this on a simple table that we have called products

  • it has 220,000 rows

1st run - 1,544 rows

52%20PM

1st run - MySQL General Logs

61 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
61 Query SET @@session.time_zone = 'UTC'
61 Query set autocommit=0
61 Query	set @@SQL_SELECT_LIMIT=2000
61 Query	-- Metabase:: userID: 1 queryType: native queryHash: <random string>
select * from products

2nd run - 1,459 rows

02%20PM

2nd run - MySQL General Logs

61 Query	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
61 Query SET @@session.time_zone = 'UTC'
61 Query set autocommit=0
61 Query	set @@SQL_SELECT_LIMIT=2000
61 Query	-- Metabase:: userID: 1 queryType: native queryHash: <random string>
select * from products

3rd run - Adding limit 3000 returns 1,621 rows.

47%20PM

However, selecting the columns gives us the max row limit - 2000 rows

15%20PM

Specific Column run - MySQL General Logs

61 Query	-- Metabase:: userID: 1 queryType: native queryHash: <random string>
select name from products

Trying the same queries on Metabase version 0.31.2

  • cache is off

We get 10,000 rows on each query call.

30%20PM

Any advice is appreciated!

Hi @nerdherd

So it looks similar to Query result issue

Can you try with DBeaver and the MariaDB driver 2.6.2, and then try with 2.7.5?
Metabase currently uses 2.6.2, but we're looking at upgrading to 2.7.5

A lot has changed since Metabase 0.31.2 - basically everything is different compared to that version.

Hey @flamber!

Thanks for providing some guidance back in March.

I managed to fix this today.

For anyone who happens to land here:

Context

My infra was setup like this:
Client -> Haproxy -> EC2 Instance running Metabase Docker

TL:DR

Issue: Haproxy (proxy/load balancer) setting

I had the following setting in Haproxy:

frontend web-http
  bind *:80
  # ...
  option http-server-close

Offending line was option http-server-close

Changed it to option http-keep-alive and managed to have correct number of rows returned for my queries.

Longer Story
From reading metabase logs, I saw that I was getting ClosedChannelException

This indicated to me that it's a network issue.

After coming across the following links:

I figured it was a network issue, and not a metabase issue.

  1. Check the metabase webserver (jetty) error logs
  2. Cross reference the errors reported and how you setup your infra

If you face the same issue, it could be a network setting in your infra because metabase uses a buffer to send the data to your client.

1 Like