Error While reading TinyInt field from MYSQL database
Moderators: chulett, rschirm, roy
Error While reading TinyInt field from MYSQL database
Hi All,
I am facing an issue while i try to read data from MYSQL table through ODBC Enterprise stage. The error that pops up when i just try to read the data through View data is as below
##I IIS-DSEE-TDOD-00080 16:41:16(000) <MYSQL_PREFFERED_SKILLS> Process meta data not available in database
Bus Error - core dumped
I also tried to run the job initially and below is the Fatal error i got
Parallel job reports failure (code 138)
I did search in the forum regarding the error, and i tired making the TInyInt as Extended Unsigned (uint8) , i still got the same error.
When I tried to read the data removing that field and i was able to read the data successfully. But since this field is needed for our process we need to pull the fiedl from the MYSQL database.
Can somebody please help me resolve this issue.
Thanks in Advance
I am facing an issue while i try to read data from MYSQL table through ODBC Enterprise stage. The error that pops up when i just try to read the data through View data is as below
##I IIS-DSEE-TDOD-00080 16:41:16(000) <MYSQL_PREFFERED_SKILLS> Process meta data not available in database
Bus Error - core dumped
I also tried to run the job initially and below is the Fatal error i got
Parallel job reports failure (code 138)
I did search in the forum regarding the error, and i tired making the TInyInt as Extended Unsigned (uint8) , i still got the same error.
When I tried to read the data removing that field and i was able to read the data successfully. But since this field is needed for our process we need to pull the fiedl from the MYSQL database.
Can somebody please help me resolve this issue.
Thanks in Advance
Thanks for looking this.
I did try running th4e job to just read the Tinyint field and it failed with the same error as i had obtained earlier when i try to run the job with all the fields including the Tinyint field
The error is as below
Info: MYSQL_SKILLS_USERDATA: Process meta data not available in database
Fatal: Parallel job reports failure (code 138)
Also my database is MYSql and not SQL Server.Not sure if that would make any difference.
Thanks in Advance
I did try running th4e job to just read the Tinyint field and it failed with the same error as i had obtained earlier when i try to run the job with all the fields including the Tinyint field
The error is as below
Info: MYSQL_SKILLS_USERDATA: Process meta data not available in database
Fatal: Parallel job reports failure (code 138)
Also my database is MYSql and not SQL Server.Not sure if that would make any difference.
Thanks in Advance
I tried doing the below accordingly
SELECT skillsId, EMPLID,cast(position AS int) as position,crdate,cast(years_exp AS int) as years_exp
FROM abc
and i am getting the below error when i just try to vie the data
##I IIS-DSEE-TDOD-00080 11:55:34(000) <MYSQL_SKILLS_USERDATA> Process meta data not available in database
>##E IIS-DSEE-TDOD-00007 11:55:34(001) <main_program> [IBM(DataDirect OEM)][ODBC MySQL Wire Protocol driver][MySQL]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int
>##E IIS-DSEE-TOPK-00007 11:55:34(006) <_PEEK_IDENT_> Input dataset does not have field: "skillsId".
>##E IIS-DSEE-TOPK-00007 11:55:34(007) <_PEEK_IDENT_> Input dataset does not have field: "EMPLID".
>##E IIS-DSEE-TOPK-00007 11:55:34(008) <_PEEK_IDENT_> Input dataset does not have field: "position".
>##E IIS-DSEE-TOPK-00007 11:55:34(009) <_PEEK_IDENT_> Input dataset does not have field: "crdate".
>##E IIS-DSEE-TOPK-00007 11:55:34(010) <_PEEK_IDENT_> Input dataset does not have field: "years_exp".
>##E IIS-DSEE-TFSR-00019 11:55:34(012) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFIP-00093 11:55:34(013) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "skillsId".
##W IIS-DSEE-TFIP-00093 11:55:34(014) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "EMPLID".
##W IIS-DSEE-TFIP-00093 11:55:34(015) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "position".
##W IIS-DSEE-TFIP-00093 11:55:34(016) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "crdate".
##W IIS-DSEE-TFIP-00093 11:55:34(017) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "years_exp".
##W IIS-DSEE-TFIP-00096 11:55:34(018) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "skillsId" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(019) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "EMPLID" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(020) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "position" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(021) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "crdate" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(022) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "years_exp" which is not present in the output interface.
>##E IIS-DSEE-TCOS-00029 11:55:34(023) <main_program> Creation of a step finished with status = FAILED.
Any help is greatly appreciated.
Thanks
SELECT skillsId, EMPLID,cast(position AS int) as position,crdate,cast(years_exp AS int) as years_exp
FROM abc
and i am getting the below error when i just try to vie the data
##I IIS-DSEE-TDOD-00080 11:55:34(000) <MYSQL_SKILLS_USERDATA> Process meta data not available in database
>##E IIS-DSEE-TDOD-00007 11:55:34(001) <main_program> [IBM(DataDirect OEM)][ODBC MySQL Wire Protocol driver][MySQL]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int
>##E IIS-DSEE-TOPK-00007 11:55:34(006) <_PEEK_IDENT_> Input dataset does not have field: "skillsId".
>##E IIS-DSEE-TOPK-00007 11:55:34(007) <_PEEK_IDENT_> Input dataset does not have field: "EMPLID".
>##E IIS-DSEE-TOPK-00007 11:55:34(008) <_PEEK_IDENT_> Input dataset does not have field: "position".
>##E IIS-DSEE-TOPK-00007 11:55:34(009) <_PEEK_IDENT_> Input dataset does not have field: "crdate".
>##E IIS-DSEE-TOPK-00007 11:55:34(010) <_PEEK_IDENT_> Input dataset does not have field: "years_exp".
>##E IIS-DSEE-TFSR-00019 11:55:34(012) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFIP-00093 11:55:34(013) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "skillsId".
##W IIS-DSEE-TFIP-00093 11:55:34(014) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "EMPLID".
##W IIS-DSEE-TFIP-00093 11:55:34(015) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "position".
##W IIS-DSEE-TFIP-00093 11:55:34(016) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "crdate".
##W IIS-DSEE-TFIP-00093 11:55:34(017) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator has a binding for the non-existent output field "years_exp".
##W IIS-DSEE-TFIP-00096 11:55:34(018) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "skillsId" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(019) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "EMPLID" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(020) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "position" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(021) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "crdate" which is not present in the output interface.
##W IIS-DSEE-TFIP-00096 11:55:34(022) <MYSQL_SKILLS_USERDATA> When checking operator: The modify operator keeps field "years_exp" which is not present in the output interface.
>##E IIS-DSEE-TCOS-00029 11:55:34(023) <main_program> Creation of a step finished with status = FAILED.
Any help is greatly appreciated.
Thanks
I did try to view the data after changing the sql to
SELECT skillsId, EMPLID,cast(position AS signed) as position,crdate,cast(years_exp AS signed ) as years_exp FROM abc
It still gave an error but this time a different error as below
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_Head,0> Calling runLocally: step=0, node=node0, op=2, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_ABORT_IDENT_,0> Calling runLocally: step=0, node=node0, op=4, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <MYSQL_SKILLS_USERDATA,0> Calling runLocally: step=0, node=node0, op=0, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_PEEK_IDENT_,0> Calling runLocally: step=0, node=node1, op=3, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_EowGenerator,0> Calling runLocally: step=0, node=node1, op=1, ptn=0
>##E IIS-DSEE-TDOD-00007 13:30:38(000) <MYSQL_SKILLS_USERDATA,0> [IBM(DataDirect OEM)][ODBC MySQL Wire Protocol driver]Invalid character value. Error in column 4.
>##E IIS-DSEE-TFOR-00089 13:30:38(001) <MYSQL_SKILLS_USERDATA,0> The runLocally() of the operator failed.
##I IIS-DSEE-TFOR-00094 13:30:38(002) <MYSQL_SKILLS_USERDATA,0> Output 0 produced 0 records.
##I IIS-DSEE-TFPM-00325 13:30:38(003) <MYSQL_SKILLS_USERDATA,0> Operator completed. status: APT_StatusFailed elapsed: 0.40 user: 0.35 sys: 0.05 (total CPU: 0.40)
>##E IIS-DSEE-TFPM-00040 13:30:38(004) <MYSQL_SKILLS_USERDATA,0> Operator terminated abnormally: runLocally() did not return APT_StatusOk
##I IIS-DSEE-USBP-00001 13:30:38(000) <_EowGenerator,0> called postFinalRunLocally
**VIEWDATA ROW LIMITER HIT**
>##E IIS-DSEE-TFOR-00089 13:30:38(000) <_ABORT_IDENT_,0> The runLocally() of the operator failed.
##I IIS-DSEE-TFOR-00163 13:30:38(001) <_ABORT_IDENT_,0> Input 0 consumed 0 records.
##I IIS-DSEE-TFPM-00325 13:30:38(002) <_ABORT_IDENT_,0> Operator completed. status: APT_StatusFailed elapsed: 0.40 user: 0.00 sys: 0.00 (total CPU: 0.00)
**VIEWDATA ROW LIMITER HIT**
>##E IIS-DSEE-TFPM-00040 13:30:38(003) <_ABORT_IDENT_,0> Operator terminated abnormally: runLocally() did not return APT_StatusOk
>##E IIS-DSEE-TFPM-00338 13:30:39(000) <main_program> APT_PMsectionLeader(2, node1), player 2 - Unexpected exit status 1.
>##E IIS-DSEE-TFPM-00338 13:30:39(001) <main_program> APT_PMsectionLeader(1, node0), player 2 - Unexpected exit status 1.
>##E IIS-DSEE-TFSC-00011 13:30:44(000) <main_program> Step execution finished with status = FAILED.
##I IIS-DSEE-TCOS-00026 13:30:44(001) <main_program> Startup time, 0:09; production run time, 0:01.
SELECT skillsId, EMPLID,cast(position AS signed) as position,crdate,cast(years_exp AS signed ) as years_exp FROM abc
It still gave an error but this time a different error as below
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_Head,0> Calling runLocally: step=0, node=node0, op=2, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_ABORT_IDENT_,0> Calling runLocally: step=0, node=node0, op=4, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <MYSQL_SKILLS_USERDATA,0> Calling runLocally: step=0, node=node0, op=0, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_PEEK_IDENT_,0> Calling runLocally: step=0, node=node1, op=3, ptn=0
##I IIS-DSEE-TFPM-00324 13:30:37(000) <_EowGenerator,0> Calling runLocally: step=0, node=node1, op=1, ptn=0
>##E IIS-DSEE-TDOD-00007 13:30:38(000) <MYSQL_SKILLS_USERDATA,0> [IBM(DataDirect OEM)][ODBC MySQL Wire Protocol driver]Invalid character value. Error in column 4.
>##E IIS-DSEE-TFOR-00089 13:30:38(001) <MYSQL_SKILLS_USERDATA,0> The runLocally() of the operator failed.
##I IIS-DSEE-TFOR-00094 13:30:38(002) <MYSQL_SKILLS_USERDATA,0> Output 0 produced 0 records.
##I IIS-DSEE-TFPM-00325 13:30:38(003) <MYSQL_SKILLS_USERDATA,0> Operator completed. status: APT_StatusFailed elapsed: 0.40 user: 0.35 sys: 0.05 (total CPU: 0.40)
>##E IIS-DSEE-TFPM-00040 13:30:38(004) <MYSQL_SKILLS_USERDATA,0> Operator terminated abnormally: runLocally() did not return APT_StatusOk
##I IIS-DSEE-USBP-00001 13:30:38(000) <_EowGenerator,0> called postFinalRunLocally
**VIEWDATA ROW LIMITER HIT**
>##E IIS-DSEE-TFOR-00089 13:30:38(000) <_ABORT_IDENT_,0> The runLocally() of the operator failed.
##I IIS-DSEE-TFOR-00163 13:30:38(001) <_ABORT_IDENT_,0> Input 0 consumed 0 records.
##I IIS-DSEE-TFPM-00325 13:30:38(002) <_ABORT_IDENT_,0> Operator completed. status: APT_StatusFailed elapsed: 0.40 user: 0.00 sys: 0.00 (total CPU: 0.00)
**VIEWDATA ROW LIMITER HIT**
>##E IIS-DSEE-TFPM-00040 13:30:38(003) <_ABORT_IDENT_,0> Operator terminated abnormally: runLocally() did not return APT_StatusOk
>##E IIS-DSEE-TFPM-00338 13:30:39(000) <main_program> APT_PMsectionLeader(2, node1), player 2 - Unexpected exit status 1.
>##E IIS-DSEE-TFPM-00338 13:30:39(001) <main_program> APT_PMsectionLeader(1, node0), player 2 - Unexpected exit status 1.
>##E IIS-DSEE-TFSC-00011 13:30:44(000) <main_program> Step execution finished with status = FAILED.
##I IIS-DSEE-TCOS-00026 13:30:44(001) <main_program> Startup time, 0:09; production run time, 0:01.
Thanks Arnald, I just removed that crdate field and ran the query i was able to read the data both in Heidi tool (mysql access tool) and datastage. Although there were few warnings for the tinyint fields in datastage which said possible truncation due to conversion from int64 to int32.
I have given the length 10 and datatype Integer in datastage.
Not sure what other datatype i need to give to read it without warnings.
Also I am not sure why the timestamp field is giving an error. Do i need to do any kind of conversion for the timestamp field?
I did check the data and they seem to be valid with either 0000-00-00 00:00:00 or some default current timestamp values.
Any idea what could the problem be?
I have given the length 10 and datatype Integer in datastage.
Not sure what other datatype i need to give to read it without warnings.
Also I am not sure why the timestamp field is giving an error. Do i need to do any kind of conversion for the timestamp field?
I did check the data and they seem to be valid with either 0000-00-00 00:00:00 or some default current timestamp values.
Any idea what could the problem be?