Jump to content

Repair/Optimize Database, Tables Crashing

Macrura

By Macrura,
in Module/Plugin Development

 Share

Recommended Posts

Macrura Hero Member

Macrura

Posted
Posted

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
Link to comment
Share on other sites
Macrura Hero Member

Macrura

Posted
  • Author
Posted

@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)

 

Link to comment
Share on other sites
  • 2 months later...
Macrura Hero Member

Macrura

Posted
  • Author
Posted

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 4
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...