new CCSID decodes backwards from ascii to ebdic

msillence commented 1 year ago

The following exception on adding the ccsid:

org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped. at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:53) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:116) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?] at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?] at java.lang.Thread.run(Thread.java:833) [?:?] Caused by: java.lang.RuntimeException: Failed to initialize snapshot context. at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:65) ~[classes/:?] at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:30) ~[classes/:?] at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] ... 5 more Caused by: java.lang.IllegalArgumentException: The column "ëáà|<" is referenced as PRIMARY KEY, but a matching column is not defined in table "DATABASE.SCHEMA.TABLE"! at io.debezium.relational.TableEditorImpl.lambda$updatePrimaryKeys$0(TableEditorImpl.java:107) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at java.util.ArrayList.removeIf(ArrayList.java:1672) ~[?:?] at java.util.ArrayList.removeIf(ArrayList.java:1660) ~[?:?] at io.debezium.relational.TableEditorImpl.updatePrimaryKeys(TableEditorImpl.java:103) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.relational.TableEditorImpl.create(TableEditorImpl.java:307) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.relational.Tables.lambda$overwriteTable$2(Tables.java:194) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.util.FunctionalReadWriteLock.write(FunctionalReadWriteLock.java:84) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.relational.Tables.overwriteTable(Tables.java:187) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.jdbc.JdbcConnection.readSchema(JdbcConnection.java:1203) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.readTableStructure(As400SnapshotChangeEventSource.java:140) ~[classes/:?] at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:62) ~[classes/:?] at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:30) ~[classes/:?] at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final] ... 5 more

Primary key in question was SEDOL:

ëáà|< = 0x53, 0x45, 0x44, 0x4F, 0x4C - EBDIC CP285 SEDOL = 0x53, 0x45, 0x44, 0x4F, 0x4C - ASCII

andymalo43 commented 1 year ago

Hi First over, I want to thank you about the connector. I got the same issue :( do you know if it will be resolved soon ? Thank you again for your answer 😃

msillence commented 1 year ago

I was hoping the latest release had fixed it- what version are you running @andymalo43 ?

andymalo43 commented 1 year ago

Hi @msillence ! I used the docker-compose.yml Do I have to build it from your source ?

THank you for your answer :)

msillence commented 1 year ago

OK I've pushed a new image and updated the tag latest please can you do a pull: docker pull silllencem/debezium-connector-ibmi:latest before restarting: docker compose up -d and see if that fixes it

andymalo43 commented 1 year ago

Hi back @msillence ,

I tried and i still have the same message :/

msillence commented 1 year ago

I tried and i still have the same message :/

