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:
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.