Hello,
I having some issues trying to load a physical dataset for an external connection.
when I try to do a SELECT * it returns me the following error
The data type from the source is and Int(10) and the database is a MYSQL
any ideas of how to fix this?
Regards
I think you need to use BIGINT. Here is the mapping of data types in your case:
https://docs.dremio.com/sql-reference/data-types-mysql/
Hi @Mahendrakar ,
Thank you so much for your reply, but I don’t see why it correct. If I am not wrong the only difference between INT and BIGINT it is the size (2^32 and 2^64).
But the number above is lower than 2^32 and it has 10 digit. Moreover, it works in mysql server, and the mapping link you shared with me seems to map **INT" with integer, so the mapping seems accurate
@capihacendado What happens if you run the same query as an External query
@capihacendado Can you please send me the profile from the external query job that failed?
Thank you for your answer @balaji.ramaswamy.
You can find attached the profile. I attached the profile here, but let me know if you want me to send it by another channel.
Let me know if you need anything else
b65b3091-7c65-4bc3-82f8-cda1e031ff4a.zip (11,4 KB)
@capihacendado What happens if you run SELECT * from matomo.piwik_log_action directly on MySQL?
Thank you for your answer @balaji.ramaswamy
Nothing special happens, it shows the data as expected.
I’ll share with you some screenshots with the max and min value, and a top 5 of those records
Let me know if you need anything else
One last request, are you able to send the profile of the query that failed when run as an external query?
Thank you so much for your answer
I think that I send you the profile a some message ago, but I’ll send again.
333b23d3-0a1f-4ad7-bce4-bb2356362a73.zip (11,4 KB)
Let me know if you need anything else
@capihacendado Not sure how the row is inserted in mysql, but when I try the value reported in the error message and the max value you have above, mysql throws an error
mysql> create table testint(name text, hash int(10));
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into testint values('ABC',123);
Query OK, 1 row affected (0.01 sec)
mysql> insert into testint values('XYZ',2965230250);
ERROR 1264 (22003): Out of range value for column 'hash' at row 1
mysql> insert into testint values('XYZ',4294966382);
ERROR 1264 (22003): Out of range value for column 'hash' at row 1
mysql>
Hi @balaji.ramaswamy thank you so much for your answer. I think we are getting closer to the solution.
The error that are you getting is because you define the hash column as signed.
As you could see in the definition of the columns, my hash column is unsigned
if you check the max value of the unsigned is greater that value you test
I replicated your example in my mysql workbench and with a signed definition it doesn’t work
could be that dremio has a bug and it doesn’t take into account the signed/unsigned definition of the int columns?
@capihacendado Not sure why but even after declaring the column as unsigned I am unable to insert into Mysql
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into testint values('ABC',123);
Query OK, 1 row affected (0.00 sec)
mysql> insert into testint values('XYZ',2965230250);
ERROR 1264 (22003): Out of range value for column 'hash' at row 1
mysql> insert into testint values('XYZ',4294966382);
ERROR 1264 (22003): Out of range value for column 'hash' at row 1
mysql>
That doesn’t make any sense, as you saw in the documentation (attached in my last post) those values are allowed. Moreover, I was able to add it myself.
Could you run this command to double check that you are using the right type?
SHOW FULL COLUMNS FROM testint;
Let me know if you want to share screen so we can go trough step by step
+-------+------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| name | text | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
| hash | int | NULL | YES | | NULL | | select,insert,update,references | |
+-------+------+--------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
mysql>Thank you so much for your answer.
It seems that you didn’t change correctly the int to unsigned. As you can see in the screenshot, you should see the type as “int unsigned”
I share the code with you that will make it work. You can check it step by step and see the outcome
create table testint2(name text, hash int(10));
SHOW FULL COLUMNS FROM testint2;
insert into testint2 values('ABC',123);
insert into testint2 values('XYZ',2965230250);
ALTER TABLE `testint2` CHANGE `hash` `hash` INT(10) UNSIGNED;
SHOW FULL COLUMNS FROM testint2;
insert into testint2 values('XYZ',2965230250);
An this is my output.
Once you double check that it works in mysql, I believe that the problem will be the bug that I mentioned in previous post, i.e, that dremio doesn’t take into account the signed/unsigned definition of the int column in mysql
Thank you for your assistance
any updates in this topic?
@balaji.ramaswamy any update in this topic??
@capihacendado Sorry for the delay, I will get to this right after the long weekend
