Database Error with tables

  • Hi guys,

    I was trying to build a new site with sportspress, having already some experience from another one. First everything was working fine. Then after manually putting in all my data for players, leagues, positiones and so on, I came upon the teams. As I was doing them, it first worked. Then I trid clicking on one of them and my site went blank. The database got an error. My support from the hosting said, the database was not closing a comand and went looping. They have sent me the comand that was not closing:

    SELECT DrTT1_posts.* FROM DrTT1_posts LEFT JOIN DrTT1_term_relationships ON
    (DrTT1_posts.ID = DrTT1_term_relationships.object_id) LEFT JOIN
    DrTT1_term_relationships AS tt1 ON (DrTT1_posts.ID = tt1.object_id) INNER JOIN
    DrTT1_postmeta ON ( DrTT1_posts.ID = DrTT1_postmeta.post_id ) INNER JOIN
    DrTT1_postmeta AS mt1 ON ( DrTT1_posts.ID = mt1.post_id ) INNER JOIN
    DrTT1_postmeta AS mt2 ON ( DrTT1_posts.ID = mt2.post_id ) INNER JOIN
    DrTT1_postmeta AS mt3 ON ( DrTT1_posts.ID = mt3.post_id ) INNER JOIN
    DrTT1_postmeta AS mt4 ON ( DrTT1_posts.ID = mt4.post_id ) INNER JOIN
    DrTT1_postmeta AS mt5 ON ( DrTT1_posts.ID = mt5.post_id ) INNER JOIN
    DrTT1_postmeta AS mt6 ON ( DrTT1_posts.ID = mt6.post_id ) INNER JOIN
    DrTT1_postmeta AS mt7 ON ( DrTT1_posts.ID = mt7.post_id ) INNER JOIN
    DrTT1_postmeta AS mt8 ON ( DrTT1_posts.ID = mt8.post_id ) INNER JOIN
    DrTT1_postmeta AS mt9 ON ( DrTT1_posts.ID = mt9.post_id ) INNER JOIN
    DrTT1_postmeta AS mt10 ON ( DrTT1_posts.ID = mt10.post_id ) INNER JOIN
    DrTT1_postmeta
    AS mt11 ON ( DrTT1_posts.ID = mt11.post_id ) INNER JOIN DrTT1_postmeta AS mt12 ON
    ( DrTT1_posts.ID = mt12.post_id ) INNER JOIN DrTT1_postmeta AS mt13 ON (
    DrTT1_posts.ID = mt13.post_id ) INNER JOIN DrTT1_postmeta AS mt14 ON (
    DrTT1_posts.ID = mt14.post_id ) INNER JOIN DrTT1_postmeta AS mt15 ON (
    DrTT1_posts.ID = mt15.post_id ) INNER JOIN DrTT1_postmeta AS mt16 ON (
    DrTT1_posts.ID = mt16.post_id ) INNER JOIN DrTT1_postmeta AS mt17 ON (
    DrTT1_posts.ID = mt17.post_id ) INNER JOIN DrTT1_postmeta AS mt18 ON (
    DrTT1_posts.ID = mt18.post_id ) INNER JOIN DrTT1_postmeta AS mt19 ON (
    DrTT1_posts.ID = mt19.post_id ) INNER JOIN DrTT1_postmeta AS mt20 ON (
    DrTT1_posts.ID = mt20.post_id ) WHERE 1=1 AND (
    DrTT1_term_relationships.term_taxonomy_id IN (18) AND tt1.term_taxonomy_id IN
    (13) ) AND ( ( DrTT1_postmeta.meta_key = ‘sp_format’ AND
    DrTT1_postmeta.meta_value IN (‘league’) ) AND ( ( mt1.meta_key = ‘sp_team’ AND
    mt1.meta_value IN (‘437’) ) AND ( mt2.meta_key = ‘sp_team’ AND mt2.meta_value IN
    (
    ‘438’) ) AND ( mt3.meta_key = ‘sp_team’ AND mt3.meta_value IN (‘464’) ) AND (
    mt4.meta_key = ‘sp_team’ AND mt4.meta_value IN (‘467’) ) AND ( mt5.meta_key =
    ‘sp_team’ AND mt5.meta_value IN (‘455’) ) AND ( mt6.meta_key = ‘sp_team’ AND
    mt6.meta_value IN (‘468’) ) AND ( mt7.meta_key = ‘sp_team’ AND mt7.meta_value IN
    (‘453’) ) AND ( mt8.meta_key = ‘sp_team’ AND mt8.meta_value IN (‘452’) ) AND (
    mt9.meta_key = ‘sp_team’ AND mt9.meta_value IN (‘451’) ) AND ( mt10.meta_key =
    ‘sp_team’ AND mt10.meta_value IN (‘446’) ) AND ( mt11.meta_key = ‘sp_team’ AND
    mt11.meta_value IN (‘445’) ) AND ( mt12.meta_key = ‘sp_team’ AND mt12.meta_value
    IN (‘444’) ) AND ( mt13.meta_key = ‘sp_team’ AND mt13.meta_value IN (‘443’) ) AND
    ( mt14.meta_key = ‘sp_team’ AND mt14.meta_value IN (‘442’) ) AND ( mt15.meta_key
    = ‘sp_team’ AND mt15.meta_value IN (‘462’) ) AND ( mt16.meta_key = ‘sp_team’ AND
    mt16.meta_value IN (‘399’) ) AND ( mt17.meta_key = ‘sp_team’ AND mt17.meta_value
    IN (‘461’) ) AND ( mt18.meta_key = ‘sp_t
    eam’ AND mt18.meta_value IN (‘460’) ) AND ( mt19.meta_key = ‘sp_team’ AND
    mt19.meta_value IN (‘459’) ) AND ( mt20.meta_key = ‘sp_team’ AND mt20.meta_value
    IN (‘457’) ) ) ) AND DrTT1_posts.post_type = ‘sp_event’ AND
    ((DrTT1_posts.post_status = ‘publish’ OR DrTT1_posts.post_status = ‘future’))
    GROUP BY DrTT1_posts.ID ORDER BY DrTT1_posts.post_date DESC

    Can u get a grip of it? It is always occuring when I try to look at some teams in my sportspress library. I think it has something to do with leagues. Because in the query above mentioned there are 20 things. My table is 20 teams big. The teams without the table on the screen are working fine, just the two teams where I was showing the table had those failures. Another info is, that I did not implement matches right now, so my table was all things at zero.

    I also tried reinstalling my whole WordPress, get a new database and start from scratch. After I was getting it the second time now and never had any problemes before I am realllly frustrating – as u can imagine.

    Can you help me? Maybe point me in the right direction as how to further debugg that problem? Can u check it, if there is a problem with tables that dont have any games played yet? I have zero problems on my site, exepct that one with sportspress…

    hope to hear from you, thank you for that awesome plugin and your work!

