ADF mapping dataflow using AlterRow sink error

Question

ADF mapping dataflow using AlterRow sink error

Gayatri Krishnan 41

Hi,

I want to perform a merge operation to destination Azure mySQL. I am using mapping data flow AlterRow transformation to achieve the same. The source and destination column names and types are a match but still on run mapping data flow is trying to create a table in destination whereas the table already exists with correct columns and data types. Error is returned indicating it is trying to create a table in destination and denying user doesn't have permission. Why is the table creation getting fired when the options are not set to do so? Am I missing something?

Error
{"message":"Job failed due to reason: at Sink 'sink1': java.sql.SQLSyntaxErrorException: CREATE command denied to user 'portal_data'@'20.211.6.147' for table 't_5175_f35eff520ab74d6faefe6744c5a95793'. Details:java.sql.SQLSyntaxErrorException: CREATE command denied to user 'portal_data'@'20.211.6.147' for table 't_5175_f35eff520ab74d6faefe6744c5a95793'\n\tat shaded.msdataflow.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)\n\tat shaded.msdataflow.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)\n\tat shaded.msdataflow.com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)\n\tat shaded.msdataflow.com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)\n\tat shaded.msdataflow.com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108)\n\tat shaded.msdataflow.com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)\n\tat org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.createTable(JdbcUtils.scala:863)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter.createTable(JDBCStore.scala:591)\n\tat com.microsoft.dataflow.transformers.store.JDBCWriter.createTable(JDBCStore.scala","failureType":"UserError","target":"Data flow1","errorCode":"DFExecutorUserError"}

Gayatri Krishnan 41 Reputation points

2022-01-11T05:08:12.76+00:00

Hi,

Any ideas with this problem?

3 answers

Your answer

Gayatri Krishnan 41 Reputation points

2022-01-11T05:08:12.76+00:00

Hi,

Any ideas with this problem?

Answer 1

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

Hi @Gayatri Krishnan ,

Thank you for posting query in Microsoft Q&A Platform.

Could you please confirm below,

Did you used and pre SQL scripts or Post SQL scripts in Sink settings?
Could you please try using normal Dataset instead of inline dataset?
Could you please check in Data preview of alter row is all required rows getting update policy?

Gayatri Krishnan 41 Reputation points

2022-01-07T12:21:35.893+00:00

Hi,

I am not using any command in pre and post sql scripts in sink settings. I have tried it using both inline and integration dataset and get the same error. Data preview in alter row does indicate the update policy is getting applied whereever applicable.

Thanks,

Gayatri

Answer 2

ShaikMaheer-MSFT 38,631 Microsoft Employee Moderator

Hi @Gayatri Krishnan ,

Looks like error message is confusing here.

Could you please check manually using SSMS, to make your user id which is used in SQL linked service can able to update records in your destination table?

If not, kindly make sure to get related permission on your user id and try it updating records using SSMS and also using ADF.

Kindly have CREATE permission also on your user id and try.

Hope that may helps. Please let us know how it goes. Thank you.

Gayatri Krishnan 41 Reputation points

2022-01-13T12:37:20.11+00:00

The user does have permission to insert, update and delete rows. I have tried insert, update and delete commands using copy activity and has worked without any issues or errors. It is only Data flow sink that is giving permission denied error that too on a create table statement whereas there is no create command fired. I need to use Data flow but due to the error I am using a workaround through copy activity.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-01-13T16:35:16.47+00:00
Hi @Gayatri Krishnan ,

Looks strange to me Kindly check below pointers once if that helps.

Are you using inline dataset in Sink? Could you please try using Integrated Dataset.

Could you please try by having CREATE permissions as well on your user id. Its should not be needed but since issue seems strange may be we can give a try.

To understand issue better it seems having idea about your complete dataflow implementation may give some hint. So feel free to share if you can.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-01-19T13:15:19.193+00:00

Hi @Gayatri Krishnan - Checking if you get chance to share updates.

Answer 3

DavidR 40

I have a very similar problem and I am not able to solve it.

My user has grants to read/create/update/delete registars on the sink table.

I'm using an integrated dataset, I don't have any script and my Data Preview works fine, the problem cames out when I excute the pipeline, I have posted a question here if you want to view more details https://learn.microsoft.com/en-us/answers/questions/1527655/azure-data-factory-denied-permission-when-trying-t

It seems that is a bug of Azure Data Factory.

Share via

ADF mapping dataflow using AlterRow sink error

3 answers

Your answer