Registrations to Open-AudIT forums are now closed. To ask any new questions please visit Opmantek Community Questions.

Open-AudIT

What's on your network?
It is currently Fri Mar 29, 2024 5:26 am

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: Restore database
PostPosted: Wed Mar 24, 2010 2:20 am 
Offline
Newbie

Joined: Sat Nov 17, 2007 1:02 am
Posts: 29
Location: Belgium
A simplified error list of 500 simular lines:
---
[code]
Restoring the Database

Task Restore
Opening MySQL Dump './backup/openaudit_Backup_23-03-2010_16-44-57.sql' Done.
Running SQL upload.
INSERT INTO `environment_variable` VALUES('4694', '55BFB330-0A46-11DE-BBDA-0526D23F18A9', 'Path', 'C:\\Program Files\\PC Connectivity Solution\\
MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''C:\\Program Files\\PC Connectivity Solution\\' at line 1
C:\\ODI\\ostore\\OStore\\bin
MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'C:\\ODI\\ostore\\OStore\\bin' at line 1
%SystemRoot%\\system32
MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%SystemRoot%\\system32' at line 1
%SystemRoot%
MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%SystemRoot%' at line 1
%SystemRoot%\\System32\\Wbem
MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%SystemRoot%\\System32\\Wbem' at line 1
C:\\Program Files\\Proximus\\Manager', '20100323083419', '20100315153307')
[/code]

_________________
[size=85]OS : Windows XP with XAMPP install
Auditing: 120 Desktops/Laptops (mixed 200 and XP), +10 Windows Servers (mixed 2000 and 2003)
Multiple printers (MFP), switches
LDAP: AD[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Wed Mar 24, 2010 2:31 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
Mysql uses a semi-colon as a separator between SQL statements. I'd guess OpenAudit is not dealing with these. I'll take a look.


Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Wed Mar 24, 2010 3:01 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
It needs a quote aware explode at line 63 in database_restore_sql.php.

The php manual has an example [url=http://www.php.net/manual/en/function.explode.php#94024]csv_explode[/url]


Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Wed Mar 24, 2010 4:45 am 
Offline
Newbie

Joined: Sat Nov 17, 2007 1:02 am
Posts: 29
Location: Belgium
[quote="jpa"]It needs a quote aware explode at line 63 in database_restore_sql.php.

The php manual has an example [url=http://www.php.net/manual/en/function.explode.php#94024]csv_explode[/url]


Could you posibly give an example of what needs to be changed?

_________________
[size=85]OS : Windows XP with XAMPP install
Auditing: 120 Desktops/Laptops (mixed 200 and XP), +10 Windows Servers (mixed 2000 and 2003)
Multiple printers (MFP), switches
LDAP: AD[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Wed Mar 24, 2010 11:11 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
Unless someone better with PHP has a fix it will take me some time to figure this out. The example csv_explode I linked isn't sufficient.


Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Thu Mar 25, 2010 7:43 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
This is quite the example of why I'm not a programmer but it works for me. Replace the database_restore_sql.php file with the attached version after removing the ".txt" extension.

This is a hack and slash of code from the net. I added a function to properly split the mysql backup file at the semi-colons without breaking when semi-colons are in the data fields. Again, not genius level work. If someone else has something better use that.

Post back if it doesn't work.

EDIT: Deleted attached database_restore_sql.php with first try at csv_explode. It wasn't good enough to leave here.


Last edited by jpa on Sat Mar 27, 2010 6:41 am, edited 1 time in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Thu Mar 25, 2010 7:16 pm 
Offline
Newbie

Joined: Sat Nov 17, 2007 1:02 am
Posts: 29
Location: Belgium
Hi jpa, thanks for the help.
Another error now:
[code]Fatal error: Maximum execution time of 300 seconds exceeded in C:\Program Files\xampp\htdocs\OA_TEST\database_restore_sql.php on line 94
Restoring the Database

Task Restore
Opening MySQL Dump './backup/openaudit_Backup_23-03-2010_16-44-57.sql' Done.
Running SQL upload.[/code]

I've managed to restore my database with myphpadmin.

_________________
[size=85]OS : Windows XP with XAMPP install
Auditing: 120 Desktops/Laptops (mixed 200 and XP), +10 Windows Servers (mixed 2000 and 2003)
Multiple printers (MFP), switches
LDAP: AD[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Sat Mar 27, 2010 6:14 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1259
Looking back through the SVN commits the backup code was written before the ability to audit environment variables.

After reviewing the backup routines I'd say a mysqldump/mysqlimport or other means of backup and restore would be better than the built in stuff. As the database size increases the built in restore routines begin to have problems with timeouts or memory exhaustion as the entire import file is loaded into memory and manipulated at once.

I have taken another stab at improving the backup so it's not totally broken. I removed a couple ereg_replace calls in the backup routine. They are deprecated in PHP 5.3 but I also couldn't see that they were needed. They actually cause problems because a dump-restore-dump shows data changes between the dump files where there should be none. I also replaced mysql_escape_string with mysql_real_escape_string as it's deprecated in 5.3 and we've used the latter elsewhere.

For the restore routine I've changed my quote aware explode so it's not quite as stupid as my previous try. The previous version used str_replace which would have negatively impacted the execution time and memory use. It's still pretty ugly. I also upped the php execution timeout.

Also the MySQLAdministrator backup routine from MYSQL Tools for 5.0 cheerfully leaves table data with semi-colons out of the backup file. Not good for data integrity. MySQLAdministrator is deprecated but I'd say don't ever use it for backups.


Attachments:
database_restore_sql.php.txt [4.34 KiB]
Downloaded 411 times
database_backup_sql.php.txt [7.72 KiB]
Downloaded 396 times
Top
 Profile  
Reply with quote  
 Post subject: Re: Restore database
PostPosted: Wed Mar 31, 2010 6:35 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
The backup and restore were written back in the dark ages ( :oops: by me, but they have not had any fixes since)

I backup using phpmyadmin, so I never got around to sorting any issues, 'cos I never really used them.

You would almost certainly be better rewriting the built in backup/restore functions using mysqldump/mysqlimport to do the same job as suggested, let me know how you get on, and I can push any changes back in to the SVN.

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group