0

I am trying to combine JSON datatype columns in a grouped mysql result with GROUP_CONCAT but at character 5001 i get this weird missing piece of json.

{
    "IMO": "XX",
    "LAT": "52.093330",
    "LON": "3.587667",
    "DSRC": "TER",
    "MMSI": "XX",
    "SPEED": "117",
    "COURSE": "120",
    "SHIPID": "XX",
    "STATUS": "0",
    "HEADING": "118",
    "TIMESTAMP": "2017-05-03T18:16:27",
    "UTC_SECONDS": "27"
}, {
    "IMO": "XX",
    "LAT": "52.097700",
    "LON…:28:57",
    "UTC_SECONDS": "58"
}, {
    "IMO": "XX",
    "LAT": "52.192000",
    "LON": "3.340167",
    "DSRC": "TER",
    "MMSI": "XX",
    "SPEED": "128",
    "COURSE": "124",
    "SHIPID": "XX",
    "STATUS": "0",
    "HEADING": "125",
    "TIMESTAMP": "2017-05-03T17:25:07",
    "UTC_SECONDS": "9"
}

I have set group_concat_max_len to 1000000 and max_allowed_packet to 100M in my.cnf and am using the GROUP_CONCAT like this

CONCAT('[', GROUP_CONCAT(p.position_data) ORDER BY p.datetime DESC), ']')
Han Dijk
  • 1,602
  • 1
  • 14
  • 20
  • Can you check the record which is being truncated? – Nigel Ren May 03 '17 at 19:11
  • The row that is being truncated contains {"IMO": "XX", "LAT": "52.097700", "LON": "3.576170", "DSRC": "TER", "MMSI": "XX", "SPEED": "123", "COURSE": "122", "SHIPID": "XX", "STATUS": "0", "HEADING": "511", "TIMESTAMP": "2017-05-03T18:14:26", "UTC_SECONDS": "57"} – Han Dijk May 03 '17 at 19:20
  • take a look at this, it might help https://stackoverflow.com/questions/26553823/what-is-the-maximum-allowance-for-group-concat-max-len-in-mysql/41066175 – WNB Mar 27 '18 at 11:38

0 Answers0