PHP Forums with a MySQL DB - Big Problem - Need Help

[NiteCloak]

Dear All,

 

I just found these forums and I hope you guys can shed some light on a very taxing issue I have.

 

Background Summary:  I run SMF Forums (latest release), a PHP based forum with a MySQL database back-end running on a Linux Box and hosted by GoDaddy!

 

I have contacted my hosting provider GoDaddy, regarding the speed of the server, access times etc and they insist (for the 3rd time) that it isn't them and that the problem lies with my MySQL database for the forums.

 

I have also posted (several times) on the SMF Community Forums but they have been little to no help.

 

The Problem:

 

Basically, any time I or my users try to click on any link in my forums now, whether as a user or as admin, whether a link to a topic or a link to posting/replying to a topic, we are getting enormous page delays to the point where after minutes of waiting, nothing happens.  I end up with a blank screen, or, and here's why I am here at phpFreaks, we end up with this message:

 

: Lost connection to MySQL server during query

/home/content/n/i/t/my-DB-name/html/forums/Sources/Load.php

390

 

All my forum users are complaining of the same thing.  Pages not loading at all or if they do, taking forever or multiple attempts or receiving the message above.

 

I am not a coder or programmer so my abilities are minimal but I do know how to listen and follow directions.  I downloaded UltraEdit (very good text editor by the way) and managed to find out what line 390 pertained to.  Here is what I got:

 

