MySQL Database Server Overload Site Blocked

Worker Joined: May 08, 2007 Posts: 130

Hi

My hosting company has temporarily suspended my phpnuke driven site due to a mysql server overload that happened and they saying its happening because of my site.

Fact is its on a shared hosting account and i believe they have overcrowded the server with thousand accounts..

They want me to optimize the following queries as they were the highest queries showing in their log

Code:




Time: 7% (2060 sec)


Amount: 6% (138 queries)


Rows Examined/Sent: 17519 / 160


Avg. Query Exec/Lock Time: 14 / 5 sec.


Used databases: [database_name]


Query example: use database_name; SELECT time FROM nuke_session WHERE uname='65.55.208.25';





Time: 6% (1807 sec)


Amount: 6% (122 queries)


Avg. Query Exec/Lock Time: 14 / 5 sec.


Used databases: [database_name]


Query example: use database_name; UPDATE nuke_stories SET counter=counter+1 where sid=20392;





Time: 5% (1680 sec)


Amount: 5% (120 queries)


Avg. Query Exec/Lock Time: 14 / 4 sec.


Used databases: [database_name]


Query example: use database_name; UPDATE nuke_stats_hour SET hits=hits+1 WHERE (year='2007') AND (month='09') AND (date='10') AND (hour='12');





Time: 6% (1887 sec)


Amount: 5% (115 queries)


Avg. Query Exec/Lock Time: 16 / 6 sec.


Used databases: [database_name]


Query example: use database_name; UPDATE nuke_counter SET count=count+1 WHERE (type='total' AND var='hits') OR (var='Netscape' AND type='browser') OR (var='Other' AND type='os');





Time: 6% (1747 sec)


Amount: 5% (113 queries)


Avg. Query Exec/Lock Time: 15 / 5 sec.


Used databases: [database_name]


Query example: use database_name; DELETE FROM nuke_session WHERE time &lt; 1189441800;





Time: 5% (1521 sec)


Amount: 5% (111 queries)


Avg. Query Exec/Lock Time: 13 / 4 sec.


Used databases: [database_name]


Query example: use database_name; UPDATE nuke_stats_month SET hits=hits+1 WHERE (year='2007') AND (month='09');





Time: 5% (1500 sec)


Amount: 5% (104 queries)


Avg. Query Exec/Lock Time: 14 / 5 sec.


Used databases: [database_name]


Query example: use database_name; UPDATE nuke_stats_year SET hits=hits+1 WHERE year='2007';





Time: 4% (1424 sec)


Amount: 5% (104 queries)


Rows Examined/Sent: 3174080 / 2496


Avg. Query Exec/Lock Time: 13 / 4 sec.


Used databases: [database_name]


Query example: use database_name; SELECT hour FROM nuke_stats_hour WHERE (year='2007') AND (month='09') AND (date='10');

Does these queries look normal to you or are they are abnormal in nature? I checked the ip address as mentioned in the 1st query thats a msn ip can be a bot.

Any idea how do i stop these queries or optimize it and get my site up by following their fair usage (ahem!) policy..

Site is blocked for 36 hours almost now....also will be nice if some people can suggest names of companies which provied dedicated server option in an economical budget and good service quality.

Thanks in advance

Posted: Sat Sep 15, 2007 3:00 am

I must admit I don't know whether the above stats are normal, or not. Hopefully Raven, or one of the other Site Admins can comment, but if this is being caused by a bot you can add a crawl delay to your robots.txt file.

What version of Nuke are you using? Are you using Sentinel?

When it comes to hosting your answer is right in front of your face. Wink

Raven provides hosting for a number of people who use these forums and I believe he is very competitive.

Posted: Sat Sep 15, 2007 3:45 am

Any idea on what to add on the robots.txt file for crawl delay?

Nuke 7.0 and Yes Sentinel is being used want to upgrade both soon in fact.
They asked me to optimize the queries..any idea what to do? I do regular optimize database from inside php nuke.

What i can think of is
A) Add that crawl delay to robots.txt
B) Deactivate Stats Module as lots of queries happening there

I checked out some dedicated server options but now i feel managed dedicated server is what i need and that again is beyond my budget. So staying there and controlling the data usage is a viable option for me right now.

I have checked Raven's plans but i get a lots of bandwidth eaten up - site does around 1k uniques a day - mainly text traffic though

And they are yet to activate it even though i have stated that i am agreeing to their request for compliance etc..around 8 hrs back!

Posted: Sat Sep 15, 2007 5:22 am

The crawl delay has been discussed before in these forums, so if you do a search you should find the answer........ but to save you time have a look at this thread: http://www.ravenphpscripts.com/posts11725-highlight-.html Wink

I would definitely recommend asking Raven for a quote. Send him your monthly stats and see what he says.

Upgrading might help to optimise the queries, if you were to go for RavenNuke.
A lot of work has gone into minimising the queries to reduce the server load and decrease page load times.

Posted: Sat Sep 15, 2007 5:44 am

Great will use that delay switch. And Upgrading becomes a priority now when i get the database back.

They have firewalled the database off so even i cannot access it!

Planning to upgrade to 7.6 first then will try to shift to RN distro. But that also some time taking meanwhile they want me to optimize the queries...i mean when everything goes back up again..

Ya wont mind asking for a quote actually

Posted: Sat Sep 15, 2007 7:31 am

That is ridiculous, how do they expect you to change anything if you can't access your database, or files!?! Confused

RN is essentially 7.6 at the heart, but with loads of improvements, so I would consider going to RN as soon as possible.

I would recommend installing XAMPP on your computer and testing the uprade process before applying it to your site.

registered · Posted: Sat Sep 15, 2007 8:19 am

bugsy, the queries are normal for a busy site. If you can reduce the number of queries cause by bots, yes, that will help. The part which concerns me is not the number of queries but the time these are taking.

Do you Optimize your database on a frequent basis? For busy sites, you have to do this often. If you do not know what I mean, than that could be your problem... Wink

There is an Optimize function within the nuke Admin Control Panel which does this, so you don't even have to use phpMyAdmin.

If you ARE optimizing every few days, then I am wondering if you lost some indexes on these tables. Once you have a working XAMPP on your computer, you can use mySQLDiff to analyze the differences in indexes between your site and maybe something like RavenNuke.

Posted: Sat Sep 15, 2007 8:23 am

Thats ideally speaking Hosting Company playing GOD Smile

they transferred my database to a different location or blocked it from access due to that load distribution problem. I called them and they told me some angel support person will respond to my ticket very shortly (Its 12 hrs i was forced to say i will agree to their clauses etc) .. they event told me if you do not agree to our request of compliance we will Not make the site live! Thats hosting highhandness or blackmail in other words. .

Anyway i once tried a switch to RN in XAMPP only but everything got installed and ran only my old data like news stories and user tables did not get transfered so i decided to upgrade till 7.6..will make it a priority as soon as the present crisis is solved.

Files i can access..database i cant..so can't the config file for sure!

Posted: Sat Sep 15, 2007 8:58 am

Montego, the database gets optimized everyday by that nuke optimization switch only. Gets optimized 5 times a week actually.

I can try that MYSQL Diff ... but will not comparing with 7.0 will be a better option? Table s must have changed anyway with upgrades..

Once site is up first thing i will do is get the bot delay implemented and deactivate the stats module. It takes up lots of data space also.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

It appears that they wan you to optimize your queries, not necessarily the database. Very different issues and complexities.

If you desire a hosting quote then look at http://www.ravenwebhosting.com for some examples. If you need a customized quote then please send me an email and include the disk space and bandwidth needed.

Posted: Sat Sep 15, 2007 7:16 pm

bugsy wrote:

Great will use that delay switch. And Upgrading becomes a priority now when i get the database back.

They have firewalled the database off so even i cannot access it!

Planning to upgrade to 7.6 first then will try to shift to RN distro. But that also some time taking meanwhile they want me to optimize the queries...i mean when everything goes back up again..

Ya wont mind asking for a quote actually

This post should help you in your conversion.
http://www.ravenphpscripts.com/postx14146-0-0.html

Posted: Sun Sep 16, 2007 1:45 am

Raven, i will try to figure out a requirement sheet and send it to you...and i checked out that post and will try another switchover in Xaamp.

