Simple Machines Community Forum

I'm going to say right now, if you have a big (or moderate-size) YaBB forum please skip down below....

The first is pretty easy.  I'm attaching a database restoration tool.  It features the following:

• Restoration from a file on the server (uploaded with FTP not HTTP.)
• Timeout and overload prevention.
• Doesn't require SMF to be installed.
• Extracts zip and gzip archived dumps.

In other words, no more upload time outs... no more timeouts from too many queries... just plain high-grade database backup restoration ;).

Except one small thing... it needs testing.  I'm not going to release this, and later find out that in some cases the timeout prevention skips data.  Bad bad.  Not for a restoration utility... so what I'd like is someone with a decent size database (bigger = better) to give it a test, in a test database or even on a test server, to no harm or anything... and report results.

If you're game, it's attached.  Thanks a lot!

Okay, here's number two.  It's not as easy, but I'm hoping to get some "hits".  It's for the new YaBB converter I'm writing.

You see, I love YaBB.  I really do.  It's great.  The problem is, Perl is going out.. and for large forums... well, I also like SMF better (sorry anyone from the YaBB team.)  If you're here, especially if you're reading this, it's quite possible you agree - and want to convert over.

However, this is usually not easy, for larger forums.  Currently, you have to first go to YaBB SE, then SMF.  Even without that, the YaBB converter will quickly time out if you have even a decent-size forum.  I don't think a converter even exists for larger YaBB forums... it may have seemed impossible to escape.

This converter features timeout prevention.  Direct conversion to SMF.  All those very very nice things we work hard for in SMF and its tools/converters.

But, I need a forum with a bunch of posts to test it with.  Preferrably, more than 80,000.  That should be enough to give it a good amount of growing pains and testing.  And larger is probably even better.

So, if you have one I could test with... and a server with MySQL (or are willing to transfer the entire database of files to me) please send me a message, email me, or reply here.  I don't know how many want to convert... but this is a bit of a challenge, and that makes it enough ;).

Thanks!
-[Unknown]

Will test it tonight, my database is currently about 340MB so that should be large enough.

Ended up popping out and it's nearly 1am so will try it tomorrow.

Ended up going to the pub, will be at the footy tomorrow too so earliest I will get to try it is wednesday and I'll probably be overworked, will see what I can do.

Heh... well, I decided, "hey, wait a minute... I've got one right here!" and downloaded the database dump for this database here ;).

It didn't work properly; really funny, I accidentally used ";" but didn't parse those, so it kept restarting from the beginning (I had tested it previously only with a shorter dump.)  I fixed that, however, and added a percentage (appoximatation) of completion.  It's looking better, imho ;).

The data restoration looked okay, but I'd still love it if you could test it.. and least to make sure it runs, but it'd be nice to make sure the data works properly too.  This database was only 30 mb (gzipped) anyway.

Attached is the updated version...

-[Unknown]

Have some time now, will give it a go.

Ok, seemed to start restoring, but have killed it as the load went upto 12 and my board stopped responding, guess I should test it on my local test box rather than my server.

Here's another, as promised, to create backups - saem timeout prevention, gzips optionally, took about 5 minutes to do this site without affecting load in the slightest.

Again, testing is a wonderful, wonderful thing ;).  Don't everyone volunteer at once, now :P.

-[Unknown]

SMF databases only or will it work with a YaBB SE one as well?

It would even work with Mambo :P ;).

-[Unknown]

I'll test the restore one again, but on a test box, only issue is, it only has 256 MB ram, Athlon 2600+ processor though (shame it's wasted and just sits there backing up my site nightly).

Anyway it may not perform too well with so little ram.

First with permissions set to 644 I got these errors:


Warning: fopen(/home/anguz/public_html/database_2004-10-25.sql.gz): failed to open stream: Permission denied in /home/anguz/public_html/create_backup.php on line 185

Warning: fwrite(): supplied argument is not a valid stream resource in /home/anguz/public_html/create_backup.php on line 265

etc...


Then changed the permissions to 777 and got these:


Warning: fopen(/home/anguz/public_html/database_2004-10-25.sql.gz): failed to open stream: Permission denied in /home/anguz/public_html/create_backup.php on line 172

Warning: fwrite(): supplied argument is not a valid stream resource in /home/anguz/public_html/create_backup.php on line 182

Warning: fwrite(): supplied argument is not a valid stream resource in /home/anguz/public_html/create_backup.php on line 210

Warning: fwrite(): supplied argument is not a valid stream resource in /home/anguz/public_html/create_backup.php on line 231

Warning: fwrite(): supplied argument is not a valid stream resource in /home/anguz/public_html/create_backup.php on line 265

Warning: fwrite(): supplied argument is not a valid stream resource in /home/anguz/public_html/create_backup.php on line 271

etc...


Did I do something wrong?

Quote from: Ben_S on October 25, 2004, 08:26:27 PM
Anyway it may not perform too well with so little ram.

Ideal testing conditions!

Anguz, what if you create a zero byte file with that name in that directory, and make it 777?

-[Unknown]

I'll try that... hold on.

It didn't work the first time, then I gave it 777 and it did. Had to turn off the compression because it changed the file extension (I created the 0b file with .sql).

The resulting file is almost 50 megs in size. Haven't checked it to see if it's complete though and can't right now, but will later.

I tried it (create_backup.php) and it worked perfectly for me. ^_^ Will this script be integrated in a future version of smf?

Quote from: Miyuki-chan on October 27, 2004, 09:33:25 PM
I tried it (create_backup.php) and it worked perfectly for me. ^_^ Will this script be integrated in a future version of smf?

I have the same question Unknown.

-Jazhawk

I'm not sure.  Currently, it's a separate tool.

-[Unknown]

Unknwon , Are still looking for more servers you can test on?I have one that you work on...I also dio email you about it but I have not haerd a singel word from you.

I am very very busy at the moment, sorry.

-[Unknown]

let me know when you can.

Tried this on a test forum just for the hell of it but it only restored 3 tables from a database dump (the dump was in sql.gz format) it said the restore was successfull. Am I missing something? 

Actually, the ones attached above probably won't work properly.  Here are newer ones...

-[Unknown]

still only restores 3 tables but now gives me this error

Notice: Undefined index: line in /home/worldcic/public_html/restore_backup.php on line 181

mmm sorry after examining my sql file  it seems my dump via phpmyadmin wasnt complete for some reason.

I tried the backup feature on my main forum and it wouldnt let me back up fully unless I selected the forum prefix only. I have a table called spelling_words which is bit under 10mb in size which is the only table without the smf prefix.
The other thing is that I selected Gzip and my databse which has a size of 40mb(not counting the spelling table) into a 40mb sql.gz file. Is this normal for gzip or is it host related as to why it didnt compress.. Sorry for going off track with the current subject :)

Quote from: oscar2001 on December 07, 2004, 12:26:34 AM
I tried the backup feature on my main forum and it wouldnt let me back up fully unless I selected the forum prefix only. I have a table called spelling_words which is bit under 10mb in size which is the only table without the smf prefix.

If you export this with phpMyAdmin, I assume it works?  Could I perhaps see a backup of this table?  Does it give errors or what if you don't specify a prefix?

QuoteThe other thing is that I selected Gzip and my databse which has a size of 40mb(not counting the spelling table) into a 40mb sql.gz file. Is this normal for gzip or is it host related as to why it didnt compress.. Sorry for going off track with the current subject :)

What does it say?  Does it say you have zlib?  Can I see a phpinfo.php?

What is phpinfo.php? (http://www.simplemachines.org/community/index.php?topic=18250.0)

-[Unknown]

I tried the script on a database of 95.000 Messages, 5100 Threads & 1330 Members. After entering the MySQL details + clicking on proceed, I directly got a grey screen just displaying the header "Backup Restoration Tool", with nothing happening for 30 minutes. As said in the other thread, I unfortunately tried an outdated version of the script (october), so please delete this message if this info isn't useful to you.

I tried it for my "small" database (about 10mb) and it worked perfectly! All the data has been restored.

Quote from: [Unknown] on December 06, 2004, 04:25:53 PM
Actually, the ones attached above probably won't work properly. Here are newer ones...

-[Unknown]

I have tried and tried over again, but with a Gzipped file, it stops at smf_messages for my forum with 8k posts. But with a normal SQL file it will get most of the datbases :-\

The restore does?  After how long does it stop?  What did you create the dump with?

-[Unknown]

When I create a gzipped (or zipped with phpmyadmin) with the admin tool or phpmyadmin, the restore utility will stop after a few seconds at smf_messages. I assume it stops because smf_messages is a large table. Anything after smf_messages does not get imported. I tried a plain sql file and it stopped at smf_topics.

Quote from: Jerry on December 13, 2004, 11:23:22 PM
When I create a gzipped (or zipped with phpmyadmin) with the admin tool or phpmyadmin, the restore utility will stop after a few seconds at smf_messages. I assume it stops because smf_messages is a large table. Anything after smf_messages does not get imported. I tried a plain sql file and it stopped at smf_topics.

Well, it shouldn't stop like that, but it's built to handle several small inserts, not one large insert.  I still suggest you use the backup creation utility attached with it.

-[Unknown]

I will try the backup creation utility to see if it works better, I cant recall if I ever used it.

I have sucessfully backed-up my database from one table into another table. The first time I tested this (about a week ago) everything worked fine, but now when I took the final step I get some strange errors in smf:

Guest       Today at 14:11:40
Apply Filter: Only show the error messages of this IP address       
Apply Filter: Only show the error messages of this URL http://www.taxibalk.nl/index.php?option=com_smf&Itemid=40&?action=dlattach;id=16;type=avatar
Apply Filter: Only show the errors with the same message
2: session_start(): Cannot send session cookie - headers already sent by (output started at /data/members/paid/t/a/taxibalk.nl/htdocs/forum/index.php:2)
/data/members/paid/t/a/taxibalk.nl/htdocs/forum/Sources/Load.php
1192
   
Apply Filter: Only show the error messages of this member marije    Today at 14:11:31
Apply Filter: Only show the error messages of this IP address 81.204.225.8      Apply Filter: Only show the error messages of this session da72b581e9e971533e04d07f1a1896be
Apply Filter: Only show the error messages of this URL http://www.taxibalk.nl/index.php?option=com_smf&Itemid=40&?action=login2
Apply Filter: Only show the errors with the same message
2: Cannot modify header information - headers already sent by (output started at /data/members/paid/t/a/taxibalk.nl/htdocs/forum/index.php:2)
Bestand: /data/members/paid/t/a/taxibalk.nl/htdocs/forum/Sources/Subs.php
Regel: 1191
   
Apply Filter: Only show the error messages of this member marije    Today at 14:11:31
Apply Filter: Only show the error messages of this IP address 81.204.225.8      Apply Filter: Only show the error messages of this session da72b581e9e971533e04d07f1a1896be
Apply Filter: Only show the error messages of this URL http://www.taxibalk.nl/index.php?option=com_smf&Itemid=40&?action=login2
Apply Filter: Only show the errors with the same message
2: Cannot modify header information - headers already sent by (output started at /data/members/paid/t/a/taxibalk.nl/htdocs/forum/index.php:2)
Bestand: /data/members/paid/t/a/taxibalk.nl/htdocs/forum/Sources/Subs-Auth.php
Regel: 70

An it goes on and on, about 40 pages of errorreports. I'm sure the path of the forum and sources are correct, can anyone tell me what it is?

Sounds like there's something at the beginning of index.php...

-[Unknown]

Nevermind, I did the most incredible stupid thing. I put the backup of my index.php in my mambosection, of wich I didn't make a backup yet.... STUPID STUPID STUPID

So I decided to reinstall everything and hope the backup of my database lived...

Tip: don't ever start working on your site after you drunk some glasses of wine  :D

Quote from: [Unknown] on December 14, 2004, 12:53:59 AM

Quote from: Jerry on December 13, 2004, 11:23:22 PM
When I create a gzipped (or zipped with phpmyadmin) with the admin tool or phpmyadmin, the restore utility will stop after a few seconds at smf_messages. I assume it stops because smf_messages is a large table. Anything after smf_messages does not get imported. I tried a plain sql file and it stopped at smf_topics.

Well, it shouldn't stop like that, but it's built to handle several small inserts, not one large insert. I still suggest you use the backup creation utility attached with it.

-[Unknown]

I used the create_backup.php utility and it worked like a charm, thanks [Unkown] saves me alot of time. Don't have to split the database into a million files any more ;D

Quote from: [Unknown] on December 17, 2004, 01:29:41 PM
Sounds like there's something at the beginning of index.php...

-[Unknown]

I found the problem; I restored the database in my www dir. in stead of my forum dir. Now I've made another restore on the location of my forum and it worked perfectly!

So... no one's having problems anymore?

I don't suppose oscar2001 has had a chance to see this yet?

-[Unknown]

[Unknown]

Im having same problem i lost my forum since it doesnt want to upload it like just stops after a few seconds. It does that in phpmyadmin. I eventually got it to sorta work though  ::)

[Unknown], I finally got to test this today. Actually it's not just a test, I need it to migrate a db from one webhost to another.

The backup creation went fine. It took over 40 minutes to create it. The db size is 98.7 MB big, says in phpMyAdmin. Although I chose to have the backup GZIPped, it required that much free space in my webhost account to create the backup. After that was complete, the .gz file size dropped to 27.9 MB, so I'm guessing it does the compression after finishing the .sql file creation.

I fortunately had Joseph help me giving me a temporal space increase to create the backup, but for those without that luck, maybe doing it in parts, compressing every so many bytes, would be better. When I ran out of space first, the script didn't say there was that problem, though. If it were done in smaller parts and the script mentioned it can't continue, it could probably pause the backup creation, let me download the first few parts to make some space and then resume.

The restore was much faster, less than 10 mins. I got some errors at the very end, though:

Quote
Backup Restoration Tool
Some of the queries were not executed properly. Technical information about the queries:
Line #33620: BLOB column 'notifies' used in key specification without a key length
Line #33883: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #34141: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #34399: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #34657: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #34915: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #35173: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #35431: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #35689: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #35947: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #36205: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #36463: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #36721: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #36979: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #37237: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #37495: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #37753: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #38011: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #38269: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #38527: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #38785: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #39043: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #39301: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #39559: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #39817: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #40075: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #40333: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #40591: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #40849: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #41107: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #41365: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #41623: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #41881: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #42139: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #42397: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #42655: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #42913: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #43171: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #43429: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #43687: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #43945: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #44203: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #44461: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #44719: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #44977: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #45235: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #45493: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #45751: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #46009: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #46267: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #46525: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #46783: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #47041: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #47299: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #47557: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #47815: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #48073: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #48331: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #48589: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #48847: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #49105: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #49363: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #49621: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Line #49781: Table 'laakadem_yabbse.yabbse_topics' doesn't exist
Restoration process complete!
Congratulations! Your database backup has been restored successfully.

If you had any problems, please tell us about them so that we can help you get them resolved.
Good luck!
Simple Machines

Comparing the two databases in phpMyAdmin, these are the differences I see:

yabbse_log_activity       408 ->    407
yabbse_log_boards        7195 ->   7192
yabbse_log_online           5 ->      1
yabbse_log_topics      343294 -> 343286
yabbse_members           3917 ->   3916
yabbse_messages        170777 -> 170776

These changes are most probably due to the fact that I thought the forum was in Maintenance Mode before I created the backup, when in fact wasn't and put it in that mode after a while. Also, although in that mode, some people still visit the page.

The important difference, and it's obviously related to the errors I got at the end of the restore, is the missing yabbse_topics (in the original db it says it has 16031 records, but that doesn't match with the number of errors I got, unless each error refers to batches of records). From what I read there, the very first error was in creating the table, which caused the following ones.

It'd be good to not say "Congratulations! Your database backup has been restored successfully" when you get even one error, actually.

I noticed that the progress bar isn't very reliable. I've been comparing the progress percentage with the file size of the backup via FTP. From what I saw, the one displayed by the script is based on the number of records, not bytes. At the beginning it jumped to 60+ percent done almost instantly, probably because of the small tables before getting to messages, where it took a long time to change, while the file size grew and grew. If at all possible, it'd be better to do it by bytes, I'd say, or have two bars, one for records and one for bytes.

One more idea. Right now you have to upload the backup file via FTP first to run the script with it. Since I originally had it in another server, instead of having to download it to my machine first, with a bad speed, and then upload to the new server, with a worse speed, would it be possible to have the option of pulling it from a URL?

Ah, weird thing with both scripts was that at first it'd say that the MySQL information wasn't right, referring most probably to the password, although it was. After several attempts and logging in to cPanel with my user/pass to make sure, the scripts worked. It happened with the backup creation in the original webhost and the restore one in the new one. Dunno if it's important, but thought I'd mention it.

Other than those, it worked great.

When, and if, you provide a new version of the script, can I use the same backup although most of it was already restored? Will it ignore existing records? If I created the table by hand, for example, could I run the restore script again and it'll only add those missing? Just wondering.

Thanks for the great work!

It cannot create one gzip file (.sql.gz) unless it waits until it has the entire data to compress.  You can't just compress it in chunks and slap them together :P.

The error you got was a MySQL version difference, and caused the topics table not to be created :/.

Errors refer to batches.  I would edit the database dump, and copy out all the topics table information.  Then I would edit the CREATE TABLE for the topics table to be compatible with the MySQL on your new server.

> I noticed that the progress bar isn't very reliable
It SAYS it's not reliable.  It gives you an approximate idea, but if it were to be even somewhat more accurate, the database dumping itself would take about twice as long.  Obviously, you cannot do it by bytes without calculating how many bytes there are to do.  The whole freaking point of this utility is not to waste time on that.

Pulling it from a URL is possible, yes.  Just use the http://.... URL.

But, like I said... the errors you got with the topics table are not the backup or restore's fault.

-[Unknown]

Quote from: [Unknown] on December 21, 2004, 09:45:34 PM
It cannot create one gzip file (.sql.gz) unless it waits until it has the entire data to compress. You can't just compress it in chunks and slap them together :P.

Yes, I can understand that, I just didn't think about it much at first and thought that the gzipped version would require little free space. About the chunks thing I suggested, I was talking about creating and gzipping them separately, not putting them together.

Quote from: [Unknown]
The error you got was a MySQL version difference, and caused the topics table not to be created :/.

Errors refer to batches. I would edit the database dump, and copy out all the topics table information. Then I would edit the CREATE TABLE for the topics table to be compatible with the MySQL on your new server.

I got the topics table out, but am not sure what to change to make it compatible. My server is running MySQL 4.0.22-standard. This is the table creation code from the dump:


DROP TABLE IF EXISTS `yabbse_topics`;

CREATE TABLE `yabbse_topics` (
  `ID_TOPIC` int(11) NOT NULL auto_increment,
  `ID_BOARD` int(11) NOT NULL default 0,
  `ID_MEMBER_STARTED` int(11) NOT NULL default 0,
  `ID_MEMBER_UPDATED` int(11) NOT NULL default 0,
  `ID_FIRST_MSG` int(11) NOT NULL default 0,
  `ID_LAST_MSG` int(11) NOT NULL default 0,
  `ID_POLL` int(11) NOT NULL default -1,
  `numReplies` int(11) NOT NULL default 0,
  `numViews` int(11) NOT NULL default 0,
  `locked` tinyint(4) NOT NULL default 0,
  `notifies` text,
  `isSticky` tinyint(4) NOT NULL default 0,
  PRIMARY KEY (`ID_TOPIC`),
  KEY `notifies` (`notifies`)
) TYPE=MyISAM;


Looking at the MySQL manual, the error seems to be obviously related to this:
13.2.6 CREATE TABLE Syntax

Quote
When indexing a BLOB or TEXT column, you must specify a prefix length for the index. For example:

Code Select Expand


CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM and InnoDB tables as of MySQL 4.1.2). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters.

So the line I have to modify is that, but what to? Would this be right?

  KEY `notifies` (`notifies`(255))


Quote from: [Unknown]
> I noticed that the progress bar isn't very reliable
It SAYS it's not reliable. It gives you an approximate idea, but if it were to be even somewhat more accurate, the database dumping itself would take about twice as long. Obviously, you cannot do it by bytes without calculating how many bytes there are to do. The whole freaking point of this utility is not to waste time on that.

I didn't say "I hate your tool for not being accurate!" I just made an observation and am aware that it's not very important. I think you utility rocks and is really helping me here.

Quote from: [Unknown]
Pulling it from a URL is possible, yes. Just use the http://.... URL.

Ah.. I assumed it had to be in the same account. Thanks.

Quote from: [Unknown]
But, like I said... the errors you got with the topics table are not the backup or restore's fault.

Ok. I didn't think it was, since everything else went just fine. I did think that you would probably know how to prevent this kind of error though, like figuring out how to create the table depending on MySQL's version. Again, I'm not criticizing.

KEY `notifies` (`notifies`(30))

Should be fine.  Or just no key at all.  I'm curious... the server you're moving from, what version of MySQL was it?  If you do:

SHOW CREATE TABLE yabbse_topics;

What do you get?

-[Unknown]

Quote from: [Unknown]
KEY `notifies` (`notifies`(30))
Should be fine. Or just no key at all.

Is 30 for lenght enough? I thought it had to be much larger... Wouldn't not having the key, make it slower? Not that it bothers me much, I'm planning to upgrade this forum to SMF in a few days anyway. I've been really neglecting it lately, since the other December. :P

Quote from: [Unknown]
I'm curious... the server you're moving from, what version of MySQL was it?  If you do:
SHOW CREATE TABLE yabbse_topics;
What do you get?

It's the same MySQL version: 4.0.22-standard.

The query returns

CREATE TABLE `yabbse_topics` (
`ID_TOPIC` int(11) NOT NULL auto_increment,
`ID_BOARD` int(11) NOT NULL default '0',
`ID_MEMBER_STARTED` int(11) NOT NULL default '0',
`ID_MEMBER_UPDATED` int(11) NOT NULL default '0',
`ID_FIRST_MSG` int(11) NOT NULL default '0',
`ID_LAST_MSG` int(11) NOT NULL default '0',
`ID_POLL` int(11) NOT NULL default '-1',
`numReplies` int(11) NOT NULL default '0',
`numViews` int(11) NOT NULL default '0',
`locked` tinyint(4) NOT NULL default '0',
`notifies` text,
`isSticky` tinyint(4) NOT NULL default '0',
PRIMARY KEY  (`ID_TOPIC`),
FULLTEXT KEY `notifies` (`notifies`)
) TYPE=MyISAM


That's odd, if it's invalid for this MySQL version, why does it show the result without the length?

FULLTEXT?!?!?  I guess my backup utility doesn't support FULLTEXT...

(but why in the world do you have a full text key there??!)

-[Unknown]

Quote from: [Unknown] on December 22, 2004, 03:56:15 AM
FULLTEXT?!?!? I guess my backup utility doesn't support FULLTEXT...

(but why in the world do you have a full text key there??!)

-[Unknown]

To tell the truth,  I haven't got a clue.

I finished restoring the missing table.

In general, I'd say your scripts are just awesome. ;)

Test please.  I can't seem to figure out why, but the YaBB converter doesn't do read topic data *completely* right.  Everything else is good, though.

Please report any timeouts.

Thanks,
-[Unknown]

, with the backup restore/create scripts I now have a new problem. creating a backup works fine.. but restoring a database gives a small error:

QuoteSome of the queries were not executed properly. Technical information about the queries:
Line #15790: 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 ''L 11/06/2004 - 15:36:07: Log file started (file "logs\\L1
Line #15892: 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 'joined team "CT"', 'xx'),
(4379, 372, 14, 1099863449

Everything seems to have been restored from looking at phpmyadmin. Is this anything I should worry about?

Can you post those lines of the backup file?

-[Unknown]

lhere is line 15790 http://www.raclan.org/15790.txt

and here is (on and around 15892)

QuoteINSERT INTO `smf_messages`
   (`ID_MSG`, `ID_TOPIC`, `ID_BOARD`, `posterTime`, `ID_MEMBER`, `subject`, `posterName`, `posterEmail`, `posterIP`, `smileysEnabled`, `modifiedTime`, `modifiedName`, `body`, `icon`)
VALUES (4493, 384, 51, 1100103551, 21, 'Re: want to join your clan', 'Jimmy', '[email protected]', '81.229.77.158', 1, 0, NULL, 'Yeah, in my opinion this is our VERY last CS player!', 'xx'),

the INSERT INTO `smf_messages` doesn't look good in the second error..

Strange, I don't see anything wrong with those lines.

Can phpMyAdmin read it?

-[Unknown]

yes, worked fine with phpMyAdmin :). Amazing though, usually it times out :P

Quote from: Jerry on January 21, 2005, 11:24:57 AM
yes, worked fine with phpMyAdmin :). Amazing though, usually it times out :P

Would you be willing to email me the entire file ([email protected]) so I can figure out what is going wrong?

-[Unknown]

I have sent you the email, don't know if hotmail send it though :-X

[Unknown], great scripts, anyway to email the file to an email instead of saving it.

- Thanks

No.  It at least needs a temporary location to save the file, if nothing else.

-[Unknown]

Ok I input all the info from repair_settings.php into create_backup.php and I get this meaage:

Cannot connect to the MySQL database server with the supplied data.

If you are not sure about what to type in, please contact your host.  

Also I tried a backup of my forum create from the admins panel on 03-04-2005 and tried using restore_backup.php and get these messages

Warning: Unknown(): open(d:\php\sessiondata\sess_21badb8b0625361069fb2d030ce5c83b, O_RDWR) failed: Permission denied (13) in Unknown on line 0

Warning: Unknown(): Failed to write session data (files). Please verify that the current setting of session.save_path is correct (d:\php\sessiondata) in Unknown on line 0


Also my forum will not display and has not since right after the backup on the 4th I am trying to make sure I have a good backup for a reinstall as we have had some problem as of late. Member are getting restless and starting to send nasty emails pleases help!!

Quote from: Eagle1 on March 05, 2005, 05:06:39 AM
Cannot connect to the MySQL database server with the supplied data.

If you are not sure about what to type in, please contact your host.  

Are you entirely certain the password you used corresponded properly with the MySQL (not SMF!) account you specified?

Quote
Warning: Unknown(): open(d:\php\sessiondata\sess_21badb8b0625361069fb2d030ce5c83b, O_RDWR) failed: Permission denied (13) in Unknown on line 0

Warning: Unknown(): Failed to write session data (files). Please verify that the current setting of session.save_path is correct (d:\php\sessiondata) in Unknown on line 0

Contact your host about these errors.  Either SYSTEM or IUSR_COMPUTERNAME needs write access to d:\php\sessiondata and the files within.

Really sorry for responding later than I meant to... so many posts I end up missing the important ones sometimes :/.

-[Unknown]

Backedup from Mysql version 4.1.9-log  and resotred to 4.0.2.3  and I get these errors.... which don't look good at all. :(

Some of the queries were not executed properly. This could be caused by an unsupported (development or old) version of MySQL.

Technical information about the queries:
Line #369: BLOB column 'caption' used in key specification without a key length
Line #632: Table 'blooz_bloozbox.gallery_pictures' doesn't exist
Line #669: Table 'blooz_bloozbox.gallery_pictures' doesn't exist
Line #977: 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 'option varchar(50) NOT NULL default '',
ordering int(11) NOT
Line #1012: Table 'blooz_bloozbox.mos_components' doesn't exist
Line #1099: 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 'mediumtext NOT NULL default '',
state tinyint(3) NOT NULL def
Line #1121: Table 'blooz_bloozbox.mos_content' doesn't exist
Line #66908: BLOB column 'text' used in key specification without a key length
Line #66922: Table 'blooz_bloozbox.smf_quotes' doesn't exist

2nd try -- exact same errors. 

I'm afraid that this utility doesn't currently very cleanly handle DOWNGRADING from 4.1.x to 4.0.x...

You'll want to use mysqldump with the compatibility options on.

-[Unknown]

I've been using the backup tool for a while and I've just tried to use the restore_backup tool to load a database to my test site. I have tried compressed and uncompressed and it never seems to complete step 2.

Using restore_backup.php: 'Software Version: SMF 1.1 Beta 1'                         

Give it time.  If you want, you can try phpMyAdmin instead.

-[Unknown]

I have tried phpMyAdmin, with a gz compressed file of circa 4mb, it uploads and then I get a 500. So I have done an uncompressed one. Uncompressed the database file is 14mb which rules out phpMyAdmin unless I chop it up.

I let resore_backup run for at least 30 minutes.

I think I have found out what the problem was, the ftp transfer mode. I have changed it from binary to ascii and retransfered all the critical files. Restore_backup.php now runs and can be seen to be running.

Just thought I'd say I used the backup restore script for the first time yesterday and it worked great! Though the SQL file was only about 15MB, phpMyAdmin couldn't handle it yet this tool did a brilliant job!

Been thinking, I know that can be dangerous for the over 50's BUT (isn't there always a big but?)

After sussing out what was wrong (see above) I am now trying to give my SMF test site a database which is a copy of the main board but with a different table name (damage limitation). Is it possible for you to include code to do this in your restore_backup.php?

I was thinking along the lines of being able to specify the table reference/name that you restore it too.

cant seem to find the back up..

anyway to make it save to the desktop?

Hope this is still live as I'm trying it now  :D

LOL [unknown] is such a hero  8)

Hours of getting timeouts then he bleddy loads it in 5 seconds  :D

Hi All,
please see here a new report:

http://www.simplemachines.org/community/index.php?topic=35972.new#new

what woudl cause a 500 internal error ...restoring a 10.8mb .zip file?

hi can anyone help a newbie and tell me where to put these 2 files  so they would work

cheers

jet

I have created a backup folder in the main SMF folder and keep the files and the backups in there. If you are going to transfer the files anywhere by FTP, set sql & sql.gz to ascii mode (I also have txt, php & css set to ascii mode as well)

Umm.... .gz should definitely be binary, not ascii.

-[Unknown]

I keep getting the error that it cant login with the info supplied, but when I go to phpmyadmin with the same info, it logs me right in. Is there a specific place to put the php file when I run it?

I have mine in a folder called backup, where I store the last 3 d/b backups. They get downloaded when I do a files backup.

I've had this problem in the past and never found the reason. I stopped getting it when I moved to another host, which suggested to me that the problem is being caused by the server configuration.

anyone might knwo why im getting this error when trying to upload the database to the new server?

Some of the queries were not executed properly. Technical information about the queries:
Line #212: Query was empty
Line #245: You have an error in your SQL syntax near '���!�T�_&���@���T�L)���Q�q��^
%%�f(�
��@�dK�
�x��9�)1J�c��1�����S�����' at line 2
Line #555: You have an error in your SQL syntax near 'Z�N���������%S���Q
]�
���~ak/��H`���wu�����|�6�î���^IpP�s<x⊚�W�kLr' at line 2
Line #566: You have an error in your SQL syntax near '��XO��=S�Gô�>l$�0FV��[A��
�my��\���p�3���: �]H``��sY.�3�,,�t�~' at line 2
Line #1162: You have an error in your SQL syntax near '��^���)��' at line 2
Line #1504: You have an error in your SQL syntax near 'fWkB�>0�b��)QcXRn{�
BK��t`A�2�\�.�z:ה�9��WCt9��ތ-
��ȒPCt���B�h�' at line 2
Line #1620: You have an error in your SQL syntax near '�,�u�A4`��?v���8.=��ڈ��4���[
L"&>=[f��L���(8��U' at line 2
Line #1746: You have an error in your SQL syntax near 'lʡ^R^D�7��$�m^l���$n�9�е7���6��(�<���W�T*̹�nTB�-�Q�����w0�x-K��%' at line 2
Line #2441: You have an error in your SQL syntax near '��������-޸�������W��
M�pZ�ȹ2Tܔ���4G{�Us�|Ǯ�-�7p`0��\Z`��
zп�]wDO��' at line 2
Line #3136: You have an error in your SQL syntax near '�#����8' at line 2
Line #3176: You have an error in your SQL syntax near '?{�b� ��,���Z��P\{@��!q{x}|�vx���=p�=�O�&�Vfz�0��>�� ���I��' at line 2
Line #3355: You have an error in your SQL syntax near '��&�x:�V����}�e5s+�4�v҇>U^���}�&�� .O�}�"]пX�A�딐�/��thF������?��' at line 2
Line #3610: You have an error in your SQL syntax near 'Se���F' at line 2
Line #3744: You have an error in your SQL syntax near '�gZ*��p5�B�xf��
`~ZDH�1��*i�����Z�ЅA���a^�
���Jy� 5k
2
u�*�' at line 2
Line #4010: You have an error in your SQL syntax near 'qƗ�1E( ڌ��S=R �L�='GD��0�v�m�� �gO' at line 2
Line #4217: You have an error in your SQL syntax near '=s' at line 2
Line #4223: You have an error in your SQL syntax near 'Ρ^w
�+�y
��_yQ&�=.�R����TRW�^�U�V
0;�e��Scz*�_�-�/�ԕ;)/' at line 2
Line #4256: You have an error in your SQL syntax near '&�zE
P�'��/1ؕ��?�����S' at line 2
Line #4427: You have an error in your SQL syntax near '�P�Li @
�.D�pp)���'G@���^@��y2���1�e' at line 2
Line #4628: You have an error in your SQL syntax near '��Mγ��<s�6�2ضEޜ6���
l��P�' at line 2
Line #4705: You have an error in your SQL syntax near '�?�!
?*^�-���
���t:6��`���d' at line 2
Line #4740: You have an error in your SQL syntax near 'W' at line 2
Line #5492: You have an error in your SQL syntax near '���YG�ͩ0' at line 2
Line #6143: You have an error in your SQL syntax near '�������
F��
��PZ��h���ɇ����A���-�=�h�$�
��p�:bAk�H㭀�MR��ϔ�ǝ���/g9eM' at line 2
Line #6632: You have an error in your SQL syntax near '�"��
jW}��z�oN4>7���;~���˝����\7���S�!$CIaڀz�O
��\M.�
MA������' at line 2
Line #6678: You have an error in your SQL syntax near '�sA��mx�1y/e�
�#��nk��-ι.ƾÄkc��%�&(Žܛ��e�/�`�7%�L���O4x�Ъ�&' at line 2
Line #7418: You have an error in your SQL syntax near 'gh��6�F�l���Ow�/���d"����GE4�8��C�L$��]��9j�k(U��r0����f�D`A
���q�8' at line 2
Line #7566: You have an error in your SQL syntax near 'O"ҏ��,��]��\�;��6ҽnL��k���:~\v�
W���M�O8
1J��plx��(�Ȉ�##�*�"���g3���' at line 2

restore_backup.php and creater_backup.php its nice scripts from UNKNOWN to backup and restore MySQL database :)
But its only work, if you have enough space on your host. My hosting server have not enough space to create the dump files. And PHPMyadmin cannot do the help (memory limited, time out issue). But, my host server gave the unlimited SQL file. I want to replicate the database in to new prefix (for example from smf_database to new_ database)

So, I seen every installing or upgrading new SMF version. There is a copy function/procedure from old database to backup (before upgrade). Which script (from installation files) that I can use to copy/replicating the database?

Thank you for your help

Ok I installed this in my smf folder and when I run it it jumps up to 84% real fast and stops.

QuoteUndefined index: line in /home/..../forum/restore_backup.php on line 191

What can I do? I have this message when I use restore_backup.php , I don't change nothing in data base.

Alright - running the restore_backup.php on a 7.41 MB SQL file, I get the following error:

Some of the queries were not executed properly. Technical information about the queries:
Line #3434: BLOB column 'post' used in key specification without a key length
Line #3697: Table 'daiship71.ibf_posts' doesn't exist
Line #3955: Table 'daiship71.ibf_posts' doesn't exist
Line #4213: Table 'daiship71.ibf_posts' doesn't exist
Line #4466: Table 'daiship71.ibf_posts' doesn't exist

Anyone able to advise a workaround for this one?

Best regards,

Max

What did you use to create the backup?

It looks like the create table statements aren't in the backup.

Believe it or not, I used the included create_backup.php. Any suggestions?

Which version of SMF are you using?

SMF?

I'm actually using this to transfer the database between two Invision Power Board installs.

You will need to find the posts create table query in the install for ipb and then run it with phpmyadmin or something similar.

I would give you the query but I don't have it as I run SMF not IPB.

Maybe you should consider converting to SMF :P

If I were to run the default installer for IPB (which creates the database), and then, after that, run the 'restore_backup' script, do you think it might work around the problem?

That would probably work

Fantastic. Thanks! I'll try that now, and then advise of the result.

No luck. Help!!

How can I change this data back into a text type file which I would get from downloading through the database backup feature?

thanks

I just used it to backup our production forum and restore it to a test environment, and it worked like a charm. It was so much faster and less frustrating than other methods I have used.

Our forum is small, with only about 350 members and 850 messages right now.

I think this utility ought to come out of the "anyone willing to test" post here, and onto one of the FAQ pages. Right now the FAQs make it sound like the php database Admin utility is the only way to migrate data, but this is actually much more effective.

Can anyone do that? If we need basic instructions, like in the current FAQs, I am happy to write something up. I do not want to presume to post these somewhere, though, when they are Unknown's work.

After my forum grew so large that I could no longer use SMF's built in backup database, I started to use phpMyAdmin for backing up purposes.  When that stopping working, I installed [Unknown]'s create/restore backup utility.  It worked for a while, but now that, too, no longer works.  Instead, the backup gets to 100% and then produces the blank white screen of death.

Does anyone have suggestions on how I can get [Unknown]'s tool to work again?  Or maybe other backup ideas?

Thank you!

-LuckyOne

Wow.   :D
That worked great.
Super easy and everything seems to be there.

I did find that if you have multiple forums and you aren't careful in changing the default paths that you can restore the wrong forum, thereby totally destroying one that wasn't broken in the first place, and that you foolishly didn't back up because, of course, that wasn't the one you were working on. sigh

well i used this restore backup thing, but it didn't restore all the data...most of the messages are there, but the topics are not visible...very odd..

yeah I had a problem with a "duplicate entry" in one of the tables "smf_log_search_topics"

all of my members and boards were extracted from the .sql file, but even though it says the posts are there, you can't see them at all. I did a "recount" on the post stats and stuff and it all got set back to zero.

so close.

I'm so close to having the file restored.. as soon as I got it to run.. this is what I got.


Some of the queries were not executed properly. Technical information about the queries:
Line #33: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=3' at line 33
Line #60: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=6' at line 19
Line #95: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=6' at line 24
Line #119: 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 'DEFAULT CHARSET=latin1' at line 13
Line #1027: Duplicate entry '-1-0-poll_view' for key 1
Line #1057: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=30' at line 29
Line #1095: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1' at line 19
Line #1114: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=168' at line 18
Line #1286: Duplicate entry '1' for key 1
Line #1300: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=7' at line 13
Line #1322: 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 'DEFAULT CHARSET=latin1' at line 11
Line #1345: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1' at line 22
Line #1368: 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 'DEFAULT CHARSET=latin1' at line 22
Line #1375: Duplicate entry '2007-03-03' for key 1
Line #1391: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1' at line 15
Line #1409: 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 'DEFAULT CHARSET=latin1' at line 17
Line #1434: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1' at line 24
Line #1451: 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 'DEFAULT CHARSET=latin1' at line 16
Line #1472: 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 'DEFAULT CHARSET=latin1' at line 14
Line #1490: 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 'DEFAULT CHARSET=latin1' at line 17
Line #1509: 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 'DEFAULT CHARSET=latin1' at line 18
Line #1552: 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 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`ID_MEMBER` me
Line #1571: 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 'DEFAULT CHARSET=latin1' at line 12
Line #2839: 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 'DEFAULT CHARSET=latin1' at line 11
Line #2859: 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 'DEFAULT CHARSET=latin1' at line 19
Line #2877: 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 'DEFAULT CHARSET=latin1' at line 17
Line #2885: Duplicate entry 'smf-1' for key 1
Line #2897: 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 'DEFAULT CHARSET=latin1' at line 11
Line #2916: 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 'DEFAULT CHARSET=latin1' at line 18
Line #2938: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=33' at line 21
Line #2971: Duplicate entry '1' for key 1
Line #3041: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=370' at line 69
Line #3200: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=13' at line 15
Line #3217: Duplicate entry '1' for key 1
Line #3249: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=88431' at line 31
Line #57057: 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 'DEFAULT CHARSET=latin1' at line 11
Line #57076: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=2' at line 12
Line #57082: Duplicate entry '1' for key 1
Line #57095: 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 'DEFAULT CHARSET=latin1' at line 12
Line #57139: Duplicate entry '-1-search_posts' for key 1
Line #57158: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=25809' at line 18
Line #59027: 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 'DEFAULT CHARSET=latin1' at line 16
Line #60893: 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 'DEFAULT CHARSET=latin1' at line 13
Line #61432: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=278' at line 18
Line #61545: 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 'DEFAULT CHARSET=latin1' at line 12
Line #61564: 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 'DEFAULT CHARSET=latin1' at line 11
Line #61733: Duplicate entry 'smfVersion' for key 1
Line #61750: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=46' at line 16
Line #61800: Duplicate entry '6' for key 1
Line #61815: 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 'DEFAULT CHARSET=latin1' at line 14
Line #61852: Duplicate entry '1-0-name' for key 1
Line #61878: 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=4567' at line 25

Although it seems as if 99% stuff transfered.

Rookie question time:  I'm currently trying to restore a small db - 9.25mb .gz

The back up restoration tool seems to have stopped at "step 2".  What would prevent
it from continuing?

i used the restore_backup.php and it seemed to restore my forum to the new location successfully however i did encounter this error

Some of the queries were not executed properly. Technical information about the queries:
Line #5181: 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 'DROP TABLE IF EXISTS `smf_log_search_topics`' at line 6

anyone know why i got this and what i can do to fix it?

any updates??

I tried it and did not work

1.1.2
10m .gz

Quote from: [Unknown] on December 06, 2004, 04:25:53 PM
Actually, the ones attached above probably won't work properly.  Here are newer ones...

-[Unknown]

I tried today these tools for the very first time and I was amazed at how smooth and great they worked, both of them. I used them several time in my test forum and everything went without a hitch. Before I had always one problem or another when trying to restore my back-up from the phpmyadmin of my cp, but now I am like in the seventh heaven. Thank you very much [Unknown] for creating and sharing them with us.

P.s. I had one question, will these tools work as well with the 2.0 version and the other versions to come?

Why is some one making a forum with a function to back up and not to restore?I lost my forum but made a backup to my pc,the stress and panic i feel now over not being able to  find a simple way to restore or even how to in this maze of information is  killing me.

Quote from: msginabttl on June 20, 2007, 01:11:42 AM
Why is some one making a forum with a function to back up and not to restore?I lost my forum but made a backup to my pc,the stress and panic i feel now over not being able to  find a simple way to restore or even how to in this maze of information is  killing me.

No need to stress out or panic, just use the restore backup tool and everything will be ok

I did backup prior to downloading the backup restore tool that I am guessing you are referring to in the tool section.I have no idea how to use that to restore my forum and using my existing backups.

It is very easy, all you have to do is follow a few steps. To restore your back up, first you have to upload the restore_backup.php tool to your forum directory, then upload the back up file as well there. Not the create back_up.php tool, but the back up file which is in the format sql, or zipped, that you made from the admin''s panel or the phpmyadmin of your CP
Then, point your browser to the restore_backup tool, i.e. type in your favorite browser www.yourdomain.com/forum/restore_backup.php, follow the instructions and you will be all set.

P.s. If the forum files are in your root directory, then point your browser to : www.yourdomain.com/restore_backup.php

Hope this helps, let me know how it will go

I think I set it up right but there is no function to use to restore a forum.So much of basic information is left out.I tried everything.

Quote from: msginabttl on June 20, 2007, 04:15:57 AM
I think I set it up right but there is no function to use to restore a forum.So much of basic information is left out.I tried everything.

Are you sure that you tried everything? What exactly did you get in the way of an error?
I have been testing those tools many times at my testing forum, and everything went very smooth, without a single hitch

I have been to phpmyadmin on my hosting company using cp panel,I followed the instruction given here on the forum.I selected a file on the left side panel and then went to sql on the top.the forum said then i should select "choose" and pick the file on my pc that is the backup i want.I can't find the button choose in the Sql.How ever if I go to "import" i find a button where I can download a file from my pc,but when i select the backup and press go I get some error message.

To be exactly sure we are talking about the same tool for backup i picked the one that is available in this forum under tools.

ok you say upload the restore backup file tool to my forum.I will try that. now

Quote from: msginabttl on June 20, 2007, 04:25:01 AM
ok you say upload the restore backup file tool to my forum.I will try that. now

And after pointing your browser to the restore_backup.php tool, just follow the instruction and you will be fine

prior to trying this tool i did try in the php adm upploading the forum and it just did minus all the content all i got now is the different board and i could not uppload the tool to the forum so i am still not ok.3 hours and still no progress to speak about.

This is the error message i got when i tried to import my backup file from phpadm and selecting import,because if i did as they said in the forum(I wish I could find the qoute now)to select sql and then pick "choose" in the upper corner ,I would not gone anywhere.

Error

SQL query:

CREATE TABLE `smf_gallery_comment` (
ID_COMMENT int( 11 ) NOT NULL AUTO_INCREMENT ,
ID_PICTURE int( 11 ) NOT NULL default '',
ID_MEMBER mediumint( 8 ) unsigned NOT NULL default 0,
approved tinyint( 4 ) NOT NULL default 0,
COMMENT text,
date int( 10 ) unsigned NOT NULL default 0,
PRIMARY KEY ( ID_COMMENT )
) TYPE = MYISAM ;

MySQL said: Documentation
#1067 - Invalid default value for 'ID_PICTURE'

How can anyone expect a beginner to understand any on these things?
Short of being an expert on script the above error message is meaningless to me.

I once again lost the forum but i also want to disclose all this problem started when I tried to upload a  smf forum to my addon domain when I lost my forum on the main domain.The addon is up and looks ok and just need the content.I also have the basic forum on my main minus all the content and theme.I also tried the backup function in cp panel as well with no luck.I will join as a charter member and pay for the support.I did not know that I could do that until someone mention it and it took another five minutes before I could find out how and where to do that in this maze of information.Thank you for your help but I need to have some one that do this for a living and are willing to get involved for a fee otherwise I spend the rest of my life in here going no where.

Quote from: [Unknown] on December 06, 2004, 04:25:53 PM
Actually, the ones attached above probably won't work properly.  Here are newer ones...

-[Unknown]

I clicked on these links and nothing happened

I am so lousy on this I can't even quote something right.I tried to click on your links to the restore backup tools and nothing happened .I signed up with you as a charter member and open a ticket and I got a response with questions about what i have done so far.I answered those question and have heard nothing yet.I spoke to my domain hst and they tried to restore my data base.that did not work.The answer I got from them is that I was burning a dvd and trying to play it in a vcr.I tried monster.com backup/restore tool as well as php adm.The only thing I manage to do was some how upload a different forum with no access for me .It says user name is missing.So when can I get hold of some one that can restore the right forum in the right domain for me?I dont even know how to get the file restore  restore back up php.

Quote from: msginabttl on June 23, 2007, 08:17:04 PM

Quote from: [Unknown] on December 06, 2004, 04:25:53 PM
Actually, the ones attached above probably won't work properly.  Here are newer ones...

-[Unknown]

I clicked on these links and nothing happened

I just clicked on those links and the tools started to download immediately
If you want I can take a peek and see if I can restore the database for you, but I can not promise anything.

please do can we talk over the phone or do you need me to send you any information?

Is there another link or location I can go to to download this tool?

Quote from: msginabttl on June 23, 2007, 08:55:30 PM
please do can we talk over the phone or do you need me to send you any information?

You can send me the following info with PM, but before you can trust me with any kind of sensitive info I want you to read this post very carefully

http://www.simplemachines.org/community/index.php?topic=87130.0

Now, if you decide to trust me, pm these information:

1. Ftp adress, username and password.

2.MySQL username, MySQL password and MySQL database name

3. Upload the backup copies that you made from the admin''s panel and phpmyadmin, both of them, to the forum directory and I will take it from there. Provide a url to your site as well.

4. As I stated in my previous post, I can''t promise anything, but taking a peek.

Quote from: msginabttl on June 23, 2007, 09:31:47 PM
Is there another link or location I can go to to download this tool?

You can download them from here, just click at the tools

sorry i click on the links and nothing happens.

Quote from: msginabttl on June 24, 2007, 04:57:11 AM
sorry i click on the links and nothing happens.

Working okay here?

well I am giving up for now.:((

Quote from: msginabttl on June 24, 2007, 05:37:28 AM
well I am giving up for now.:((

I don''t understand why you can not download them? They work ok, where exactly did you click?
Anyway,the info you provided me it is not that info I  asked you. If you don''t know them, then I suggest that you contact your host for the exact info I asked you and when you have them ready, pm them to me, si I can take a look to restore your database

I clicked on the two links above and all they do is recycling the page.Nothing gets downloaded to my pc that I can see and even if it did ,it does not tell me where.

Somehow I manage to upload from where I don't know the wrong back up forum to my domain and it does not give me access so I can't make any changes and when I click on email me the password it does so but I don't know where it sends me the email.I got two domain and the one I lost was on my main domain and I by accident uploaded a copy of my  add on domains forum to that one and I don't know how to get rid of it when I don't have access to it.

Quote from: msginabttl on June 24, 2007, 01:20:02 PM
Somehow I manage to upload from where I don't know the wrong back up forum to my domain and it does not give me access so I can't make any changes and when I click on email me the password it does so but I don't know where it sends me the email.I got two domain and the one I lost was on my main domain and I by accident uploaded a copy of my  add on domains forum to that one and I don't know how to get rid of it when I don't have access to it.

You pm me the mysql info. Now pm me the ftp info, upload the backup copy to the forum directory and I will take it from there.

I find it very strange that you can not download those 2 tools I attached......

Your forum is restored successfully. Have a look and let me know what you think

i got this

Line #793: Invalid default value for 'ID_PICTURE'
Line #834: Invalid default value for 'ID_PICTURE'
Line #3875: Invalid default value for 'last_update'
Line #3898: Table 'b9_704571_narutosyndicate.smfsessions' doesn't exist
Line #4175: Invalid default value for 'ID_GROUP'
Line #4184: Table 'b9_704571_narutosyndicate.smfstaff' doesn't exist

So am I correct in presuming that these utilities posted in this thread are somehow better than the ones available in the SMF admin or in the CPanel of the webhost?  Just to be sure before I even attempt to try them out.  Thanks.

i succeeded in creating a backup thru the create_backup.php but when i use the restore_backup.php, i get this error:

Some of the queries were not executed properly. Technical information about the queries:
Line #634: Invalid default value for 'logTime'
Line #642: Table 'smf.smf_log_online' doesn't exist
Line #1078: Invalid default value for 'last_update'
Line #1087: Table 'smf.smf_sessions' doesn't exist

Restoration process complete!
Congratulations! Your database backup has been restored successfully.

---- what does the errors mean? how do i fix them?
im using smf1.1.3 on a xampp1.6.2 local server.

---- now when i open my webpage:
i see a  white page with the text :Table 'smf.smf_sessions' doesn't exist

im really having a hard time restoring back-up. its so easy to create one but restoring creates errors similar to the ones i experienced when i use phpmyadmin

now i see this error, when i tried restoring and opening my webpage

Table 'smf.smf_log_online' doesn't exist
File: D:\xampp\htdocs\mysite\Sources\Subs.php
Line: 2543

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 1.1.3, while your database is at version 1.1.2. The above error might possibly go away if you execute the latest version of upgrade.php.

i can't find where upgrade.php is... help..

I cannot back up my forum, I always get an error saying I've passed the file size limit or something like that.

can anyone tell me how to use the create backup file. thanks

And also can anyone tell me the approx size of a database.
(Info-  11411 Posts in 7431 Topics by 4853 Members)

I want to know, becoz my webhost sucks, and it doest download full file, it sumtimes download 5 mb and sumtimes 22 MB and Sumtimes....................................................

Quote from: Cool999 on July 20, 2007, 09:32:57 AM
can anyone tell me how to use the create backup file. thanks

And also can anyone tell me the approx size of a database.
(Info-  11411 Posts in 7431 Topics by 4853 Members)

I want to know, becoz my webhost sucks, and it doest download full file, it sumtimes download 5 mb and sumtimes 22 MB and Sumtimes....................................................

My forum has 125,000 posts in 50,000 topics and this backup script doesn't allow for it to be finished.

Hi, ive recently moved to another host.  I installed a new smf installation have tried to use phpmyadmin to upload the backup, it failed because i have such a huge forum and it timed out.

So i downloaded the restoration tool, put all the info in clicked continue and nothing happens, i just get backup restoration tool with a blank page and down the bottom says Done.  Can anyone help as i have a huge forum and dont want to lose anything.

Thanks
nat

Try BigDump (http://www.ozerov.de/bigdump.php)

P.s. How mb is your backup file?

One more question. When you installed the smf at the new host, did you use the same db info like the other forum at your old host?

Hi babjusi, thanks for the quick reply.

My backup file is 14.9mb gzipped. 

Not sure what you mean with the same db info??  Sorry, ive had a little experience with databases but not heaps :D

Just another thing, i was able to restore the database using the restore tool in cpanel.  But....its created its own database, i just need to be able to move it to the new installation.  But im not sure how to drop the tables from the new installation, for it to go through.  And i keep getting an error that the table already exists.

QuoteHi babjusi, thanks for the quick reply.

My backup file is 14.9mb gzipped.

Not sure what you mean with the same db info??  Sorry, ive had a little experience with databases but not heaps

Restore_backup.php tool should handle 14.9 mb. Anyway, unzip the backup file and give it another shot.

What I mean by db info, is the mysql info, like the name,  username and password. When creating the database at the new host, make sure that they are the same as the ones of the old host.

Just fixed it, all i did was copy the database over to the new one.

Thanks again!!

Quote from: Natz on July 24, 2007, 09:07:12 PM
Just fixed it, all i did was copy the database over to the new one.

Thanks again!!

Glad to hear that you fixed it. Which tool did you use to import your database, the Bigdump or the restore_backup.php?

I used neither, i just used the backup tool in cpanel and it uploaded fine there but it created its own database.  So while searching around phpmyadmin i found copy database.  All i did was dropped the tables and copied it over to my new installation.

Its a little screwy but nothing that cant be fixed, i still have files to upload through FTP.  I only have 1 problem though, its replaced symbols with question marks.  Does anyone know how to fix that?

Quote from: [Unknown] on December 06, 2004, 04:25:53 PM
Actually, the ones attached above probably won't work properly.  Here are newer ones...

-[Unknown]

How does this work ?

I want to download my db from phpmyadmin but dunno how with this ?

Thanks.

If you have CPANEL you can do it that way.

@Unknown

Can you tell why your script is killing database charset encoding ? I've made a backup but all special characters are wrong displayed.

I'm kinda hopeless, without ssh access i cant make a prober backup. cPanel is killing encoding as well. phpmyadimn is useless as my database is simply too big and is causing timeout durring export.

I haven't read all of this thread, but as it was created back in 2004 I would hazard to guess it's no longer valid.  Try the converters in the converters section is probably the best bet.

This is valid.
It always works. Well atleast for me it does.
This is Not SMF or phpBB dependent. It depends on MySQL so it works.

Quote from: Dragooon on November 18, 2007, 05:59:34 AM
This is valid.
It always works. Well atleast for me it does.
This is Not SMF or phpBB dependent. It depends on MySQL so it works.

That is totally right. Those tools still work like a charm. They even work with the new version, SMF 2.0 Beta 1. The best tools on the subject. I am amazed as to why they aren''t included yet at the download section under ''Tools''

it is not completely valid.

it does *not* support utf8, which IIRC was introduced after unknown left.

You guys check out this script:

http://www.mysqldumper.de/en/

found it out a couple days ago, works like charm so far.

I've been getting this error message when trying to use backup_restore to restore from an SMF complete backup:

Notice: Undefined index: line in /Applications/xampp/xamppfiles/htdocs/smf/restore_backup.php on line 160

The restoration is not successful.  Any ideas what the problem/solution might be?
Is there a way to run the sql statement in the backup file outside of backup_restore, or phpmyadmin?  I believe that phpmyadmin has an sql file size limit to small for the backup.

Thanks in advance,
joe

You can try the BigDump (http://www.ozerov.de/bigdump.php) application to do a database restore.

it couldnt back it up right but it restored it fine, thank you so much.

The create_backup seems to break when using with my database.

It gets to about 200mb (even though while GZipped it should only be 50mb) then crashes.

Quote from: anshar on November 28, 2007, 05:07:39 AM
You guys check out this script:

http://www.mysqldumper.de/en/

found it out a couple days ago, works like charm so far.

Yes, that is a very good script as well

Still working!
Thanks.

Vl Try

Works for SMF 2.0.2.

Works for SMF 2.0.2. thank you