phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Does my database have all the required fields?


 
Search this topic... | Search Knowledge Base (KB)... | Search Box
Register or Login to Post    Index » Knowledge Base (KB)  Previous TopicPrint TopicNext Topic
Author Message
espicom
Board Member



Joined: 24 Nov 2008

Posts: 55
Location: Woodstock, IL


flag
PostPosted: Tue Jan 13, 2009 7:52 pm 
Post subject: Does my database have all the required fields?

Sometimes, you get errors after software updates that can be confusing. You might get "unknown column XXXX in table YYYY", or simply have settings in the Admin Control Panel (ACP) that you cannot change... they keep coming back to what they were before you changed them.

There is a script to validate your database structure and templates against the requirements for version 2.0.23. It currently is only works completely with MySQL, but that covers 80% or more of the phpBB installations on internet, so...

The link to download it is http://espi.com/text/validate_phpbb_v23.php.txt

RIGHT click on the link above, and select "save as". Save it to your local disk. You then upload it as "validate_phpbb_v23.php" to your PHPBB directory, and run it with your browser. The results will tell you what fields and/or tables are missing. It also checks a few features of your PHP installation that can affect how phpBB or certain MODs operate.

If anything shows up in red, other than the database version number, copy the "bbcode version" of the report from the text box at the bottom of the page and paste into the topic you opened for your problem (not inside a QUOTE or CODE tag, and NOT as a reply to this message, please!). That will give whoever is helping you some idea of what may or may not be contributing to your problems.

If you prefer, here is the script in a format you can select, copy to your computer's clipboard, and paste into a TEXT editor, to save it to your system, as described above:


Code:
<?php
// output string
$output_raw = "";
// initialization of arrays
$tables = array(
   'auth_access' => "Missing", 'user_group' => "Missing", 'groups' => "Missing", 'banlist' => "Missing", 'categories' => "Missing",
   'config' => "Missing", 'confirm' => "Missing", 'disallow' => "Missing", 'forum_prune' => "Missing", 'forums' => "Missing", 'posts' => "Missing",
   'posts_text' => "Missing", 'privmsgs' => "Missing", 'privmsgs_text' => "Missing", 'ranks' => "Missing", 'search_results' => "Missing",
   'search_wordlist' => "Missing", 'search_wordmatch' => "Missing", 'sessions' => "Missing", 'sessions_keys' => "Missing",
   'smilies' => "Missing", 'themes' => "Missing", 'themes_name' => "Missing", 'topics' => "Missing", 'topics_watch' => "Missing",
   'users' => "Missing", 'vote_desc' => "Missing", 'vote_results' => "Missing", 'vote_voters' => "Missing", 'words' => "Missing");

