what's broken?

[eMEraLdwPn]

ever since i bought a server, i've been having ridiculous problems with it. it constantly crashes and my server admin says it's because i'm using up too many resources.... i think the version of mysql is really messed up, for the following reason:

whenever i use phpMyAdmin, the queries do not work. i try to do something, and it executes at least two times. example:

Error

SQL-query :

CREATE TABLE `test_table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`field1` VARCHAR( 255 ) NOT NULL ,
`field2` INT NOT NULL ,
`id` INT NOT NULL AUTO_INCREMENT ,
`field1` VARCHAR( 255 ) NOT NULL ,
`field2` INT NOT NULL ,
PRIMARY KEY ( `id` )
) CHARACTER SET = latin1

MySQL said:
#1060 - Duplicate column name 'id'

the charset also refuses to be set to anything but latin1, which has resulted in really screwing up my tables, which have all become corrupted. this happens with whatever type of query i try to do... update, drop, etc. do you think this duplicate query problem is the fault of myPhpAdmin, or is it a mysql problem?

[chromate]

Easy way to find out is to SSH in, log in to MySQL and try using it from the command line. If it works out ok, then it's obviously phpMyAdmin. If not, then it's probably a MySQL setting.

I think your server admin is fobbing you off to be honest. Your server should slow down when you use a lot of its resources - not crash. If the server is crashing, then it's probably not set up right.

[The New Guy]

Uh just like it says you have "Duplicate column name 'id'". Look at the query you have two id's which cant be done.

[chromate]

He knows that's what the problem is. He's trying to figure out what is causing everything to be duplicated like that. I think?

[eMEraLdwPn]

yeah, like chromate said... i just enter the 3 fields to be created, and nothing works because it doubles them....

are you sure the server wouldn't crash from getting too much traffic? he told me that my code must be very unoptimized (which it really isn't, except for maybe one page, which isn't *that* bad) because i'm using a ridiculous amount of memory and the server can't handle it... i don't see why though, i generally only average like a pageview per 5 seconds during peak times

this is just really starting to aggravate me, since i started using this server the downtime has to have been at least 25%, a lot of the time during peak hours.... and then just randomly during the night too, when hardly anyone is using it

[Westech]

I don't think it's at all possible that heavy resource usage would cause this problem with phpmyadmin. I agree that it sounds like your admin is making excuses.

Try this (I'm assuming you're using Redhad Linux or something similar) :

1. Make a backup copy of the file /etc/httpd/conf/httpd.conf just in case this messes something up.

2. Open the file /etc/httpd/conf/httpd.conf and find two lines that look like this:
SetOutputFilter PHP
SetInputFilter PHP

3. Comment out those two lines by adding a # to the beginning of each one so they look like this:

#SetOutputFilter PHP
#SetInputFilter PHP

4. Save the modified file.

5. Restart Apache:
httpd restart
or
/etc/init.d/rc.d/httpd restart

6. Make sure your websites that use php still work. If they don't work, put /etc/httpd/conf/httpd.conf back the way it was and restart apache again to fix them. If they do still work, check phpmyadmin again and see if we solved the problem.


FYI, if you want to see if you're really using too many resources, you can check your server's load average by typing:
cat /proc/loadavg

You'll see something like this:
0.20 0.14 0.11 1/95 25716

If you check this during your server's peak usage times and the first three numbers are all less than around .80, it's a pretty safe bet that your server isn't loaded down enough to cause any real slowdown. If they are above 1.0, your server is being slowed down by high resource usage, but there may be some things that can be done to optimise the server to help it handle things better.

[eMEraLdwPn]

thanks westech.. i'm not entirely sure how to ssh in with root access, and i don't think i can do most of those things with my login, but i'll find out once my admin comes around and ask him for the other logs...

another problem i've had with the server, i had a script that allowed users to upload images. i've never had any problem with this script, but for some reason, 98% of all images that are uploaded now get corrupted. most can't even be viewed at all afterwards, some end up looking like this: http://www.lyricallysquared.com/uploads/f77660uiyyq%5B1%5D.jpg

seems to me something is seriously wrong, but i have no idea what...

[Westech]

No problem. If you show that post to your admin, I'll bet he can fix things up for you. (You might want to edit out the part about him making excuses... ;) )

Sorry, but I don't really know what could be causing the corrupted images. What script is it? If it's not a custom script you might check the script writer's website or do some Google searching to see if you can find a solution. Good luck!

[eMEraLdwPn]

alright, something happened today that pissed me off to the point where i don't even want to deal with this crap anymore... besides the fact that all my tables somehow become corrupted, today my most important field, which was of type longtext, changed itself to varchar(255) by itself, so i lost all my data. i have a backup from about a month ago, and my admin has one from a week ago... i asked him to do a restore, he said sure... after about an hour he said he was having a problem, then just went offline and never came back, leaving the site in it's ruined condition

i'm starting to doubt the fact that i even need a dedicated server to run my sites atm, i only have one large one, and it's only using about 300 mb space and 80 gig bandwith / month

there's some concerns i'm having about giving up the dedicated server though... if my script really is unoptimized, which i don't think it really is, and it's using a ton of resources, will it be able to run on a shared server without running really slowly?
also if i experience similar problems will i be able to get the support that i need?

lastly, if i did move onto a shared server, who would you use for hosting? i'd need someone who can provide a large amount of bandwith... i was looking at this package, seemed pretty decent, and the hosting company had good reviews.

http://www.hostforweb.com/hosting_supreme.html

sorry i have so many questions, i'm just really kind of stressed out from this whole thing, the last two months have been like hell with this server...

[MarkB]

I doubt that a db field would change itself - are you sure someone didn't just mess up?

[chromate]

I would just get yourself a new dedicated server with a different hosting company. Even if you don't need it now, it looks like you will need it soon.

[eMEraLdwPn]

i thought about it, but there's a few problems about getting a new server..
1) with all this downtime i'm barely managing to make profit because it's so expensive to have this server..
2) i paid $200 to have this server configured and firewalled, i don't really feel like paying that fee again and having the same problems arise
3) the same problems could arise...

do you think that these problems are all because of the server? i've had ev1 run a ton of checks, every time they find nothing wrong with it.. so i really don't know what the problem is...

[MarkB]

I'd look at your script first to see if it really IS unoptimised and killing your box.

[eMEraLdwPn]

is there a general rule of thumb for the amount of queries that are too many to process on one page? i'm assuming the amount of php coding doesn't really matter, that it's more about the sql...

[MarkB]

Why, how many are there now? And can they be lessened at all?

[eMEraLdwPn]

my friend rewrote part of this for me so i'm not entirely sure what every single query does, but it does look like there are a few useless ones

i think most pages have something like 30 queries (all for the side menu), and then depending on the page only 4 - 5 for the actual page

i'll see if i can find a way to dramatically decrease the queries on the sidemenu...

[chromate]

wow, 30 just for the side menu?! :) Depending on what the queries involve, I can see how this could be a problem actually.

EV1 are normally pretty good from what I've heard. So maybe they do have a point.

[eMEraLdwPn]

yeah, i thought all the queries for the sidemenu were in that file, but it looks like my friend put them elsewhere too... of course the server has been down for like 12 hours now so i can't even see if it will make a difference. most of the queries are just count(*) or getting the most recent result though, not sure how intensive that is...

edit: server actually came back up... there were like 15 queries on the sidemenu for some feature that was kind of pointless... so typical pages now have something like 10-13 queries, the most intensive pages have maybe 20-25 queries...

[eMEraLdwPn]

there's absolutely no way i can stay on this server anymore. as soon as the server came up, i made those script changes that should have made the code run much more efficiently. within... 20 minutes i was getting "too many connection errors" every 4 minutes. about an hour later, just after i had finished restoring the db myself from an old backup i had my admin came online and told me that the resource usage was outrageous or something. the site kept going up and down from various errors, but the server wasn't crashing...

today, the majority of the day the site just had one error: "Can't open file: 'lyrics_songs.MYI' (errno: 144)"

when i looked at the table in phpmyadmin, it just said "in use" and i couldn't do anything with it. i left for a while, came back, and the server was down again. so i'm wondering how it could crash when the site wasn't even running at all to begin with...

something else that i realized is that there is definitely something wrong with the server, not my data. my admin claimed that because of my code, it had caused my entier database to become corrupted. one thing that was wrong with it was while looking at it in myphpadmin, all the tables would show up as "unknown" size and "unknown" amount of records. after the restore, which i first restored into a separate test database, it was doing the same thing. so unless my entire database and the new test database became totally corrupted within a matter of seconds, i doubt any of this was my fault.

i guess now i just need to figure out what to do... and seeing as how i was friends with my admin from college it's going to be rough telling him that something is wrong, it isn't my fault like he says, and i'm not dealing with this **** anymore...

[chromate]

Hold on to your current server. But get another server at the same time, copy all your stuff over and see if it works out ok on the new server. If it does, then you can tell your admin that you have a different server somewhere else anyway and that it doesn't suffer from any of the same problems (hopefully!). Then you can get rid of your old server.

That way you can be absolutely sure it is the server and not your code. nocster.com looks good, but I don't know anyone that's used them.

[r2d2]

Was this site working OK somewhere else? What kind of traffic levels were you getting then? What are you getting now? Can you not use some kind of caching to prevent 15 queries just for the sidebar?

[eMEraLdwPn]

my admin has a hosting company and was letting me use one of his machines before this.... overall it was running pretty well, occasionally it would get really slow and take like 30 seconds for a page to load, but it was an older machine and i was sharing it with about 200 people... traffic now is more or less the same, but i couldn't tell you for sure because the server has never managed to stay online for more than 24 hours straight in a month and a half..

i modified the sidemenu and cut it down to i think only 3 or 4 queries, but there was no improvement at all... it actually got even worse.

[Blue Cat Buxton]

Have you tride it without the side menu? that would then rule out that element as causing the problem?