One major problem i faced is in loading the sql queries in HediSQL and even editing it - my sql file is over 80 mb! Its because its got over 20000 news stories have been publihed (We distribute news releases online)...so maintaining stories and user accounts is a priority for me.

But one good thing is i have already customized the RN.2.10 scripts as per requirement..like carrying ads and all...during the last run so all i have to do is try the processes mentioned there.

And as for the present database site is still down I am getting one support mail a day almost from them...and chances are there they blew the database by accident. They said they do backup twice a week..so hoping not to loose too much of data.

Unfortunately...my own backup is one and half months old so will be short of..750 stories and min 100 members i think.

Posted: Sun Sep 16, 2007 11:06 am

Try these posts for help in loading large tables Smile

http://www.ravenphpscripts.com/postt12774.html

Also, search the forums for bigdump. That should solve your problems.

Posted: Sun Sep 16, 2007 12:24 pm

Hi thanks all Smile

The site is finally up again...it gave me the paranoia of my lifetime! Have deactivated some modules which i felt can do with out and also implemented that bot delay robots.txt.

I tried another go at migrating to RN2.10 ...tried both the methods as discussed in http://www.ravenphpscripts.com/postx14146-0-0.html

a) First method

Quote:

What I did to convert phpNuke 8.0 to RavenNuke_v2.10.01
- Backed up my old database
- Installed RavenNuke_v2.10.01 into a subdirectory
- Created a NEW Database
- Imported using phpMyAdmin the database backup I created from my phpNuke 8.0 Site into the NEW database
- Ran the database down grader script that was NOT tested on phpNuke 8.0 DOWNLOAD IT HERE But it seems to have worked for me.
- I ran ALL the database primers except for CORETABLES found in the INSTALLATION/installSQL.php

It just didn't work for me - I upgraded till 7.6 instead of downgrading from 8 like him.

Second Medthod

Quote:

You can convert to it. I converted a 7.9 site to it.

In my case, I used the MySQLDiff tool to convert the database table structures of 7.9 into the format expected by RN. After that, it is just a matter of exporting the data out of the old site and importing it into the new with phpMyAdmin and/or bigdump.

Then you just delete the old files and upload the new ones. If you had modified any of the 8.0 files you'll have to carry those edits over to RN (if you want them).

I ran a MySQL Diff and first got confused on which switches to be kept on and which switches to be kept off and tried some combinations and when i tried to apply the changes on the old database some invalid "cid" entry kept popping up.

Any idea which switches i have to keep on in MYSql Diff?

They are:
Options: MySQLDiff 1.5.0
Yes No Change table type
Yes No Alter table options
Yes No Consider auto_increment parameter
Yes No Alter table charset
Yes No Alter comments
Yes No Generate hint on changes in attribute format
Yes No Merge statements
Yes No Use Backticks for table and attribute names
Yes No Syntax highlighting
Yes No Move foreign keys to the end of script
Yes No Deactivate foreign key checks before script run.
Yes No Create INSERT- or REPLACE-statements for selected tables.
Yes No Show connection state

I believe I have to keep the 7.0 database as source and RN2.10 as Target and the apply the diff in 7.0

If any idea on this one pl do let me know

And as for the time being just hoping the site doesnot push the mysql servers too much!

But ok site is up again cheers Smile

Posted: Mon Sep 17, 2007 5:59 am

I usually use the default values with the exception of I switch off the "Alter table charset". However, I would compare the charsets on your tables between your 7.0 local and 7.0 original just to makes sure they did not change when you imported this into your local mySQL database.

Posted: Mon Sep 17, 2007 6:37 am

ok...you mean to say..
1) Compare charset difference between Production Database 7.0 Backup SQL File with XAAMP Imported and Installed Database 7.0
2) Then apply the required changes if required.
3) Then Upgrade XAAMP Database to 7.6
4) Install locallly a RN2.10
5) Compare Upgraded 7.6 and Installed RN2.10 with Default Values and switching off the "Alter table charset".
6) Apply the difference to 7.6
7) Install RN files with config pointing to 76.6 Difference applied database.

