Error While reading TinyInt field from MYSQL database

sshettar · Post by **sshettar** » Fri Aug 24, 2012 2:46 pm

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

ArndW · Post by **ArndW** » Sat Aug 25, 2012 12:58 am

I didn't get that error with tinyint but if your SQL Server tabel contains a column of type "image" and you try to read that column you will get DataStage aborting on you.
Can you reproduce your error in a job where you read ONLY that one column from your table?

sshettar · Post by **sshettar** » Mon Aug 27, 2012 8:06 am

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

ArndW · Post by **ArndW** » Mon Aug 27, 2012 8:23 am

You'll always see the first part of the message regarding process metadata, so don't worry about that.
If you do a "cast( original_column_nameAS int) as 'original_column_name'" and declare the column as integer in datastage what is the result?

sshettar · Post by **sshettar** » Mon Aug 27, 2012 9:56 am

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

ArndW · Post by **ArndW** » Mon Aug 27, 2012 10:54 am

I checked Google for the MySQL Cast() syntax and it would seem that one needs to use "signed" rather than "int".

sshettar · Post by **sshettar** » Mon Aug 27, 2012 11:39 am

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.

ArndW · Post by **ArndW** » Mon Aug 27, 2012 9:50 pm

And what happens if you run this query in mysql? Note that column 4 would be "crdate" - what type is that in mysql and what is your DataStage definition?

sshettar · Post by **sshettar** » Tue Aug 28, 2012 7:29 am

I ran the same query in Heidi tool(tool to access mysql database). below is the error i recieve

SQL Error (2027): Malformed packet

The 4th field is a datetime in the mysql database and in the datastage i have given it a Timestamp(6)

ArndW · Post by **ArndW** » Tue Aug 28, 2012 7:33 am

And if you remove the field from you query does it work? I googled "SQL Error (2027): Malformed packet" and got many hits.

sshettar · Post by **sshettar** » Tue Aug 28, 2012 8:25 am

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?

sshettar · Post by **sshettar** » Tue Aug 28, 2012 8:56 am

I wanted to add this information that the other tables where i am able to read the timestamp fields are of datatype Datetime in the mysql databse , unlike this one filed 'crdate' which is of datatype TIMESTAMP in mysql database.
Is there an issue reading the timestamp field from the mysql database?