Viewing 9 replies - 16 through 24 (of 24 total)

  • this is the problem code:

    SELECT saeeposts . *
FROM saeeposts
LEFT JOIN saeeterm_relationships ON ( saeeposts.ID = saeeterm_relationships.object_id )
LEFT JOIN saeeterm_relationships AS tt1 ON ( saeeposts.ID = tt1.object_id )
INNER JOIN saeepostmeta ON ( saeeposts.ID = saeepostmeta.post_id )
INNER JOIN saeepostmeta AS mt1 ON ( saeeposts.ID = mt1.post_id )
INNER JOIN saeepostmeta AS mt2 ON ( saeeposts.ID = mt2.post_id )
INNER JOIN saeepostmeta AS mt3 ON ( saeeposts.ID = mt3.post_id )
INNER JOIN saeepostmeta AS mt4 ON ( saeeposts.ID = mt4.post_id )
INNER JOIN saeepostmeta AS mt5 ON ( saeeposts.ID = mt5.post_id )
INNER JOIN saeepostmeta AS mt6 ON ( saeeposts.ID = mt6.post_id )
INNER JOIN saeepostmeta AS mt7 ON ( saeeposts.ID = mt7.post_id )
INNER JOIN saeepostmeta AS mt8 ON ( saeeposts.ID = mt8.post_id )
INNER JOIN saeepostmeta AS mt9 ON ( saeeposts.ID = mt9.post_id )
INNER JOIN saeepostmeta AS mt10 ON ( saeeposts.ID = mt10.post_id )
WHERE 1 =1
AND (
saeeterm_relationships.term_taxonomy_id
IN ( 470 )
AND tt1.term_taxonomy_id
IN ( 468 )
)
AND (
(
saeepostmeta.meta_key = 'sp_format'
AND saeepostmeta.meta_value
IN (
'league'
)
)
AND (
(
mt1.meta_key = 'sp_team'
AND mt1.meta_value
IN (
'7896'
)
)
AND (
mt2.meta_key = 'sp_team'
AND mt2.meta_value
IN (
'12445'
)
)
AND (
mt3.meta_key = 'sp_team'
AND mt3.meta_value
IN (
'12446'
)
)
AND (
mt4.meta_key = 'sp_team'
AND mt4.meta_value
IN (
'12447'
)
)
AND (
mt5.meta_key = 'sp_team'
AND mt5.meta_value
IN (
'7904'
)
)
AND (
mt6.meta_key = 'sp_team'
AND mt6.meta_value
IN (
'12448'
)
)
AND (
mt7.meta_key = 'sp_team'
AND mt7.meta_value
IN (
'12449'
)
)
AND (
mt8.meta_key = 'sp_team'
AND mt8.meta_value
IN (
'7891'
)
)
AND (
mt9.meta_key = 'sp_team'
AND mt9.meta_value
IN (
'7898'
)
)
AND (
mt10.meta_key = 'sp_team'
AND mt10.meta_value
IN (
'12450'
)
)
)
)
AND saeeposts.post_type = 'sp_event'
AND (
(
saeeposts.post_status = 'publish'
OR saeeposts.post_status = 'future'
)
)
GROUP BY saeeposts.ID
ORDER BY saeeposts.post_date DESC

    How do I disable tie break?!

    Thanks for finding the temporary fix!

    In admin area

    Found it! Awesome! ??

    Disabling tie-break also worked.

    Roch

    (@rochesterj)

    Thanks a lot for this guys!

    We’ll keep looking into this.

    Thanks!

    Kind Regards,
    -Roch

    Plugin Author Brian

    (@brianmiyaji)

    @pa7rock @tgo_dc @erickfrench @gator8 @sealkat @estorus Thanks for all your feedback. This is turning out to be a pretty complicated issue, as there appears to be an infinite loop in some cases. The tiebreaker feature itself is a loop that is meant to iterate through teams that are tied to calculate the relative positions of those teams.

    I’ve patched up some code that may or may not be related to this bug. Here are the code changes on GitHub:
    https://github.com/ThemeBoy/SportsPress/commit/1e08cc9b7bc564f52ec1e55ee596175bb385ba37

    If anyone gets a chance to update the includes/class-sp-league-table.php file on your site, please let me know if the bug is still there. In the meantime, I’ll be testing additional use cases to see if the issue lies elsewhere.

    Thanks!

    • This reply was modified 7 years, 8 months ago by Brian.
    Plugin Author Brian

    (@brianmiyaji)

    A quick update on this one! I was able to pinpoint and fix this issue, which was caused by a combination of:

    a) Inefficient meta queries and
    b) The head-to-head comparison not limiting events to teams being compared

    The patch is available on GitHub, and will also be in the next update (version 2.2.11)

    Thanks for your feedback everyone! It has helped ??

    Plugin Contributor Savvas

    (@savvasha)

    Nice one! Thanks!!!! ??

    Roch

    (@rochesterj)

    You’re most welcome!

    Let us know if you need anything else.

    Kind Regards,
    -Roch

Viewing 9 replies - 16 through 24 (of 24 total)
  • The topic ‘Database Error with tables’ is closed to new replies.