@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.
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.
aha!
I have an error to work with now. Using select getdate() this is thrown in the log:
[9b89bf0a-fc7f-4957-b0b0-5fcea702cf39] 2019-11-29T08:21:39-05:00 ERROR metabase.query-processor.middleware.results-metadata Error recording results metadata for query:
clojure.lang.ExceptionInfo Input to infer-special-type does not match schema:
[(named {:name (not (“Non-blank string” “”))} field-or-column)]
("–> sync.analyze.classifiers.name$fn__41695$infer_special_type__41700.invoke(name.clj:142)"
“sync.analyze.query_results$fn__43794$maybe_infer_special_type__43799$fn__43800$fn__43801.invoke(query_results.clj:50)”
“sync.analyze.query_results$fn__43794$maybe_infer_special_type__43799$fn__43800.invoke(query_results.clj:43)”
“sync.analyze.query_results$fn__43794$maybe_infer_special_type__43799.invoke(query_results.clj:39)”
“sync.analyze.query_results$results__GT_column_metadata$iter__43865__43869$fn__43870.invoke(query_results.clj:87)”
“sync.analyze.query_results$add_insights$iter__43838__43842$fn__43843.invoke(query_results.clj:65)”
“sync.analyze.query_results$add_insights.invokeStatic(query_results.clj:65)”
“sync.analyze.query_results$add_insights.invoke(query_results.clj:60)”
“sync.analyze.query_results$results__GT_column_metadata.invokeStatic(query_results.clj:88)”
“sync.analyze.query_results$results__GT_column_metadata.invoke(query_results.clj:81)”
“query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__43890.invoke(results_metadata.clj:91)”
“query_processor.middleware.format_rows$format_rows$fn__38080.invoke(format_rows.clj:26)”
“query_processor.middleware.add_dimension_projections$add_remapping$fn__35043.invoke(add_dimension_projections.clj:232)”
“query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__35684.invoke(add_source_metadata.clj:107)”
“query_processor.middleware.resolve_source_table$resolve_source_tables$fn__41181.invoke(resolve_source_table.clj:46)”
“query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__35531.invoke(add_row_count_and_status.clj:16)”
“query_processor.middleware.driver_specific$process_query_in_context$fn__37567.invoke(driver_specific.clj:12)”
“query_processor.middleware.add_settings$add_settings$fn__35554.invoke(add_settings.clj:45)”
“query_processor.middleware.resolve_driver$resolve_driver$fn__40795.invoke(resolve_driver.clj:22)”
“query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881$fn__36882.invoke(bind_effective_timezone.clj:9)”
“util.date$call_with_effective_timezone.invokeStatic(date.clj:88)”
“util.date$call_with_effective_timezone.invoke(date.clj:77)”
“query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__36881.invoke(bind_effective_timezone.clj:8)”
“query_processor.middleware.store$initialize_store$fn__43915$fn__43916.invoke(store.clj:11)”
“query_processor.store$do_with_store.invokeStatic(store.clj:46)”
“query_processor.store$do_with_store.invoke(store.clj:40)”
“query_processor.middleware.store$initialize_store$fn__43915.invoke(store.clj:10)”
“query_processor.middleware.async$async__GT_sync$fn__34195.invoke(async.clj:23)”
“query_processor.middleware.async_wait$runnable$fn__36607.invoke(async_wait.clj:89)”)
I stood up a new Metabase installation on a fresh 18.04 ubuntu install.
I tested with Docker, and with the jar. Production is currently using the jar file.
I could not reproduce the issue in either case, with the same SQL server configuration, and jdk versions.
Is there a documented process for re-installation while retaining current query’s, users, dashboards, etc?
Hi @Bwilliamson
The Non-blank string can be ignored. It will be fixed from next version because of PR #11351
Can you post your Diagnostic Info from Admin > Troubleshooting, from the instance, where select getdate() is failing? I have a feeling that the problem stems from your Java version or perhaps the Windows version.
Since I don’t know where your Metabase application data is stored, then I’ll wait to answer the last question until you’ve posted the diagnostic info.
Hi @flamber
I thought JRE/JDK was an issue as well, Talend had thrown similar fits without the proper version in place. I upgraded the JRE on production from OpenJDK 1.8 to 11. Same on the test VM I spun up to confirm this issue is isolated. Confirmed the JRE it’s using is the new 11, and the issue persists.
Diagnostics:
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.4+11-post-Ubuntu-1ubuntu218.04.3”,
“java.vendor”: “Ubuntu”,
“java.vendor.url”: “https://ubuntu.com/”,
“java.version”: “11.0.4”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.4+11-post-Ubuntu-1ubuntu218.04.3”,
“os.name”: “Linux”,
“os.version”: “4.15.0-70-generic”,
“user.language”: “en”,
“user.timezone”: “SystemV/EST5”
},
“metabase-info”: {
“databases”: [
“sqlserver”,
“mysql”,
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “mysql”,
“run-mode”: “prod”,
“version”: {
“date”: “2019-11-19”,
“tag”: “v0.33.6”,
“branch”: “release-0.33.x”,
“hash”: “be1e0e1”
},
“settings”: {
“report-timezone”: “US/Eastern”
}
}
}
Metabase application data is stored in MySql on the same host.
@Bwilliamson
Dang it. Hoped it was something simple as changing/updating Java.
But I notice this SystemV/EST5 - that’s a very strange timezone, and since this issue has been about dates, then I’m thinking it could be the cause.
Well, if it works on your Docker, then that’s great - and let’s just leave it at that 
To use your MySQL, you simply just add the environment variables to the Docker run-command, so it uses MySQL for you metadata. Example:
docker run -d -p 3000:3000 -e "MB_DB_TYPE=mysql" -e 'MB_DB_CONNECTION_URI="mysql://<hostname>:<port>/<database>?user=<username>&password=<password>"' --name metabase metabase/metabase
1 Like
Thanks @flamber,
I didn’t notice the timezone difference in the diagnostics section. I’ve just updated that to proper US/Eastern to test, and low and behold- THAT was the issue.
SystemV/EST5 was deprecated a while back I believe. I have no idea how that got in there.
You were right. Configuration issue. I never would have expected these symptoms to come from THAT.
@balibaba - Check your server/java timezone settings, maybe you’re in the same boat.
2 Likes