#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    Help with group_concat, new to mysql


    it appears that maybe a system parm needs to be changed when using group_concat, I am inserting the group_concat into a text field and it appears that when I reach some size limit the insert crashed. I have tried to define the field as text, longtext, blob, longblob with no success. I know the data I am inserting does not exceed the allowable text size

    thanks in advance
    bruce
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    could you do a SHOW CREATE TABLE for the tables involved, as well as showing the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0
    create table temp
    (
    kid double,
    api_number varchar(255),api_num_nodash double,lease varchar(255),well varchar(255),
    field varchar(255),latitude double,longitude double,long_lat_source varchar(255),
    township double,twn_dir varchar(255),rangex varchar(255),range_dir varchar(255),
    section double ,spot varchar(255),feet_north double,feet_east double,
    foot_ref varchar(255),rig_operator varchar(255),curr_operator varchar(255),elevation double,
    elev_ref varchar(255),depth double,form_at_depth varchar(255),produce_form varchar(255),
    permit datetime,spud datetime,completion datetime,plugging varchar(255),
    oil_kid double,gas_kid double,status varchar(255),status2 varchar(255),
    comments varchar(255),final longtext);

    INSERT into temp
    select b.kid,b.api_number,b.api_num_nodash,b.lease,b.well,b.field,b.latitude,b.longitude,b.long_lat_source,
    b.township,b.twn_dir,b.rangex,b.range_dir,b.section,b.spot,b.feet_north,b.feet_east,b.foot_ref,b.ori g_operator,b.curr_operator,
    b.elevation,b.elev_ref,b.depth,b.form_at_depth,b.produce_form,b.permit,b.spud,b.completion,b.pluggin g,b.oil_kid,
    b.gas_kid,b.status,b.status2,b.comments,(select group_concat(ltrim(rtrim(a.elev_ref)) ,',',ltrim(rtrim(a.formation)),
    ',',ltrim(rtrim(a.top)),',',quote(a.base) SEPARATOR ' # ')
    from tops_ks_cheyenne_co as a where a.kid = b.kid )
    from wells_ks_cheyenne_co as b ORDER BY 1;

    the problem is when I insert another field after ',',quote(a.base)
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0
    before adding a new field the max width of the group_concat is 863 characters, after the addition i estimate the width to be 2000 characters
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    you said it "crashed" -- what error message did you get?

    and could you do the SHOW CREATE TABLE for the tops_ks_cheyenne_co table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Perhaps group_concat_max_len was exceeded.

    I know, I know - I'm just guessing again...
  12. #7
  13. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    Originally Posted by cafelatte
    I know, I know - I'm just guessing again...
    Twenty lashes on the back with a wet noodle and DON'T do it again!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Ah, you've read 50 shades of grey too. [spoiler alert]
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    50? all i need is one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo