https://www.icyphoenix.com/viewtopic.php?f=35&t=6205
-----------------------------------
Turbotech
Thu 30 Jul, 2009 18:02

Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
After doing everything BY THE BOOK (According to IP's upgrade information) I have had enough with the issues which remain unanswerd which were produced durring the 1.3 upgrade I can not afford the downtime....
I posted 3 days ago looking for feedback however It still remains unanswered, could not afford to wait as people USE the website every day "or did until this fabulous upgrade...."

Have worked the last 2 DAYS to restore my IP 1.2.0.27c website (from my FULL BACKUPS of the entire site as well as the database itself "saved from within IP ACP System 3x to be safe) yeah know they only gave us 100 warnings to BACK UP EVERYTHING JUST INCASE...

Im using the SAME ACCOUNT, SAME URL, THERE HAS BEEN NO CHANGES SERVER WISE and 1.2.027c had ran flawlessly since Jan09 when I decided to try out IP....

WTH good is it if the BACKED UP DATABASE SCRIPT HAS ERRORS IN IT FROM THE START? what the heck am I missing here?
I am not running any MODS nor have I made ANY MODIFICATIONS to IP Core Scripts (everything is STOCK Per INSTALL). The only addons are a few TEMPLATES which were downloaded from this site (and to date have been flawless).

For the record (After the issues I ran into with 1.3) before I attempted to Restore my original files I DELETED ALL FILES FROM THE SITE ROOT BEFORE UPLOADING my 100% COMPLETE BACKUP FILES..... Yes this also includes "as directed" my htaccess and config.php files...
as well as full permissions have been verified...

Install.php was run to reestablish full conectivity since simply uploading my files to the same diretory they came from in the first place resulted in NO ACP access let alone site access itself.  Figured "reestablish access, UPLOAD MY BACKED UP DATABASE with its 200+ User profiles & 2000+ POSTS

The database itself is 15mb+, Restoring it via phpmyadmin via my host isn't possible due to PHP/SQL limitation issues 1and1.com will accept up to 8mb max so using "AS SUGGESTED BY THIS SITE: MYSQL BIG DUMP".

The Downloaded BACKUP file ZIPED is aprox 1.5mb, UNZIPED 15mb... Have attempted to RESTORE the database via ACP appears to be uploading everything however when I check the database itself? It shows all of 400kb "ish" in size has been restored *also to make sure I was not looking at something the wrong way "Backed up the current database to see what the file size was... same as posted above aprox 400kb... 

First attempt was directed to the Target database, with this error posted below, 2nd & 3rd attempt was targeted at 2 new databases "one configed as MySQL4, the other MySQL5, simply to eliminate that factor as a possible problem... BOTH tests ending the same as the 1st with the ERROR LISTED BELOW....

Attempt 2 & 3 were done with BRAN NEW DATABASES, So there WERE NO TABLES in them to be issues "so it isn't that"...


ERROR////////////////////////////////////////////////////////////////////////////////////////////

BigDump: Staggered MySQL Dump Importer v0.29b
Processing file: backup_1248615578_20090726_181390701a0ba9a9.sql

Starting from line: 3001

Error at the line 3828: );

Query: CREATE TABLE ip_force_read_users(
user varchar(255) DEFAULT '' NOT NULL,
read int(1) DEFAULT '0' NOT NULL,
time int(10) DEFAULT '0' NOT NULL
);


MySQL: 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 'read int(1) DEFAULT '0' NOT NULL, time int(10) DEFAULT '0' N

Stopped on error

Start from the beginning (DROP the old tables before restarting)

2003-2008 Alexey Ozerov - BigDump Home

END ERROR///////////////////////////////////////////////////////////////////////

(DROP the old tables before restarting?) kind of hard to do that with 2 NEW DATABASES with NOoooooo Tables in them what so ever....

Current Status of my site: I can access it (after 2 days of screwing with it I now have ACP ACCESS AGAIN..... ) Album/Gallery can be accessed, all files and pics appear to be present, NO USER ACCOUNTS EXSIST "other than my own" Forum Headers/Topics/Categories EXSIST "they also ALL STATE x Number of posts X number of topics of what should be in each forum however when you click on ANY of them, there is absolutely NOTHING posted....


So, considering that MY SITE has been OFFLINE since TUESDAY MORNING with many people who access it EVERY DAY WAITING, it's now THURSDAY wth is the REAL DEAL with restoring an IP WEBSITE? "I am no noob however I am NOT a SQL DATABASE GURU" I have Migrated, Restored, and BUILT a number of WEBSITES over the years "using prefab GNU based CMS cores" and have never had this much of a problem as I have with this Icy Phoenix CMS.... 
The core itself is amazing, feature and config wise but isn't any good if it takes days to fix simple problems......

If there is ANYONE who can shed some light on "what it is I may be doing wrong or what I am missing here" The feedback would be a huge help as my WEBSITE is WORTHLESS if it cannot be accessed by the users who support it.......

Thx in advance.........


-----------------------------------
spydie
Thu 30 Jul, 2009 18:37

Re: Problem RESTORING DATABASE AFTER FAILED 1.3 UPGRADE
-----------------------------------
the error you´re running in is due to 

The DB allready exist´s with all tables in it.

even if they are empty. 

that´s why he throughs this error.

same happend to me 

so what did i do.

i opend that DB file and deleted the create table part.

and ran only the insert into part.


to me it ran well.


Edit
O you delete only the create DB part of it


-----------------------------------
Turbotech
Thu 30 Jul, 2009 18:51

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
Thx for the fast reply however to clarify things (The Create Database) you speak of, that would be in my (backup database that I am trying to upload? or found in the bigdump.php script?)

The "test databases" that I have created have no tables in them what so ever.

Any suggestions as to what app would be best to find this command? 
As posted above I am by no means a SQL guru lol.. however I am slowly learning more and more.

UPDATE: After your post I went back to the 2 TEST Databases I have created and in both cases I see that (62) IP tables were in fact created in both, last table created in the lists was (IP_FORCE_READ)

So it was in fact trying to upload to the database itself however slamed into a wall along the way which halted the upload...


UPDATE: I pulled up one of the TEST DATABASES I have created posted above, "dropped" the (62) tabes & data that it did manage to create, modified the bigdump.php script so it would ignore any Create Database sql commands it may run into while running (this is an option in the script itself and not a mod made by me), 

Ran the script once again with the same end results, (62) tables created (out of say 157) that should be there. And once again it failed when it attempted to create the IP_FORCE_READ Table itself as in the past 2 tests...... 

What Am I missing here? lol ;)


-----------------------------------
spydie
Thu 30 Jul, 2009 20:09

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
In the backup file

you should decompress and open that file to have a look.

Edit

try to open that file and part it , lets say in 3 parts.

first
create tables

second
al the other data splitt in 2 parts.

maybe that underruns the 8 MB thing

and run that ip_force_read table creation as a consult apart ( by itself)


-----------------------------------
Turbotech
Thu 30 Jul, 2009 21:26

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
I do appreciate your time for sure however I am starting to think there is a communication gap of sorts "As posted above in my initial post, the DATABASE BACKUP Created before the attempted 1.3 upgrade is 1.4+MB Compressed, Uncompressed It is 15mb+

Again: this is an attempt to Recover my 1.2.0.27c website since the 1.3 did not work for me and produced problems.. 1.2.0.27c database being restored to the original backup files of my 100% FLAWLESS I might add 1.2.0.27c website files..... well. flawless till now  oO sigh.

A: I have attempted to restore the COMPRESSED FILE via IP ACP I receive INTERNAL SERVER ERROR 500 (this is with evey backup that was created before the 1.3 attempted upgrade. - Tables were not deleted since I would assume if I did, I would have no ACP access or control of the site for that matter...

B: I have attempted to restore the COMPRESSED FILE via MyPhPAdmin (which again is only 1.4MB in size and well below the 8MB limit, Via the 1and1.com Admin panel. - Target database: TEST DBF WITH NO TABLES IN IT... CLEAN... RESULT (Not one single error is posted from myphpadmin and not one single table is created in the target DBF not one however you can see the system UPLOADING the file....

C: I have attempted to restore the COMPRESSED FILE via BIGDUMP.php method resulting in only (62) tables being created with a ERROR Stopping the execution at IP_FORCE_READ  - Target database: TEST DBF WITH NO TABLES IN IT... CLEAN... 

D: I have attempted to restore the UNCOMPRESSED 15MB+ sql file via BIGDUMP.php again resulting in only (62) tables being created with a ERROR Stopping the execution at IP_FORCE_READ - Target database: TEST DBF WITH NO TABLES IN IT... CLEAN... 


Apparently I am not understanding your last post at all.. its not a matter of breaking down the over all size of the file itself its a matter of resolving the ERRORS being created durring the proccess...

And again "for the record" I used Icy Phoenix ACP Database BACKUP to create not one but 3 different backups 2 which were targeted at the main backup folder which is in the IP-ROOT directory and 1 that was directed only to my HDD localy.. 2 copies went to the backup dir, but 3 were actualy downloaded to my personal local HDD. all reporting the same exact size as expected. No other Backup tools from 1and1.com admin console was used ONLY IP ACP as Recomended by its creators..... 


So where does this leave my data? oO

Worse yet where does this leave my 200+ users?

Again thx for the help for sure "dont get the wrong impression from my post your only trying to help and thx for that for sure ;)" 


NEXT THOUGHTS ANYONE? lol


-----------------------------------
spydie
Thu 30 Jul, 2009 21:46

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
[quote]So where does this leave my data? oO[/quote]

At the moment in the sh...er .

If we don´t get this fixed.

I remember having problems with exactly the same folder on restore (ip_force_read).

I solved that creating that table with an separtated consult with PhpMyAdmin.

BTW. I ran the hole restore with PhpMyAdmin (not the Icy one)

Thing is , y did that all manualy.

That means i decompressed the sql file 

selected part by part (ca. 100 lines by the time)

and ran consult after consult.


Edit:
BTW what issues did you have on update to 1.3.0.53

Maybe we can sort that out, and you keep running the new Icy


-----------------------------------
Turbotech
Thu 30 Jul, 2009 22:45

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
rofl.... >>> After 1.2.0.27 To 1.3.0.53 No Pics From Album Display

you were the only one out of 42 views who even tryed to post a solution however if you remember "you said this one is better left for MG to sort out lol".

I will take a closer look at the solution you have posted and see if I can figure something out with it.;

I will most deffinitly post the result "that is if I can figure out how to do what you did lol.

Heck, 90% of everything fired up just fine for the 1.3.0.53 update all except the fact that for my Album/Pic Gallery, all infos were there, place holders, for both the thumbs & larger size pics all links to each component worked just fine with the exception of NO ACTUAL PICTURES were being shown, (only a white box with a red X in the middle) Permissions ect had all been verified... however there was no solution...

At that time, EVERYTHING except for that 1 problem appeard to work just fine however the fact I put so many hours into uploading something like 500pics 1 at a time was reason enough for me to want that to work.... (At that time from what I could see (ALL USER ACCOUNTS & FORUM THREADS were right where they should be)...

I continued to screw with THAT setup, managed to get ALL THE GALLERY to work and was bout ready to throw a party!, umm that is untill I realized when I went to my own forums to post the updates (all forum headders, topics ect "just like now" were there but when you clicked on any of them, there wasnt one single thread or post in any forum..... 
Didnt bother to post that fact since no one could help with the other issue so said "Smurff it!" and started rolling things back to 1.2.0.27c why? again, couldnt afford the downtime..

MG never had a chance to look at the issue, (know what that is like) 43 views later I said screw it (cant afford the downtime) figured wouldnt be any big deal to ROLL BACK THE 1.2.0.27c setup, Ya know "since i BACKED EVERYTHING UP, hell I figured a couple hrs delete/reupload time, then figured import the data base (of course after dropping ALL current tables from the 1.3 install) and I would be back in business...... 

ummm, NOPE...... didnt work out that way.

down side to all of this is (there is no way to go back to 1.3 if I cant even get the current database remounted is there? I mean ummm, all my data still sits in the sql file and well, the original database had been (upgraded to 1.3) so needed to drop all the tables....... Or I should say "Other than my current backups of the 1.2.0.27c dbf config, I dont have any other files to pull from.

heh... figured instead of doing this specific site with Joomla! (as I do for others) I would cut a few corners and run with IP, why? Had everything I needed in place for the most part, looked tight and figured wth, but, apparently my shortcut came back around 7 months later to bite me in the #$$.... live and learn I guess.

If I had suspected ANYTHING would put me in this possition, I would have gone to the trouble to either A: port everything to my personal LINUX rig here at my office, OR transfer copies of everything to another domain on my webserver account to test it out...  Didnt see any real critical issues being reported that I couldnt handle with 1.3 sooo, figured would be safe with a simple sys & dbf backup, worse case RESTORE what I had then sort out the upgrade issue at a later date...... BAD CALL ON MY PART I guess..... oO

If you have any suggestions Im wide open to feedback.... Like I said (other than the couple of weird issues I had after the 1.3 upgrade, everything else appeard to work) Thing I would have been ahead of the game to simply cut my losses and reupload every pic from scratch.


-----------------------------------
spydie
Thu 30 Jul, 2009 23:37

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
What i suggest is a job that can take you a few hours off your time.

You open that sql file.

Thats nothing else then an wordpad sheet.

You go to your PhpMyAdmin ( on Hosting)

select your DB.

Then you select SQL

copy and past parts of that sql file there. run consult

and over and over again till you reach the end of that list.

DB restored.

Thats the manual way of it.

It takes time but it works


-----------------------------------
Lopalong
Fri 31 Jul, 2009 03:02

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
[quote][b]The problem …[/b]

A PHP-Script has a maximum of execution time; anything after above the limit (Usually sets to 30 seconds) will result in losing data. Such behavior renders large database backup impossible. Perhaps you already noticed how much of a problem it could be while using other tools.[/quote] 

[hr]

I suggest that you download this and install it to your server.

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

Create or EMPTY the current database.

DELETE all IP/cache/files* (THIS IS IMPORTANT!)

Upload your DB to mysqldumper and give mysqldumper the connection strings to your Db.

Then tell it to restore the uploaded Db.

PS: I haven't use it for a while, but I think I've got the procedure right. ;)

[b]Edit:[/b]

What operating system do you use on your computer. ?


-----------------------------------
Turbotech
Fri 31 Jul, 2009 13:44

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
Well, I managed to get the site up and running 100% as it was before the attempted 1.3 update "just before Spidies Last Post lol. For the record Spidys method will work however we  did it a slightly different way.

After tracking down the information in reguards to the IP_FORCE_READ Table error "and unfortunatly I do need to list this as an error. Went into the sql file and "removed the table itself in reguards to that issue "for the record we did NOT have a solution to fix the script error itself however it was eliminated, used BigDump.php and uploaded the data to a test database "with no tables". The result was all infos were uploaded to (112) tables a new error croped up, this had to do with a short span of French Posts that had been made in one of my forums "We have users from around the globe from 75+ countries. 

The error "and not saying this is a IP issue however you may wish to look into it", was created by a French Text Charactor [size=18][b]e'[/b][/size] It would kick back a duplicate entry error for say a word such as [size=18][b]joure'[/b][/size]. As there were few posts that had been posted in French "isolated to one forum", it was not any huge issue to track them down "in notepad" deleted the entries "however after the fact simply changing [size=18][b]e'[/b][/size] to [b][size=18]e[/size][/b] would have most likely been fine as well.

Each time to see where the errors may be we used BigDump.php to simply upload the script, find the "next error" produced by the French charactor untill it ran clean. 

After the basicly 2 changes that were made, 1 to the IP_FORCE_READ table, and the [size=18][b]e'[/b][/size] issue, the database uploaded to my test file without error, 100% completed as well as produced all 145 tables required to run IP.

[u][i][b]Please note:[/b][/i][/u] We are still scratching our heads trying to figure out exactly how the IP_FORCE_READ table was still created in the database itself especialy since it was in fact deleted from the sql script itself........ makes no sence.

Droped all tables in the Website Target DBF "after backing it up of course, didnt want to lose what took 2 days to get back even if it was no where near correct" Used BigDump.php to upload the semi modified dbf backup file to the website with 0 errors.

Launched the webpage, and everything has returned 100% to where it was before this all began. So far I can not find any issues, I have notified my users to be sure to POST to me should they come across "ANY" issues or oddities so that I can isolate the problem quickly
and report the results "if any" back to this thread.

NOw, just for the sake of saying it, I am NOT recomending this as a solution to the problem however this did work and considering a slight change in the database structure was made I am not saying that a problem may not stem from this action down the road however as posted above "I cant find any problems so far with the site itself".

My current OS is Windows 7 Ultimate x64 RC 7100, this was used to upload the data, current Browser IE8 < this combo should have absolutly no bearing on the sql error as the error encounterd was clearly on the server end of things.

The Webserver itself is hosted by 1and1.com Business Class Linux Webserver account.

Open to feedback and if you have additional questions please be sure to ask as I will be tracking this thread for some time "just incase".... And will provide any infos that I can to help track this issue.

[i][b]At this time I am NOT posting this thread as (SOLVED) simply due to the fact that a unconventional change was made in the database as I do NOT want others to attempt the same thinking this was a clean cut FIX.[/b][/i] Also this has been recomended by my wife who was the one who made the changes to the databse sql file. As posted above "I know very little about SQL and will not pretend that I do lol.... She on the other hand is a Teacher / Network Lab manager for the NSSA department at a local college and clearly identified the above posted Bigdump.php "error report" as a deffinitive problem at least where the upload of the backed up database is concerned however (again, we did not have a clear answer as to what could be done to correctly modify this IP_FORCE_READ table.... 

Again... The SQL Time issue you pointed out had nothing to do with this problem.... And I have clarified that fact with the above information... (reading closer may point that out and will post the info again if needed......)

I will also be willing to bet that now that THIS has been modified that I will be able to UPLOAD the same file (Gziped of course) to another databse via the IP ACP without resulting in a INTERNAL SERVER ERROR 500 "Which I have noted is a unanswerd question in may other threads. 

I will be recreating this entire website backup an another domain I have to A: test what I just posted, B: In hopes of installing the 1.3 upgrade without problems. but have no intrest in screwing with my live site as it has taken days to get it back up and running.


[i][b]DELETE all IP/cache/files* (THIS IS IMPORTANT!)[/b][/i]

Question......... where was this line in any of the IP UPGRADE Information? is this a common task that needs to be completed after a restore or, say the 1.3 upgrade? I did not find this in any readme file or instructions that I had found...

I would assume this applies to Any and or all Cache files of any type found within the ACP?

Better yet, umm, As I have NOT cleared any cache file ever on this site core (runing it 8 months now) Why does it appear to currently be working exactly as it had in the past?
[i]Please note: Again, we are NOT saying what we did above fixed anything I am simply trying to understand the issue over all lol.... Also I am not indicating that I think that I shouldnt need to clear it either, simply scrating my head at this point and want to be sure to do the correct things to ensure I have no additional problems....[/i]

If this is common knowledge apparently I have missed it.

Also I need to point out once again (If the IP_FORCE_READ TABLE WAS CLEARLY DELETED FROM THE SCRIP) how is it that it did in fact create a new table in the squeeky clean database file on upload? That one makes no sence at all. As posted above all (145) Tables were in fact created in the database even with the above posted table being 100% removed from the sql file itself... 

Any feedback or questions for additional information reguarding this issue is welcome ;)

Thx again Spidy & loop ;)


-----------------------------------
spydie
Fri 31 Jul, 2009 13:56

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
Glad you got your site back up and running.


-----------------------------------
Lopalong
Fri 31 Jul, 2009 22:48

Re: Problem Restoring DATABASE After Failed 1.3 Upgrade
-----------------------------------
[quote]My current OS is Windows 7 Ultimate x64 RC 7100, this was used to upload the data, current Browser IE8 < this combo should have absolutely no bearing on the sql error as the error encountered was clearly on the server end of things. [/quote]

The reason for this was to suggest that you download XAMPP (For Windows), mysqldumper and Notepad++ and install them all to your local drive(s) to give you a local server and the tools to work with it properly. But I'm not sure if any of it will work with Win7 and may need to be upgraded. Then you could do all of your testing locally.

Re: Clearing the cache.

It's all too common that most people get totally screwed up when things start going wrong and leave bits and pieces behind - Clearing the cache ensures that there is nothing in there that will mismatch the next attempt at fixing it.

;)


