Jump to content

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


Recommended Posts

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!

 

 

 

 

 

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.

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

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.

 

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.