One Star

MySQL SCD Example in Components Guide

Good morning,
I'm using Talend Open Studio for Big Data, version 5.4.
I recreated the MySQL SCD example on p. 502 of the Components Guide, using my own data extract as a source. When I run it, I get the following error:
"Incorrect table definition. There can only be one auto column and it must be defined as a key"
The SCD configuration and schema are below. I didn't find this issue elsewhere in the Help Center.
Has anyone else encountered this? Is there something wrong with how I followed the example?

Thanks,
Mitch

  • Big Data
15 REPLIES
Moderator

Re: MySQL SCD Example in Components Guide

Hi,
Have you read TalendHelpCenter:tMysqlSCD with the section of "SCD management methodologies".
Best regards
Sabrina
One Star

Re: MySQL SCD Example in Components Guide

Hi Sabrina,
Thank you for your prompt response.
I have reviewed that section of the Component Guide. The only discrepancy I noticed is that I did not map an input field for the surrogate key. The documentation implies that this is mandatory, but I set it to auto-increment (as in the example provided), so this shouldn't be an issue.
Is there some other factor that I'm missing, or am I misunderstanding the role of the input field?

Thanks,
Mitch
One Star

Re: MySQL SCD Example in Components Guide

This is what the job looks like:
One Star

Re: MySQL SCD Example in Components Guide

The tMysqlSCD_1 component also gives this error: "The schema from the input link "row2\" is different from the schema defined in the component"
The output schema for tLogRow_1 is the same as the input schema for tMysqlSCD_1. The difference appears to be in the SCD output schema, with the additional fields. Open Studio ought to recognize them, and not throw an error (this would have come up in the example case).
One Star

Re: MySQL SCD Example in Components Guide

I synced the columns in the MysqlSCD component, removing the SCD-specific fields. The "row2 schema" issue disappeared.
When I ran it, every row was rejected. The Rejects output shows the errorCode field as null, and errorMessage as "Unknown column '' in 'where clause' - Line: 0" .
One Star

Re: MySQL SCD Example in Components Guide

Instead of using my own data, I copied the data in the example, and followed the same configuration.
Again, every row was rejected. This time, however, errorMessage said "Unknown column 'status' in 'field list' - Line: 0". The status header and data were included in the reject output, so there were no nulls or confusion there.
Any assistance would be greatly appreciated. A successful SCD operation is critical for the functionality I need, and it's frustrating to see a simple example consistently fail.
One Star

Re: MySQL SCD Example in Components Guide

One other thing I noticed:
Previously, the SCD fields in the MysqlSCD schema were highlighted in green. This time, they aren't.
The "row2 schema" issue is still there, though.
One Star

Re: MySQL SCD Example in Components Guide

I scrapped my current attempt, and started over. After a schema correction in LogRow_2, the example ran successfully (both initial and update). w00t!
So now I'm trying to build my own, and I'm running into problems again. First it was "Unknown column 'scd_end' in 'where clause' - Line: 0" when scd_start and scd_end were added in the SCD component. After I included scd_version and scd_active, I got "Unknown column 'scd_version' in 'field list' - Line: 0".
Has anyone seen these errors, and figured out why?
One Star

Re: MySQL SCD Example in Components Guide

*bump*
One Star

Re: MySQL SCD Example in Components Guide

Anyone at all?
One Star

Re: MySQL SCD Example in Components Guide

Happy New Year!
One Star

Re: MySQL SCD Example in Components Guide

Hi,
It looks like column is missing primary key column altogether or there is some difference in the schema of the table and the structure used.
I would suggest to check the table structure, if it has one primary key column which is defined as auto_increment column. If not there already than define one. And again draw the graph freshly after refreshing the table metadata.
It should automatically recognize the key column and should not face any issue in this new job.

--
Thanks & Regards,
vinod_talend
One Star

Re: MySQL SCD Example in Components Guide

Hi Vinod,
Thanks for your response.
The primary key column is not auto-increment, but the values are unique. The SCD component adds an auto-increment key column (SK1).
As for "some difference in the schema of the table and the structure used", I changed the order of the fields in the SCD schema, so they would match the incoming schema from the tLogRow; same result.
Any further advice would be greatly appreciated.

Thanks again,
Mitch
Community Manager

Re: MySQL SCD Example in Components Guide

Hi Mitch
Can you please upload a screenshot of your table structure? There must be a auto-increment key column in the table, and set the name of the column in the surrogate key field; Also, a field called scd_start and a field called scd_end should be defined in the table if you uses type2 fields.
The tMysqlSCD_1 component also gives this error: "The schema from the input link "row2\" is different from the schema defined in the component"

The schema of tMysqlSCD should be the same as tLogRow, move the SK1 column from the schema of tMsqlSCD.

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MySQL SCD Example in Components Guide

Hi Mitch
Can you please upload a screenshot of your table structure? There must be a auto-increment key column in the table, and set the name of the column in the surrogate key field; Also, a field called scd_start and a field called scd_end should be defined in the table if you uses type2 fields.

Hi Shong,
Thanks for your response.
My table structure is attached. ORG_CD is the main key field, and SK1 is the auto-incrementing surrogate.
The tMysqlSCD_1 component also gives this error: "The schema from the input link "row2\" is different from the schema defined in the component"

The schema of tMysqlSCD should be the same as tLogRow, move the SK1 column from the schema of tMsqlSCD.

I'm not sure what you're asking for here. The surrogate is critical for the SCD; removing it to make the schemas match better would be counter-productive.

Mitch