MySQL Server returning wrong values


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"


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)
  End If
  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.

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?


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`"