1

Hello,
we use mysql and for id’s we use binary format to store uuid, so executing query in redash we always see
Error running query: ‘utf8’ codec can’t decode byte 0xe8 in position 1: invalid continuation byte

I like how HeidySQL behaves, it builds on top hex(id) and gives alias, also it can be toogled. So I would like to suggest also this feature.

2

You’re storing binary data inside fields marked as text in mysql?

Can’t you fix this on the mysql side? Perhaps with a function that decodes the binary data back to the UUID? Tell us more about how it’s stored on the mysql side and I can probably suggest a function.

1 Like
3

Just for information why: https://medium.com/@galopintitouan/auto-increment-is-the-devil-using-uuids-in-symfony-and-doctrine-71763721b9a9

I know function, but it is annoying every time seeing ‘utf8’ codec can’t decode byte 0xe8 in position 1: invalid continuation byte search erroneous column and paste such peace of code:
LOWER(CONCAT(SUBSTR(HEX(id), 9, 8),"-", SUBSTR(HEX(id), 5, 4),"-", SUBSTR(HEX(id), 1, 4),"-", SUBSTR(HEX(id), 17, 4),"-", SUBSTR(HEX(id), 21, 12))) as id

4

What is the column type of id?

It this a problem with how the python library automatically picks the type based upon the content? I do mean to have a look at that as it really annoys me - particularly when wanting to use links in the Table viz - if I want to do something simple like date_format(date, “%Y-%m-%d”) in my query and then use it as a field in a link… it gets turned into a Python date and formatted into some huge string that’s not friendly to the URL I’m trying to point to.

5

That sounds like a bug. Can you share an example of this?

6

Easy:

SELECT "2019-11-12";

Clearly returns a string from a SQL point of view. But it’s a date by the time it’s in Redash.

7

Interesting… digging a bit deeper, seems the MySQL side of things within Redash looks good; when I look at the JSON within Redash’s internal DB, I see:

redash=> select query, data from query_results where id=8613073;
                        query                        |                                                                                                data                                                                                                 
-----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 select "2019-01-01" string, date("2019-01-01") date | {"rows": [{"date": "2019-01-01", "string": "2019-01-01"}], "columns": [{"type": "string", "friendly_name": "string", "name": "string"}, {"type": "date", "friendly_name": "date", "name": "date"}]}

… so it’s something downstream that’s not rendering things correctly. Wonder whether this means it’s affecting all datasources?

8

Could it also be the cause of this bug?

9

It’s here:

10

Given that with the move to Python 3 there’s already an effort to solicit lots of input as to whether anything has broken - this seems like a good opportunity to clear this up and figure out whether there are query runners that are not populating the type parameters correctly. @arikfr would you agree?

For my use-cases (mostly MySQL data-sources) removing the date/datetime overrides here seems straightforward, I’ve not looked at what situations the ‘object’ type is being used or when the number -> float translation is taking place.

11

Interesting…

When I remove those two translations I get a bigger change than I was expecting. Before when I had a MySQL query:

select "2019-01-01", date("2019-01-01");

I got rendered two columns each td class=“display-as-datetime” showing “01/01/19”.

Now I get first a td class=“display-as-string” showing “2019-01-01” (so that’s good!) followed by a td class=“display-as-datetime” (… still good…) but with the value “2019-01-01”. So for some reason the date formatting isn’t happening. I’m out of my depth in JS-land…

UPDATE because this lame forum won’t allow my to reply to my own replies more than 3 times (grr):-

That JS code is horrible. I’ll publish a PR later… think I’ve cleaned it up (with the caveat - I don’t do JS!).

This still doesn’t address the problem that @webmake was reporting - which I think can be most simply reproduced with:

SELECT unhex("ffff");

In this case we need to make a change to the MySQL query runner to extract the flag that says this field is a binary FIELD_TYPE_VAR_STRING and map it appropriately.

FURTHER UPDATE:

12

I’m not sure about this. There is already as it is quite a lot to test and maybe it will be better to leave such change to another time, maybe after we have some integration tests for the data sources? I doubt the test will be rigorous enough to cover such changes across all query runners.

Yeah the logic there and the one in query results fetching survived all the refactors and probably one of the longest living pieces of code in Redash :grimacing:

Did you figure out why is this happening?

13

Yes did figure out what was happening with the date formatting - the date/datetime strings in the rowdata are parsed into moment objects by that code; so if you look at my PR that’s basically all it’s left doing.

That PR does break a couple of tests (I’ve not yet figured out where those tests are defined) but that’s because the tests are looking for incorrect behaviour!