WordPress.org

Make WordPress Core

Opened 2 years ago

Last modified 3 months ago

#22884 new enhancement

What if I delete wp_posts table from database.

Reported by: mukkundthanki Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 3.5
Component: Database Keywords: dev-feedback
Focuses: Cc:

Description

I am not a experienced or mature programmer but I have seen it While I was playing with database of WordPress.

I have deleted wp_posts table in database in WordPress - 3.5(beta).

It looks normal on other pages at admin side but when i open single post it shows post-new.php and when hit save, it shows "wp_error" with this message "You are not allowed to edit this post."

That's OK. I was expecting error but not this type of error.

It seems WordPress doesn't know that its most important table has gone away and that's why admin may not know( well for a while). I think its not good. well I don't know its issue or it is way it behaves with database and its normal but I think WordPress should know what exactly happened.

when I have updated WordPress with stable 3.5 it has updated but database is still same which is normal.

Should we( can we )track tables( some of tables which are important ) by PHP ?

Attachments (2)

22884-handle_error.diff (3.3 KB) - added by jipmoors 3 months ago.
handle_error added to wp-db.php
22884-handle_error-2.diff (3.5 KB) - added by jipmoors 3 months ago.
moving require outside of the loop + improving readability

Download all attachments as: .zip

Change History (13)

comment:1 @knutsp2 years ago

  • Cc knut@… added
  • Keywords close added

Joking here? If not ...

And if you have the posts table, but have removed a column or two from it? I guess it's quite unpredictable and I wouldn't expect it to be otherwise.

I guess that when WordPress is updated, and there are schema changes, this could be detected and the table created and/or fixed.

I think that id you drop the options table, then WordPress will think it's not installed. Try that, and WordPress will recreate the posts table.

To check if someone has tampered with your database schema since installed, I guess a plugin could be handy.

comment:2 @nacin2 years ago

  • Keywords close removed
  • Type changed from defect (bug) to enhancement

What if I delete wp_posts table from database.

I found that unintentionally hilarious. Beyond that, though, you have a good enhancement request.

WordPress currently checks for the options table on startup. See is_blog_installed(). If the options table is missing, it checks to see if any other tables are missing. If they are *all* missing, it assumes you haven't installed WordPress yet. If only some are missing, it assumes things are "insane" and issues a repair warning and dies with dead_db().

We *could* do similar checks throughout the admin. Check posts and postmeta on edit.php/post.php, etc. I don't think it is truly necessary, but it's a good thought.

Last edited 2 years ago by nacin (previous) (diff)

comment:3 @markjaquith2 years ago

Shouldn't WPDB throw an error if you try to query a table that does not exist?

comment:4 @Ipstenu2 years ago

Mark, maybe that actually is the first time you try to query that table? The only place I would think it might otherwise show up is on the post lists page, or the main dashboard when it queries post numbers (though that may default to 0 if none are found).

How much would running this check impact performance? We don't want to slow WP Admin down even more.

comment:5 @mukkundthanki2 years ago

yes mark. I think it should. I have found that when I hit save it checks if this post exists or not with "get_post" and "get_instance" and at last "get_row" method of WPDB class. so "get_instance" returns false to "get_post" and it returns null and finally die with capability check with user and thats why we get "You are not allowed to edit this post." error. well I think it should be died earlier while checking with db. can we alter only db methods with this type of checks like table exists or what actually returns and then taking further actions with specific results.

Thanks nacin. I have seen what you have stated and its really useful. well My thought is about making WordPress realize what actually happened out there, may be it gives more load to admin and affect performance.

Last edited 2 years ago by mukkundthanki (previous) (diff)

comment:6 @wonderboymusic10 months ago

  • Keywords needs-patch added; dev-feedback removed

comment:7 @JasonM456310 months ago

Would it make sense to set up a SQL trigger to detect schema changes? Then have WPDB check for a changes (via a checksum), trigger a comparison and take appropriate action.

@jipmoors3 months ago

handle_error added to wp-db.php

comment:8 follow-up: @jipmoors3 months ago

  • Keywords needs-testing has-patch added; needs-patch removed

I have added a function to handle specific errors from the SQL server.

Implemented a check for the Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE) error.
If the table does not exist, it will check if any WP tables are missing and if so running dbDelta to recreate them.

The part that I don't 100% like is the include of the upgrade.php and dbDelta calls.

Open for ideas/suggestions/fixes.

comment:9 in reply to: ↑ 8 @SergeyBiryukov3 months ago

Replying to jipmoors:

The part that I don't 100% like is the include of the upgrade.php and dbDelta calls.

require() is probably fine, but it should not be done inside the foreach() loop.

@jipmoors3 months ago

moving require outside of the loop + improving readability

comment:10 follow-up: @pento3 months ago

I love 22884-handle_error-2.diff for the insanity/audacity of silently rebuilding tables on the fly. :-)

That said, I don't think this is quite the right way to handle it. It seems like there would be significant cases where we don't want to rebuild tables automatically.

  • If wp_posts disappears, do we really want to create a new table without giving the chance to restore a backup? This won't help the front end of the site - a database error vs. no content is not really a useful fix. It may hinder in some ways - an uptime monitoring script will probably notice the DB error, but will possibly miss having no content.
  • If wp_users disappears, we can't create an empty users table without prompting the admin to re-create their admin account.
  • If wp_options disappears, we'd be resetting all the site options if we recreated it, which would also include disabling security or custom login plugins the user may have installed.

Now, I'm wondering if there's a place for only doing this check in wp-admin, and modifying maint/repair.php to handle missing tables. Losing tables is a pretty significant (and hopefully rare!) occurrence, I think we can afford to put an extra hurdle into repairing the situation.

comment:11 in reply to: ↑ 10 @jipmoors3 months ago

  • Keywords dev-feedback added; needs-testing has-patch removed

Replying to pento:
That was kind of the underlaying thing when I mentioned that I did not feel good about putting the require once and dbDelta at that spot. Thanks for putting it into words more clearly and pointing out those critical dependencies.

The missing wp_options table is caught in the is_blog_installed code, where the suppress_errors() would disable the recreation of tables.

In my research to the problem I did first try to use repair.php functionality to repair the tables, but since they are missing and not broken it obviously didn't work. Which got me wondering if the way this is used in is_blog_installed is kind of minimal and should be expanded for more specific problems and exceptions.

Requesting feedback on:

Could somebody argument for me that it's ok to use dbDelta to try and fix any and all problems that might be there, or should there be more splitting in the definition of SQL/Tables. The only options there are now is global or blog/multisite inside wp_get_db_schema().

I just don't know what the possible problems might be when you have a missing table and a broken table when running dbDelta.

Last edited 3 months ago by jipmoors (previous) (diff)
Note: See TracTickets for help on using tickets.