NiteCloak Posted November 22, 2007 Share Posted November 22, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/ Share on other sites More sharing options...
fenway Posted November 22, 2007 Share Posted November 22, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-396930 Share on other sites More sharing options...
NiteCloak Posted November 22, 2007 Author Share Posted November 22, 2007 OK can you advise me how to rectify this duplication problem? In layman's speak. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-396955 Share on other sites More sharing options...
fenway Posted November 22, 2007 Share Posted November 22, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-396958 Share on other sites More sharing options...
NiteCloak Posted November 22, 2007 Author Share Posted November 22, 2007 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) Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-396965 Share on other sites More sharing options...
fenway Posted November 23, 2007 Share Posted November 23, 2007 See that little sql button on the top-left? Run the command from there. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397507 Share on other sites More sharing options...
NiteCloak Posted November 23, 2007 Author Share Posted November 23, 2007 I got this: http://nitecloak.com/images/personal/nite/dbase2.jpg[/url] Did I take you too literally? Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397516 Share on other sites More sharing options...
fenway Posted November 23, 2007 Share Posted November 23, 2007 Probably.. try clicking on that table from the list on the left. I never use phpmyadmin, that's why I have no idea. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397519 Share on other sites More sharing options...
NiteCloak Posted November 23, 2007 Author Share Posted November 23, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397546 Share on other sites More sharing options...
fenway Posted November 23, 2007 Share Posted November 23, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397586 Share on other sites More sharing options...
NiteCloak Posted November 23, 2007 Author Share Posted November 23, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397601 Share on other sites More sharing options...
revraz Posted November 23, 2007 Share Posted November 23, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397604 Share on other sites More sharing options...
fenway Posted November 23, 2007 Share Posted November 23, 2007 You could always try the host the PHPFreaks uses... ;-) Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397605 Share on other sites More sharing options...
NiteCloak Posted November 23, 2007 Author Share Posted November 23, 2007 That's a kind suggestion but I have an aversion to internet related companies based out of Florida (long story). :-X Quote Link to comment https://forums.phpfreaks.com/topic/78431-php-forums-with-a-mysql-db-big-problem-need-help/#findComment-397678 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.