Sessions Table issues

kalamitykatie · Post by **kalamitykatie** » Wed May 25, 2016 8:09 pm

We have an old Win 2003 server on which we host a long running and sometimes busy board v 3.0.10.
I have been asked to change the style sheet and install mods.
I am trying to back up the database using MySQL Admin prior to doing so.
The backup stalled out in the sessions table. The backup shows current number of rows and the row currently being backed up. The number of the last row backed up was actually greater than the number of current rows.
So then I started Googling about that table...
The table has 19802538 rows and that makes me think something is amiss. (board currently has 33 users online, 5 registered and 28 guests)
Every thread I saw urged backup before clearing the sessions table. Well, that brings me back to the original problem.
Purging the sessions in the ACP - is it likely, under these circumstances, to blow up?

And this whole process brings up another issue. I have Carbonite backing up the MySQL databases and the Carbonite file size is a mere fraction of the MySQL backup file size - Carbonite tells me that is normal because of their compression, but I'm concerned.

~Katie

Post by **Brf** » Wed May 25, 2016 8:15 pm

Just truncate the sessions table.
It will log everyone out, but that is OK.

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Wed May 25, 2016 8:19 pm

no need to backup before you empty the sessions table.
you can truncate( empty, not delete ) the sessions table and all three search tables without harm.

this will make your backup about 1/3 the size it would be otherwise.

the carbonite backup is probably only backing up in a certain way that minimizes the file size. don't worry about it.

you really should be looking at upgrading to 3.1 as 3.0 has already reached "end of life" and support for it will only last a bit longer.

robert

kalamitykatie · Post by **kalamitykatie** » Wed May 25, 2016 8:20 pm

In MySQL Admin or by purging in the ACP?

Thanks!

3Di · Post by **3Di** » Wed May 25, 2016 9:15 pm

kalamitykatie wrote:In MySQL Admin or by purging in the ACP?

Thanks!

I do not remember if in 3.0.x there is an ACP option for this.
Check your ACP/index page though..

shot from 3.2

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Wed May 25, 2016 9:38 pm

I forget that the option for doing that is in the admin panel.
yes, it is in 3.0 and 3.1 as well.

do it there and you don't have to worry about it.

robert

kalamitykatie · Post by **kalamitykatie** » Tue Nov 01, 2016 5:25 pm

CRAP!

Today that table crashed. I remembered this discussion, but not correctly. I dropped the table instead of emptying it.

Can someone help rescue me?

~Katie

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Tue Nov 01, 2016 7:53 pm

using phpmyadmin, access the correct database and run this query:

Code: Select all

DROP TABLE IF EXISTS phpbb_sessions;
CREATE TABLE `phpbb_sessions` (
  `session_id` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `session_user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `session_last_visit` int(11) unsigned NOT NULL DEFAULT '0',
  `session_start` int(11) unsigned NOT NULL DEFAULT '0',
  `session_time` int(11) unsigned NOT NULL DEFAULT '0',
  `session_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
  `session_browser` varchar(150) COLLATE utf8_bin NOT NULL DEFAULT '',
  `session_forwarded_for` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `session_page` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `session_viewonline` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `session_autologin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `session_admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `session_forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`session_id`),
  KEY `session_time` (`session_time`),
  KEY `session_user_id` (`session_user_id`),
  KEY `session_fid` (`session_forum_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

edit the table prefix if yours is not phpbb_

done.

Executing SQL Queries in phpMyAdmin

RMcGirr83 · Post by **RMcGirr83** » Tue Nov 01, 2016 7:58 pm

Code: Select all

DROP TABLE IF EXISTS phpbb31_sessions;

!=

Code: Select all

CREATE TABLE `phpbb_sessions`

phpbb31 != phpbb

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Tue Nov 01, 2016 8:00 pm

thanks, I forgot to edit that line when I pasted it here. fixed now.

robert

kalamitykatie · Post by **kalamitykatie** » Tue Nov 01, 2016 8:11 pm

THANK YOU for the response. I'm trying to follow your instructions and obviously (as otherwise I wouldn't be in this predicament) this is not my strong suit.

So, the database is selected.
Use Tables has all selected.
I pasted the query in the query box and submitted.
It returned "you have to choose at least one column to display."

(there are fields at the top of the page for field, sort, show criteria, etc.)

Thanks!
~Katie

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Tue Nov 01, 2016 8:34 pm

not sure what you are doing.

using phpmyadmin, find your database.
make sure it is listed on the left side of the page.

on the right side of the page you should see all the phpbb tables listed.

don't touch any of that.

on the top of the page you will see a tab that says sql.

click on that.

you will then see a big box. if there is any text in that box, delete it.

then , copy and paste what I posted above into that blank sql box.

then click the go button.

you should get a nice green success message.

you are done.

robert

kalamitykatie · Post by **kalamitykatie** » Tue Nov 01, 2016 8:43 pm

Thank you, thank you, THANK YOU!
I was on the query tab instead of the SQL tab.
Followed your instructions, got the wonderful success message, and the board is loading again.

Ever so grateful for your help!

~Katie

Lumpy Burgertushie · Post by **Lumpy Burgertushie** » Tue Nov 01, 2016 8:54 pm

good work! glad we could help.

robert