I am using HeidiSQL for importing data etc that i hope is a good choice for this kind of work and hopefully it is not what changing the tables..!

I have downloaded the Production database form the site using the conventional PHPNuke Databackup process so it should not get changed when backing it up in local machine i guess.

Will have another go in this process and see where it goes..If i can migrate to RN2.10 theres nothing like it. Amount of hacks i am implementing in 7.6 to install all the modules that are pre-installed in RN....is ..quite some days job!

Posted: Mon Sep 17, 2007 6:03 pm

Yes, that pretty much sums it up.

I have never heard of HeidiSQL, so I'll have to look that one up. I have always used BigDump.

Posted: Sat Sep 22, 2007 11:46 pm

Hi,

The database server again overflowed and they have again blocked the database. I have temporarily restored a backup database in someone else's mysql server and calling it remotely from the present site so at-least normal functions continue.

From the restored database the site is running properly but any kinda posting is not happening. Two instances caught my eye..

a) I posted a notice (nuke_messages) and after hitting submit a blank page loaded and message did not get posted..later i posted it from phpmy admin and its showing

b) I logged in as a normal user and posted a news there too the news did not get posted and this message came over blank page "_Error"

Any idea how to solve this and get the site working?

Database was exported by PHP MyAdmin and was imported by HeidiSQL and is being called from site remotely from another server. All features running except posting...

Posted: Sun Sep 23, 2007 6:53 am

bugsy, I've never tried to run nuke with a remote database. I am surprised even that your host allows external connections on that port through its firewalls nor the other host allowing them in.

Are these just intermittent issues or every time?

I'll say it again, time to get a new host.

Posted: Sun Sep 23, 2007 7:13 am

I tried as a user quite a few times with same results "_Error" while posting news as normal user and blank page while posting message as admin.

On the contary as a normal user i was able to post in my forums.

I am using a VPS Hosting account for running the back up database. I will change host for sure but that has to be in a proper researched manner..and also talking with some people but this remote process has to continue till permanent shifting is done..its running in a sub domain so have to shift whole domain to new hosts and that will take some time.

Somehow i need the makeshift remote arrangement to pull the site till permanent solutions are worked out...but if users can't post news...I am back to square one!

Posted: Sun Sep 23, 2007 11:34 am

It has anything to do with table structure of the imported nuke stories database?

Code:




sid int(11) No 0      


catid int(11) No 0      


aid varchar(30) No           


title varchar(80) Yes NULL      


time datetime Yes NULL      


hometext text Yes NULL      


bodytext text No           


comments int(11) Yes 0      


counter mediumint(8) Yes NULL      


topic int(3) No 1      


informant varchar(20) No           


notes text No           


ihome int(1) No 0      


alanguage varchar(30) No           


acomm int(1) No 0      


haspoll int(1) No 0      


pollID int(10) No 0      


score int(10) No 0      


ratings int(10) No 0      


associated text No

What i intend to do is once i get the old database back will run a MySQL diff between old and new databases and if i see any thing has changed in new will apply that (Will keep olddatabase as target and new as source) ..though its more of a hunch and not backed by solid evidence!

Posted: Mon Sep 24, 2007 5:29 am

I cannot see anything wrong with your nuke_stories table. Sad

I wonder if charset or collations could cause an issue, but unfortunately, I don't know much about that.

I think mySQLDiff is a good route to take. But if everything looks right, then what...

Posted: Mon Sep 24, 2007 6:56 am

They have released the database and I ran a mysqldiff and got this result

Code:

SET FOREIGN_KEY_CHECKS = 0;





#


# DDL START


#


