The House of Carpe
https://forums.houseofcarpe.com/

Forums/DB Update
https://forums.houseofcarpe.com/viewtopic.php?f=84&t=7539		 Page 1 of 1
Author:  Moog [ Sun Aug 10, 2025 10:30 pm ]
Post subject:  Forums/DB Update
With the Captain's Log post I got a weird error twice (once trying to view the page the other trying to update it):

Quote:
SQL ERROR [ mysqli ]

Out of range value for column 'topic_views' at row 1 [1264]

SQL

UPDATE phpbb_topics SET topic_views = topic_views + 1....[rest of the error]


When I looked at the database structure I saw it's a mediumint so I updated it to be a bigint. I find it hard to believe this one topic has had this many views...

Code:
MariaDB [houseofcarpe]> select topic_views from phpbb_topics WHERE topic_id = 147;

+-------------+
| topic_views |
+-------------+
|    16777215 |
+-------------+
1 row in set (0.001 sec)

MariaDB [houseofcarpe]> ALTER TABLE phpbb_topics MODIFY topic_views BIGINT(20) unsigned;
Query OK, 7113 rows affected (0.162 sec)               
Records: 7113  Duplicates: 0  Warnings: 0


I think this is one of the only pages accessible to the greater internet without logging into the forums on an account, so I guess it's possible it's just been crawled a fuckton of times.

Either way my change made it so the max value went from 16777215 to 2^64-1 (I guess I COULD have made it be just a regular int which maxes out at 4294967295 but I don't want to have to deal with it again.

Hopefully this means anyone else who happens to be looking at the topic won't see an error (which went away after refresh, presumably phpBB has some way of just saying "yup, I threw an error but they really want to see the topic"
Author:  Moog [ Sun Aug 10, 2025 11:13 pm ]
Post subject:  Re: Forums/DB Update
Ok so poking around more I also found we are almost out of disk space so I went looking at at that. Deleted some old old log files but also for some reason the forums had a 5Gb "forum sessions" database file.

Code:
MariaDB [houseofcarpe]> select count(*) from phpbb_sessions;
+----------+
| count(*) |
+----------+
| 27437506 |
+----------+


I'll be working on scrubbing a bunch of these out, I originally tried to wipe everything where last visit was before Jan 1 of this year but it crushed the system, so I'll do a year or two at a time.
Author:  Moog [ Mon Aug 11, 2025 1:01 pm ]
Post subject:  Re: Forums/DB Update
Aaanddd I crashed the sessions table which crashed the database... But it's fixed, and we're back

TL;DR - I deleted everything that was session user id of 1, which I'm assuming is the session value to everything that hits the front page even if they don't log in.

Code:
MariaDB [houseofcarpe]> SELECT session_user_id, count(DISTINCT session_id) FROM phpbb_sessions GROUP BY session_user_id;
+-----------------+----------------------------+
| session_user_id | count(DISTINCT session_id) |
+-----------------+----------------------------+
|               1 |                   13261708 |
|               2 |                          4 |
|              53 |                         15 |
|              55 |                          1 |
|              63 |                          4 |


Now we are using half our allotted disk space instead of 96% and things should run a little snappier without the db bogged down like that.
Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/