WordPress.org

Ready to get started?Download WordPress

Forums

wp_options table severely broken, already tried most (2 posts)

  1. Devstorm
    Member
    Posted 2 years ago #

    Hi,

    I have a severe problem with wp_options table I am stuck with and found no solution yet. I use WP 3.1.1 with WPML for multilanguage support.

    The problem:

    I develop on local VM, with the DNS mapped via hosts-file, so I can migrate the WP-DB 1:1, as often done before.
    The problem I am facing now is the wp_options table is broken. I cannot import the sqldump on the destination host.
    All posts are properly displayed, the sidebar menu and widgets do not work (from a first quick visual inspection)

    How can I track down which specific entries are problematic?
    I also tried exporting via mysqldump/mysql and phpMyAdmin. Importing on the destination live host is only possible via Webhoster-Interface phpMyAdmin.
    All other tables were imported without any problems.
    The WP-Options table I exported with all recommended options (backticks for table-identifiers, UTF-8 ex+import...), also tested in ANSI-SQL-Format and others and did a XML-export. All cannot be properly imported into the new DB. I got some more entries properly imported via XML-ex/import.

    It is NOT this error, which is easy to fix:
    https://wordpress.org/support/topic/error-establishing-a-database-connection-171

    But my understanding of the DB is: if it's inside, it should be able to get out of it, if the DB/table itself is not broken, only single entries.
    What do you propose? creating a script exporting each line or range of lines and importing on same server in different newly created table just for testing? then take the working entries and migrate?

    # SQL: CREATE TABLE wpcms_options_backup SELECT * FROM wpcms_options;

    This works fine on the same VM, can I migrate this via mysqld across the servers? This would presumably solve the problem.

    works on same server
    mysqldump --user=xxx --password="xxx" --max_allowed_packet=12M --default-character-set=utf8 [--add-drop-table] wpcms wpcms_options > table-wpcms_options.sql

    ## THEN TO IMPORT:
    mysql -v --user=xxx --password="xxx" --default-character-set=utf8 wpcms < table-wpcms_options.sql

    What is the best way to manage to migrate/regenerate the options-table on the live server?
    Deactivating all plugins (is complete plugin uninstallation also required?)? Are usually also other settings than plugin/theme-configurations inside this table?

    Then it is the problem of a plugin to track down, maybe it's just a serialized string missing a quote close or so.
    Is there some JSON-/syntax-checker available?

    Does it help or can it do any harm to include in wp-config:
    define(‘WP_ALLOW_REPAIR’, TRUE);

    on the development server (SRC)
    and/or the live server (DEST).

    AFAIK it only does: ANALYZE TABLE, REPAIR TABLE and OPTIMIZE TABLE
    I run backWPup with these options daily, so this should not be required AFAICT.

    I am stuck here and the timing is worst possible, I need to get the website up "yesterday".
    So any assistance is greatly appreciated.

    Another topic: is the plugin working?
    https://wordpress.org/extend/plugins/wp-migrate-db/

    I use it for several years now, without any problems (found), the benefit: It can also replace inside JSON-encoded/serialized data.

  2. cosmicdreammachine@gmail.com
    Member
    Posted 1 year ago #

    First just make sure it's not a CSS error or otherwise....

    If it is critical timing and it isn't an immensely modified website, creating a new fresh install of a clean wordpress, then importing the posts, postmeta, commnents, users, etc into the new installation... this way it is a clean options table with a fresh install..... I have done this when stuff was entirely broken before, only takes about an hour if you know your way around mySQL..... you need to make sure to avoid importing the old admin user from the original install if you create a new one in the table because it can screw up options.... this is my experience..

    Someone else could probably answer this question way better, the options table appears to be full of many encrypted values which are generated on installation or something, and I am uncertain how to modify them as well......, I tried to copy options from one blog to another and edited the corresponding information in the tables to match the correct addresses yet some other encrypted value in the options table created an error.......

Topic Closed

This topic has been closed to new replies.

About this Topic