kjtocool
-
Posts
187 -
Joined
-
Last visited
Never
Posts posted by kjtocool
-
-
Thank you!!! I don't think I would have found that on my own.
-
The only way I've found that I can do this, is if I unset the connection variable after I close it, like so:
$this->connection->close();
unset($this->connection);
That allows me to check isset and correctly know if the object needs closed. I would prefer not to do that. I want to do something like:
$this->connection->close();
if (*some test to see if it's open*)
{
$this->connection->close();
}
else
{
// Already closed, don't need to do anything
}
-
That thread is the same question, but the user in that thread decided to simply manually close the connection. Never was it answered HOW you can test if a connection is open.
I am attempting to always close the connection in my destruct, but if it was already closed, i'm running into an error. I understand how PHP cleanup works, but I still want to close it manually in the destruct if possible as there are some benefits. To do this, I need to know how to test it, and can't figure it out. I even tried:
$this->connection->ping()
But that too throws the same error: mysqli::ping() [mysqli.ping]: Couldn't fetch mysqli
-
I have a class I use to handle my various DB objects. I want to close any connections, if needed, in my destructor. I want to do this even though I already know the connection has been closed previously. What I am looking for is a way to test the $connection variable I have to see if it's in need of closing. I can't figure out how to do this.
if (isset($this->connection)) { echo "is set"; } if ($this->connection) { echo "here"; }The above would output "is set" and "here", because in both tests say the already closed connection is still there. Can anyone offer any help?
-
I do, but not as recent as I'd like. I'd lose about 100,000 records worth of data.
I don't think it's that bad however. Even though the table is locked, I think I still have read access and should be able to make a full dump of the DB if need be. The issue seems to be an insert statement that hung, locked the table, and now the keys are all messed up.
I've decided to try repairing the table via command line, unfortunatly the command "myisamchk --recover --quick phpbb_posts.MYI" can't be used because it fills up the /tmp directory to 100% capacity then hangs as it waits for more space. As far as I know, the only way to grant that directory more space would be to reformat, ugh.
I'm trying instead to repair the table via command line using "myisamchk --safe-recover --quick phpbb_posts.MYI" which uses memory rather than the tmp directory. Unfortunatly it's also incredibly slow, so I'm waiting as it runs, hopefully if it completes my issue will be fixed.
-
I have a large phpbb forum, the phpbb_posts table crashed late last night. When I run a check on the table, I get:
worldofk_phpbb3.phpbb_posts check warning Table is marked as crashed and last repair failed worldofk_phpbb3.phpbb_posts check warning 1 client is using or hasn't closed the table prope... worldofk_phpbb3.phpbb_posts check warning Size of indexfile is: 1847600128 Should be: 1... worldofk_phpbb3.phpbb_posts check warning Size of datafile is: 858198988 Should be: 84... worldofk_phpbb3.phpbb_posts check error Found 1469781 keys of 1084641 worldofk_phpbb3.phpbb_posts check error Corrupt
Any attempt to repair the table causes Apache (httpd) to crash. Executing "Unlock Tables" executes successfully, but does not clear the lock on phpbb_posts. Restarting the MYSQL service does not clear the lock. Restarting the server did not clear the lock.
I'm out of ideas, any help is greatly appreciated.
-
Just curious, did you consider any of that?
At this point, no. And only because I know there is an issue with my current indexes. I figure it's more important to get the current indexes working before I start trying to optimize them even more.
As is, since all the keys are disabled, even had I added that index, it would run into the same issue where it would have a cardinality of none and never be used. Assuming enabling keys does fix my indexes/cardinality issue, I may then consider adding the index you suggested to test out the possible gain.
-
I think I found my issue!
I did the following query: SHOW INDEXES FROM phpbb_posts, which showed me that my keys were all disabled. I then ran the following query: ALTER TABLE phpbb_posts ENABLE KEYS
That query is currently running, and my guess is it will take quite a while as it will have to rebuild all the indexes. But, assuming it works, that SHOULD solve my issue. Wish there was more visibility into the keys being turned off!
-
Yep, that's my problem then. The cardinalities are all wrong. My selects aren't able to discount rows based on forum_id (which would limit searchs from 1.5 million rows to no more than 420,000 on the largest forum). If I can figure out why the cardinalities of those additional indexes aren't correct, or how to rebuild them, I'll solve the issue.
Any ideas? ???
An analyze SHOULD fix this issue, but it's not in my case.
-
I tried an optimize, didn't affect the cardinality, nor did it change the results of the explain. From what I know of cardinalities, there sure shouldn't be NONE for every index other than the primary key, and I'm sure that's one of the reasons it's forced to search all rows. I tried optimizing, repairing and analyzing the table. Everything came back looking normal, but no change was seen in the cardinalities.
I'm interested in the idea of table stats, but through a quick google search i wasn't able to find anything I understood that could help. If anyone knows more about table stats or analytical stats and any relevant command that would let me shed some light onto what is going on here I would really appreciate it.
-
I have a 1.5 million row table, it happens to be a posts table for a large forum. Each query that hits this table takes a long time (3 to 10 seconds). The table has various keys:
Keyname Type Cardinality Action Field PRIMARY PRIMARY 1430198 post_id forum_id INDEX None forum_id topic_id INDEX None topic_id poster_ip INDEX None poster_ip poster_id INDEX None poster_id post_approved INDEX None post_approved tid_post_time INDEX None topic_id post_time post_subject FULLTEXT None post_subject post_text FULLTEXT None post_text post_content FULLTEXT None post_subject post_text
Doin an explain on a few of the slow queries showed me that possible_keys, key, key_len and ref are all NULL. I am guessing, but am fairly certain that the lack of a key on a 1.5 million row table is what's causing the issue. I can't imagine searching all 1.5 million posts each time is a very efficient way to go about things. One of the slow queries:
SELECT post_id, topic_id, forum_id FROM phpbb_posts WHERE topic_id = 51544
Returns three rows, takes an average of about 3 and a half seconds. Explain on the query above returns:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE phpbb_posts ALL NULL NULL NULL NULL 1430559 Using where
The things that jump out to me are that A) the rows searched about 1.5 million and B) there are no indexes used, so it has to search ALL 1.5 million rows.
As this forum continues to grow, my understanding is it's going to get slower and slower. Unless I'm mistaken, the fact it must search ALL post records is the reason for the slow response. I am hoping that A) someone can confirm this and B) someone can tell me if there is anything I can do to speed up queries hitting the phpbb_posts table.
-
I just ran the following: SHOW VARIABLES LIKE 'max_allowed_packet'; Which returned: max_allowed_packet 33554432
I then tried an insert directly from phpmyadmin, and it now works! Unfortunately, the error still persists on the forum, and now I'm even more confused as to how to fix it. I really appreciate anyone who helps out, I'm losing my mind over here.
-
Heh, I went ahead and figured out how to add the following to my .my.cnf file:
[mysqld]
max_allowed_packet=32M
It didn't seem to do anything. Any other ideas?
-
Well, I got SSH access, now it's just a matter of finding the commands to accomplish what I want. Any suggestions?
-
Yeah, not running windows, or I would know how to do this. My issue is, I have no idea how to get to the file. There is no access through WHM, I know no other way into the server's files, and I can't connect with the root account via FTP to do it that way.
Thus, I'm left sitting here like an idiot, knowing something simple would fix my issue and no idea how to do it. So frustrating, I hate not knowing how to do things that are so simple.
-
How do I change my.ini?
-
I just tried the following:
SET GLOBAL max_allowed_packet=1000000000; insert into phpbb_bots values(1, 1, 't', 1, 't', 't');
Doing that allowed the statement to execute, but trying to do it without the first statement still doesn't work. It looks like that's my problem, I just don't know how to fix it. Anyone care to give me an idiots guide on how to do this?
-
62 different tables, standard phpbb 3.0, biggest is 659.6 KiB, most much smaller. All are MyISAM.
-
The forum runs fine, just like normal, until I go to post, at which point it hangs and gives the error. The issue just started today, has been fine for over a year, no server changes were made.
-
Hey guys,
I'm at my wits end here, and I really don't know what else to do. I operate my own dedicated server and a few sites on it, one site has a phpbb forum, and the forum is encountering issues. The error comes when trying to insert / update records:
SQL ERROR [ mysqli ]MySQL server has gone away [2006]
SQL
SELECT topic_type, forum_id FROM phpbb_topics WHERE topic_id = 0
BACKTRACE
FILE: includes/db/mysqli.php
LINE: 143
CALL: dbal->sql_error()
FILE: posting.php
LINE: 871
CALL: dbal_mysqli->sql_query()
The server is managed by me, and my host won't help. The only way they will take a look at it is if I pony up $300 for to switch to a managed server. I've restarted, I've upgraded, I've restarted services, I've repaired the database, I've optimized the database, but no avail.
The only other thing I can think of is increasing the max_packet_size, but I don't know how to do it. It requires to run something from the command prompt, and I have no experience with that, nor do I know how to do it. (If anyone wants to walk me through it, I'd be grateful) I only have experience within WHM.
When I try and manually insert, I get the following:
You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.Which reaffirms the idea that the max_packet_size is an issue. The thing I simply don't get is that I'm only trying to run: insert into phpbb_bots values(1, 1, 't', 1, 't', 't'), so how is this running into a size issue?!
-
Couldn't think of any other way to get the data how I needed it. Any ideas?
-
I have a query used in an AJAX call, it currently takes quite a while to come back. Was wondering if anyone could think of a way to optimize the query (or function):
function sort_forum_topics($db_conn, $sort_on, $sort_direction, $forum_id, $skip) { $mysql = " SELECT e.topic_id, e.forum_id, IF( SUBSTRING( UPPER( e.topic_title ) , 1, 4 ) = 'THE ', CONCAT( SUBSTRING( e.topic_title, 5 ) , ', The' ) , e.topic_title ) AS topic_title, e.topic_replies, e.topic_views, IFNULL(((a.score + b.score + c.score + d.score + f.score) / (a.num_votes + b.num_votes + c.num_votes + d.num_votes + f.num_votes)), 0) AS total_score, IFNULL((a.num_votes + b.num_votes + c.num_votes + d.num_votes + f.num_votes), 0) AS total_votes FROM phpbb_topics e LEFT JOIN (SELECT topic_id, poll_option_total * 5 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 1) a ON e.topic_id = a.topic_id LEFT JOIN (SELECT topic_id, poll_option_total * 4 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 2) b ON e.topic_id = b.topic_id LEFT JOIN (SELECT topic_id, poll_option_total * 3 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 3) c ON e.topic_id = c.topic_id LEFT JOIN (SELECT topic_id, poll_option_total * 2 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 4) d ON e.topic_id = d.topic_id LEFT JOIN (SELECT topic_id, poll_option_total * 1 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 5) f ON e.topic_id = f.topic_id WHERE forum_id = " . $forum_id . " AND topic_type != 1 AND topic_type != 2 ORDER BY " . $sort_on . " " . $sort_direction . " LIMIT " . $skip . ", 500"; $result = mysqli_query($db_conn, $mysql); $row = mysqli_fetch_assoc($result); $num_topics = Count_Topics($db_conn, $forum_id); $html = '<table border="0" cellpadding="0" cellspacing="0">'; if ($num_topics > 500) { $html = $html . '<tr> <td colspan="5" align="right"><span class="style1">' . Get_Page_Numbers($num_topics) . '</span></td> </tr>'; } $html = $html . '<tr> <td class="style2" width="350" height="35"><a href="#" class="topics_header" onclick="Header_Clicked(\'title\');">Topic Title</a></td> <td class="style2" width="100" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'replies\');">Replies</a></td> <td class="style2" width="100" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'views\');">Views</a></td> <td class="style2" width="150" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'votes\');">Number of Votes</a></td> <td class="style2" width="150" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'score\');">Average Score</a></td> </tr>'; while ($row) { $html = $html . '<tr> <td class="style1" height="20"><a href="http://www.worldofkj.com/forum/viewtopic.php?f=57&t=' . $row['topic_id'] . '" alt="Topic Title">' . iconv("ISO-8859-1", "UTF-8", $row['topic_title']) . '</a></td> <td class="style1" align="center">' . $row['topic_replies'] . '</td> <td class="style1" align="center">' . $row['topic_views'] . '</td> <td class="style1" align="center">' . $row['total_votes'] . '</td> <td class="style1" align="center">' . $row['total_score'] . '</td> </tr>'; $row = mysqli_fetch_assoc($result); } $html = $html . '</table>'; mysqli_free_result($result); return $html; }Any help would be greatly appreciated, as I can't seem to get it any quicker than it currently is.
-
I modified your code only slightly, because it was causing an error. I don't think it liked the +100 years bit, I tried 2099, but it didn't like that either, so I then tried 2031, which it took:
<?php $sd = '2007-10-22'; $ed= '2031-10-22'; $st = strtotime($sd); $et = strtotime($ed); $databaseConnect = mysqli_connect("localhost", "username", "pass", "db"); while ($st < $et) { $sdate = date ('Y-m-d', $st); $edate = date ('Y-m-d', strtotime('+6 days', $st)); $sql = "INSERT INTO table_name (start_date, end_date) VALUES ('$sdate', '$edate')"; mysqli_query($databaseConnect, $sql); st = strtotime ('+7 days', $st); } ?> -
Can anyone help point me in the right direction on how to go about populating a table in the way described below?
Basically I have a table: kj_weeks
week_id - int(10) ... AUTO_INCREMENT ... Primary Key
start_date - date ... NOT NULL
end_date - date ... NOT NULL
What I want to do, is populate this table starting now, and going out 100 years. I want to simply populate it like so:
id - start_date - end_date
1 - 10/27/2008 - 11/02/2008
2 - 11/03/2008 - 11/09/2008
etc
Is there a painless way I can do this, other than manually entering weeks?
How can I test to see if a connection is closed?
in PHP Coding Help
Posted
You're not wrong (for typical connections). The garbage collection will clean it up. There are some performance benefits however to closing it explicitly, you regain the memory quicker and other minor things. The main reason I'd advocate closing it manually is simply because it's good practice.
In this instance, the call to close the connection is actually a redundant one. It doesn't NEED to be done, it was just infuriating me that I didn't know HOW to do it.