ALTER TABLE nuke_autonews


    MODIFY anid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_autonews.anid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_banner


    MODIFY bid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_banner.bid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bannerclient


    MODIFY cid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bannerclient.cid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbbanlist


    MODIFY ban_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbbanlist.ban_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbcategories


    MODIFY cat_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbcategories.cat_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbdisallow


    MODIFY disallow_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbdisallow.disallow_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbforum_prune


    MODIFY prune_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbforum_prune.prune_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbforums


    MODIFY forum_id smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbforums.forum_id changed from smallint(5) unsigned NOT NULL DEFAULT '' COMMENT '' auto_increment to smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbgroups


    MODIFY group_id mediumint(8) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbgroups.group_id changed from mediumint(8) NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbposts


    MODIFY post_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbposts.post_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbprivmsgs


    MODIFY privmsgs_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbprivmsgs.privmsgs_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbranks


    MODIFY rank_id smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbranks.rank_id changed from smallint(5) unsigned NOT NULL DEFAULT '' COMMENT '' auto_increment to smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbsearch_wordlist


    MODIFY word_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbsearch_wordlist.word_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbsmilies


    MODIFY smilies_id smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbsmilies.smilies_id changed from smallint(5) unsigned NOT NULL DEFAULT '' COMMENT '' auto_increment to smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbthemes


    MODIFY themes_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbthemes.themes_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbtopics


    MODIFY topic_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbtopics.topic_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbvote_desc


    MODIFY vote_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbvote_desc.vote_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_bbwords


    MODIFY word_id mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_bbwords.word_id changed from mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_blocks


    MODIFY bid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_blocks.bid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_comments


    MODIFY tid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_comments.tid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_contactbook


    MODIFY contactid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_contactbook.contactid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_downloads_categories


    MODIFY cid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_downloads_categories.cid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_downloads_downloads


    MODIFY lid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_downloads_downloads.lid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_downloads_modrequest


    MODIFY requestid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_downloads_modrequest.requestid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_downloads_newdownload


    MODIFY lid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_downloads_newdownload.lid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_downloads_votedata


    MODIFY ratingdbid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_downloads_votedata.ratingdbid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_encyclopedia


    MODIFY eid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_encyclopedia.eid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_encyclopedia_text


    MODIFY tid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_encyclopedia_text.tid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_ephem


    MODIFY eid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_ephem.eid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_faqanswer


    MODIFY id tinyint(4) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_faqanswer.id changed from tinyint(4) NOT NULL DEFAULT 0 COMMENT '' auto_increment to tinyint(4) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_faqcategories


    MODIFY id_cat tinyint(3) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_faqcategories.id_cat changed from tinyint(3) NOT NULL DEFAULT 0 COMMENT '' auto_increment to tinyint(3) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_groups


    MODIFY id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_groups.id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_groups_points


    MODIFY id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_groups_points.id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_headlines


    MODIFY hid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_headlines.hid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_journal


    MODIFY jid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_journal.jid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_journal_comments


    MODIFY cid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_journal_comments.cid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_journal_stats


    MODIFY id int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_journal_stats.id changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_links_categories


    MODIFY cid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_links_categories.cid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_links_links


    MODIFY lid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_links_links.lid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_links_modrequest


    MODIFY requestid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_links_modrequest.requestid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_links_newlink


    MODIFY lid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_links_newlink.lid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_links_votedata


    MODIFY ratingdbid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_links_votedata.ratingdbid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_message


    MODIFY mid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_message.mid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_modules


    MODIFY mid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_modules.mid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_pages


    MODIFY pid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_pages.pid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_pages_categories


    MODIFY cid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_pages_categories.cid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_poll_desc


    MODIFY pollID int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_poll_desc.pollID changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_pollcomments


    MODIFY tid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_pollcomments.tid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_popsettings


    MODIFY id int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_popsettings.id changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_priv_msgs


    MODIFY msg_id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_priv_msgs.msg_id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_public_messages


    MODIFY mid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_public_messages.mid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_queue


    MODIFY qid smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_queue.qid changed from smallint(5) unsigned NOT NULL DEFAULT '' COMMENT '' auto_increment to smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_quotes


    MODIFY qid int(10) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_quotes.qid changed from int(10) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_referer


    MODIFY rid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_referer.rid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_related


    MODIFY rid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_related.rid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_reviews


    MODIFY id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_reviews.id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_reviews_add


    MODIFY id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_reviews_add.id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_reviews_comments


    MODIFY cid int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_reviews_comments.cid changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_seccont


    MODIFY artid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_seccont.artid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_sections


    MODIFY secid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_sections.secid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_stories


    MODIFY sid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_stories.sid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_stories_cat


    MODIFY catid int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_stories_cat.catid changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_subscriptions


    MODIFY id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_subscriptions.id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_topics


    MODIFY topicid int(3) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_topics.topicid changed from int(3) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(3) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_users


    MODIFY user_id int(11) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_users.user_id changed from int(11) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(11) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_users_temp


    MODIFY user_id int(10) NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_users_temp.user_id changed from int(10) NOT NULL DEFAULT 0 COMMENT '' auto_increment to int(10) NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





