SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxxx' for key 'group_key'

Question

I currently have the SELECT using PDO but I am having this error message

SELECT a.*, UNIX_TIMESTAMP(a.createdon) AS timestamp, ct.cityname, ct.SeoCityName, scat.subcatname, scat.SeoSubcatName, 
               COUNT(*) AS piccount, p.picfile, UNIX_TIMESTAMP(feat.featuredtill) AS featuredtill 
            FROM prefix_ads a
                 INNER JOIN prefix_cities ct ON a.cityid = ct.cityid 
           INNER JOIN prefix_subcats scat ON a.subcatid = scat.subcatid
          LEFT OUTER JOIN prefix_adxfields axf ON a.adid = axf.adid
                LEFT OUTER JOIN prefix_adpics p ON a.adid = p.adid AND p.isevent = '0'        
                LEFT OUTER JOIN prefix_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'        
            WHERE a.catid = '10' 
              AND a.enabled = '1' AND a.verified = '1' AND a.expireson >= NOW()   
            GROUP BY a.adid
            ORDER BY a.createdon DESC
            LIMIT 0, 18

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxxx' for key 'group_key'

xxxx = used to be if of the listing

Can someone please help?

EXPLAIN

Array
(
    [id] => 1
    [0] => 1
    [select_type] => SIMPLE
    [1] => SIMPLE
    [table] => scat
    [2] => scat
    [type] => ALL
    [3] => ALL
    [possible_keys] => PRIMARY
    [4] => PRIMARY
    [key] => 
    [5] => 
    [key_len] => 
    [6] => 
    [ref] => 
    [7] => 
    [rows] => 152
    [8] => 152
    [filtered] => 100.00
    [9] => 100.00
    [Extra] => Using temporary; Using filesort
    [10] => Using temporary; Using filesort
)
Array
(
    [id] => 1
    [0] => 1
    [select_type] => SIMPLE
    [1] => SIMPLE
    [table] => a
    [2] => a
    [type] => ref
    [3] => ref
    [possible_keys] => subcatid,cityid,verified,enabled,expireson,catid,ct_ads
    [4] => subcatid,cityid,verified,enabled,expireson,catid,ct_ads
    [key] => subcatid
    [5] => subcatid
    [key_len] => 2
    [6] => 2
    [ref] => DB.scat.subcatid
    [7] => DB.scat.subcatid
    [rows] => 150
    [8] => 150
    [filtered] => 100.00
    [9] => 100.00
    [Extra] => Using where
    [10] => Using where
)
Array
(
    [id] => 1
    [0] => 1
    [select_type] => SIMPLE
    [1] => SIMPLE
    [table] => ct
    [2] => ct
    [type] => eq_ref
    [3] => eq_ref
    [possible_keys] => PRIMARY
    [4] => PRIMARY
    [key] => PRIMARY
    [5] => PRIMARY
    [key_len] => 2
    [6] => 2
    [ref] => DB.a.cityid
    [7] => DB.a.cityid
    [rows] => 1
    [8] => 1
    [filtered] => 100.00
    [9] => 100.00
    [Extra] => 
    [10] => 
)
Array
(
    [id] => 1
    [0] => 1
    [select_type] => SIMPLE
    [1] => SIMPLE
    [table] => axf
    [2] => axf
    [type] => ref
    [3] => ref
    [possible_keys] => adid
    [4] => adid
    [key] => adid
    [5] => adid
    [key_len] => 4
    [6] => 4
    [ref] => DB.a.adid
    [7] => DB.a.adid
    [rows] => 1
    [8] => 1
    [filtered] => 100.00
    [9] => 100.00
    [Extra] => Using index
    [10] => Using index
)
Array
(
    [id] => 1
    [0] => 1
    [select_type] => SIMPLE
    [1] => SIMPLE
    [table] => p
    [2] => p
    [type] => ref
    [3] => ref
    [possible_keys] => adid
    [4] => adid
    [key] => adid
    [5] => adid
    [key_len] => 5
    [6] => 5
    [ref] => DB.a.adid,const
    [7] => DB.a.adid,const
    [rows] => 1
    [8] => 1
    [filtered] => 100.00
    [9] => 100.00
    [Extra] => 
    [10] => 
)
Array
(
    [id] => 1
    [0] => 1
    [select_type] => SIMPLE
    [1] => SIMPLE
    [table] => feat
    [2] => feat
    [type] => eq_ref
    [3] => eq_ref
    [possible_keys] => adid
    [4] => adid
    [key] => adid
    [5] => adid
    [key_len] => 7
    [6] => 7
    [ref] => DB.a.adid,const
    [7] => DB.a.adid,const
    [rows] => 1
    [8] => 1
    [filtered] => 100.00
    [9] => 100.00
    [Extra] => 
    [10] => 
)

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  scat    ALL     PRIMARY     NULL    NULL    NULL    152     Using temporary; Using filesort
1   SIMPLE  a   ref     subcatid,cityid,verified,enabled,expireson,catid,c...   subcatid    2   DB.scat.subcatid    150     Using where
1   SIMPLE  ct  eq_ref  PRIMARY     PRIMARY     2   DB.a.cityid     1   
1   SIMPLE  axf     ref     adid    adid    4   DB.a.adid   1   Using index
1   SIMPLE  p   ref     adid    adid    5   DB.a.adid,const     1   
1   SIMPLE  feat    eq_ref  adid    adid    7   DB.a.adid,const     1

Regards G

yes, there is no INSERT or UPDATE but I am having that issue at my website. This is the reason why I am asking your expert help. — Mysql Newbie

Helio Gabrenha Jr Helio Gabrenha Jr · Accepted Answer · 2014-04-16T18:35:52

Your query isn't correct.
You shouldn't use that many select fields and group by just properly.

For example:

SELECT                                                                                                  
    UNIX_TIMESTAMP(a.createdon) AS timestamp,                                                               
    ct.cityname,                                                                                            
    ct.SeoCityName,                                                                                         
    scat.subcatname,                                                                                        
    scat.SeoSubcatName,                                                                                     
    p.picfile,                                                                                              
    UNIX_TIMESTAMP(feat.featuredtill) AS featuredtill,                                                      
    COUNT(*) AS piccount                                                                                    
FROM                                                                                                    
    prefix_ads a                                                                                          
    INNER JOIN prefix_cities ct ON a.cityid = ct.cityid                                    
    INNER JOIN prefix_subcats scat ON a.subcatid = scat.subcatid                                 
    LEFT OUTER JOIN prefix_adxfields axf ON a.adid = axf.adid                                     
    LEFT OUTER JOIN prefix_adpics p ON a.adid = p.adid AND p.isevent = '0'                  
    LEFT OUTER JOIN prefix_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'        
WHERE
    a.catid = '10'                                                                        
    AND a.enabled = '1' 
    AND a.verified = '1' 
    AND a.expireson >= NOW()                         
GROUP BY 
    1,2,3,4,5,6,7                                                                             
ORDER BY 
    a.createdon DESC                                                                   
LIMIT 0, 18

Since COUNT is a grouping function, it doesn't go on 'group by' clause

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxxx' for key 'group_key'

1 Answers