Options table both revert and convert, and syntax error

  • Resolved mnkhalid

    (@mnkhalid)


    2 years, 1 month ago

    My wo_options table is present both in revet key and convert key, and when i try to either convert or revert using the plugin page or wp-cli i get the following error,

    “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘UNIQUE, DROP KEY INDEX”

    So I did a –dry-run and found the Query and Put it on my workbench still the same error.

    ALTER TABLE wp_options ADD UNIQUE KEY option_name (option_name), DROP KEY option_id, DROP KEY UNIQUE, DROP KEY INDEX;

    the error goes if i remove “DROP KEY UNIQUE, DROP KEY INDEX” from the query.

Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter mnkhalid

    (@mnkhalid)

    So I manually dropped the two indexes “UNIQUE” and “INDEX” using mysql workbench and run the cli command for enabled and it worked successfully.

    Plugin Author OllieJones

    (@olliejones)

    Thanks for the bug report. Could you possibly upload your metadata. Visit Tools > Index MySQL > About.

    Plugin Author OllieJones

    (@olliejones)

    It seems possible your indexes had strange or missing index names.

    Here’s the defect report. https://github.com/OllieJones/index-wp-mysql-for-speed/issues/50

    Plugin Author rjasdfiii

    (@rjasdfiii)

    If possible, provide the output from SHOW CREATE TABLE wp_options;

    Thread Starter mnkhalid

    (@mnkhalid)

    @olliejones uploaded the metadata with id – FKYvvgC6

    @rjasdfiii here is the output for

    wp_GD3z9A_drm_in_ is my custom table prefix (ofcourse not sharing the original, but formatted kind of like this, i have just changed the characters) in case that is relevant.

    CREATE TABLEwp_GD3z9A_drm_in_options` (
    option_id bigint unsigned NOT NULL AUTO_INCREMENT,
    option_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ”,
    option_value longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
    autoload varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ‘yes’,
    PRIMARY KEY (option_name),
    UNIQUE KEY option_id (option_id),
    KEY autoload (autoload)
    ) ENGINE=InnoDB AUTO_INCREMENT=17047358 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci`

    Plugin Author OllieJones

    (@olliejones)

    Thanks for the information. Your options table looks right.

    One thing: you may have a performance problem with your MySQL server. One of our metadata items measures the time taken by the simplest possible MySQL query (SELECT 1). This takes 12.8 milliseconds in your environment. That is painfully slow. It probably means your MySQL server, or the network between your web server and MySQL, is overloaded.

    Under two milliseconds would be much better. If I were you I would contact the hosting provider and ask for help with that.

    • This reply was modified 2 years, 1 month ago by OllieJones.
    Thread Starter mnkhalid

    (@mnkhalid)

    thank you for the heads up on mysql. I am currently using a 2vCPU + 8GB Ram instance on Amazon Lightsail along with a 1 vCPU + 2GB Ram Lightsail Managed Database.

    I get about 1000 visitors per day with about 30-40 orders. I am considering moving to a local VPS provider offering 6 core 15 gb and the SELECT 1 query running on workbench gave me the following results.

    Lightsail managed database – 16 miliseconds

    VPS (6 core + 15 gb) with mysql running on localhost currently at almost no traffic. 15 miliseconds.

    i couldnt find anything on the internet on this. I’ll look for more.

    thanks again for the completely new information.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Options table both revert and convert, and syntax error’ is closed to new replies.