Database backup problem

In this board you can talk about general questions about phpMyFAQ

Moderator: Thorsten

Post Reply
monkeybot
Posts: 22
Joined: Mon Nov 29, 2004 9:58 pm

Database backup problem

Post by monkeybot » Mon Nov 21, 2005 10:59 pm

Updated from 1.5.2 to 1.5.4,
backed up the database .sql
Made some changes on the DB document
Uplodaded DB
Now from 340+ entries, it only shows 190
Tried to upload unchanged DB with same results.
I can see the entries on the backup .sql document but they are not showing up on the faq.

On the admin area: I see some of them say: Query: okay
Some of them show the content of the article with this at the end: failed (Reason: You have an error in your SQL syntax near

Help!

matteo
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Post by matteo » Mon Nov 21, 2005 11:12 pm

Have you performed a PMF backup export and then (re-)import?
No errors such as "Query XYZ failed (Reason: DBERROR), during the restoring of the PMF backup?

monkeybot
Posts: 22
Joined: Mon Nov 29, 2004 9:58 pm

Post by monkeybot » Mon Nov 21, 2005 11:41 pm

That's what I did, before I updated the DB I made a fresh backup.
. But I didn't get the error that you mention.


Most of the unloaded records show you "You have an error in your SQL syntax near" embeded near the end of the entry

Image

Image

The edited and unedited backup show the same errors

matteo
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Post by matteo » Tue Nov 22, 2005 12:10 am

Perhaps the issue is a bad (precisely a miss of) encoding of ' characters that happens in some configurations.
You can try to fix the PMF backup file manually finding all of the ' chars inside your data replacing ' with \' if I read correclty from your screenshot something like:

what is a tmax\'

and then re-import the backup.

Another solution is to change temporarly a line into build_insert function inside inc/functions.php but this would require a DB upon which no restore has been peformed :( just to try to export a PMF backup correctly encoded.

"NNNN of NNNN was successfull" means that all data has been processed: this doesn't strictly mean that the PMF backup was restore w/o errors: this last info is given by the absence of "Query... failed" lines.

What is the system upon which PMF is running? I mean: OS and PHP version.

monkeybot
Posts: 22
Joined: Mon Nov 29, 2004 9:58 pm

Post by monkeybot » Tue Nov 22, 2005 12:42 am

You exactly right. The single quote was not coded correctly. So any article that has a single quote generated an error.
The only backups that I did were with 1.5.2 and 1.5.4 being the last one the most current with heavy editing so I can't go back to the 1.5.2 one.
I better start fixing 1 meg backup file manually. :x :? :(

Any ideas for future back ups?

Server: Apache/2.0.53 (Fedora)
PHP: 4.3.1
Last edited by monkeybot on Tue Nov 22, 2005 7:17 pm, edited 1 time in total.

Thorsten
Posts: 15051
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Post by Thorsten » Tue Nov 22, 2005 7:42 am

Hi,

which PHP version do you use exactly?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

jazcyk
Posts: 385
Joined: Wed Sep 07, 2005 1:32 pm

Any ideas for future back ups?

Post by jazcyk » Tue Nov 22, 2005 11:58 am

Any ideas for future back ups?
You won't necessarily need to use the backup routine built-in the phpmyfaq. There are third part tool available for the various databases. Some can schedule the backup to run for instance daily and automatically - some will let you 'synchronize' databases so only new and changed rows need to be transferred and not the complete base. Some of those tools use a webinterface, some connect to the database in some other way.

I think a system like phpmyfaq should have a built-in backup routine. And it has. But it is quite elementary. When your FAQ grows you might find it TOO elementary. And also a good DB admin tool will let you do various types of database management/maintenaince more smart than using the phpmyfaq admin panel.

The admin panel does the elementary things only and rightly so.

Thorsten
Posts: 15051
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Post by Thorsten » Tue Nov 22, 2005 12:47 pm

Hi,

you're right. We never can copy phpMyAdmin for example. But these errros should be fixed.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

matteo
Posts: 572
Joined: Sun Nov 20, 2005 6:53 pm
Location: Italy

Post by matteo » Tue Nov 22, 2005 2:02 pm

monkeybot wrote:You exactly right. The single quote was not coded correctly. So any article that has a single quote generated an error.
The only backups that I did were with 1.5.2 and 1.5.4 being the last one the most current with heavy editing so I can't go back to the 1.5.2 one.
I better start fixing 1 meg backup file manually. :x :? :(
Please make a copy of your PMF backup file and only upon this copy try this perl script below. I've make a quick test (so PLEASE use it ONLY UPON a copy of you PMF backup file!!!) and it seems to work to quickly fix ' with \' where expected:

Code: Select all

#!/usr/bin/perl -w
# Set the file to the copy of the broken PMF backup.
my $PMF_broken_backup = '#INSERT_HERE_YOUR_PATH_AND_FILENAME_TO_A_COPY_OF_THE_PMF_BACKUP_TO_BE_FIXED#';

# Open the file for reading.
open DATA, "$PMF_broken_backup" or die "can't open $PMF_broken_backup $!";
# Read it
my @array_of_data = <DATA>;
close (DATA);

# Start replacing ' with \' in the content according to the need of SQL commands
foreach my $line (@array_of_data)
{
 $line =~ s/(\w|\s)\'(\w|\s)/$1\\\'$2/gi;
}

# Open the file for writing.
open DATAOUT, ">$PMF_broken_backup" or die "can't open $PMF_broken_backup $!";
# Overwrite it
foreach my $line (@array_of_data)
{
  print DATAOUT "$line";
}

# Close the file.
close (DATAOUT)

monkeybot
Posts: 22
Joined: Mon Nov 29, 2004 9:58 pm

Post by monkeybot » Tue Nov 22, 2005 5:21 pm

Thanks for the script! I fixed the DB manually last night. :shock:
I also found that there were some tab characters I had to delete to make the articles valid again.

Great program though. :D

monkeybot
Posts: 22
Joined: Mon Nov 29, 2004 9:58 pm

Post by monkeybot » Mon Dec 05, 2005 4:26 pm

In order to fix this issue, the following line of code (in red) was added to the inc/functions.php:

/**
* build_insert()

* This function builds the the queries for the backup
*
* @param string query
* @param string table name
* @return array
* @access public
* @author Meikel Katzengreis <meikel@katzengreis.com>
* @author Thorsten Rinne <thorsten@phpmyfaq.de>
* @since 2003-03-24
*/
function build_insert($query, $table)
{
global $db;
if (!$result = $db->query($query)) {
return;
}
$ret = array();
$ret[] = "\n-- Table: ".$table;
while ($row = $db->fetch_assoc ($result)) {
$p1 = array();
$p2 = array();
foreach ($row as $key => $val) {
$p1[] = $key;
if ('rights' != $key && is_numeric($val)) {
$p2[] = $val;
} else {

$val = str_replace("'","\'",$val);
$p2[] = "'".$val."'";
}
}
$ret[] = "INSERT INTO ".$table." (".implode(",", $p1).") VALUES (".implode(",", $p2).");";
}
return $ret;
}

Thorsten
Posts: 15051
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Post by Thorsten » Mon Dec 05, 2005 4:27 pm

Hi,

this is already fixed in 1.5.4.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist

Post Reply