Empty response on SQL-Server queries containing DATETIME

Metabase version: 0.33.4

Works for me, I'm also using 0.33.4:

Anything non-standard about your MS SQL install?
What happens if you click the play button?

MS-SQL looks fine, queries run easily on SSMS.
I've also tried to use another instance of Metabase on my PC and that worked fine.

About clicking the play button, there is no error or warning, the only thing I get is an empty response with status code of 200.
Server log:

2019-11-04T11:10:20+03:30 DEBUG metabase.middleware.log POST /api/dataset 200 [ASYNC: completed] 298.9 ms (9 DB calls) Jetty threads: 2/50 (5 idle, 0 queued) (147 total active threads) Queries in flight: 0

What happens if you start a new question, then enter the query again? I donā€™t know how youā€™ve got the play button displaying if the query hasnā€™t just been modified.

Any query containing a DATETIME column results in empty response. even when I use query builder.
query on entire table for example.

The play button is not the issue, the problem is that ā€œThe API endpoint is not working properlyā€.
Iā€™ve tested the API with Postman and the result was the same.

Are you sending Metabase a datetime(6) value? It doesnā€™t render these property and is a known issue; I will try to dig up the references. Try casting to string / datetime(3) and see how you go?

@balibaba I think youā€™re seeing this issue, which is probably what @chucklessmith was thinking about:
https://github.com/metabase/metabase/issues/10285
But all the reports has been about MySQL, so Iā€™m unsure if this is the exact same problem with SQL Server.

please consider that I canā€™t change the column data type in the database.
and my problem is not getting null value on the rows, the problem is that the whole response fails with no errors.
@chucklessmith casting to string works fine(as you can see in the first screenshot)

@balibaba
Which version of SQL Server?
And to reproduce this issue, you simply make a query like select getdate() ?
When you say that you tried from another Metabase instance and it worked - whatā€™s the difference between the two instances?

Which version of SQL Server?

14.0.1000.169

And to reproduce this issue, you simply make a query like select getdate() ?

Yes

When you say that you tried from another Metabase instance and it worked - whatā€™s the difference between the two instances?

The instance with problem: hosted on Kubernetes, with a MariaDB database
"another Metabase instance" : hosted on my own pc, os:windows 10, H2 database

@balibaba Okay, that seems like an interesting issue, but difficult to debug.
At least now, it seems like itā€™s not an issue with the SQL Server, but maybe the MariaDB.

Are there any special connection string parameters for the SQL Server (Admin > Database)?

Can you supply Diagnostic Info from both instances (Admin > Troubleshooting)?

Which version of MariaDB? Do you see any activity in the log of MariaDB, when running the failing query?

Iā€™m having the same issue-
Just upgrade my .jar to 33.6 to confirm it wasnā€™t one of the fixes.

Any MSSQL query including a date, time, or datetime fields (Even casting a string as these types) will return an empty response. This is regardless of the data model settings. Easily reproduced with getdate() or select CAST(ā€˜2019-05-08 12:35:29.123ā€™ AS time) AS ā€˜datetimeā€™ etc

The metabase log table shows the query returns 1 row, but nothing comes back. No errors in other logs.

Any ideas as to whatā€™s going on here?

@balibaba, did you ever find anything note worthy on this?

@Bwilliamson
Can you post the request response in your browsers developer Network-tab?
Which version of MSSQL?
Can you reproduce this if your time does not include milliseconds?

@flamber - certainly.
SQL version is 2014 MSSQL server (Technically V 12.0.4237.0)
Response for getdate() returns 200, but nothing in response

Response for casting as datetime, time, or date is still 200 but no response:

Example just now did not include milliseconds.

@Bwilliamson Can you double-check that the server is actually receiving the query and returns a result? Itā€™s important to figure out where the problem is, since it will help a lot with debugging.

@flamber
The server is getting queries, and returning results, as long as they aren't date/time/datetime specifically.
Running the following query without casting returns 200 with no response.
I'm going to dig into the mssql side to be sure metabase isn't dropping the ball in between.

@flamber
Oh yea, MSSQL is getting the queries.
The cast and not are highlighted:

@Bwilliamson

I have tried multiple things to reproduce the problem, but without success.

It must be something specific with the version of MSSQL or a configuration.

The only available MSSQL I can find is on Docker Hub, so I installed mcr.microsoft.com/mssql/server:2017-latest
But no matter what I do, I cannot make Metabase return empty/nothing/null.

Since there has only been two reports of this issue (you and @balibaba) , Iā€™m guessing itā€™s related to a configuration somewhere.
Thereā€™s hundreds of Metabase installations connected to MSSQL, so there would have been a lot more reports if this was a general problem.

Metabase 0.34.0 will upgrade the SQL Server driver from 7.0.0 to 7.4.1 - I donā€™t think thatā€™s going to change anything, but who knows.

Iā€™m running a mixture of Metabase 0.33.x on both Windows and Linux Servers. One running H2, the rest MySQL.
All connect to MS SQL, various versions from 2012 to 2016. Various compatibility levels from 2008 to 2016.

All installs have questions that use datetimes. All are working perfectly.

I donā€™t know what the problem is, but Iā€™d be looking at configuration of MS SQL (collation or some other oddity).
First step would be a brand new install of SQL Express with just default settings and one of the MS Sample databases.
If that works, put the sample on your live server.
By this time, you could start looking at configuration differences.

1 Like

Thanks for the verification @flamber and @AndrewMBaines - Iā€™ll stand up fresh instances and test for configuration issues.