For months, if not years, MS and certain fellow MVPs have been promoting the virtues of Microsoft Flow. Until recently, I simply never had a reason to even look at it as I had no need for any of its supposed capabilities. That said, working on a new project for one of my clients and a new request by my client made me look into one particular aspect of Flow, the MySQL Connector.

My Goal

In the scenario we are talking about in this posting, I had a pretty simple MySQL database in which we wanted to create a table to save information relating to incoming and outgoing e-mails. Now since their e-mails are all through Office 365 and Flow now offers a PREMIUM (you pay extra for) MySQL connector, I had all the ingredients to make a go of things.

My Actual Experience

Data Types (My First Challenge!)

There were several challenges to getting everything operation and the first of which was trying to create a table in my database to collect the meta data (From, To, Subject, Date/Time) regarding all the incoming and outgoing e-mails for a specific e-mail account. You would think this would be the easiest thing in the world, yet it was incredibly difficult to get any information regarding the data types of the data being returned by flow to be able to create the proper fields in the table. I couldn’t find anywhere the data type information for the Flow fields!

Eventually, someone, in a forum, directed me to https://docs.microsoft.com/en-us/connectors/outlook/#clientreceivemessage which greatly helped, but still only provides part of the overall picture. In it, Microsoft indicates, for instance, that certain fields are Strings, great!, but does not give the length?! Furthermore, they indicated certain fields are defined as ’email’ what is that and what is the equivalent to be used through the MySQL connector?  At the end of the day, it was online research (looking at forum posts, RFC standards) and trial and error that allowed me to create a table structure that I hoped would work, so far it hasn’t blown up in my face, but who knows!

The Interface (My Second Challenge!)

Refresh Me Not

In the process of setting things up and trying to get things to work I was continuously making changes to my table structure and database schema.  On some occasions things updated within the interface, yet on other occasions they did not?!  What I found disappointing was that there was no refresh button to force a refresh of tables/fields for the occasions when it didn’t auto-refresh.  What was even more disappointing was that even existing my flow and then editing it again did not refresh the tables/fields and so my only option was to delete it and start over.

The Layout and Expression Builder

My second complaint regarding the Flow builder interface is the Expression builder control is so small (it allows for viewing 41 characters, as shown in the image below) that you can’t see much of any expression you enter and doesn’t scroll.  There is so much wasted screen real-estate that I don’t understand why Microsoft has made the element so small.

Don’t get me wrong, it looks pretty, but sadly is annoying to work with.

• Why not shift everything to the left and increase the available room for the Expression Builder?
• Why not make the expression control scrollable?
• Why not make the expression control auto-expanding?
• …

So many option are available!

Dealing with Errors/Troubleshooting (The Headache)

False Positives?

During the process of testing what I was building, obviously, I ran multiple tests.  What surprised me is that on several instances, I received error messages indicating the Flow failed, yet the data had been successfully inserted into my table in its entirety.

I also encountered instance where when I’d run the flow, it would report success (and the data was in fact inserted successfully), but when I’d review the previous Flow it was now marked as failed?

Good luck understanding and debugging!!!

When I initially tried use flow to insert hard coded information using the MySQL connector I got:

“We can’t update the value. The operation or its arguments aren’t supported by the value.
inner exception: We can’t update the value. The operation or its arguments aren’t supported by the value.”

Now, while I appreciate be notified that the operation was not successful, the error message certainly isn’t very helpful! Which field, which value?

Another error I received at times was:

BadRequest

As you can imagine, this amount of information was very helpful in trying to determine the issue!

Is there any way of getting more detail error logging? No clue at this point in time.  So Flow quickly can becomes very frustrating to work with!

Help & Support

Getting support seems to a huge issue.  At this point in time, it would seem you need to primarily rely on either trial and error which can only take you so far and the knowledge and generosity of the forum community.

If you are using common aspects of Flow, I suppose you have a better chance of getting the help you might need.  In my instance, I was trying to work with a Premium Connector, that it appears few people are knowledgeable about.  So I got generic help which didn’t really help.  It was 2 weeks of head banging and, at the end of the day, switching approaches that enabled me to implement a functional flow.

Help Within Flow Itself

This was the element I was most disappointed in.  I expected a minimal level of help within the application itself and sadly it was majorly lacking!  For instance, within the Flow ‘panel’ I would choose to use an Expression, so use their expression builder and it displays a select list of available functions grouped as Microsoft saw fit, but no where can you display all the functions.  You need to basically know what you are looking for to be able to find it.  Furthermore, even if you do find a function you might be interested in, you can’t get any information or help page on it from there.

So then, I said to myself, let’s use the main ? button in the application horizontal navbar and try their Documentation.  I performed a search for ‘guid()’ and that’s when the nightmare started!  Needless to say, I quickly switched to Google to find actual help since Microsoft’s documentation and search were less than helpful.

The simple fact that you can’t browse all available functions, that you can’t access help from within the expression builder is deplorable.  For anyone looking, the following link is a great resource for the expression builder functions: Reference guide to using functions in expressions for Azure Logic Apps and Power Automate

Forum(s)