:(

just checking - do you actually need the ccid conversion - i.e. is the data in the tables different to the character set on the table? the override is only for https://github.com/jhc-systems/debezium-connector-ibmi/blob/main/jt400-override-ccsid/readme.md

Annoyingly it's working for us even without the latest fix, I'll have to check with our iseries expert as we do need to fix this - not having a reproducible issue does make it harder.

andymalo43 commented 1 year ago

Hi ! Yep I have to convert our ccsid (some tables are on 65xxx) but I just find that I didn't do anything on ccsid configuration when I was uploading a connector on your IBMi debezium. I m going to check that today and tell you back after Thank you again !

andymalo43 commented 1 year ago

Hi back @msillence I got the same behavior after pull the last docker image :(

I delete the volume "mskafka" before up the docker-compose and I still got the same problem :/

msillence commented 1 year ago

to help diagnose this please can you send me the results from running this:

docker run -it --rm -e "FORCED_CCSID=<ccsid to force>" -e "ISERIES_HOST=<iseries name/ip>" -e "ISERIES_USER=<iseries user>"  -e "ISERIES_PASSWORD=<password>" -e "ISERIES_SCHEMA=<schema>" -e "TABLE=<table>" silllencem/jt400-test:latest-SNAPSHOT

replacing the stuff between <> with your settings - note that you need the long name for the table if is is different from the short name

andymalo43 commented 1 year ago

hi @msillence , here is my console execution*

msillence commented 1 year ago

hi @msillence , here is my console execution*

Thanks, sadly that doesn't appear to have worked I was hoping to see a lines such as:

12:55:38.244 [main] [INFO] class com.ibm.as400.access.AS400JDBCConnectionForcedCcsid - requested ccsid 37 using forced ccsid 285

my guess is it's not connected to the database yet and is going to timeout

maybe a problem with the hostname resolution in docker?

you could try adding: --add-host "<myiseries hostname>:<iseries ip address>" to the docker command or use the ip address in -e "ISERIES_HOST=<iseries name/ip>"

testing locally I get more output for everything other than the ip address being wrong

msillence commented 1 year ago

Our current theory is: The driver request for the table metadata is running CALL SYSIBM.SQLCOLUMNS and we suspect for some systems this is being returned in utf16/utf8/ascii and we then convert it to the forced CCSID. I've a new release building which will only convert between two specific character sets - assuming that the system default that comes back for CALL SYSIBM.SQLCOLUMNS is not the same as the you wish to convert from then we should be OK It does mean we need an extra parameter from_ccsid and I've renamed the forced_ccsid to to_ccsid

https://github.com/jhc-systems/debezium-connector-ibmi/pull/46

new image: silllencem/debezium-connector-ibmi:2.2.1.Final_1.9.1

andymalo43 commented 1 year ago

Hi @msillence it seems to work with the last docker image :D Sorry for the late answering, I didn't have so much time to work on it. Thank you a lot So now it's all good. and the keys for table are recognized. But there are little problems : When i imported the connector, debezium load all my rows in redpandas with their initial state and their value -> It's allright I tried to update/delete some rows, but nothing happened to topics in redpanda.
I'm not a big debezium user so maybe it's the standard comportment. I hope use your connector for our need to sync data from ibmi to clickhouse/postgres/or what else sql/nosql database :p Thank you again for your time and your knowledge's sharing :)

martintosney commented 1 year ago

Hi @andymalo43, that's great news that you've got the initial snapshot working.

It sounds like, for whatever reason, the connector is having some trouble reading the journal for the objects. The README is not currently very clear on the requirements on the IBM i side, but the main things you need are:

A journal linked to the library the tables reside in (set with STRJRNLIB)
Journaling active for all the tables in your connector configuration, being recorded to the same journal as that of the library

Do you see anything interesting in the Debezium container logs that might indicate what happened?

(It may also be worth opening a new issue, since it sounds like the initial problem is now resolved)

msillence commented 1 year ago

please can you check the logs for errors:

docker logs ibmi-connector |   jq -rcR 'fromjson? | [.timestamp, .severity, .message]' | grep ERROR

also review the readme here for setting up journaling: https://github.com/jhc-systems/debezium-connector-ibmi/blob/main/journal-parsing/Readme.md

msillence commented 1 year ago

Also are you seeing the correct decoding of CCSID in the latest image - did you change the FORCED_CCSID to the TO_CCSID and add in the FROM_CCSID?

andymalo43 commented 1 year ago

Hi both of you :D

@msillence , there was no error message in my log :) @martintosney for the CCSID, the options from_ccsid et to_ccsid are usefull :)

Thank you a lot for your answers ! Everything 's going fine :

It was my bad, I journaled my files with a journal that was not in the *LIB => I Just updated my file and now it detects everythings :open_mouth:

--> for this constraint, do you think that you'll be able to update it ? I'm not a big java developper but I scrolled through the code and saw this on this page I think that maybe it's where the problem is. I use a query to get journal info https://gist.github.com/andymalo43/16231be5805866b523abe3271409a0e5 I hope this can help you :)

have a great day

And again : Thank you very much for your work :)

msillence commented 1 year ago

This has been working for a while now closing

jhc-systems / debezium-connector-ibmi

new CCSID decodes backwards from ascii to ebdic #40