Error establishing a database connection

  • Resolved neritondias

    (@neritondias)


    2 years, 11 months ago

    Good evening everyone!

    I’m having problems indexing the wp_postmeta table, this table has 960,000 records and when I click to index it the site goes down and loses the connection with the database, after updating the page the same comes back.

    If I run the command via WP-CLI it returns the error:

    Error: Error establishing a database connection.

    When updating the plugin page, the error appears:

    Problems Rekeying
    You cannot rekey some tables without resetting their keys first.

    This often means they have already been rekeyed by some other plugin or workflow.
    Reset Keys
    Reset the keys on these tables: remove the keys set by some other plugin or workflow.

    Select All
    Select None
    wp_postmeta has unexpected keys, so you cannot rekey it without resetting it first.
    ?The expected key PRIMARY KEY does not exist.
    ?The key meta_id exists when it should not.

    I already reset the key but trying to index again gives the same error. =/

    My server is one with 128GB of ram and 48 cores.

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author OllieJones

    (@olliejones)

    Sorry you’re having trouble.

    It sure sounds like your database server crashed. Could you please go to the About tab in the plugin and push the Upload Metadata button?

    Does anything in wp-cli work? wp core version is a super-simple command. wp index-mysql status is almost as simple.

    If so, try wp index-mysql tables and show the output in a reply. Also show the upload id from when you uploaded metadata.

    Also, ples

    Thread Starter neritondias

    (@neritondias)

    Hello friend, thanks for replying.

    Below are the outputs

    wp –allow-root core version

    5.8.2

    wp –allow-root index-mysql status

    Index WP MySQL For Speed 1.3.3
Versions MySQL:8.0.27-0ubuntu0.20.04.1 WordPress:5.8.2 php:7.4.26
Add high-performance keys to these tables to make your WordPress database faster.
Use this command: wp index-mysql enable wp_postmeta

Your WordPress tables now have high-performance keys. Revert the keys on these tables to restore WordPress's defaults.
Use this command: wp index-mysql disable wp_usermeta wp_termmeta wp_options wp_posts wp_comments

    wp –allow-root index-mysql tables

    Index WP MySQL For Speed 1.3.3
Versions MySQL:8.0.27-0ubuntu0.20.04.1 WordPress:5.8.2 php:7.4.26
+----------+--------+--------+-------------+--------------+----------------+--------+------------+------------------------+
| table    | prefix | count  | distinct_id | distinct_key | autoload_count | engine | row_format | collation              |
+----------+--------+--------+-------------+--------------+----------------+--------+------------+------------------------+
| comments | wp_    | 98     | 95          |              |                | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
| options  | wp_    | 766    | 764         |              | 576            | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
| postmeta | wp_    | 591791 | 591056      | 1676         |                | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
| posts    | wp_    | 76365  | 76266       |              |                | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
| termmeta | wp_    | 0      |             | 0            |                | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
| usermeta | wp_    | 256    | 7           | 82           |                | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
| users    | wp_    | 7      |             |              |                | InnoDB | Dynamic    | utf8mb4_unicode_520_ci |
+----------+--------+--------+-------------+--------------+----------------+--------+------------+------------------------+
+-------------+------------------------------+------------------------------------------------------------------------------------------------------+
| table       | key                          | definition                                                                                           |
+-------------+------------------------------+------------------------------------------------------------------------------------------------------+
| wp_postmeta | PRIMARY KEY                  | ADD PRIMARY KEY (meta_id)                                                                            |
| wp_postmeta | meta_key                     | ADD KEY meta_key (meta_key(191))                                                                     |
| wp_postmeta | post_id                      | ADD KEY post_id (post_id)                                                                            |
| wp_usermeta | PRIMARY KEY                  | ADD PRIMARY KEY (user_id, meta_key, umeta_id)                                                        |
| wp_usermeta | umeta_id                     | ADD UNIQUE KEY umeta_id (umeta_id)                                                                   |
| wp_usermeta | meta_key                     | ADD KEY meta_key (meta_key, user_id)                                                                 |
| wp_termmeta | PRIMARY KEY                  | ADD PRIMARY KEY (term_id, meta_key, meta_id)                                                         |
| wp_termmeta | meta_id                      | ADD UNIQUE KEY meta_id (meta_id)                                                                     |
| wp_termmeta | meta_key                     | ADD KEY meta_key (meta_key, term_id)                                                                 |
| wp_options  | PRIMARY KEY                  | ADD PRIMARY KEY (autoload, option_id)                                                                |
| wp_options  | option_id                    | ADD UNIQUE KEY option_id (option_id)                                                                 |
| wp_options  | option_name                  | ADD UNIQUE KEY option_name (option_name)                                                             |
| wp_posts    | PRIMARY KEY                  | ADD PRIMARY KEY (ID)                                                                                 |
| wp_posts    | post_author                  | ADD KEY post_author (post_author, post_type, post_status, post_date, ID)                             |
| wp_posts    | post_name                    | ADD KEY post_name (post_name(191))                                                                   |
| wp_posts    | post_parent                  | ADD KEY post_parent (post_parent)                                                                    |
| wp_posts    | type_status_date             | ADD KEY type_status_date (post_type, post_status, post_date, post_author, ID)                        |
| wp_comments | PRIMARY KEY                  | ADD PRIMARY KEY (comment_ID)                                                                         |
| wp_comments | comment_approved_date_gmt    | ADD KEY comment_approved_date_gmt (comment_approved, comment_date_gmt)                               |
| wp_comments | comment_author_email         | ADD KEY comment_author_email (comment_author_email(10))                                              |
| wp_comments | comment_date_gmt             | ADD KEY comment_date_gmt (comment_date_gmt)                                                          |
| wp_comments | comment_parent               | ADD KEY comment_parent (comment_parent)                                                              |
| wp_comments | comment_post_ID              | ADD KEY comment_post_ID (comment_post_ID)                                                            |
| wp_comments | comment_post_parent_approved | ADD KEY comment_post_parent_approved (comment_post_ID, comment_parent, comment_approved, comment_ID) |
| wp_comments | woo_idx_comment_type         | ADD KEY woo_idx_comment_type (comment_type)                                                          |
+-------------+------------------------------+------------------------------------------------------------------------------------------------------+

    I tried several times to index wp_postmeta and it always gives mysql error.

    Error establishing a database connection

    Thread Starter neritondias

    (@neritondias)

    @olliejones Can you help me friend?

    Plugin Author OllieJones

    (@olliejones)

    I confess this is the first time I have seen an “error establishing a database connection” when trying to reindex wp_postmeta. And, the plugin has handled reindexing of that table with over a million rows in other sites. So, this is a new one for me.

    Please upload your site’s metadata. Give the command wp index-mysql upload_metadata. And please reply here with the upload id.

    Your wp index-mysql tables output tells me your wp_postmeta table has WordPress’s standard keys. Did you restore it from backup?

    It’s possible your host OS doesn’t have enough space in its /tmp filesystem to do the reindexing. Please see this. https://www.ads-software.com/support/topic/temporary-file-write-failure/

    If you have access to a MySQL client program, like phpmyadmin or the mysql command line program, you can try giving this SQL statement to do the reindexing.

    ALTER TABLE wp_postmeta 
DROP PRIMARY KEY, ADD PRIMARY KEY (post_id, meta_key, meta_id), 
DROP KEY meta_key, ADD KEY meta_key (meta_key, meta_value(32), post_id), 
DROP KEY post_id, 
ADD UNIQUE KEY meta_id (meta_id), 
ADD KEY meta_value (meta_value(32), meta_key, post_id);
    • This reply was modified 2 years, 11 months ago by OllieJones.
    Thread Starter neritondias

    (@neritondias)

    Thank you very much for the reply friend!

    I ran the query in phpmyadmin and it returned the following error:

    Query SQL:

ALTER TABLE wp_postmeta 
DROP PRIMARY KEY, ADD PRIMARY KEY (post_id, meta_key, meta_id), 
DROP KEY meta_key, ADD KEY meta_key (meta_key, meta_value(32), post_id), 
DROP KEY post_id, 
ADD UNIQUE KEY meta_id (meta_id), 
ADD KEY meta_value (meta_value(32), meta_key, post_id)

Mensagem do MySQL: Documenta??o
#2006 - MySQL server has gone away
    Plugin Author OllieJones

    (@olliejones)

    Holy cow! The ALTER TABLE ... ADD KEY statement really does crash your MySQL server. SQL statements, even complex data definition language (DDL) statements like mine, should not crash the server. Ever. There are servers running in the world with uptimes measured in years.

    Your MySQL server must be misconfigured. You may find some useful advice here. https://dev.mysql.com/doc/refman/8.0/en/crashing.html

    Also (I pointed this out before) your server’s OS may have a /tmp file system that’s much too small for its purpose. https://www.ads-software.com/support/topic/temporary-file-write-failure/

    I wish I could help you further. But I fear I cannot.

    Percona offers a free and open source toolkit. Their pt-mysql-summary tool can find many misconfigurations.

    • This reply was modified 2 years, 11 months ago by OllieJones.
    • This reply was modified 2 years, 11 months ago by OllieJones.
Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Error establishing a database connection’ is closed to new replies.