Per the usual, I found help/documentation to be non-existent, at least for what I needed help with, and forums were where I turned to.  The official forum can be found at:

Forums - Power Platform Community

Microsoft Power Automate Community

Sadly for me it would seem that few, or perhaps no one, had any actual experience with the MySQL Connector so days went by with little to no real help with my questions.

Documentation

As discussed previously, I have found that the documentation to be very poor.  It is hard to find in the first place and even when you do find it, it is incomplete.

For example, the MySQL Connector, the only webpage I could find on the subject was:

MySQL - Connectors

MySQL is an open-source relational database management system. Connect to on-premise MySQL database to read and write data.

Wow, that’s immensely helpful, not!  Tell me something that I didn’t already know.

Templates

Flow offers numerous Templates to try and help users get up and running quickly and perhaps use as a starting point to learn and build upon.  Sadly for me, Microsoft didn’t have a single Template for the MySQL connector?!  Not one!

Now For The Bad News (It Gets Worse)

As if all of the above wasn’t enough to deter a new user, I was faced with one more hurtle!  Using the Premium MySQL Connector, even using manual triggered hard coded values that I knew worked, I couldn’t get a simple Insertion to work!!!

It is important to note that my initial table primary key was setup as an Auto Increment field.

Everyone knows that to perform an insertion, for a table with an Auto Increment PK field, you can typically use one of 2 approaches:

1. Provide a NULL value

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

2. Omit the PK field altogether

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

yet, neither worked.  Option 1 simply did not worked and erred and Option 2 couldn’t be save as Flow insisted on having a value for the PK field.

I asked online and sadly no one had a clue, no one seemed to have any experience with this connector and no one from Microsoft seemed to monitor the forum or be willing to offer any assistance on the matter.

So left to my own devices, I try various things and managed to figure out that I could perform and Insert if I supplied a PK value.  So the question then became, how do I determine the next available value to use for an Insertion?!  Eventually, I figured out, that I would need to add a Get rows prior to the Insert row so as to determine the current Max PK value and add 1 to it.

Now, I won’t bore you with all the details, and it did work (sort of), but once put into official production, being  executed and e-mails coming in simultaneously at times, this method was unreliable and collisions occurred, errors started to abound, thus ultimately making it simply unacceptable for true production.  Another idea that went up in smoke.

So it was back to the drawing board for me.

Finally, I woke up and managed to think outside the box that I had confined myself within for days and had an idea, what if I used random PK values!  Originally, I wanted to generate a random string, but couldn’t figure out how to do so (documentation and help being what it is) and decided to use a GUID.  Luckily for me, Flow had a guid() function to generate one for me.  So I modified my table structure as required, changed my flow and used the guid() expression for the PK field and voila, my flow now worked, reliably (it’s been running for 2 months now without issue).

Another idea that I was suggested was to setup and use a web service as an intermediary.  So use Flow to push its data to a web service (that I would create) to process and natively insert into my database.

Dealing with Date/Time Values

One thing I wanted to also briefly focus on was dealing with Date/Time values.  I was tracking the E-mail’s reception Date/Time, but as I found out, Flow was passing the UTC value.  Thankfully, this is one thing that Flow makes easy to convert.  By using the convertTimeZone() function one can easily convert the Date/Time into whatever format you need.  So, in my case I ended up using an expression like:

convertTimeZone(triggerBody()['DateTimeReceived'],'UTC','Eastern Standard Time','yyyy-MM-dd HH:mm:ss')

Microsoft in All of This?

Because I have the luxury of being a Microsoft MVP, I was able to directly report the bugs with the MySQL Connector and trying to perform Insertions.

I also reported issues relating to the website as a whole in which I could log out to switch users amongst other things.

I can only hope these gets addressed.  As for will they and when, your guess is as good as mine!

Conclusion

I don’t know about Flow as a whole, but with regard to the PREMIUM MySQL connector Microsoft should be ashamed of themselves when the most basic Insertion fails. They should be reimbursing every user that has the connector until which time they actually have it working properly.

This all brings me back to Microsoft’s QA (Quality Assurance or testing if your prefer) once again! I’ve seen a disgusting lack of proper QA  from Windows 10 and Office (2016, 2019 and Office 365), and as this case clearly demonstrated to me, Flow as well. This experience clearly proved to me that no one ever even tested making a simple Insert using their connector before releasing it and charging users.  The MySQL Connector, at the time of my testing (November-December 2019) is simply not ready for prime time and is no more than a BETA concept IMHO!

The Silver Lining and Light at the End of the Tunnel

The one thing I will say positive about my experience is that after 2-3 weeks (yes it took 2-3 weeks for myself and another IT Admin of playing around to finally manage to get something production worthy) I finally manage to create a flow that was ready for a production environment and it has worked flawlessly ever since.

The Story Continues

Be sure to check out my follow-up article:

To Flow, or Not To Flow, That is the Question!

I’ve been using Power Automate Flows for heading on 2 years now on an extensive project for one of my client’s and I thought I’d give people some more insight into them. You can consult my original post on the subject:   I’ve used a number of connectors, such as: HTTP Request is received Send…

Continue reading →