ALTER TABLE nuke_xml_feeds


    MODIFY id tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '';


#


#  Fieldformat of


#    nuke_xml_feeds.id changed from tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '' auto_increment to tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT ''.


#  Possibly data modifications needed!


#





#


# DDL END


#





SET FOREIGN_KEY_CHECKS = 1;

...now when i tried to apply the changes mysql rejected it in the first entry itself!

Anyway nothing is mentioned regarding news posting here only auto news entry and lots of other ones.

Old database was running on

MySQL client version: 4.1.20
Protocol version: 10

PHP: 4.3.11

And new one is powered by

MySQL client version: 5.0.27
Server version: 4.1.22-standard
Protocol version: 10

PHP: 4.4.6

Now in new server i can go ahead with PHP 5+ and MySQL 5+ but in both cases i decided to stay with 4 as earlier one used to run on 4!

I am planning another dump out of the original database using export in phpmyadmin and keeping the defaults

SQL export compatibility: MySQL 40

Structure
* Add AUTO_INCREMENT value
* Enclose table and field names with backquotes

Data
* Complete inserts
* Extended inserts
* Maximal length of created query: 50000
* Use hexadecimal for binary fields
* Export type: Insert

Now is that the right way to export?

AUTO_INCREMENT value has made lots of entries in MySQLdiff

Posted: Tue Sep 25, 2007 2:45 am

This is the query i am going to make on the nuke stories database (What got dumped from PHPMyAdmin

Code:

 


CREATE TABLE `nuke_stories` (


  `sid` int(11) NOT NULL,


  `catid` int(11) NOT NULL default '0',


  `aid` varchar(30) NOT NULL default '',


  `title` varchar(80) default NULL,


  `time` datetime default NULL,


  `hometext` text,


  `bodytext` text NOT NULL,


  `comments` int(11) default '0',


  `counter` mediumint(8) unsigned default NULL,


  `topic` int(3) NOT NULL default '1',


  `informant` varchar(20) NOT NULL default '',


  `notes` text NOT NULL,


  `ihome` int(1) NOT NULL default '0',


  `alanguage` varchar(30) NOT NULL default '',


  `acomm` int(1) NOT NULL default '0',


  `haspoll` int(1) NOT NULL default '0',


  `pollID` int(10) NOT NULL default '0',


  `score` int(10) NOT NULL default '0',


  `ratings` int(10) NOT NULL default '0',


  `associated` text NOT NULL,


  PRIMARY KEY  (`sid`),


  KEY `sid` (`sid`),


  KEY `catid` (`catid`),


  KEY `counter` (`counter`),


  KEY `topic` (`topic`)


) TYPE=MyISAM AUTO_INCREMENT=20808 ;

In case this helps to understand the problem of stories not getting posted... I can't attach site to old database again as it will again overload their server so have to stick to remote database till long term issues are worked out.

I intend to take two more data dumps from PHPMyAdmin

a) Once Turning off Add AUTO_INCREMENT value
b) Secondly Turning Off "Add AUTO_INCREMENT value" and making "SQL export compatibility: NONE" and test both versions in case one dump works proper...again going on hunch.

Posted: Tue Sep 25, 2007 6:41 am

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.

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 would NOT turn on any SQL compatibility. I have had that screw up my dumps every time.

The only real hick-ups that I have found are in the Charset / collation and in even the export dump timing out.

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.

I don't have time atm to go back and re-read some threads regarding this, where fkelly had run into this and determined the root cause issue. It might be worth pointing him to my reply here with a PM and see if he is able to give you a summary of the issue.

Sorry that I did not think of this earlier. I just assumed the environments were the same as you did not mention differences earlier. Again, sorry that I didn't think to ask... Sad