line384 {
		$request = db_query("
			SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType
			FROM {$db_prefix}members AS mem
				LEFT JOIN {$db_prefix}attachments AS a ON (a.ID_MEMBER = $ID_MEMBER)
			WHERE mem.ID_MEMBER = $ID_MEMBER
line390  		LIMIT 1", __FILE__, __LINE__);
		$user_settings = mysql_fetch_assoc($request);
		mysql_free_result($request);

		if (!empty($modSettings['cache_enable']) && $modSettings['cache_enable'] >= 2)
			cache_put_data('user_settings-' . $ID_MEMBER, $user_settings, 60);
line396		}

 

Now I know this is the SQL help board and here is why I chose to post this here rather than the php help board.

 

I also managed to figure out how to log in to phpMyAdmin and I although I am clueless I was able to figure out enough to select all the 'SMF' tables and run a 'check all' and these three error lines keep popping up:

 

Problems with indexes of table `smf_messages`

Warning UNIQUE and INDEX keys should not both be set for column `ID_TOPIC`

Warning UNIQUE and INDEX keys should not both be set for column `ID_MEMBER`

Warning More than one INDEX key was created for column `ID_MEMBER`

 

 

Clearly, they seem to be related to the php line 390 error.  Can someone please advise me what the problem is and how to go about fixing it?

 

 

The SMF Community blame GoDaddy and GoDaddy blames the SMF MySQL d/base and here I am stuck in the middle with a bunch of irritated forum users not knowing what to do.

 

Thanks in advance!

 

 

 

 

[fenway]

The "index problems" you speak of would just represent a slight performance hit... while I'm surprised that these indexes are duplicated, it shouldn't cause the error you describe.

[NiteCloak]

 

OK can you advise me how to rectify this duplication problem?  In layman's speak.

[fenway]

 

OK can you advise me how to rectify this duplication problem?  In layman's speak.

If you run a SHOW CREATE TABLE LIKE `smf_messages`, you'll see the duplicates -- since UNIQUE is just a special case of INDEX, there's no need to have them both.  You can simply drop the exra ones.  Post the output and I'll tell you what to do.

[NiteCloak]

I'm sorry to sound like a complete idiot but I have NO idea how to do what you just asked.

 

When I log in, this is what I see:

 

 

Where do I go from here?

 

Oh and Happy Thanksgiving (assuming you celebrate it)

[fenway]

See that little sql button on the top-left? Run the command from there.

[NiteCloak]

I got this:

 

http://nitecloak.com/images/personal/nite/dbase2.jpg[/url]

 

Did I take you too literally?

[fenway]

Probably.. try clicking on that table from the list on the left.

 

I never use phpmyadmin, that's why I have no idea.

[NiteCloak]

When you say run a "SHOW CREATE TABLE LIKE `smf_messages`"

 

Did you mean I needed to type in exactly this --->  SHOW CREATE TABLE LIKE `smf_messages`  Quote marks and everything?

 

I tried a few combinations of what you wrote and I got a result from typing this --> SHOW CREATE TABLE smf_messages

 

Here is the output:

 

CREATE TABLE `smf_messages` (
`ID_MSG` int(10) unsigned NOT NULL auto_increment,
`ID_TOPIC` mediumint( unsigned NOT NULL default '0',
`ID_BOARD` smallint(5) unsigned NOT NULL default '0',
`posterTime` int(10) unsigned NOT NULL default '0',
`ID_MEMBER` mediumint( unsigned NOT NULL default '0',
`ID_MSG_MODIFIED` int(10) unsigned NOT NULL default '0',
`subject` tinytext NOT NULL,
`posterName` tinytext NOT NULL,
`posterEmail` tinytext NOT NULL,
`posterIP` tinytext NOT NULL,
`smileysEnabled` tinyint(4) NOT NULL default '1',
`modifiedTime` int(10) unsigned NOT NULL default '0',
`modifiedName` tinytext NOT NULL,
`body` text NOT NULL,
`icon` varchar(16) NOT NULL default 'xx',
PRIMARY KEY  (`ID_MSG`),
UNIQUE KEY `topic` (`ID_TOPIC`,`ID_MSG`),
UNIQUE KEY `ID_BOARD` (`ID_BOARD`,`ID_MSG`),
UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_MSG`),
KEY `ipIndex` (`posterIP`(15),`ID_TOPIC`),
KEY `participation` (`ID_MEMBER`,`ID_TOPIC`),
KEY `showPosts` (`ID_MEMBER`,`ID_BOARD`),
KEY `ID_TOPIC` (`ID_TOPIC`)
) TYPE=MyISAM

[fenway]

Excellent -- good work.

 

So let's look at the messages you got originally:

Warning UNIQUE and INDEX keys should not both be set for column `ID_TOPIC`

Warning UNIQUE and INDEX keys should not both be set for column `ID_MEMBER`

Warning More than one INDEX key was created for column `ID_MEMBER`

You can see from this:

UNIQUE KEY `topic` (`ID_TOPIC`,`ID_MSG`),
UNIQUE KEY `ID_BOARD` (`ID_BOARD`,`ID_MSG`),
UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_MSG`),
KEY `ipIndex` (`posterIP`(15),`ID_TOPIC`),
KEY `participation` (`ID_MEMBER`,`ID_TOPIC`),
KEY `showPosts` (`ID_MEMBER`,`ID_BOARD`),
KEY `ID_TOPIC` (`ID_TOPIC`)

That the `ID_TOPIC` key is already contained in the `topic` key -- hence it's redundant.

 

As for the id_member field it's complaining about, all the keys are different two-column indexes, so that's fine; and is the cause of the 3rd and final message.

 

Personally, since it's third-party software, I'd contact them, and do nothing about it.  But if you really want, you can drop the last key.

[NiteCloak]

 

Thanks for the valuable feedback. I guess being a total n00b the error messages concerned me because they seemed to imply (to me) that fixing them might improve my situation but from what you are saying, there may be some redundancy but it shouldn't impact performance in the manner I have been experiencing.

 

I'm beginning to suspect that my problem is GoDaddy as a php/mysql host. If I had the money I would love to replicate what I have on a dedicated server instead of a shared hosting environment.  I'm suspecting some of my issues are being caused by that more than the code but I guess I will never know for sure.

 

Thanks for all your help... and patience.

[revraz]

From what I've read, Godaddy doesn't even support their packages.  I'd find a new host.  There are plenty of good php/mysql hosts for under $10/mo.

[fenway]

You could always try the host the PHPFreaks uses... ;-)

[NiteCloak]

 

That's a kind suggestion but I have an aversion to internet related companies based out of Florida (long story).  :-X