WordPress.org

Ready to get started?Download WordPress

Forums

Please help! Error establishing a database connection (8 posts)

  1. daniel7rusu
    Member
    Posted 2 years ago #

    I installed Better WP Security, which changed the prefix of the tables from wp_ to 74e5_. Now when going to http://www.lehmlaw.com/, I get "Error establishing a database connection". Going to http://www.lehmlaw.com/wp-admin/ gives "One or more database tables are unavailable. The database may need to be repaired." Repairing does nothing.

    I checked the wp-config file and all information is right, including "$table_prefix = '74e5_';".

    Is there another setting that needs to be changed from wp_ to 74e5_? What's wrong?

    Thanks!

  2. Tara
    Member
    Posted 2 years ago #

    -First, try contacting the hosting provider if the database server is online / if they have any other problem at their end, or they made any changes to your account, or if there are any limitations/restrictions, etc .

    - double check your wp-config.php file settings for the database name, database username and database password. This is where most errors occur.

    -Then check that you don't need to change the database host from "localhost" to something else. You can get that info from your hosting provider.

    -Check you have actually created a database with the same database name as is in your wp-config.php file.

    -Last, but not least, if all that information is correct your database probably has a problem with it, and you may need to contact your hosting provider.

    There are also many threads on this topic on these forums: http://wordpress.org/search/Error+establishing+a+database+connection?forums=1

  3. daniel7rusu
    Member
    Posted 2 years ago #

    Thanks for the reply, but I checked all those things and that's not where the problem lies.

    The problem, as far as I see, is the plugin changed the prefix to all the tables from wp_ to 74e5_. It changed it in the database tables, and in the wp-config file. If I change the wp-config file "$table_prefix to "$table_prefix = 'wp_';", it now wants to install WP.

    I'm thinking there's a problem in the DB itself, anyone every experience this?

  4. mistaecko
    Member
    Posted 2 years ago #

    I experienced the very same problem just recently (while the feature had worked well for me before).

    Interestingly it happened with a very similar prefix (7e59).

    It failed at some point during the name change process AFTER renaming all tables and AFTER setting the new prefix in wp-config.php.

    My guess is that the chosen prefix is problematic because it is interpreted by mysql as a hex number.

    So while I describe the steps that might make the rename process complete below, it might very well turn out to not fix the problem. You would then have to change to a different prefix altogether (maybe one that does not contain any numbers at all)

    You can try to fix it following these steps

    (1)
    Make sure that all your table names have been changed to the new prefix.
    Make also sure that the prefix setting in wp-config.php has been updated to the new prefix value.
    In my case these steps actually completed successfully.

    (2) Fix PREFIX_options table

    UPDATE XXXX_options SET option_name = REPLACE(option_name, 'wp_','XXXX_') WHERE option_name LIKE '%user_roles%';

    (3) Fix XXX_usermeta table

    UPDATE XXXX_usermeta SET meta_key = REPLACE(meta_key, 'wp_','XXXX_') WHERE meta_key LIKE 'wp_%';

    Note: change XXX to your prefix
    credits to http://tdot-blog.com/wordpress/6-simple-steps-to-change-your-table-prefix-in-wordpress

    I guess you have solved the problem by now anyway since 2 weeks have gone by, but maybe this can help others.

    mistaecko

  5. mistaecko
    Member
    Posted 2 years ago #

    Btw, it seems the author of this plugin is currently traveling the world ;) see http://wordpress.org/support/topic/plugin-better-wp-security-changing-table-prefix-kills-wp-admin

    But the link in my previous post details all steps involved in renaming the wp_ prefix manually.

    So any rescue attempt should check all these steps and fix those that didn't go through.

    I recommend to *not* use this feature for now, especially on production systems and if you are not familiar with SQL.

    mistaecko

  6. mistaecko
    Member
    Posted 2 years ago #

    After looking into this problem more I can confirm that there is a bug in the plugin.

    *Some* of the generated prefix strings will get mysql confused and break the process, leaving the database in an inconsistent state: the table prefixes will have been renamed, but some values in the options tables not.

    This applies at least to patterns that start with a number and include an 'e'. This is the scientific notation for exponential numbers, e.g. 74e5. There might be other patterns (e.g. hex like prefixes) that could cause problems as well.

    I have informed the author of the plugin about this bug.

    To fix a broken rename process please see my previous post but make sure you use backticks around the table names:

    UPDATE `XXXX_options` SET option_name = REPLACE(option_name, 'wp_','XXXX_') WHERE option_name LIKE '%user_roles%';
    
    UPDATE `XXXX_usermeta` SET meta_key = REPLACE(meta_key, 'wp_','XXXX_') WHERE meta_key LIKE 'wp_%';

    The bug in the plugin is due to missing these backticks in four SQL statements in database.php, namely in line 67, 74, 85, 90.

    A fixed version of the database.php file (plugin v2.10) can be found here: https://gist.github.com/1421722

    This version should work with ANY prefix, and is tested against 74e5 and works!

  7. Euthenics
    Member
    Posted 2 years ago #

    Hi

    Need some help and guidance. After changing the prefix, now all website including admin access cannot be access anymore. Getting some kind of page cannot be directed error in the browser.

    I'm not so familiar with MySQL, tried the code you provided above but getting some #1064 - 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 '7e4x__options SET option_name = REPLACE(option_name, 'wp_','7e4x_') WHERE ' at line 1.

    I only have the full database (complete) backup using buddybackup but since I cannot even access my site, can't really use its restoration.
    function.

    Would you be able to assist how to get it recover back?
    How does one use the replace prefix function in the SQL tab? I saw the guide but don't really quite undertand it. Any good reference where I can learn up on this?

    Right now, my site is completely not accessible. Any idea on how to resolve this?

    Thank you in advance.

  8. Euthenics
    Member
    Posted 2 years ago #

    Managed to get my db restored back. The prefix change function definitely has some bug in it. Not recommended to use it now until the developer fix it.

Topic Closed

This topic has been closed to new replies.

About this Topic