MySQL Server returning wrong values

Hello,

this code on a pushbutton action event returns different values on some tables every time I execute:

Dim db As New MySQLCommunityServer

db.Host = "xxx"
db.Port = 3306
db.DatabaseName = "xxx"
db.UserName = "xxx"
db.Password = "xxx"

datalist.DeleteAllRows

If db.Connect Then
  dim rs as RecordSet

  rs= db.SQLSelect("SELECT  `schedule`.`FromDT`, "_
  +"`schedule`.`ToDT`, "_
  +"`displaygroup`.`DisplayGroup`, "_
  +"GROUP_CONCAT(DISTINCT`display`.`MacAddress` ) , "_
  +"`campaign`.`Campaign`, "_
  +"GROUP_CONCAT(DISTINCT`media`.`storedAs` ) "_
  +"FROM `lkwidgetmedia` "_ 
  +"INNER JOIN `media`  ON `lkwidgetmedia`.`mediaId` = `media`.`mediaID` "_
  +"INNER JOIN `widget`  ON `lkwidgetmedia`.`widgetId` = `widget`.`widgetId` "_
  +"INNER JOIN `region`  ON `region`.`regionId` = `widget`.`playlistId` "_
  +"INNER JOIN `widgetoption`  ON `widget`.`widgetId` = `widgetoption`.`widgetId` "_
  +"INNER JOIN `lkcampaignlayout`  ON `lkcampaignlayout`.`LayoutID` = `region`.`layoutId` "_
  +"INNER JOIN `layout`  ON `lkcampaignlayout`.`LayoutID` = `layout`.`layoutID` "_
  +"INNER JOIN `campaign`  ON `lkcampaignlayout`.`CampaignID` = `campaign`.`CampaignID` "_
  +"INNER JOIN `schedule`  ON `schedule`.`CampaignID` = `campaign`.`CampaignID` "_
  +"INNER JOIN `lkscheduledisplaygroup`  ON `schedule`.`eventID` = `lkscheduledisplaygroup`.`eventId` "_
  +"INNER JOIN `lkdisplaydg`  ON `lkscheduledisplaygroup`.`displayGroupId` = `lkdisplaydg`.`DisplayGroupID` "_
  +"INNER JOIN `display`  ON `lkdisplaydg`.`DisplayID` = `display`.`displayid` "_
  +"INNER JOIN `displaygroup`  ON `lkdisplaydg`.`DisplayGroupID` = `displaygroup`.`DisplayGroupID` "_
  +"GROUP BY `campaign`.`Campaign`"_
  +"ORDER BY `schedule`.`FromDT`")
  
  
  If rs <> Nil Then
    While Not rs.EOF
      dataList.AddRow ""
      For i As Integer = 0 To rs.FieldCount - 1
        dataList.Cell(dataList.LastIndex, i) = ConvertEncoding(rs.IdxField(i + 1).StringValue, Encodings.utf8)
      Next
      rs.MoveNext
    Wend
  End If
  rs.Close
  
else
  MsgBox "error db connection"
end if

if I execute this query in e.g. Valentina Pro Studio everything is as expected.

Any suggestions?

edit: hmm if i run this query in phpmyadmin and it also return different values every time I execute it

I don’t know for what this part exists in your workflow, but MacAddress is not a synonym of fixed code for years now. My cellphone dynamically and randomly change it all the time, for example. https://source.android.com/devices/tech/connect/wifi-mac-randomization

yes, but the MAC of the built-in ethernet adapter doesn’t change.

Not sure what you mean by different results, maybe you can post an example?

Have you tried specifying the order in GROUP_CONCAT?

SELECT GROUP_CONCAT(DISTINCT column ORDER BY something)

I’m not sure if the results are consistent without specifying the order.

Call you query multiple times, check the diff of them, notice what changed, try to understand what data change could cause it. You are doing lots of inner joins, it means possibly filtering many data out.

The results should be consistent, but with the following grouping

"GROUP BY `campaign`.`Campaign`"