$table_fields = array(
'auth_access' => array(
   'group_id' => "Missing",'forum_id' => "Missing",'auth_view' => "Missing",'auth_read' => "Missing",
   'auth_post' => "Missing",'auth_reply' => "Missing",'auth_edit' => "Missing",'auth_delete' => "Missing",'auth_sticky' => "Missing",
   'auth_announce' => "Missing",'auth_vote' => "Missing",'auth_pollcreate' => "Missing",'auth_attachments' => "Missing",
   'auth_mod' => "Missing"),
'user_group' => array(
   'group_id' => "Missing",'user_id' => "Missing",'user_pending' => "Missing"),
'groups' => array(
   'group_id' => "Missing",'group_type' => "Missing",'group_name' => "Missing",'group_description' => "Missing",
   'group_moderator' => "Missing",'group_single_user' => "Missing"),
'banlist' => array(
   'ban_id' => "Missing", 'ban_userid' => "Missing", 'ban_ip' => "Missing", 'ban_email'),
'categories' => array(
   'cat_id' => "Missing", 'cat_title' => "Missing", 'cat_order' => "Missing"),
'config' => array(
   'config_name' => "Missing", 'config_value' => "Missing"),
'confirm' => array(
   'confirm_id' => "Missing", 'session_id' => "Missing", 'code' => "Missing"),
'disallow' => array(
   'disallow_id' => "Missing", 'disallow_username' => "Missing"),
'forum_prune' => array(
   'prune_id' => "Missing", 'forum_id' => "Missing", 'prune_days' => "Missing", 'prune_freq' => "Missing"),
'forums' => array(
   'forum_id' => "Missing", 'cat_id' => "Missing", 'forum_name' => "Missing", 'forum_desc' => "Missing",
   'forum_status' => "Missing",    'forum_order' => "Missing", 'forum_posts' => "Missing", 'forum_topics' => "Missing",
   'forum_last_post_id' => "Missing", 'prune_next' => "Missing",    'prune_enable' => "Missing", 'auth_view' => "Missing",
   'auth_read' => "Missing", 'auth_post' => "Missing", 'auth_reply' => "Missing", 'auth_edit' => "Missing", 'auth_delete' => "Missing",
   'auth_sticky' => "Missing", 'auth_announce' => "Missing", 'auth_vote' => "Missing", 'auth_pollcreate' => "Missing",
   'auth_attachments' => "Missing"),
'posts' => array(
   'post_id' => "Missing", 'topic_id' => "Missing", 'forum_id' => "Missing", 'poster_id' => "Missing", 'post_time' => "Missing",
   'poster_ip' => "Missing", 'post_username' => "Missing", 'enable_bbcode' => "Missing", 'enable_html' => "Missing", 'enable_smilies' => "Missing",
   'enable_sig' => "Missing", 'post_edit_time' => "Missing", 'post_edit_count' => "Missing"),
'posts_text' => array(
   'post_id' => "Missing", 'bbcode_uid' => "Missing", 'post_subject' => "Missing", 'post_text' => "Missing"),
'privmsgs' => array(
   'privmsgs_id' => "Missing", 'privmsgs_type' => "Missing", 'privmsgs_subject' => "Missing",
   'privmsgs_from_userid' => "Missing", 'privmsgs_to_userid' => "Missing", 'privmsgs_date' => "Missing", 'privmsgs_ip' => "Missing",
   'privmsgs_enable_bbcode' => "Missing", 'privmsgs_enable_html' => "Missing", 'privmsgs_enable_smilies' => "Missing",
   'privmsgs_attach_sig' => "Missing"),
'privmsgs_text' => array(
   'privmsgs_text_id' => "Missing", 'privmsgs_bbcode_uid' => "Missing", 'privmsgs_text' => "Missing"),
'ranks' => array(
   'rank_id' => "Missing", 'rank_title' => "Missing", 'rank_min' => "Missing", 'rank_special' => "Missing", 'rank_image' => "Missing"),
'search_results' => array(
   'search_id' => "Missing", 'session_id' => "Missing", 'search_array' => "Missing", 'search_time' => "Missing"),
'search_wordlist' => array(
   'word_text' => "Missing", 'word_id' => "Missing", 'word_common' => "Missing"),
'search_wordmatch' => array(
   'post_id' => "Missing", 'word_id' => "Missing", 'title_match' => "Missing"),
'sessions' => array(
   'session_id' => "Missing", 'session_user_id' => "Missing", 'session_start' => "Missing", 'session_time' => "Missing",
   'session_ip' => "Missing", 'session_page' => "Missing", 'session_logged_in' => "Missing", 'session_admin' => "Missing"),
'sessions_keys' => array(
   'key_id' => "Missing", 'user_id' => "Missing", 'last_ip' => "Missing", 'last_login' => "Missing"),
'smilies' => array(
   'smilies_id' => "Missing", 'code' => "Missing", 'smile_url' => "Missing", 'emoticon' => "Missing"),
'themes' => array(
   'themes_id' => "Missing", 'template_name' => "Missing", 'style_name' => "Missing", 'head_stylesheet' => "Missing",
   'body_background' => "Missing", 'body_bgcolor' => "Missing", 'body_text' => "Missing", 'body_link' => "Missing", 'body_vlink' => "Missing",
   'body_alink' => "Missing", 'body_hlink' => "Missing", 'tr_color1' => "Missing", 'tr_color2' => "Missing", 'tr_color3' => "Missing",
   'tr_class1' => "Missing", 'tr_class2' => "Missing", 'tr_class3' => "Missing", 'th_color1' => "Missing", 'th_color2' => "Missing",
   'th_color3' => "Missing", 'th_class1' => "Missing", 'th_class2' => "Missing", 'th_class3' => "Missing", 'td_color1' => "Missing",
   'td_color2' => "Missing", 'td_color3' => "Missing", 'td_class1' => "Missing", 'td_class2' => "Missing", 'td_class3' => "Missing",
   'fontface1' => "Missing", 'fontface2' => "Missing", 'fontface3' => "Missing", 'fontsize1' => "Missing", 'fontsize2' => "Missing",
   'fontsize3' => "Missing", 'fontcolor1' => "Missing", 'fontcolor2' => "Missing", 'fontcolor3' => "Missing", 'span_class1' => "Missing",
   'span_class2' => "Missing", 'span_class3' => "Missing", 'img_size_poll' => "Missing", 'img_size_privmsg' => "Missing"),
'themes_name' => array(
   'themes_id' => "Missing", 'tr_color1_name' => "Missing", 'tr_color2_name' => "Missing", 'tr_color3_name' => "Missing",
   'tr_class1_name' => "Missing", 'tr_class2_name' => "Missing", 'tr_class3_name' => "Missing", 'th_color1_name' => "Missing",
   'th_color2_name' => "Missing", 'th_color3_name' => "Missing", 'th_class1_name' => "Missing", 'th_class2_name' => "Missing",
   'th_class3_name' => "Missing", 'td_color1_name' => "Missing", 'td_color2_name' => "Missing", 'td_color3_name' => "Missing",
   'td_class1_name' => "Missing", 'td_class2_name' => "Missing", 'td_class3_name' => "Missing", 'fontface1_name' => "Missing",
   'fontface2_name' => "Missing", 'fontface3_name' => "Missing", 'fontsize1_name' => "Missing", 'fontsize2_name' => "Missing",
   'fontsize3_name' => "Missing", 'fontcolor1_name' => "Missing", 'fontcolor2_name' => "Missing", 'fontcolor3_name' => "Missing",
   'span_class1_name' => "Missing", 'span_class2_name' => "Missing", 'span_class3_name' => "Missing"),
'topics' => array(
   'topic_id' => "Missing", 'forum_id' => "Missing", 'topic_title' => "Missing", 'topic_poster' => "Missing",
   'topic_time' => "Missing", 'topic_views' => "Missing", 'topic_replies' => "Missing", 'topic_status' => "Missing", 'topic_vote' => "Missing",
   'topic_type' => "Missing", 'topic_first_post_id' => "Missing", 'topic_last_post_id' => "Missing", 'topic_moved_id' => "Missing"),
'topics_watch' => array(
   'topic_id' => "Missing", 'user_id' => "Missing", 'notify_status' => "Missing"),
'users' => array(
   'user_id' => "Missing", 'user_active' => "Missing", 'username' => "Missing", 'user_password' => "Missing",
   'user_session_time' => "Missing", 'user_session_page' => "Missing", 'user_lastvisit' => "Missing", 'user_regdate' => "Missing",
   'user_level' => "Missing", 'user_posts' => "Missing", 'user_timezone' => "Missing", 'user_style' => "Missing", 'user_lang' => "Missing",
   'user_dateformat' => "Missing", 'user_new_privmsg' => "Missing", 'user_unread_privmsg' => "Missing", 'user_last_privmsg' => "Missing",
   'user_emailtime' => "Missing", 'user_viewemail' => "Missing", 'user_attachsig' => "Missing", 'user_allowhtml' => "Missing",
   'user_allowbbcode' => "Missing", 'user_allowsmile' => "Missing", 'user_allowavatar' => "Missing", 'user_allow_pm' => "Missing",
   'user_allow_viewonline' => "Missing", 'user_notify' => "Missing", 'user_notify_pm' => "Missing", 'user_popup_pm' => "Missing",
   'user_rank' => "Missing", 'user_avatar' => "Missing", 'user_avatar_type' => "Missing", 'user_email' => "Missing", 'user_icq' => "Missing",
   'user_website' => "Missing", 'user_from' => "Missing", 'user_sig' => "Missing", 'user_sig_bbcode_uid' => "Missing",
   'user_aim' => "Missing", 'user_yim' => "Missing", 'user_msnm' => "Missing", 'user_occ' => "Missing", 'user_interests' => "Missing",
   'user_actkey' => "Missing", 'user_newpasswd' => "Missing", 'user_login_tries' => "Missing", 'user_last_login_try' => "Missing"),
'vote_desc' => array(
   'vote_id' => "Missing", 'topic_id' => "Missing", 'vote_text' => "Missing", 'vote_start' => "Missing", 'vote_length' => "Missing"),
'vote_results' => array(
   'vote_id' => "Missing", 'vote_option_id' => "Missing", 'vote_option_text' => "Missing", 'vote_result' => "Missing"),
'vote_voters' => array(
   'vote_id' => "Missing", 'vote_user_id' => "Missing", 'vote_user_ip' => "Missing"),
'words' => array(
   'word_id' => "Missing", 'word' => "Missing", 'replacement' => "Missing"),
);
$config_names = array(
   'config_id' => "Missing", 'board_disable' => "Missing", 'sitename' => "Missing", 'site_desc' => "Missing",
   'cookie_name' => "Missing", 'cookie_path' => "Missing", 'cookie_domain' => "Missing", 'cookie_secure' => "Missing", 'session_length' => "Missing",
   'allow_html' => "Missing", 'allow_html_tags' => "Missing", 'allow_bbcode' => "Missing", 'allow_smilies' => "Missing", 'allow_sig' => "Missing",
   'allow_namechange' => "Missing", 'allow_theme_create' => "Missing", 'allow_avatar_local' => "Missing", 'allow_avatar_remote' => "Missing",
   'allow_avatar_upload' => "Missing", 'enable_confirm' => "Missing", 'allow_autologin' => "Missing", 'max_autologin_time' => "Missing",
   'override_user_style' => "Missing", 'posts_per_page' => "Missing", 'topics_per_page' => "Missing", 'hot_threshold' => "Missing",
   'max_poll_options' => "Missing", 'max_sig_chars' => "Missing", 'max_inbox_privmsgs' => "Missing", 'max_sentbox_privmsgs' => "Missing",
   'max_savebox_privmsgs' => "Missing", 'board_email_sig' => "Missing", 'board_email' => "Missing", 'smtp_delivery' => "Missing",
   'smtp_host' => "Missing", 'smtp_username' => "Missing", 'smtp_password' => "Missing", 'sendmail_fix' => "Missing",
   'require_activation' => "Missing", 'flood_interval' => "Missing", 'board_email_form' => "Missing", 'avatar_filesize' => "Missing",
   'avatar_max_width' => "Missing", 'avatar_max_height' => "Missing", 'avatar_path' => "Missing", 'avatar_gallery_path' => "Missing",
   'smilies_path' => "Missing", 'default_style' => "Missing", 'default_dateformat' => "Missing", 'board_timezone' => "Missing",
   'prune_enable' => "Missing", 'privmsg_disable' => "Missing", 'gzip_compress' => "Missing", 'coppa_fax' => "Missing", 'coppa_mail' => "Missing",
   'record_online_users' => "Missing", 'record_online_date' => "Missing", 'server_name' => "Missing", 'server_port' => "Missing",
   'script_path' => "Missing", 'version' => "Missing", 'allow_autologin' => "Missing", 'max_autologin_time' => "Missing",
   'max_login_attempts' => "Missing", 'login_reset_time' => "Missing", 'search_flood_interval' => "Missing", 'rand_seed' => "Missing",
   'search_min_chars' => "Missing", 'default_lang' => "Missing", 'board_startdate' => "Missing");

