topic Re: MySQL SCD Example in Components Guide in Talend Studio

MySQL SCD Example in Components Guide

Anonymous — Sat, 16 Nov 2024 11:49:18 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Wed, 27 Nov 2013 03:34:16 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Mon, 02 Dec 2013 15:21:46 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Mon, 02 Dec 2013 17:13:00 GMT

This is what the job looks like:

Re: MySQL SCD Example in Components Guide

Anonymous — Tue, 03 Dec 2013 14:32:33 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Thu, 05 Dec 2013 16:42:38 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Fri, 06 Dec 2013 20:23:48 GMT

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.

Re: MySQL SCD Example in Components Guide

Anonymous — Fri, 06 Dec 2013 20:28:26 GMT

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.

Re: MySQL SCD Example in Components Guide

Anonymous — Mon, 09 Dec 2013 21:04:10 GMT

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?

Re: MySQL SCD Example in Components Guide

Anonymous — Mon, 16 Dec 2013 14:46:41 GMT

*bump*

Re: MySQL SCD Example in Components Guide

Anonymous — Fri, 20 Dec 2013 16:01:23 GMT

Anyone at all?

Re: MySQL SCD Example in Components Guide

Anonymous — Tue, 07 Jan 2014 14:36:42 GMT

Happy New Year!

Re: MySQL SCD Example in Components Guide

Anonymous — Wed, 08 Jan 2014 13:29:09 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Wed, 08 Jan 2014 16:17:44 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Thu, 09 Jan 2014 02:44:30 GMT

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

Re: MySQL SCD Example in Components Guide

Anonymous — Thu, 09 Jan 2014 15:46:09 GMT

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