Insert into MySQL

[Anonymous]

Firstly, I am completely new to Flow, so please be explicit with your instructions.

This is somewhat of a cross-post from https://powerusers.microsoft.com/t5/General-Power-Automate/flow-data-types/m-p/417700/highlight/fals... , just not sure which forum is best for this type of question.

I am currently trying to create a manual flow to insert a record into MySQL using the MySQL connector, eventually this will be used to process incoming e-mail, but I'm not there yet, let's keep things simple at this point in time.

My issue is, after several attempts, I found out Flow won't insert into a table without a PK?

Then I figured out, that it would seem that it requires that I provide the PK value even though the column is defined as an auto increment and normally is auto generated.  Is this normal?

So, the only way I managed to get things working was to create an action to retrieve the greatest PK value and then use that value+1 in my Insert action.  This seems wrong to me, and requires a normally not required step. 

I tried omitting the PK field, but flow says it's required.
I tried pushing null, but then the flow errs.

So I ask you, how can I perform a simple Insert using the MySQL Connector in a way that Flow allow MySQL to auto increment the PK value itself?  Or am I doing things the 'right way', well according to Flow at least?

[v-bacao-msft]

Hi @Anonymous ,

I am afraid that I'm not familiar with MySQL and don't have a suitable test environment, I may not be able to give a suitable solution. Expect more users to participate in this topic.

Similarly, when I test the Insert row action in the SQL server, I will set a field of type number to the identity column in the table, so as to avoid inserting duplicate data.

And when updating the row, the specified row is also updated according to this identity column.

If you set the specified field as a PK field, and it cannot be empty, you must fill this field when configuring the Flow, because it has been set to required.

"The only way I managed to get things working was to create an action to retrieve the greatest PK value and then use that value + 1 in my Insert action. This seems wrong to me, and requires a normally not required step."

It seems that the method you provided is feasible. What kind of problems did you encounter during configuration?

Best Regards,

[Anonymous]

It's not that I encountered any problem, it's just that it is performing an extra query of the database for no reason every time is wrong.

Normally, in say PHP, or VBA, … you can either do:

Provide a NULL value

INSERT INTO `emails2` (`EmailId`, `EmailFrom`, `EmailTo`, `EmailSubject`, `ProjectID`, `Direction`, `ConversationID`, `MessageID`) VALUES (NULL, 'test@test.com', 'test2@test2.com', 'some subject', '11', 'R', '1', '2');

Or

Omit the column altogether

INSERT INTO `emails2` (`EmailFrom`, `EmailTo`, `EmailSubject`, `ProjectID`, `Direction`, `ConversationID`, `MessageID`) VALUES ('test@test.com', 'test2@test2.com', 'some subject', '11', 'R', '1', '2');

in which case the database, since it is a PK auto number/increment column, generates it automatically.  No need to first query it to figure out the last used value to pass to the PK field.

By having to first query the db to retrieve the greatest value in the manner I currently have to will have an impact on performance as time goes on as this table will grow rapidly (so flow will over time take longer and longer to run), I also assume this will impact Azure costs since I will be performing an extra database query for everything Insertion, and it risks creating collisions as there is no guarantee that multiple flows might run at the same time generating the same Max(value)+1 for the Insertion operation.

[Anonymous]

Don't know what happened to my other reply (just disappeared?!).

I'm not experiencing any issues, yet, it works, but it's not the 'proper' way of performing Insertions in a database.

Typically, to perform an Insert you would do either:

Push a NULL value for the PK

INSERT INTO `emails2` (`EmailId`, `EmailFrom`, `EmailTo`, `EmailSubject`, `ProjectID`, `Direction`, `ConversationID`, `MessageID`) VALUES (NULL, 'someone@somewhere.com', 'someoneelse@somewhere.com', 'My Subject', '1', 'R', '2', '3');

OR

Omit the PK altogether

INSERT INTO `emails2` (`EmailFrom`, `EmailTo`, `EmailSubject`, `ProjectID`, `Direction`, `ConversationID`, `MessageID`) VALUES ('someone@somewhere.com', 'someoneelse@somewhere.com', 'My Subject', '1', 'R', '2', '3');

and this way, because the PK is auto increment (identity) the database will automatically generate the PK value at runtime.

Why am I concerned about my current approach?  For several reasons:

• It adds an extra action in the flow which will slow down over time as the table grows. Thus, the extra query is likely to slow down my flow, and this will progressively get worse over time
• I am making useless database queries.  This isn't efficient, and defeats the entire purpose of having an auto increment column.  I also assume (someone can tell me I'm wrong) that the extra db queries will impact monthly Azure pricing/costs.
• Collisions.  There is no guarantee that with this approach, if multiple flows run, that they won't grab the same Max(value)+1 and have collisions when performing the Insertion.

[MJ646]

@v-bacao-msft Is there a solution to this issue?