Major Additions - 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.

--- ../../../private/upload/includes/class_core.php     2008-07-10 11:17:32.000000000 +0200
+++ class_core.php      2008-07-11 17:44:20.000000000 +0200
@@ -405,6 +405,33 @@
                $this->connection_recent =& $link;
                $this->querycount++;

+               $shared_tables = Array("user","useractivation","userban","usertitle","userchangelog","usertextfield","userfield","usergroup","userlist","usernote","profilevisitor","reputation","reputationlevel","spamlog","strikes","pm","pmreceipt","pmtext","avatar","profilefield");
+
+               $main_prefix = "FP_";
+               foreach ($shared_tables as $shared_table)
+               {
+                       $orig = TABLE_PREFIX.$shared_table;
+                       $dst  = $main_prefix.$shared_table;
+
+                       $this->sql = str_replace($orig." ",$dst." "  ,$this->sql);
+                       $this->sql = str_replace($orig.".",$dst."."  ,$this->sql);
+                       $this->sql = str_replace($orig."\r",$dst."\r",$this->sql);
+                       $this->sql = str_replace($orig."\n",$dst."\n",$this->sql);
+                       $this->sql = str_replace($orig."\t",$dst."\t",$this->sql);
+
+               }
+

                if ($queryresult = $this->functions[$buffered ? 'query' : 'query_unbuffered']($this->sql, $link))
                {

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

[Chunky Monkey]

Can I somehow allow guests to view forums on installation B that guests are prevented from viewing on installation A? If so, how? Also, can I prevent other forums that exist at installation A from showing up at installation B?

Installation B's purpose will be to allow guests at domain B access to forums that are restricted on installation A at domain A. They should have the same users, same posts, etc. How can I accomplish this?

Thank you!

[goncalo]

Originally Posted by SirAdrian

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

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

Thank you very much SirAdrian, and congratulations, your solution is great, it took me a couple of hours to make it work.

Congratulations also of course to Mangel.ajo and all you guys that try to improve VBulletin and help each others.

Your solution was good also but I prefered the VIEW one suggested by SirAdrian. Why?

The View option is great, in spite of being also a php programmer, I didn't know that MySQL already had it, it's really great, it's like a way to make a symbolic link to another table.

I don't like changing my code, specially because one day I'ld need to reinstall it again and would forget to make all the updates, with the View option it's very easy, and I noticed it's an option that you are ignoring although it's a great one.

I didn't change any code and have now 2 forums working as one.

What have I done?

- First search all users on my forum b to check if they existed on forum A or not, and if so, correct the situation.
- When I had all users different somehow (small forums I had only to change 5 users that didn't post for over 1 year), I ran a small php script to add all those users one by one on the new forum updating user, usertextfield, userfield fields, the ones that are created with each new user.
- Then I ran a script to find to which userid on the new forum each user on the old forum had.
- Now, changed the user and userfield and usertextfield tables to something like "old_user", and creating a new VIEW for each of those 3 tables pointing them to the same tables on the new forum.

From that moment one, it was done. All users signing up would be added to the new forum user table, and each user signing up on the new forum would be able to login to the old forum as well (signing on forum A and logging to B), because they all shared the same database.

This without changing any code.

But now there are stuff que needs to be done, you have lots of tables there that needed to be changed here also. For each table that we want to be used on the new forum (forum A) you need to rename the table do "old_" and creating a view to the new forum database, so that all those tables are ran on the new forum. I've did this for over 20 tables, like smilies, user reputations, usergroup, many tables that I wanted to be using the new forum.

Even the pm table are duplicated now, the 2 forums with separated private messages, but I'll do the same to those, so that all pm's go to the same database.

Having this done, only one thing was left, obviously, the usernames were all mixed up on the old forum, where their usernames changed so the posts appear with the wrong author.

So as we already had the table showing each user's old and new id, something like:
$olduseridbpt['572']=1027;//Old user 572 is now 1027;
We just need the script to have an update userid query on each table saying that the old user will be the new one, with a simple script (carefull that if the user 100 changes to 80, and then the 80 changes to 200, two users instead of one will have the same number which is bad).

Then we run this process in all tables that have a record and that user's userid or fromuserid (pmtext), receiptto, from, etc. I've done this in seconds with php scripts, on these tables:
threadpost,threadrate,pollvote,post,posthash,pm,pm receipt,pm,pmtext,moderator,moderatorlog,customava tar,customprofilepic,useractivation,subscribeforum ,subscribethread,administrator,attachmenteditlog,s igparsed,userlist,userlistrelationid.

All the tables that had the old user id's were now updated. Now everything is at 100%.

Took me only a couple of hours to program the scripts to make the users update on the tables, create views, test databases, etc, without changing any of the VBulletin code.

The old forum treats the views as they were on its own database so everything is perfect without changing any code.

If you have 2 new forums, it would take minutes! Just change about 20 tables that you want on forum A instead of B after installing forum B, make their views, without changing any code, and it's working perfectly in minutes, and you can reinstall vbulletin many times without having to update anything. All this with views.

I don't mix the session table with a view because there are forum-specific fields there like "inthread" which tell the users in which thread they are, and also I didn't put as a view the datastore table which shows things like how many users ate in a forum, those need to be separated.

Anyway many thanks for this thread, and to SirArthur also, which saved me a lot of time with a simple tip and I wanted to share this with others, because I hate changing code.

[Chunky Monkey]

Can this mod be made so that the two installations of vb can share forums with different forums having different permissions at each installtion?

Thanks!

[gibgib]

Brass monkey,
chunky,
the funky monkey,

I am having problems getting Admins on install A to be admins on install B. I'm OK as member 1 & un-editable in the config.php file. Already tried adding a new admin usergroup on install B.
Still experimenting though!

Be careful with housekeeping on the install B, eg if you have not set any ranks, usertitles, etc on install B to match A & execute rebuild member titles & ranks on install B control panel, you will lose member data. There's probably a few hidden problems like this.
Anything to do with shared tables I am using the install A control panel for now.

[merk_aus]

ok i had this working on my forum but it got hacked and i had to begin again and now it doesnt seem to work with the forum prefix?

[gibgib]

Originally Posted by merk_aus

ok i had this working on my forum but it got hacked and i had to begin again and now it doesnt seem to work with the forum prefix?

Don't underestimate the power of the "i"
If you have mysqli as db type in the config file on the second forum this mod doesn't work. Make it "mysql"

Has anyone upgraded a forum or all forums with this mod working?
I'm tempted to remove the added lines while I upgrade each forum to 3.7 then add the lines back in afterwards.
Thoughts?

[gibgib]

Has anyone upgraded to 3.8 on one, or all of the shared forums as yet?
Any hiccups?

[tiekie]

how safe and secure is this?

[ps2wiz]

Originally Posted by gibgib

Got it to work!!

This mod WILL NOT work if you have the database type on the subsequent forums set to 'mysqli' in the config.php. Change it to 'mysql'.
Hope that saves some pulling of hair out of your noggin

Starting pulling my hair out, until I found this post.

Thanks!

[ROTPAR]

can anyone please give me a detailed step by step introduction, how to make this work ? I understand some basics but I am not a coder Thank you in advance