// Program starts working here!

// read config file
define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
$template_path= $phpbb_root_path . "templates/";
include($phpbb_root_path . 'common.'.$phpEx);

$sql="SHOW TABLES LIKE '$table_prefix%'";
if( !($result = $db->sql_query($sql)) )
{
   die ("Unable to locate any PHPBB tables in your database!<br />");
}
else
{
   $tablefield = $db->sql_fieldname(0,$result);
   while ( $row = $db->sql_fetchrow($result) )
   {
      // set found table names to something recognizable
      $index = (substr($row[$tablefield],strlen($table_prefix)));
      $tables["$index"] = 'Found<br />';
   }
}

while ($chktable = each($tables))
{
   $a = $chktable['key'];
   // find out if table has defined fields
   if ((!empty($table_fields[$a])) && ($chktable['value'] != "Missing"))
   {
      $sql = "SHOW FIELDS IN $table_prefix$a";
      if( !($result = $db->sql_query($sql)) )
      {
         $sql_store = $sql;
         $sql_error = $db->sql_error();
         $debug_text = '';
         if ( $sql_error['message'] != '' )
         {
            $debug_text .= '<br /><br />SQL Error : ' . $sql_error['code'] . ' ' . $sql_error['message'];
         }
   
         if ( $sql_store != '' )
         {
            $debug_text .= "<br /><br />$sql_store";
         }
   
      }
      $fieldname = $db->sql_fieldname(0);
      while ( $row = $db->sql_fetchrow($result) )
      {
         $index = $row[$fieldname];
         if ($table_fields[$a][$index]== "Missing")
         {
            $table_fields[$a][$index]= "Found<br />";
         }
         else
         {
            $table_fields[$a][$index]= "Mod related<br />";
         }
      }
   }
   else if ($chktable['value'] != "Missing")
   {
      $tables[$a] = "Mod related<br />";
   }
}

// we've now got an inventory of tables and fields
while ($chktable = each($table_fields))
{
   if ($tables[$chktable['key']] == "Missing")
   {   // table isn't there
      $output_raw .= "<font color='ff0000'>Required table $table_prefix$chktable[key] is missing!</font><br />";
   }
   else
   {
      $missing_count = 0;
      $output_raw .= "Required table $table_prefix$chktable[key] ";
      while($chkfield = each($table_fields[$chktable['key']]))
      {   // check for required fields
         if ($chkfield['value'] == "Missing")
         {   // required field missing
            if ($missing_count == 0)
               $output_raw .= "is <font color='ff0000'>missing ";
            else
               $output_raw .= ", ";
            $output_raw .= $chkfield['key'];
            ++$missing_count;
         }
      }
      if ($missing_count == 0)
         $output_raw .= "<font color='00ff00'>has all required fields</font><br />";
      else
         $output_raw .= "</font><br />";
   }
}

// now check for required config items
$sql = "SELECT config_name,config_value FROM ".$table_prefix."config";
if( !($result = $db->sql_query($sql)) )
   die("Unable to retrieve configuration values!");
$fieldname = $db->sql_fieldname(0,$result);
while ( $row = $db->sql_fetchrow($result) )
{
   $chkname = $row[$fieldname];
   if (!empty($config_names[$chkname]))
   {
      $config_names[$chkname] = "Found";
   }
   if ($chkname == 'version')
      $dbver = "Database reports it is version <font color='ff0000'>2$row[config_value].</font><br />";
}

