Table keeps getting locked

  • Resolved roshans89

    (@roshans89)


    4 years, 10 months ago

    Hello,

    I have the following queries that keeps running (after loggin queries)

    ALTER TABLEpool_wp_scorehistory_s1_t1CHANGEranking_idranking_idINT UNSIGNED NOT NULL, CHANGEscore_orderscore_orderINT UNSIGNED NOT NULL DEFAULT '0', CHANGEtypetypeTINYINT UNSIGNED NOT NULL DEFAULT '0', CHANGEsource_idsource_idINT UNSIGNED NOT NULL, CHANGEuser_iduser_idINT UNSIGNED NOT NULL, CHANGEscorescoreINT UNSIGNED NOT NULL, CHANGEfullfullSMALLINT UNSIGNED NOT NULL DEFAULT '0', CHANGEtotototoSMALLINT UNSIGNED NOT NULL DEFAULT '0', CHANGEgoal_bonusgoal_bonusSMALLINT UNSIGNED NOT NULL DEFAULT '0', CHANGEgoal_diff_bonusgoal_diff_bonusSMALLINT UNSIGNED NOT NULL DEFAULT '0', CHANGEtotal_scoretotal_scoreINT UNSIGNED NOT NULL DEFAULT '0', CHANGErankingranking` INT UNSIGNED NOT NULL

    CREATE TABLE IF NOT EXISTS pool_wp_scorehistory_s1_t1 ( ranking_id int(10) unsigned NOT NULL, score_order int(10) unsigned NOT NULL DEFAULT ‘0’, type tinyin`

    See Image:

    https://ibb.co/47hLKk6

    I have disabled all caches. The website becomes unresponsive. Any way to prevent this from running / any codes I can comment?

    Thanks
    Roshan

    The page I need help with: [log in to see the link]

Viewing 6 replies - 1 through 6 (of 6 total)
  • Thread Starter roshans89

    (@roshans89)

    4 years, 10 months ago

    I commented all sql queries in data/install.txt and data/updateXX.txt

    It seems to be OK

    Plugin Author AntoineH

    (@antoineh)

    4 years, 10 months ago

    These ALTER TABLE queries are triggered by a plugin update. The “ALTER TABLE” is from the v2.7.0 upgrade. Did you recently update from a lower version to the latest version (v2.9.5)? Or are these transactions older?

    If there is a lot of data in the scorehistory table, it is possible that your database server has trouble updating columns or indexes on the columns. If this is the case, then for future updates I would recommend to clean these tables first, then do the update and finally do a re-calc to fill the scorehistory again.

    It’s hard to tell what the current state of the database is. But when the update has not finished correctly, then you will eventually run into trouble because the code does not match with the database definition.

    My advice for next steps are first to try to create a backup of your database, then kill all these transactions and finally check your database definition. An easy way for this would be to export all “pool_wp_” tables to an SQL file and compare it to an SQL dump of a clean install of the plugin (install it somewhere else). Any differences should be easy to spot with a textual compare (e.g. in notepad++ or any other tool for this). When you know the differences, you can manually update your database definition. You may need some professional guidance if you do not have a lot of database experience.

    Thread Starter roshans89

    (@roshans89)

    4 years, 10 months ago

    Yes I updated from World Cup 2018 to this one.
    It is not able to update and create index because it already exists.
    The issue is that it keeps trying and it locks the table. It spawned almost 800 processes. My whole website was down.

    I solved the issued by deleting all sql queries in data/install.txt and data/update-XXX.txt

    Thanks
    Roshan

    • This reply was modified 4 years, 10 months ago by roshans89.
    Plugin Author AntoineH

    (@antoineh)

    4 years, 10 months ago

    But if you commented out all queries, then your database is not updated. And you miss some correct indexes on the tables, which will mean you miss the positive impact they bring on performance, and you’ll generate errors/warnings because the bonus questions table misses a column.

    If you send me a dump of your table structure (no data) I can help you to create an update script that you can run directly in your database.

    Thread Starter roshans89

    (@roshans89)

    4 years, 10 months ago

    Apparently the changes were already made. Really strange. That’s why I keep getting those errors on the index creation – because it already exists.
    It works fine – no worries.
    Thanks a lot.

    Plugin Author AntoineH

    (@antoineh)

    4 years, 10 months ago

    Okay, thanks for letting me know. Marking this as resolved.

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘Table keeps getting locked’ is closed to new replies.