SQL Query Review: MySQL db accepts directly, but error in Metabase

1

Primarily want to know if what I’m attempting is not possible at this time in Metabase. I’m a hack when it comes to queries – at this time just trying to find a way to make it work :wink:

Goal is to calculate weekly growth as a percentage (week 2 # of users compared to week 1 # of users) given limited data within the table. The query currently works using MySQLWorkbench.

Anyone see something obvious below that would be causing the error? Appreciate any/all feedback.

Here’s the Query:

SET @runningTotal = 0;
SELECT 
    create_date,
    concat(round(totals.num_accounts / @runningTotal * 100), '%') as percentGrowth,
    @runningTotal := @runningTotal + totals.num_accounts AS runningTotal
FROM
(SELECT 
    DATE(Created_ts) AS create_date,
    COUNT(*) AS num_accounts
FROM `db-name`.table-name AS u
Where Active != 'N'
GROUP BY weekofyear(u.created_ts)) totals
ORDER BY create_date;
2

Is there an error message in the server logs?

3

Ah, that would have been helpful the first time!

You bet. Pulled this from AWS Beanstalk:

BatchUpdateException:
 Message: Unknown or incorrect time zone: 'US/Pacific'
 SQLState: HY000
 Error Code: 1298
e[0m
2016-01-27 20:57:39,934 [DEBUG] metabase.driver.query-processor.annotate :: e[33mSorted fields:
([:1-aggregation 2147483647 :2-other :count]
 [:3-other 2147483647 :2-other :Active]
 [:3-other 2147483647 :2-other :Active])
e[0m
2016-01-27 20:57:39,938 [DEBUG] metabase.models.interface :: DB CALL:  QueryExecution 334
2016-01-27 20:57:39,940 [DEBUG] metabase.middleware :: e[32mPOST /api/dataset 200 (66 ms)e[0m
2016-01-27 20:57:40,048 [DEBUG] metabase.middleware :: e[34mPOST /api/dataset e[0m
2016-01-27 20:57:40,049 [DEBUG] metabase.models.interface :: DB CALL:  Database 2
2016-01-27 20:57:40,056 [DEBUG] metabase.driver.query-processor :: e[34m
QUERY: ?
{:database 2,
 :type "native",
 :native
 {:query
  "SELECT u.Created_ts, count(*)\nFROM (select DISTINCT date(Created_ts) Created_ts from `****`.users) u\njoin `****`.users u2 on u.Created_ts >= date(u2.Created_ts)\nWhere Active != 'N'\ngroup by u.Created_ts"},
 :constraints {:max-results 10000, :max-results-bare-rows 2000}}
e[0m
2016-01-27 20:57:40,057 [DEBUG] metabase.db.internal :: DB CALL:  Database [:engine :details] (where (limit (select* ENTITY21388) 1) {:id database-id})
2016-01-27 20:57:40,067 [DEBUG] metabase.driver.generic-sql.native :: e[32mSET @@session.time_zone = ?;e[0m
2016-01-27 20:57:40,073 [ERROR] metabase.driver.generic-sql.native :: e[31mFailed to set timezone: Unknown or incorrect time zone: 'US/Pacific'e[0m
2016-01-27 20:57:40,073 [DEBUG] metabase.driver.generic-sql.native :: e[32mSELECT u.Created_ts, count(*)
FROM (select DISTINCT date(Created_ts) Created_ts from `****`.users) u
join `****`.users u2 on u.Created_ts >= date(u2.Created_ts)
Where Active != 'N'
group by u.Created_tse[0m

Helpful if I send over file with last 100 entries… or is this enough?

4

Looks like this might be related:

Theres a timezone bug we're working on - Time series charts are off by one day due to timezone differences · Issue #1635 · metabase/metabase · GitHub

5

I believe you are actually running into this issue … https://github.com/metabase/metabase/issues/1699

Unfortunately it’s a quirk with the way the postgres connection works and we don’t have a clear fix at the moment. The work around is to unset the reporting timezone on the Settings page of the Admin Panel, that should prevent this error from happening.

6

Thanks, @agilliland. I did unset the timezone. The github issue was helpful.

Now getting a SQL syntax error in the logs. Tricky to diagnose further because I can successfully run the query from mysql shell.

[ERROR] metabase.driver :: e[31mQuery failure: You have an error in your SQL syntaxe[0m

Happy to try and diagnose further if you have ideas/direction.

7

I’d have to see the full query to give you a better sense of what’s going on. Postgres should be giving you back a better error message than that though :confused:

The only thing that I know doesn’t work in SQL queries is when you try to batch multiple statements together. So if you are writing a statement that ends with a semicolon, then writing another statement after it that won’t work. Otherwise we haven’t had any issues.