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.

database problems after a mysql upgrade

Goto page 1, 2  Next
 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
bsd_mike
Board Member



Joined: 28 Jan 2009

Posts: 4



PostPosted: Thu Jan 29, 2009 12:58 pm 
Post subject: database problems after a mysql upgrade

Is anybody else seeing database problems after a mysql upgrade?

My web server provider updated mysql recently and I am seeing problems which require the tables to be repaired.

Any thoughts? Plans?

-Mike
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Thu Jan 29, 2009 3:39 pm 
Post subject: Re: database problems after a mysql upgrade

What was the old version? And what was the new version?

When I upgraded from 4.1 to 5.0.x, I did not need to repair tables, but it is recommended in the manual. I merely copied the data files into the new directory and that was it. When I upgraded from 5.0.x to 5.1.30, I dumped all the databases into a file and then imported them using the mysql monitor. In this most recent case, copying the data files did NOT work.

If doing a REPAIR TABLE command does not work, then your web host may have made some errors upgrading MySQL. Have you any backups? That's always a good plan.

_________________
Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield IL Mac 512K Blog Mac GUI
Back to top
bsd_mike
Board Member



Joined: 28 Jan 2009

Posts: 4



PostPosted: Thu Jan 29, 2009 4:02 pm 
Post subject: Re: database problems after a mysql upgrade

I guess went went from MySQL 5.0 to 5.1 (or something like that)
Repair the tables is working, but it seems to be 'one by one'.

I do have a backup of the database.

I think the service providers automated script to repair the tables checks them and then repairs them if it notices they are broken..
I wonder if I should repair them anyway?

Thanks,
-Mike

Update: Happened again. Now I am at a loss. Repair seems to work...but
I don't want to keep doing this.
Back to top
Ptirhiik
Board Member



Joined: 19 Nov 2008

Posts: 114


flag
PostPosted: Thu Jan 29, 2009 9:15 pm 
Post subject: Re: database problems after a mysql upgrade

This can occur if you are using php 5.2.8 & mysql 5.1.3: there is a bug with the php early mysqli extension that makes mysql communication unstable. To solve it, you have to force the usage of the mysql extension in place of the mysqli extension.
Back to top
bsd_mike
Board Member



Joined: 28 Jan 2009

Posts: 4



PostPosted: Thu Jan 29, 2009 9:26 pm 
Post subject: Re: database problems after a mysql upgrade

Thank you.
How do you do that?
-Mike
Back to top
espicom
Board Member



Joined: 24 Nov 2008

Posts: 55
Location: Woodstock, IL


flag
PostPosted: Thu Jan 29, 2009 11:03 pm 
Post subject: Re: database problems after a mysql upgrade

If you are not running phpBB version 3, mysqli isn't an option.

There is a script that will step through ALL your tables and repair them. You can find it here: Fixing MySQL 1016/1030/1034 errors
Back to top
bsd_mike
Board Member



Joined: 28 Jan 2009

Posts: 4



PostPosted: Thu Jan 29, 2009 11:50 pm 
Post subject: Re: database problems after a mysql upgrade

Last question. I have been able to rebuild each table as it
fails...Is this a once per table or will it crash each time until the
php / mysql issue is resolved?

Our host / service provider is using:
Php: 5.2.4
Mysql: 5.1.30


Thanks,
Mike
Back to top
espicom
Board Member



Joined: 24 Nov 2008

Posts: 55
Location: Woodstock, IL


flag
PostPosted: Fri Jan 30, 2009 2:36 am 
Post subject: Re: database problems after a mysql upgrade

My experience is that copying the physical files between major versions of MySQL pretty much requires you run a repair before you turn people loose on the system. I've even got scripts to do it when a new server is built. But, my servers don't have to "go live" in 5 minutes (there are already running ones for that), so the 3 to 5 hours a server-wide repair operation takes is tolerable.

Once the tables are repaired, though, they should stay that way, unless there is a hardware issue, too.
Back to top
lumpy burgertushie
Board Member



Joined: 19 Nov 2008

Posts: 221


flag
PostPosted: Fri Jan 30, 2009 3:00 am 
Post subject: Re: database problems after a mysql upgrade

bsd_mike wrote:
Last question. I have been able to rebuild each table as it
fails...Is this a once per table or will it crash each time until the
php / mysql issue is resolved?

Our host / service provider is using:
Php: 5.2.4
Mysql: 5.1.30


Thanks,
Mike

do you happen to be using hostgator?

I ask because I have a client that had the same type of problem today.
I had to do a repair table using FRM in order to repair the table.