// we've now got an inventory of configuration items
$output_raw .= "<p>";
$output_raw .= $dbver;
$output_raw .= "Required configuration key";

$missing_count = 0;
$missing_keys = "";
while ($chkkey = each($config_names))
{
   if ($chkkey['value'] == "Missing")
   {   // required field missing
      if ($missing_count == 0)
         $missing_keys .= " <font color='ff0000'>";
      else
         $missing_keys .= ",";
      $missing_keys .= " $chkkey[key]";
      ++$missing_count;
   }
}
if ($missing_count == 0)
   $output_raw .= "s are all present<br />";
else
   $output_raw .= ($missing_count == 1 ? "" : "s")  . $missing_keys . ($missing_count == 1 ? " is" : " are")." missing!</font><br />";

// Now check templates for required fields
$sql = "SELECT template_name, style_name FROM ".$table_prefix."themes";
if( !($result = $db->sql_query($sql)) )
   die("No styles found!");
$fieldname = $db->sql_fieldname(0,$result);
while ( $row = $db->sql_fetchrow($result) )
{
   if ($row['template_name'] =='')
   {
      $output_raw .= "<p><font color='ff0000'>Theme $row[themes_id] has a blank name, and is invalid</font>";
      continue;
   }
   $check_path1 = $template_path . $row['template_name'] . "/profile_add_body.tpl";
   $check_path2 = $template_path . $row['template_name'] . "/admin/board_config_body.tpl";
   $user_text = file_get_contents($check_path1);
   $user = strpos(strtolower($user_text),"switch_confirm");
   $config_text = file_get_contents($check_path2);
   $config = strpos(strtolower($config_text),"l_visual_confirm");
   $autolog = strpos($config_text,"ALLOW_AUTOLOGIN");
   $limitlog = strpos($config_text,"MAX_LOGIN_ATTEMPTS");
   $output_raw .= "<p><b>Style '".$row['style_name']."'</b> <font color='";
   $output_raw .= $user > 0 ? "00ff00'>supports" : "ff0000'>does not support";
   $output_raw .= "</font> Visual Confirmation for registration, <font color='";
   $output_raw .= $config > 0 ? "00ff00'>supports" : "ff0000'>does not support";
   $output_raw .= "</font> Visual Confirmation configuration option,<br /><font color='";
   $output_raw .= $autolog > 0 ? "00ff00'>supports" : "ff0000'>does not support";
   $output_raw .= "</font> Auto Logon configuration option, <font color='";
   $output_raw .= $limitlog > 0 ? "00ff00'>supports" : "ff0000'>does not support";
   $output_raw .= "</font> Maximum login count configuration option.<br />";
   
}
// check for optional extensions that would be helpful
$output_raw .= "<p>";
$output_raw .= "Extension Zlib, which will enhance the security of the standard Visual Confirmation image, is <font color='".((@extension_loaded('zlib')) ? "00ff00'>" : "ff0000'>not ")."available</font>.<br />";
$output_raw .= "Extension GD, necessary for the FREECAP replacement for the Visual Confirmation image, is <font color='".((@extension_loaded('gd')) ? "00ff00'>" : "ff0000'>not ")."available</font>.<br />";
$output_raw .= "Extension Multibyte String is <font color='".((@extension_loaded('mbstring')) ? "ff0000'>" : "00ff00'>not ")."installed</font>.<br />";

echo "<p><b>Checking list of tables for required fields...</b><br /><p>";
echo $output_raw;
echo "<p>If errors are noted above, please highlight the following, and use your browser's copy and paste functions to post these results on phpbb.com:</p>\n";
echo "<textarea name='message' rows='15' cols='76' width='100%' wrap='virtual'>";
$output_cooked = str_replace("</font>", "[/color]",$output_raw);
$output_cooked = str_replace("<font color='ff0000'>","[color=red]",$output_cooked);
$output_cooked = str_replace("<font color='00ff00'>","[color=green]",$output_cooked);
$output_cooked = str_replace("<br />","\n",$output_cooked);
$output_cooked = str_replace("<p>","\n",$output_cooked);
$output_cooked = str_replace("<b>","[b]",$output_cooked);
$output_cooked = str_replace("</b>","[/b]",$output_cooked);
echo $output_cooked;
echo "</textarea>";
?>
Back to top
Display posts from previous:   
Register or Login to Post    Index » Knowledge Base (KB)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 4 Hours
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0597 seconds using 16 queries. (SQL 0.0076 Parse 0.0007 Other 0.0514)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo