# Tinyint(1) type in MySQL source

**URL:** <https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440>\
**Category:** Uncategorized\
**Created:** [December 18, 2018, 9:39am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440 "2018-12-18T09:39:11Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![jumo](https://avatars.discourse-cdn.com/v4/letter/j/77aa72/32.png) [@jumo](https://community.dremio.com/u/jumo)\
**Post date:** [December 18, 2018, 9:39am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/1 "2018-12-18T09:39:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![firefly707](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/firefly707/32/646_2.png) [@firefly707](https://community.dremio.com/u/firefly707)\
**Post date:** [December 18, 2018, 7:33pm UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/2 "2018-12-18T19:33:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [December 19, 2018, 7:43am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/3 "2018-12-19T07:43:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jumo](https://avatars.discourse-cdn.com/v4/letter/j/77aa72/32.png) [@jumo](https://community.dremio.com/u/jumo)\
**Post date:** [December 20, 2018, 11:19am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/4 "2018-12-20T11:19:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![chafidz](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/chafidz/32/1267_2.png) [@chafidz](https://community.dremio.com/u/chafidz)\
**Post date:** [October 2, 2019, 7:43am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/5 "2019-10-02T07:43:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [October 3, 2019, 3:38am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/6 "2019-10-03T03:38:16Z")

</div>

@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

---

<div class="post-metadata">

**Author:** ![chafidz](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/chafidz/32/1267_2.png) [@chafidz](https://community.dremio.com/u/chafidz)\
**Post date:** [October 3, 2019, 7:02am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/7 "2019-10-03T07:02:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pedrodoc](https://avatars.discourse-cdn.com/v4/letter/p/5e9695/32.png) [@pedrodoc](https://community.dremio.com/u/pedrodoc)\
**Post date:** [November 21, 2019, 7:14pm UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/8 "2019-11-21T19:14:22Z")

</div>

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](https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html) 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”.

---

<div class="post-metadata">

**Author:** ![Lukercio](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lukercio/32/2481_2.png) [@Lukercio](https://community.dremio.com/u/Lukercio)\
**Post date:** [May 29, 2020, 2:46pm UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/9 "2020-05-29T14:46:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Rakesh\_Malugu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/rakesh_malugu/32/2384_2.png) [@Rakesh\_Malugu](https://community.dremio.com/u/Rakesh_Malugu)\
**Post date:** [June 25, 2020, 3:16am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/10 "2020-06-25T03:16:45Z")

</div>

@Lukercio

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

Thanks,  
@Rakesh_Malugu

---

<div class="post-metadata">

**Author:** ![koolay](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/koolay/32/2407_2.png) [@koolay](https://community.dremio.com/u/koolay)\
**Post date:** [July 3, 2020, 3:41am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/11 "2020-07-03T03:41:33Z")

</div>

@Rakesh_Malugu  
This will be fixed in next release ?  
Thanks

---

<div class="post-metadata">

**Author:** ![Rakesh\_Malugu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/rakesh_malugu/32/2384_2.png) [@Rakesh\_Malugu](https://community.dremio.com/u/Rakesh_Malugu)\
**Post date:** [July 3, 2020, 3:45am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/12 "2020-07-03T03:45:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![koolay](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/koolay/32/2407_2.png) [@koolay](https://community.dremio.com/u/koolay)\
**Post date:** [August 6, 2020, 2:20am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/13 "2020-08-06T02:20:08Z")

</div>

@Rakesh_Malugu

There’s any update?  
Thanks

---

<div class="post-metadata">

**Author:** ![bigfacewo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/bigfacewo/32/4090_2.png) [@bigfacewo](https://community.dremio.com/u/bigfacewo)\
**Post date:** [August 17, 2022, 1:05am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/14 "2022-08-17T01:05:49Z")

</div>

There’s any update?  
Thanks  
@balaji.ramaswamy

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [August 17, 2022, 5:19am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/15 "2022-08-17T05:19:14Z")

</div>

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

[https://docs.dremio.com/software/sql-reference/data-types-mysql/](https://docs.dremio.com/software/sql-reference/data-types-mysql/)

---

<div class="post-metadata">

**Author:** ![bigfacewo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/bigfacewo/32/4090_2.png) [@bigfacewo](https://community.dremio.com/u/bigfacewo)\
**Post date:** [August 17, 2022, 7:04am UTC](https://community.dremio.com/t/tinyint-1-type-in-mysql-source/2440/16 "2022-08-17T07:04:41Z")

</div>

@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](https://dev.mysql.com/doc/refman/5.7/en/integer-types.html)