Jump to content
Macrura

Repair/Optimize Database, Tables Crashing

Recommended Posts

I encountered a situation over the past few months where tables have been crashing when a user saves a page in PW.

I'm assuming it is something related to the server/hosting provider (Site5), because it only happens on this host, but across completely different unrelated accounts.

When it happens the table in question gets "marked as crashed", and then shows "in use" when you see the table in PHPMyAdmin.  No data is retrievable by PW from whichever table/field is crashed, so if the body table crashes, then the front end doesn't show any body text anymore until someone goes into PHPMyAdmin and repairs the table. I'm trying to make a module or at least some button the client can click from their admin that will run a repair on the tables so i don't have to help them and go to their cPanel etc..

I added a button on the dashboard of the sites in question (for sites that i use a dashboard on), or i told them to bookmark the link to the repair process, something like example.com/repair_database.php?action=repair; so far it seems to work but wanted to check to see if anyone sees any problems or improvements to this, it was done in only a few minutes, so may have left out something...

I'm not sure if this could/should be made into a module, since it is conceivable that a table could crash that would render the modules system non functional, so thought maybe better to be a bootstrapped script(?)

 

<?php

// in root of pw installation - this is the 3.0+ version; repair_database.php

/* Bootstrap PW
----------------------------------------- */
include("/home/path/to/index.php");

$config		= \ProcessWire\wire('config');
$user 		= \ProcessWire\wire('user');

if(!$user->isLoggedin()) die("access denied");


function optimizeTables() {

    $tables = array();

    $db = \ProcessWire\wire('db');
	$result = $db->query("SHOW TABLES");

    while ($row = $result->fetch_assoc()) {
        $tables[] = array_shift($row);
    }

    foreach ($tables as $table) {
        $result = $db->query("OPTIMIZE TABLE `$table`");
        while ($row = $result->fetch_assoc()) {
        	echo $row['Table'] . ': ' . $row['Msg_text'] . "<br /> \n";
        }
    }

}


function repairTables() {

    $tables = array();
    $db = \ProcessWire\wire('db');
	$result = $db->query("SHOW TABLES");

    while ($row = $result->fetch_assoc()) {
        $tables[] = array_shift($row);
    }

    foreach ($tables as $table) {

        $result = $db->query("REPAIR TABLE `$table`");
        while ($row = $result->fetch_assoc()) {
        	echo $row['Table'] . ': ' . $row['Msg_text'] . "<br /> \n";
        }

    }

}

?>

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Database Repair &amp; Optimize Tool</title>
</head>

<body>

<pre>
    ____                   _         ____        __        __
   / __ \___  ____  ____ _(_)____   / __ \____ _/ /_____ _/ /_  ____ _________
  / /_/ / _ \/ __ \/ __ `/ / ___/  / / / / __ `/ __/ __ `/ __ \/ __ `/ ___/ _ \
 / _, _/  __/ /_/ / /_/ / / /     / /_/ / /_/ / /_/ /_/ / /_/ / /_/ (__  )  __/
/_/ |_|\___/ .___/\__,_/_/_/     /_____/\__,_/\__/\__,_/_.___/\__,_/____/\___/
          /_/
</pre>


<?php

if($input->action == 'repair') {
     repairTables();
}


if($input->action == 'optimize') {
     optimizeTables();
}

?>



</body>
</html>

example button:

dashboard_rd.jpg.eb3f65a577fc5a3f6fefcaa5e3921a95.jpg

  • Like 2

Share this post


Link to post
Share on other sites

Hey @Macrura - not much help I know, but I thought I should mention that I have one site hosted with Site5 and haven't had any problems like this. 

PS - nice "Tracy Settings" link :)

  • Like 1

Share this post


Link to post
Share on other sites

@adrian - ok thanks, that's good to know;

Not sure what could be the cause of this, it happened on some 2.7.3 sites, as well as some 3.x sites.
Overall Site5 has been good in terms of speed, uptime and reliability; i submitted a support ticket regarding this issue but never really got any answer as to why it could happen... This is the error that happens to the editors when they save, if the table crashes..

SQLSTATE[HY000]: General error: 145 Table './database_name/field_body' is marked as crashed and should be repaired (in /wire/core/WireDatabasePDO.php line 454)

 

Share this post


Link to post
Share on other sites

I ended up turning this into a module, ProcessDatabaseRepair, though it also can also check and/or optimize the tables. 
So far works well, but since it interacts with database, i'd be worried about distributing the module.
If anyone in particular needs such a module, let me know.

59ecfed915cab_Database_Repair__ProcessWire__localhost.jpg.60c049d0993a04a2c78dd66295635e41.jpg

It's quicker for me to use this to optimize the tables than to login to client's PhpMyAdmin and run it there; also if in the rare event described above, any table crashes, the hope is that this will fix it (yet to encounter a situation where it can be tested in that scenario, unless there is a way to force crash a table)...

  • Like 3

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By DooM
      Hello guys,
      I'm trying to figure out how to sync fields and templates between staging and production environments.
      I've found Migrations module by Lostkobrakai, but with use of it all the fields and templates must be created by API, which is kind of uncomfortable.
      I also tried ProcessDatabaseBackups module which can export only certain tables, but I don't think it's the best practice to do that.
      How do you guys solve this problem? It's very annoying to setup everything three times (dev, staging, production).
      Thanks a lot :)
    • By iipa
      Hi everybody!
      I have been reading about Multisite, but it kinda bugs me that every topic talks about having both admin and database same for multiple sites.
      I have a project where customer tests it by adding content to the site, while I still need to do some changes here and there in code, maybe some in database. If something crashes for a while, customer can't keep testing, which is a bit problematic.
      Is there any way that I could have two separate versions of one site ("production" and development) that share the same database, but are otherwise independent? Just the thought of having to migrate database every time I want to show client something new gives me anxiety 😁
    • By Falk
      Hi!
      After temporarily using Module Image Extra, which I completely removed, I had some troubles with my imagefield (unused table columns).
      So I just imported a previous version of this column via PHPmyAdmin, which worked pretty well.
      Anyways, in Processwire Backend all image tags are gone, although they are OK in the database.
      Other image related things work (thumbnail, title etc are OK).
      Is there any way to recreate all the images or something? Or may this be an cache-related issue?
       
      Thanks in advance 😃
       
    • By anttila
      We have many booking calendars made with ProcessWire (own databases) and I want to do a web app (SQL) which allows user to log in. First, the user chooses the right calendar and then (s)he have to log in. The user can be from any of those calendars and the app is not running on ProcessWire (it can if necessary). So if there any way to make sure that the user has rights to the calendar (s)he tries to log in and if the password is correct.
      Is there any better way to do this? I could also use PIN codes or something, but those need to be encrypted too.
      Multiple ProcessWires A lot of users per ProcessWire Everyone can log in to the web app (when using right calendar)
    • By nuel
      Hi there
      Basically I want to call code within a ProcessWire page that isn't used as a template. Example: www.mypwpage.com/myphpfile.php
      I have a working PW Website with a couple of pages like /artists, /releases, /videos etc. Now I need a page /download without any editable fields in the backend, just calling some PHP code (that was coded by another guy) containing a form that checks unique download-codes in a second database and starts the download of the desired file. The script is working fine right now as part of a static website, but since I built PW behind the site, this independent «Download Section» of the page doesn't work anymore.
      Right now I have the main file download.php as a page template on a newly created empty page called /download, so until now the form is working (wow). After sending the form containing the download-code, the file check_code.php in a subfolder /site/templates/download is called and that's where I get an error.
      Any help?
×
×
  • Create New...