[
https://issues.apache.org/jira/browse/NIFI-7785?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17196674#comment-17196674
]
zeyk commented on NIFI-7785:
----------------------------
Hi [~RobertoGarcia],
Please find the below details and thanks for taking time on this. Also have
attached the xml of the process group and screenshot of the result of
!sqlcmdReturn.png![^sample_with_enum.xml]sqlcmd
EnumDbcpPool:
db url : jdbc:mysql/endpoint:3306/db_name
driver class name : com.mysql.cj.jdbc.Driver
driver path : /home/ubuntu/drivers/mysql-connector-java-8.0.21.jar
sqlCmd
value :
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(column_type,'enum',''),')',''),'(',''),'\'','')
enums
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='sample_with_enum' AND column_name='gender';
insert flow file from CDC processor:
{
"type" : "insert",
"timestamp" : 1600231699000,
"binlog_filename" : "mysql-bin-changelog.000051",
"binlog_position" : 62784,
"database" : "ml",
"table_name" : "sample_with_enum",
"table_id" : 916,
"columns" : [ {
"id" : 1,
"name" : "id",
"column_type" : 4,
"value" : 19
}, {
"id" : 2,
"name" : "gender",
"column_type" : 1,
"value" : 2
} ]
}
table create command:
create table sample_with_enum (
id int,
gender enum('M','F'),
primary key ('id')
)
> CaptureChangeMySQL processor captures enum values as "INDEX of those values"
> from Mysql DB"
> -------------------------------------------------------------------------------------------
>
> Key: NIFI-7785
> URL: https://issues.apache.org/jira/browse/NIFI-7785
> Project: Apache NiFi
> Issue Type: Bug
> Components: Tools and Build
> Affects Versions: 1.11.4
> Environment: Ubuntu EC2 instance with 8 GB ram
> Reporter: zeyk
> Priority: Major
> Labels: features
> Attachments: Screenshot from 2020-09-15 05-29-48.png, Screenshot from
> 2020-09-15 08-05-33.png, flow.xml.gz, sample_with_enum.xml, sqlcmdReturn.png
>
>
> CaptureChangeMySQL processor captures enum values as "INDEX of those values"
> rather than the values specified.
> for example:
> A table has columns (id int, fruit enum ('apple','pears','orange'), price int)
> On doing an insert:
> insert into (1,'apple',45)
> insert into (2,'pears',56)
> I have used CaptureChangeMySql processor to capture the CDC changes, the
> process does the capture but captures the enum column alone based on its
> index like the sample below:
> for 1st insert:
>
> {
> "type":"insert",
> "timestamp":1599004442000,
> "binlog_filename":"mysql-bin-changelog.000039",
> "binlog_position":1537835,
> "database":"sample",
> "table_name":"sample",
> "table_id":82,
> "columns":[
> {
> "id":1,
> "name":"id",
> "column_type":-5,
> "value":139
> },
> {
> "id":2,
> "name":"fruit",
> "column_type":12,
> "value":0
> },
> {
> "id":3,
> "name":"price",
> "column_type":12,
> "value":45
> }
> ]
> }
>
> for 2nd insert:
>
> {
> "type":"insert",
> "timestamp":1599004442000,
> "binlog_filename":"mysql-bin-changelog.000039",
> "binlog_position":1537835,
> "database":"sample",
> "table_name":"sample",
> "table_id":82,
> "columns":[
> {
> "id":1,
> "name":"id",
> "column_type":-5,
> "value":139
> },
> {
> "id":2,
> "name":"fruit",
> "column_type":12,
> "value":1
> },
> {
> "id":3,
> "name":"price",
> "column_type":12,
> "value":56
> }
> ]
> }
>
>
> So the above has 0 and 1 in place of apple and pears respectively.
>
> Could you of you help me on this, if there are folks who have faced similar
> kinda issue
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