I was getting the error:
Code:
SQL Error : 130 Incorrect file format 'phpbb_config'



roberrt
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: Mon Feb 02, 2009 8:02 am 
Post subject: Re: database problems after a mysql upgrade

Here is a script that repairs a complete DB at once:
Code:
<?
### Enter your username and password into the connection string: ###
$dbLink = mysql_connect("DB_SERVER", "DB_USERNAME", "DB_PASSWORD") or die("Unable to connect to the database.");

$sql = "show databases";
$query = mysql_query($sql) or die("error fetching database names");

while ($rs=mysql_fetch_array($query))
{
   echo "Database: " . $rs['Database'] . "\n";
   mysql_select_db($rs['Database'],$dbLink) or die("Unable to select database: " . $rs['Database']);
   $sql = "show tables";
   $query2 = mysql_query($sql) or die("error fetching table names");

   while ($rs2=mysql_fetch_array($query2))
   {
      $key = "Tables_in_" . $rs['Database'];
      echo "Table: " . $rs2[$key] . "\n";
      $sql = "repair table " . $rs2[$key];
      $query3 = mysql_query($sql);
      $rs3 = mysql_fetch_array($query3);
      echo $rs3['Table'] . " | " . $rs3['Op'] . " | " . $rs3['Msg_type'] . " | " . $rs3['Msg_text'] . "\n";
   }
}
echo "Finished!\n";
?>

Put it in a file, e.g. "repair.php", and edit the DB-settings. Then just run the script from the browser.

/Holger
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Fri Feb 06, 2009 2:50 pm 
Post subject: Re: database problems after a mysql upgrade

It is any risk in running this? I have a problem with 1062/ 1064 SQL erros, this could fix it?
_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: Fri Feb 06, 2009 4:34 pm 
Post subject: Re: database problems after a mysql upgrade

This is only performing a repair on all tables. Nothing else.
Back to top
~Cowboy~
Board Member



Joined: 08 Dec 2008

Posts: 297
Location: Chicago


flag
PostPosted: Fri Feb 06, 2009 7:23 pm 
Post subject: Re: database problems after a mysql upgrade

Holger wrote:
Here is a script that repairs a complete DB at once:
Code:
<?
### Enter your username and password into the connection string: ###
$dbLink = mysql_connect("DB_SERVER", "DB_USERNAME", "DB_PASSWORD") or die("Unable to connect to the database.");

$sql = "show databases";
$query = mysql_query($sql) or die("error fetching database names");

while ($rs=mysql_fetch_array($query))
{
   echo "Database: " . $rs['Database'] . "\n";
   mysql_select_db($rs['Database'],$dbLink) or die("Unable to select database: " . $rs['Database']);
   $sql = "show tables";
   $query2 = mysql_query($sql) or die("error fetching table names");

   while ($rs2=mysql_fetch_array($query2))
   {
      $key = "Tables_in_" . $rs['Database'];
      echo "Table: " . $rs2[$key] . "\n";
      $sql = "repair table " . $rs2[$key];
      $query3 = mysql_query($sql);
      $rs3 = mysql_fetch_array($query3);
      echo $rs3['Table'] . " | " . $rs3['Op'] . " | " . $rs3['Msg_type'] . " | " . $rs3['Msg_text'] . "\n";
   }
}
echo "Finished!\n";
?>

Put it in a file, e.g. "repair.php", and edit the DB-settings. Then just run the script from the browser.

/Holger

I need an explanation of just how this repairs the database. What EXACTLY does this do?

_________________
Image link
We are not refugees we are trail blazers. icon_wink.gif
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: Fri Feb 06, 2009 7:28 pm 
Post subject: Re: database problems after a mysql upgrade

It does the same as if you use the command REPAIR on a table in phpmyamin.
Code:
$sql = "repair table " . $rs2[$key];


Contact me on info@gremminger.de and I will give you an example.
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 663
Location: Texas


flag
PostPosted: Fri Feb 06, 2009 8:47 pm 
Post subject: Re: database problems after a mysql upgrade

If you are using a MySQL database, the files can get out of sync. I don't know if you're old enough to remember 'chkdsk' but it was a DOS command that you would run to periodically repair the FAT (File Allocation Table) if it got messed up. The repair table command for MySQL is, well, sort of similar. icon_smile.gif
_________________
phpBBDoctor Blog
Back to top
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT
Goto page 1, 2  Next
 
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.0292 seconds using 15 queries. (SQL 0.0018 Parse 0.0010 Other 0.0264)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo