DB crash

[Husker]

Hopefully, I have that one fixed. The sessions table got screwed up. I noticed that there was no one posting last night, but it was not crashed. I was able to post a couple test messages, so I figured it was just people not being active. Well, this morning, there was an SQL error, so I have to rebuild some stuff.

I hope things are once again running well.

H.

[rad14701]

Yeah, I got a couple MySQL errors last night and then noticed no new posts after that...

[Husker]

I saw a few errors also, but was able to post, so I figgered no problem and went to bed.

I might have to look at emptying the data from the sessions table. It is large, and I think phpbb may have problems keeping it properly cleaned up. There are sessons that go back several years, I know they are not current. I will have to look at what is best, and possibly make some adjustments this weekend.

However, now that I have my first DB repair under my belt, I do not feel worried about knowing what to do in the future. I know UJ was looking at moving to to a different DB system that is more stable (postgre or something like that). That may be something we look at in the future (not yet however). That is NOT a trivial change.

H.

[rad14701]

I've written several auxiliary MySQL cleanup scripts in the past... It should be easy to write one up that does a cleanup based on the unix time stamp... Never been fond of Postgresql but it does handle transactions and rollback better than even the newer MySQL DB types...

[Bushman]

Husker, thanks for getting her back up and running This is a really an active forum and if there is a long lag between posts you can pretty well guess there is probably an error someplace! Both you and Rad were talking a different language than I interpret but I'm glad you understand each other.

[Samohon]

Thought I was the only one have problems H...

Off topic:
The cable company have been out digging up the roads here, put my connection down for nearly 3 days. Withdrawal symptoms were bad so I got one of those mobile broadband dongles to tide me over. At least now I can connect to the forum...

On Topic:
I think there is a module filter for that very thing H, but if not, I'm sure Rad will rattle up a MySQL cleanup script for us in no time at all, If I may be so presumptuous Rad...
Thanks for all the hard work keeping the boards active guy's....

[rad14701]

I'm sure Rad will rattle up a MySQL cleanup script for us in no time at all, If I may be so presumptuous Rad...

Only if asked... I haven't played with phpBB in years but could figure out the proper PHP script if I needed to I'm sure... Virtually 100% of my non-HTML coding these days is in PHP and Javascript... Ooppss...!!! There I go again with the technical jargon...

[Husker]

I looked at the phpbb3_sessions table, and there was no older data in it. There may have been some lost records which caused problems (when I was looking before).

Removing the data is/would be easy:

select * from phpbb3_sessions where session_time < number; (to delete, simply replace select * with delete).

Then simply find the unix time_t for whatever time you want to nuke records from, and place it in the above select in the 'number' location. To delete, simply change "select * " with "delete". However, before hitting the DB in a destructive way like that, I would want to see what was listed on phpbb, to make sure there was not something that would be damaged by a delete like this. I do not believe there are any foreign keys, but it is always better to dig into what side effects may happen, before pulling the trigger (and backing up first, just in case).

SQL is pretty trivial, and very powerful. You can do a LOT with just a little text, but you have to have a good understanding of the underlying relationships contained within the tables. I do not have that knowledge. I have only a cursory amount of knowledge, about a few tables.

H.

[rad14701]

That's the query, Husker... I just write PHP scripts so I don't have to dive into MySQL at the command prompt or via phpMyAdmin if I know I might be running the same query in the future...

For the session date you can use the following MySQL function in the query: UNIX_TIMESTAMP('2011-05-01 00:00:00') with the desired date...

[MuleKicker]

Hopefully the data bytes didnt get sucked into the matrix.

[Husker]

it is not post or pm, or wordlist type data.

When you log into phpbb, it creates a session 'key'. Thus as long as you are connected with same browser, IP, etc, this session stays open, and phpbb associates this to your PC. I am pretty sure this session key is also part of the session cookie that is stored on your local PC.

Keeping this type information about all users, keeps phpbb from having to do some more expensive computations every time a browser requests a file. However, this data is totally transient. There is no 'content' value to it at all, other than to help phpbb speed things up.

However, this is also one of the most active database tables in the entire phpbb system, and I think this one is more likely than any other to get corrupted, if problems happen. However, the nice thing, is if this table does get corrupted, it does no harm to the system (other than bringing the site down, because the phpbb queries now all fail).

H.

[rad14701]

Sessions and cookie management is one of the trickiest parts of complex web applications like phpBB... The amount of information they need to track can be substantial... Writing and debugging the background code that performs this management is not one of my favorite tasks... It's way down on my list to be honest... Unfortunately, it's a necessary evil...