Tinyint(1) type in MySQL source

1

Hi,

We have a MySQL table which contains a status field with tinyint(1) type.
This field has several values (from 1 to 20)

When we read data of this table, Dremio interprets them by ‘true’ or ‘false’ values (true if 0, false otherwise).
It’s the standard behaviour for reading this kind of data type : by default, tinyint(1) is consider as a boolean.

We have the same behaviour with logstash for instance.
But we can provide an option for a different interpretation by jdbc property.

For instance:
jdbc_connection_string=‘jdbc:mysql://url:ip/xx?tinyInt1isBit=false’

Is it possible to configure Dremio MySQL source with this option ?

Thanks
Julien

2

Can you send us a snippet of the ddl/dml used to create the table?

3

Hi @jumo

Would something like the below work

mysql> create table tinyint_test (a tinyint(1));

Query OK, 0 rows affected (0.07 sec)

mysql> insert into tinyint_test values(1);

Query OK, 1 row affected (0.15 sec)

mysql> insert into tinyint_test values(2);

Query OK, 1 row affected (0.03 sec)

mysql> insert into tinyint_test values(3);

Query OK, 1 row affected (0.13 sec)

mysql> insert into tinyint_test values(4);

Query OK, 1 row affected (0.17 sec)

mysql> insert into tinyint_test values(5);

Query OK, 1 row affected (0.05 sec)

On Dremio if you query this, all fields are shown as true (like you described)

SELECT * FROM localmysql.idio.tinyint_test

{“a”:true}
{“a”:true}
{“a”:true}
{“a”:true}
{“a”:true}

cast the column to a varchar like below

SELECT cast(a as varchar) FROM localmysql.idio.tinyint_test

Output will be like below

{“a_tinyint”:“1”}
{“a_tinyint”:“2”}
{“a_tinyint”:“3”}
{“a_tinyint”:“4”}
{“a_tinyint”:“5”}

Thanks
@balaji.ramaswamy

4

Hi @balaji.ramaswamy,

Thks for the tips, it works fine.
So double casting if you want an integer :

cast(cast(a_tinyint as varchar) as int)

Julien

5

@balaji.ramaswamy , sorry for bumping an old thread.

While there is a workaround like above, this tinyint(1) completely breaks UI preview. I’m using 3.3.2 and this happened when I open a MySQL datasource table from Web UI which contain tinyint(1)

FUNCTION ERROR: Invalid value for boolean: ‘2’

I know this is a feature from MySQL side which sometimes comes in handy, but if we can put extra JDBC options when creating/modifying MySQL data source, that would be great.

6

@chafidz

Mysql tinyint is BOOLEAN, if you create a Mysql table with a column type BOOLEAN, it actually stores as tinyint(1), see below example

create table testbool(col1 boolean);

mysql> desc testbool;
±------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------±-----±----±--------±------+
| col1 | tinyint(1) | YES | | NULL | |

When Dremio finds a tinyint datatype, it only expects 0 or 1 values and any other value we reject saying “Invalid value for boolean: ‘n’”

Thanks
@balaji.ramaswamy

7

If I could change the table on the source, I would. The problem is, We have no control on what our dev team team doing. That’s why if we could just add extra JDBC params to handle issue like this, that would be great!
p.s: We have another pipelines which can handle this. But in some cases, we must plug some of our DB right into Dremio.

8

Hello @balaji.ramaswamy the fact that boolean is stored as tinyint does not mean tinyint is boolean. This assumption is wrong and is causing Dremio to be unable to read simple MySQL tables. The cast solution is an workaround, not very efficient though.

Paraphrasing Michael Dunn:
The MySQL Documentation says BOOL and BOOLEAN are synonyms for TINYINT(1) (and this makes sense, since booleans are stored as a 0 or 1 in the database itself and is only rendered as true or false at select-time). The documentation does not say that TINYINT(1) is a synonym for BOOLEAN .

The documentation says “all German Shepherds are dogs” while Dremio assumes by default that “all dogs are German Shepherds”.

1 Like
9

Is Dremio considering to change this behavior in future versions? Or do we need to keep doing this workaround?

10

@Lukercio

This fix will be a part of future release. We would like to recommend to use the workaround till then.

Thanks,
@Rakesh_Malugu

11

@Rakesh_Malugu
This will be fixed in next release ?
Thanks

12

Hello @koolay

I mean in the upcoming releases. Not in the next release.
I will update the thread when the fixed version is available.

Thanks,
@Rakesh_Malugu

13

@Rakesh_Malugu

There’s any update?
Thanks

14

There’s any update?
Thanks
@balaji.ramaswamy

15

@bigfacewo Here is the mapping between Mysql and Dremio data types

https://docs.dremio.com/software/sql-reference/data-types-mysql/

16

@balaji.ramaswamy I know this mapping. I mean, should tinyint be converted to int instead of Boolean? Because of the accuracy loss, the data range of tinyint is -128 ~ 127. MySQL :: MySQL 5.7 Reference Manual :: 11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT