Share users database among many forums
 

This is a feature requested many times, and we are still waiting, so I released a patch to be able to run many forums with the same user database.

This is an EXPERIMENTAL PATCH I'm running on my forums.

You can add "session" to $shared_tables if you want to share your sessions among forums under the same domain (Thanks Lionel)

This is only useful for new forums joining anothers one user database.

Requirements:

• An old forum where all users are.
• Sharing the same database, using diferent prefixes for tables (TABLE_PREFIX)

For example: Imagine we have FORUM_A , FORUM_B, and FORUM_C each one with their respective licenses.

FORUM A has table prefix FA_ in database, FORUM_B has FB_ and FORUM_C has FC_

FORUM A is the main, and older forum, where we have all our users.

then we would install FORUM_B and FORUM_C normally, using the same database that FORUM_A uses, but setting up their own table prefixes.


once FB_ and FC_ are installed then we go patching class_core.php from FORUM_B and FORUM_C setting up $main_prefix = "FA_"; so they use FORUM_A for shared tables.

Possible problems:

• memcached issues?, not sure
• avatar/signature pics/etc issues when storage is set to disk.
• more to come...

Author: Optimizacion Web

yea this might come in handy

I implimented a similar one where the seperate forums just shared the user, pm and one or two more tables and it works pretty well. However it does require mass changes to the FB and FC PHP files where yours would not?

If I read your correctly it looks like if the table being queried is in the array it will use the FA table prefix?

I have been looking for something like this. I will follow.

Another (similar) solution is to use MySQL 5's views.

(Example: db2.user is a view of db1.user)

Is good to know that your modifications worked, It makes me feel better :D

And, yes, the result is that when any table in "shared tables" is queried the query ends up rewritten so it goes to the FA_ prefix.

Hmmm, if database can gives us that functionality it could be interesting because we wouldn't need to patch at all.

Can you point us to some document about mysql5 views?

How do you setup a view?

Greetings,
Miguel ?ngel.

add to your array
also take a look at this

Thanks Lionel!!

Anyway, I'm not sure if the table "session" would be safe to be shared among all forums... why? ... because it points to the threadid or URL the user is watching, and that would be different between one forum and another.

Anyway it would be nice because this way the total user activity in the forums would be reflected.

Session is useful only if it's for different forums from the same site. It will avoid double login and keep who's online synchronized.

In the example above, I remembered that I had to do those 2 changes also (version 3.60) since they appeared to be independent from class_core

nice, thank you

I gave a more concrete example in this thread.

Yes, for the same site it's ok. In my config I have different domain names. So I should keep session table independently

[QUOTE]

I have tried to create/delete a usergroup from admincp in the secondary forum, and it worked fine, so I suppose that this change is not needed anymore (at least for 3.7.2PL1) :-)

Thanks Lionel! :)

I doubt if those tables are safe to be shared: "reputation","spamlog"

They point to postid, and probably they would only mess. What do you think about that? Should I take them out?

I never used "reputation" and "spamlog" in the other integration 3.07 => 3.60 from 2 years ago.

This will just be duplicate content in search engines. If you think this is going to give you extra exposure your right, however at what cost? getting dropped from the search engines.

There is need for this tho, and only in some situations, eg. maybe you got a support board that is for registered users only and you have a few websites, then that would be useful.

Thanks for sharing, I do look forward to seeing this progress, however I currently don't have that need for it like some sites do. Just for the other sites who think great I can now have several domains but with the same forums showing, this is a really bad idea.

What I want is not to get my members indexed, the real intention of this patch is to make my users life easier to move between forums.

Greetings honstar1337!

I must have been really tired when I made that post lol. I can now see a much larger purpose for this mod. (I was thinking something much different yesterday lol).

I might actually end up using this afterall. Thanks.

this is really interesting and something that I was thinking about since a while..

I'm gonna keep an eye on this, and maybe I'm gonna use it soon for some reason

thanks for sharing man

best regards

:)

So, if you have 4 forums active, with members and posts now, you would first need to merge the databases manually, making sure each have their own prefix in the new mega DB. Then, somehow import just users from the other 3 into the primary one, and merge accounts for duplicate users. Then install the script, and use primary site user tables from there forward. That sound right?

Second, how is performance affected? I have several 1 million+ post forums, probably 100,000 members between them. After combining it all, the DB would be 4-6 gigs. Those are some large table scans. When server load is heavy now, I get issues with a 1.5 gig DB being accessed repeatedly (getting new server, but still).

So, yes, I know you need a solid server just to handle a large DB situation in general, but, how much overhead does this script use, and could it "crap out" if dealing with large amounts of data like this. Several 1000+ users online sites accessing the same DB concurrently.

Disk based avatars and profile pics need to be addressed, as anyone with a lot of members most likely moved to that. Xcache, memcached, eaccelerator issues need to be tested for sure. I couldn't use that until it was in some way.

Question - I take it this does not "log you in" to another site, on another domain, it just makes your user account available to log in again if you go there, correct? So you are logged in and viewing site A, then jump to site B, you would need to log into site B then again. Site A and B are different domains. Correct?

RE: Mysql view, I looked into that, and many people online say that it is not designed for high volume usage, and could actually slow things down. Tho, I just quickly scanned, and don't have deep knowledge of it in general. I'd like to hear how the view technique would work in the above situation too.

And, for the record, I would love VB to have something built into the backend for this officially, more integrated and optimized. Would allow for some really cool stuff.

Hmmm, that's the most complex situation, joining forums that are already running.

First of all, you would have to merge all the users in the Primary forum database, and then -somehow- manage to change all your userid's from pms, posts, threads, and so one to the new ID of the joined database. I'm not sure but that could require specific scripts.
[/quote]

It should add len(shared_tables)*4 str_replaces to every SQL query your system is doing. Probably is not too much, but it's load, of course. (We could do some tests to discover how much is that.)

We should also think if mysql would also penalyze us by having all the tables in one database with same prefix.

We will have to analyze that. May be running some test over real vbulletin querys and measuring time.

I've been thinking more deeply about memcached / xcache an so on, and probaby it won't be a problem, but we must test.

Yes, exactly, that's it. Don't know if it would be possible to develop some kind of mechanism to move around forums using the different session tables to move around forums.

I've never tested it, and for the simplest idea (having the mirror of a diferent table it should'nt be so slow. But analyzing VIEW in depth you can see it let's you make some complex views selecting some rows in tables, and that its a complex situation, and being prepared for that could be the reason of slowliness.

Absolutely: me too.

Hi,
is it possible that maybe some forums like off topic or so can also be used by more than 1 forum?

does that work if the new forum in another server and have completely another domain?

I just want to make sure... This is for Seperate URL's/Licenses to share the same User tables. Correct?

When I download the file I get a diff file and I do not know what to do with it.

Thanks

Could you please tell me where in class_core.php I put the code?
There seems to be many sections that this could go in.

I am guessing I put in the code with the "+"'s on the front of the line and
to remove those "+"'s as well?

Thanks

hello,

or should he put the php codes I put in class_core but I have this error

Parse error: syntax error, unexpected $end in /home/www/fd0ea7be7c752c55ff235159345d0e2a/web/tnawrestling/includes/class_core.php on line 3326

sorry for me english

Is this the project to run several different Forums (search, new Thread, Categories...) but with the same userdatabase and login?

I requestet this about 5 years ago in vbulletin support forum and i got the answer, that this wont work.

It is very interesting for sites with very different content categories. This is a very nice try and i hope you get it done.

best regards
rob

What should I do if my main forum used no prefix?Shoud I use this:
$main_prefix = " ";

i installed my main forums without a prefix. im having probs getting it to work. also do you leave the + on the code?

I've used the the view method suggested in the past by Sir Adrian.

The only issue I had was with PM's since we wanted to keep them separate between the forums, and the user table stored the stats for that. So now I'm working on that issue.

if you had the sites on the same hosting. couldn't you make cookies sub-domain based? and treat the other site as the same session? also, i notice you can't pm members or the avatars aren't carrying over. and its db based file storage

Thanks for this hack.

Ok, I guess this should work if avatars are stored in the database instead of files, but for some reason, in User CP, 'Do not use an avatar' is checked instead of 'Use Custom Avatar.' Which table holds that setting?

ETA: Ok, found it -- just add the 'customavatar' table to the list.

Ok, I have a quick question, I have a old user back up from a forum I had a year ago, can I use this to revert data bases in a way I can use the old emails to the new forum?

Can I do this?

Hmm, for some reason, I can't perform searches... It always returns 'no matches'...

Anyone make this work when your board does not use a pfefix for you database?

You can go without prefix on your main database (where users lie), changing to: $main_prefix = "";

but the new forums that you add, which will use the main user database should have prefixes, and should be new forums, you cannot join old forums with their own user databases.

Uhh, I have to check that.

For me it's working:

If I search here for "pruebecita" it works
http://www.economiahoy.es/foro/search.php

this forum is using the user database from:

http://www.foropymes.es/foro/

Could you give some details of your problem?

I got it up and running and every thing seems to function perfect besides one thing.

I store my avatars on disk (file system) instead of the database storage. Anyone know how to properly link this? I am scratching my head on this, as avatars do not show up.

Store the avatars back to the database and add 'customavatar' to the list.