You’re offline. This is a read only version of the page.
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/false#M35194 , 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?
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'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.
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,
#1
WarrenBelz
146,651
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,997
Most Valuable Professional
Share
Report
All responses (
Answers (
