Database backup problem
Moderator: Thorsten
Database backup problem
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!
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!
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.
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.
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.
Any ideas for future back ups?
Server: Apache/2.0.53 (Fedora)
PHP: 4.3.1
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.
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.
Hi,
which PHP version do you use exactly?
bye
Thorsten
which PHP version do you use exactly?
bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
amazon.de Wishlist
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.Any ideas for future back ups?
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.
Hi,
you're right. We never can copy phpMyAdmin for example. But these errros should be fixed.
bye
Thorsten
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
amazon.de Wishlist
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: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.
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)
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;
}
/**
* 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;
}
Hi,
this is already fixed in 1.5.4.
bye
Thorsten
this is already fixed in 1.5.4.
bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
amazon.de Wishlist