Master Master MySQL replication with Liferay Database (Issues)

[Prakash Khanchandani, modificado hace 8 años.]

Here is the situation:

• 4 Liferay application servers
• 4 MySQL database for each servers
• Everything is same in all the Liferay Servers and MySQL - file-system, Hardware, OS, portlets, plugins etc
• All these servers are not in cluster.
• All the 4 servers are situated geographically far away from each other.
• Out of the 4 only one server is exposed to Internet and the remaining are used in LAN and connect only few hours a day to Internet to update themselves.
• For file system we have set-up rSync using unison, so that all the file-systems across the 4 servers are in sync.
• Most of the admin access the internet server from outside the LAN and other users can access their respective servers within the LAN depending in which office they are.

Problem:
The data on all the servers needs to be synced and updated asynchronously.

Here is what we did

• Had set-up a mysql master-master replication using native mysql and later Tungsten.
• It seemed to work but quickly we faced the normal errors which users face when using Master-Master replication

Issues we are facing

• Duplicate entry exceptions (the most critical one)
• Lucene index not updating
• Database cache not updating

Envrionment:
Liferay 6.2 CE GA4 bundled with Tomcat
MySQL 5.6

Is there any way I can sync up the 4 DBs to have the same data and not have the issues listed? Or any other way to do this? Any insight would be helpful.

Thank you

[David H Nebinger, modificado hace 8 años.]

Certainly none of this.

Indexes and caches will not be updated because they are maintained by Liferay, not mysql, so those won't be updated.

Basically you're kinda hosed. Geographically spread or not, what you have is effectively a broken cluster.

Each individual Liferay accessing it's local tomcat is going to create new records based on the counter values available to it. It has no information about any other Liferay instance running and therefore will end up creating new records that will collide with those of other systems.

Because you want to sync the database throughout, you effectively have a single database and four separate Liferay instances all stepping on each other trying to update records. Since they are not set up as a cluster they are not notifying each other of index changes (which even if they could the other instance won't yet have the record in the local database).

Your only hope for this working would be to manually adjust the counter table of each individual instance to guarantee the ranges of keys used would be unique. If you go this route you'll have to make sure that you exclude the counter table from the sync process (lest all instances get back to using the same conflicting keys).

That will only fix your key conflicts, but it won't affect the indices. You could force reindexing at startup on the Liferay instance (by deleting the local lucene dir while the instance is down), and the db cache would be forgotten at shutdown.

[Prakash Khanchandani, modificado hace 8 años.]

Thanks David for confirming my doubts.

The counter solution seems viable, let me see what can be done.

[Prakash Khanchandani, modificado hace 8 años.]

So here is what we did following David's suggestion:

1) MySQL master-master replication ignoring the Counter table.
2) Updated all the keys for the records in Counter table differently in all the databases. Deployment of a new service is a pain, but we are working towards it.
3) Created scheduler to index Lucene once a day on the servers for the entities specified (since it takes around 5 hrs) when those local servers are not used.
4) Included timeToLiveSeconds attribute in ehcache XMLs

This is still not the best and the sync is anyways not real time, but seems to be working for now without the major key-conflict and the data is outdated for a day, which is tolerable for now.

Would want Lucene indexes to be replicated somehow instead of re-indexing which makes the server useless for that time.

Thanks

[Amos Fong, modificado hace 8 años.]

I'm not sure this configuration is going to work...do all the servers sync with each other? If the sync is only one way from 1 to 3 non-internet servers, then you might be able to get it to work. Even then there will probably be more unforeseen issues to deal with.

Some other things to think about:
-There are lot of other fields that are unique besides the primary key. For example, if a user was created with the same email on each server before you sync. You'll get database unique index errors, what can you do then? Maybe 1 server can overwrite others, but you'll need to go through every single table with a userId as well.
-An object is updated in 2 servers separately, which takes precedence?
-How are you handling deletions?
-Since each server will have its own counter, you'll probably want to make sure tables like ClassName_ are completely in sync including primary key.
-Scheduled jobs will run 4 times, once on each server. So if a job sends out an email, the recipient is going to get 4 emails.

[Prakash Khanchandani, modificado hace 8 años.]

Thank You.
Yes these are all valid issues, some of which we thought through but yes ultimately I can see it is doomed to failure sooner or later

Yes all the servers sync with each other.

- Yes the unique index is one thing which we would need to tackle as well. But as far as the design is concerned most of the master data is entered only in one server and other servers mostly consume. They can add/update/delete but that would be only for their particular activities concerned with their location.
- But mostly the data coming from these servers would be logically separate. But still nothing is stopping that this would not eventually happen.
- Deletion? as above. And master data like user and other things are only added through a single server.
- Some of master tables like ClassName_, Users, Groups etc are all being taken from a single server. Deployment is a tedious thing.
- Scheduler is a classic case, thank you . Users have started complaining already

Thank you for taking the time-out and listing more issues to support the dooms-day....