MySQL Database Server Overload Site Blocked

Worker Joined: May 08, 2007 Posts: 130

montego wrote:

Actually, you might be better off using cpanel to backup your database and then using that in an import through cpanel on your new host. (Hopefully they are both using cpanel?). I am just thinking about the whole charset issues.

No not C Panel....In one case HSphere and in another case they calling it Site Manager

montego wrote:

You most definitely WANT to include the Auto-Increment value so that it is properly set to where it was left off (unless you have added new records and the counts would be off, but I think you are going to drop and re-create your tables anyways right?

I am planning to drop everything from new server and load everything in new server again

montego wrote:

I would NOT turn on any SQL compatibility. I have had that screw up my dumps every time.

So will keep it to none

montego wrote:

All of core RN will work fine with mySQL 5.0.x, however, that is not 100% so with standard PHP-Nuke. So, now I am wondering if you are getting mySQL errors upon the inserts. Probably something to do with some of the default values.

Ya right now i am sticking to MYSQL 4x though client version is 5 and i am running PHP nuke right now

Will message fkelly and wait for the response......any leads welcome Smile

Till am being able to post the news site is half functional!

Posted: Tue Sep 25, 2007 7:33 am

Hello! I don't see any message but I was just "checking in". I don't have time today to work on this extensively. Here's what I can recall from out of my grey matter.

1. If you are using MYSQL 5, there is some kind of "strict" parameter that can be set that gets very sticky about sticking non-integer values into integer fields. There are places in Nuke where it might try to stick say a '' into an integer field. MYSQL 4 and even MYSQL 5 without the strict parameter will just ignore it and stick a zero in but with 5 and strict turned on you could be generating an error. Sorry I don't recall the exact parameter but you can find it in the MYSQL documentation.

2. 20,000+ is a lot of news articles. I'm not saying it should not work but I doubt anyone else has tested that. In most places in Nuke where it reads the stories table it places a limit on the number of records it reads but if there's someplace where it loops thru the whole table, that could be causing issues with performance. I don't know that's the cause, you seem to be having problems with inserts.

Other than that: bigdump is a wonderful tool. And you will eventually need a real hosting service, something like Ravenwebhosting if not the real thing so you might as well move on that. You also can't beat having XAMPP running or some other kind of local test server. I'd get a nice little sequential dump of that table you are having trouble with and then you could pound away at it locally until you can resolve the problems.

Posted: Tue Sep 25, 2007 7:54 am

Hi fkelly...I have actually selected to install MySQL4 4.1.22 in the remote server but in PHPMyAdmin there its written

On the left column

# Server version: 4.1.22-standard
# Protocol version: 10

On the right column

# MySQL client version: 5.0.27
# Used PHP extensions: mysql

Right now trying a dump with "Database Compatibility" turned OFF and also unchecked the "ADD Auto Increment" as i will redo the install..and yes problem is only happening in posting news/messages (Inserting new data from the site). Yet to check if new members can signup or not..i am talking with some people for hosting and all but right now the temporary arrangement has to run properly till everything gets worked out!

Just hoping that my export imports are happening properly

Posted: Tue Sep 25, 2007 8:39 am

Only thing that i am taking out from the dumped sql before installing it is this line

CREATE DATABASE `database_name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `database_name`;

as i already have created a new database with phpmyadmin with collation the default value

Posted: Tue Sep 25, 2007 12:35 pm

Hi strangely every things back in proper running condition now! Every things getting posted...tried out posting stories (admin end +User end) and admin message postings etc...looks quite ok to me now!

This is what i did

a)Exported the database with the following changes in settings..
* Database Compatibility: None
* ADD Auto Increment: Turned Off

b) Deleted the line

Code:

CREATE DATABASE `database_name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `database_name`;

c) Created a New Database

d) FTP uploaded the database to the Big Dump Directory

e) Used Big Dump to load the databse form the sql file that was uploaded to the server.

f) Pointed the site to the new database

Thats it! .... all started working smoothly!

Thanks everybody for all your valuable inputs and hoping the problem wont resurface again!

This will do as a temporary solution till long running things are worked out at my end!

So i believe it was either the export settings or the import software ...last time i used HeidiSQL this time used BigDump

Cheers Cheers

registered · Posted: Wed Sep 26, 2007 5:53 am

Glad you are back up and running!

Yes, I've always had good luck with BigDump. Glad it worked for you too.

Posted: Wed Sep 26, 2007 11:07 am

Thanks all of you guys for all the inputs Smile