Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
Can mariadb access another server/database?
I'm not sure if I will be asking this correctly but here goes.
We use triggers fairly heavily in in our mariadb apps and have come across a situation where we would like mariadb to connect to a remote server/database to update a record.
Someone pointed this out;
https://mariadb.com/kb/en/mariadb/federated-storage-engine/
Since this is deprecated, is there another way to accomplish this?
The situation is that when a certain piece of data comes in, we use a trigger to run a query to see if the current data needs to be updated. If not, then do nothing. If so, then update the local database column but we also need to update another server, database and table at the same time.
We are trying to avoid using php and also avoid the app which is using the db from having to deal with this update mainly for optimization reasons.
We use triggers fairly heavily in in our mariadb apps and have come across a situation where we would like mariadb to connect to a remote server/database to update a record.
Someone pointed this out;
https://mariadb.com/kb/en/mariadb/federated-storage-engine/
Since this is deprecated, is there another way to accomplish this?
The situation is that when a certain piece of data comes in, we use a trigger to run a query to see if the current data needs to be updated. If not, then do nothing. If so, then update the local database column but we also need to update another server, database and table at the same time.
We are trying to avoid using php and also avoid the app which is using the db from having to deal with this update mainly for optimization reasons.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Ha wing the second db periodically pull is pretty easy too if you are ok with being a little behind.
I didn't explain myself correctly it seems.
I am not talking about replication, the databases are completely different but there is one table where just one column on server2 that needs to match a record on server1 if that record changes.
They are not running the same databases nor the same applications, it's just one record/column that needs to match.
I am not talking about replication, the databases are completely different but there is one table where just one column on server2 that needs to match a record on server1 if that record changes.
They are not running the same databases nor the same applications, it's just one record/column that needs to match.
You have to use an external process that scans the one and updates the other, the use of an additional column that indicates this row experienced a change could simplify and speed up your process.
Note, you would need to clear the column and update the server2 in "one shot" to avoid the possibility of this script issuing a clear/updated status while a new update is made..
A different approach, on update the value in this extra column is incremented by one, when the task is performed, it is decrement by one.
Does the application accessing server also when needed pulls data from server2?
Note, you would need to clear the column and update the server2 in "one shot" to avoid the possibility of this script issuing a clear/updated status while a new update is made..
A different approach, on update the value in this extra column is incremented by one, when the task is performed, it is decrement by one.
Does the application accessing server also when needed pulls data from server2?
When server1 recognizes that a column has changed via trigger, it updates the column in the local db.
On server2, it only needs to update one column but in a completely different database. The databases are not the same, they share only that one value in one column of a db.
No need to pull anything, only update if there is already a record matching the name it would look for.
On server2, it only needs to update one column but in a completely different database. The databases are not the same, they share only that one value in one column of a db.
No need to pull anything, only update if there is already a record matching the name it would look for.
Server1 table1 columna
Server2 tableb column1
There has to be another way that correlates the entry.
On server1 table1 you need another column (updated) such that when columna changes, updated column either reflects the prior value or a 1
The prior value is useful to locate the record if no other way to identify the revord is available.
The script that polls server1 table1 for rows where updated is not null or empty '' then runs the update on server2 tableb set column1=value_pulled_colum
Then clear the status on server1 table1 updated.
Additional precaution could be to populate a transactional update table on server2 that has a trigger that updates the tableb column1 revord.
The transaction table could provide historic reference on server2 for changes of column1.
The difficulty as you articulate it.
Server1 table1 column1
Now has Joe
Server2 tableb columna
Somehow has Joe as well
On server1 an update occurred such that column1 now has Jane
How are you able to locate the row where that change exists.
Another option if both are Mariadbs to have a communication DB that is HA replicated and where on ServerB there is a trigger that would update server2 tableb columna that would correlate to server1 table1 column1's value.
Server2 tableb column1
There has to be another way that correlates the entry.
On server1 table1 you need another column (updated) such that when columna changes, updated column either reflects the prior value or a 1
The prior value is useful to locate the record if no other way to identify the revord is available.
The script that polls server1 table1 for rows where updated is not null or empty '' then runs the update on server2 tableb set column1=value_pulled_colum
Then clear the status on server1 table1 updated.
Additional precaution could be to populate a transactional update table on server2 that has a trigger that updates the tableb column1 revord.
The transaction table could provide historic reference on server2 for changes of column1.
The difficulty as you articulate it.
Server1 table1 column1
Now has Joe
Server2 tableb columna
Somehow has Joe as well
On server1 an update occurred such that column1 now has Jane
How are you able to locate the row where that change exists.
Another option if both are Mariadbs to have a communication DB that is HA replicated and where on ServerB there is a trigger that would update server2 tableb columna that would correlate to server1 table1 column1's value.
Actually, I am not asking how to deal with the records, there is no issue what so ever with that. I am asking if mariadb could update another db using the trigger we already use as explained above.
Not that I am aware. The only way a trigger on server1 can update a record on ServerB is if the servers are linked which is available on ?M?s SQL, oracle, etc.
The use of a databse/table that is replicate between mariadb servers could help achieve what you want to have a trigger update the revord on server2
Note a dummy db on server1 that the trigger will update shoukd/would/could be required.
The trigger on the replicated database/table will update the local database/table/column which might be required to work on server1 to be committed and function on server2.
It is intriguing how you seem to insist on getting answer you are being told is not possible while providing you alternate ways to achieve the result you are after.
The trigger you use can update local tables, databases. You are trying to get a trigger that would require the ability to reach out to server2 authenticate, and perform a task I believe it is not possible.
I am uncertain you San gave a trigger on server1 that will run a system script that can function to connect to the remote server and do what you want.
The use of a databse/table that is replicate between mariadb servers could help achieve what you want to have a trigger update the revord on server2
Note a dummy db on server1 that the trigger will update shoukd/would/could be required.
The trigger on the replicated database/table will update the local database/table/column which might be required to work on server1 to be committed and function on server2.
It is intriguing how you seem to insist on getting answer you are being told is not possible while providing you alternate ways to achieve the result you are after.
The trigger you use can update local tables, databases. You are trying to get a trigger that would require the ability to reach out to server2 authenticate, and perform a task I believe it is not possible.
I am uncertain you San gave a trigger on server1 that will run a system script that can function to connect to the remote server and do what you want.
"It is intriguing how you seem to insist on getting answer you are being told is not possible while providing you alternate ways to achieve the result you are after."
My biggest problem is that I cannot share enough details so have to work with what I can share in hopes of giving enough information to experts in order to get answers. Many of the answers I seek are usually 'outside the box' because we have unusual requirements because we are heavily involved in developing things that do not exist.
Add to this mix that I am not qualified to actually ask the questions in that I am not an expert at of the things I need to find out about, I am someone who has hands on in many many areas so have a more general knowledge in many areas rather than expertise in any one.
That's why I use this site. I try to provide what I can and try to get answers that might work.
>I am uncertain you San gave a trigger on server1 that will run a system script that can function to connect to the remote
>server and do what you want.
Well, now we're back to the actual question I was asking :).
I know there are many ways this could be accomplished but I'm trying to find out if mariadb could do it itself rather than needing something external such as another app, cron, php, etc etc.
Your thought about replicating just one db/table however might be something that could work.
My biggest problem is that I cannot share enough details so have to work with what I can share in hopes of giving enough information to experts in order to get answers. Many of the answers I seek are usually 'outside the box' because we have unusual requirements because we are heavily involved in developing things that do not exist.
Add to this mix that I am not qualified to actually ask the questions in that I am not an expert at of the things I need to find out about, I am someone who has hands on in many many areas so have a more general knowledge in many areas rather than expertise in any one.
That's why I use this site. I try to provide what I can and try to get answers that might work.
>I am uncertain you San gave a trigger on server1 that will run a system script that can function to connect to the remote
>server and do what you want.
Well, now we're back to the actual question I was asking :).
I know there are many ways this could be accomplished but I'm trying to find out if mariadb could do it itself rather than needing something external such as another app, cron, php, etc etc.
Your thought about replicating just one db/table however might be something that could work.
MySQL doesn't do linked db outside of the federated storage you already looked at. You can do something super ghetto like calling php from a trigger that calls something on the other server that updates the MySQL there. But I'm not even linking to an article about this because it's a terrible idea.
The "correct" way to do this
1. Whatever thing is updating this MySQL, have it update db2 at the same time. If you didn't code the front end, I can see how this would be hard.
2. A script runs, probably on box2, that periodically or on trigger, queries db1, and pulls changes into db2.
If we knew more about the front end and what you use db2 for, we might have some more detailed answers.
The "correct" way to do this
1. Whatever thing is updating this MySQL, have it update db2 at the same time. If you didn't code the front end, I can see how this would be hard.
2. A script runs, probably on box2, that periodically or on trigger, queries db1, and pulls changes into db2.
If we knew more about the front end and what you use db2 for, we might have some more detailed answers.
Mark,
We've all been in a position where people want X without the resources needed to complete the task.
This is why I tried to provide you with variations of options to achieve this.
As noted I do not know whether mariaDB can execute a system resource. Mysql was able to run a script from a trigger. There are examples. though you would have to test whether the failure of the trigger to perform the external call/function will consequently prevent the update to your table.
i.e. trigger is run, the call to an external script runs into an issue, fails, does the update that was requested complete or not?
mariaDB is a data repository it is similar to asking whether a file cabinet can be made to do something when a file/document is changed in a specific category.
Triggers, constraints, etc. are tools to facilitate data integrity and Business process.
the multi-server/multi-db single record reference dependence often would require a process/mechanism that will monitor/synchronize the needed columns, information.
Specifically your current setup could sever and break data integrity since updates to server1 could occur without seemingly a way to maintain referencing
i.e. two sequential updates of the same column...
We've all been in a position where people want X without the resources needed to complete the task.
This is why I tried to provide you with variations of options to achieve this.
As noted I do not know whether mariaDB can execute a system resource. Mysql was able to run a script from a trigger. There are examples. though you would have to test whether the failure of the trigger to perform the external call/function will consequently prevent the update to your table.
i.e. trigger is run, the call to an external script runs into an issue, fails, does the update that was requested complete or not?
mariaDB is a data repository it is similar to asking whether a file cabinet can be made to do something when a file/document is changed in a specific category.
Triggers, constraints, etc. are tools to facilitate data integrity and Business process.
the multi-server/multi-db single record reference dependence often would require a process/mechanism that will monitor/synchronize the needed columns, information.
Specifically your current setup could sever and break data integrity since updates to server1 could occur without seemingly a way to maintain referencing
i.e. two sequential updates of the same column...
I don't think more details about the app are needed, it is a very simple thing but it's doing that one thing that I need to find a solution for. MySQL seems to have functionality that Mariadb doesn't and that includes federated.
In terms of the functionality, I've posted that too.
In terms of the functionality, I've posted that too.
When server1 recognizes that one specific column has changed via trigger, it updates the column in the local db.
On server2, it only needs to update one column but in a completely different database. The databases are not the same, they share only that one value in one column of a db.
No need to pull anything, only update if there is already a record matching the name it would look for.
@arnold, there are no issues with resources, the issue is mainly only how to accomplish this.
It seems the answer is as stated somewhere above.
Simply replicate a database between two servers, then have mariadb update both databases locally. It seems that mariadb can do tihs and would take care of the problem.
It seems the answer is as stated somewhere above.
Simply replicate a database between two servers, then have mariadb update both databases locally. It seems that mariadb can do tihs and would take care of the problem.
OK,
I think we are talking past each other.
server1 table1 column1
In order to update a column you have to have some reference to identify the record that you are updating.
If you need to modify second line on a record, you have to be able to identify the record such as name of the person, page number etc.
The only thing you are telling us is that in server1 table2 column1 the current value is 12 as is the value in server2 someothertable columna has a value of 12.
now you change column1 of a record from 12 to 13, does the record on server1 table1 include details that can be used to identify the record on server2 someothertable such that columna of that record can be identified and updated?
Are there processies that monitor/handle and are able to access both database systems?
I.e using the file cabinet example. In room 212 File cabinet 1 there are files that if page 3 is update, the files in room 313 File cabinet A page 4 has to reflect that same update.
You have to be able to maintain that you are updating the same record accross ....
based on the information, I would think an approach is
Server1
Server2
You need a DB that is either identical and is replicated between the two systems that maintains the changes and the references for the columns you wish to maintain. You update this DB and the respective columns are updated on the two Servers ...
I think we are talking past each other.
server1 table1 column1
In order to update a column you have to have some reference to identify the record that you are updating.
If you need to modify second line on a record, you have to be able to identify the record such as name of the person, page number etc.
The only thing you are telling us is that in server1 table2 column1 the current value is 12 as is the value in server2 someothertable columna has a value of 12.
now you change column1 of a record from 12 to 13, does the record on server1 table1 include details that can be used to identify the record on server2 someothertable such that columna of that record can be identified and updated?
Are there processies that monitor/handle and are able to access both database systems?
I.e using the file cabinet example. In room 212 File cabinet 1 there are files that if page 3 is update, the files in room 313 File cabinet A page 4 has to reflect that same update.
You have to be able to maintain that you are updating the same record accross ....
based on the information, I would think an approach is
Server1
Server2
You need a DB that is either identical and is replicated between the two systems that maintains the changes and the references for the columns you wish to maintain. You update this DB and the respective columns are updated on the two Servers ...
Mark,
resources would include the situation at hand since MariaDB does not seem to have the capacity out of the box to do what you need.
resources would include the situation at hand since MariaDB does not seem to have the capacity out of the box to do what you need.
Yes, there are methods in place which can verify the data integrity.
"resources would include the situation at hand since MariaDB does not seem to have the capacity out of the box to do what you need"
Does this mean replicating a database/table as noted above? Meaning, can mariadb update two local databases?
I'm sure the DB person knows this. The only part I'm looking into for him is if mariadb has any methods of dealing with the situation mentioned in the original post. On Friday, some quick research showed that mariadb cannot handle federated so I needed to find some other way of dealing with this.
"resources would include the situation at hand since MariaDB does not seem to have the capacity out of the box to do what you need"
Does this mean replicating a database/table as noted above? Meaning, can mariadb update two local databases?
I'm sure the DB person knows this. The only part I'm looking into for him is if mariadb has any methods of dealing with the situation mentioned in the original post. On Friday, some quick research showed that mariadb cannot handle federated so I needed to find some other way of dealing with this.
I am uncertain of the MariaDB capabilties. Often it is simpler to have a trigger update a local DB, table as compared to have a trigger running on server1 try to update data on a remote server.
This is why a DB/table that is replicating between two servers could provide a solution that an update on server1 replicatedDB/table/record when hits the server2 replicatedb/table/record might fire the same trigger to update server2 databaseofinteresttoyou/ta
If not mistaken, the replciated DB/triggers/contraints have to be exist on both ends and function....
Complex setup/configurations as was noted can be handled at different points.
Somethings are preferable when handled at the database level.
Somethings are more suitable and best handled elsewhere.
The complexity in your situation is that when there are two separate servers such as in your case server1 and server2, usually it means access to one might be external while access to the other is internal. In this case replication between them is a non-starter.
In such a situation, people on the inside usually have access to both internal/external.
Going back to several of prior suggestions. if the data in server1 and server2 is a reference of sorts. updates to these records has to be centralized elsewhere and would not be the task for the MariaDB database admin.
Here is the questions you need to figure out on your side.
Can Data between Server1 and server2 flow without impediment? deals with whether a single replicated database can be setup
Are there resources/systems that can access server1 and server2? A script process that would perform the updates or that will maintain the data running on this system.
What makes these changes? is the interface that makes alteration either to server1 or server2 or the update can be on either depending on who makes the change.
Can there be a delay between a change on one and it showing up on the other?
Do these changes rely on a record. i.e. user a made change to column1 and what the change is including the time. user b made change to column1 and this is the change.....
This is why a DB/table that is replicating between two servers could provide a solution that an update on server1 replicatedDB/table/record when hits the server2 replicatedb/table/record might fire the same trigger to update server2 databaseofinteresttoyou/ta
If not mistaken, the replciated DB/triggers/contraints have to be exist on both ends and function....
Complex setup/configurations as was noted can be handled at different points.
Somethings are preferable when handled at the database level.
Somethings are more suitable and best handled elsewhere.
The complexity in your situation is that when there are two separate servers such as in your case server1 and server2, usually it means access to one might be external while access to the other is internal. In this case replication between them is a non-starter.
In such a situation, people on the inside usually have access to both internal/external.
Going back to several of prior suggestions. if the data in server1 and server2 is a reference of sorts. updates to these records has to be centralized elsewhere and would not be the task for the MariaDB database admin.
Here is the questions you need to figure out on your side.
Can Data between Server1 and server2 flow without impediment? deals with whether a single replicated database can be setup
Are there resources/systems that can access server1 and server2? A script process that would perform the updates or that will maintain the data running on this system.
What makes these changes? is the interface that makes alteration either to server1 or server2 or the update can be on either depending on who makes the change.
Can there be a delay between a change on one and it showing up on the other?
Do these changes rely on a record. i.e. user a made change to column1 and what the change is including the time. user b made change to column1 and this is the change.....
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
@arnold, I'm going to award you the solution, mainly because it could help someone else.
All of the questions you've asked in the last comment are things we've already assessed, confirmed and know about.
The question was/is simply if mariadb can not only update a local db but can it also update a remote db based on a trigger.
Again, we've assessed everything, there would be no issues with data integrity. We only want to update one column, nothing more. Meaning that the application dealing with the remote database is already aware that this column could be changed by something external.
I think for something like this, it might be worth doing a replication but that might also be too complex for the size of the task. We might end up writing a cron task or something else that simply updates the remote.
In fact, a simple mysql -u xxx -pxxx -D xxx -h xxx with a little code to read/write is almost all that is needed.
We were hoping to avoid needing something external since mysql is so powerful. Or, I guess it was, before Oracle bought into it and now mariadb seems to have limitations. I don't know, I'm not a db dev.
All we want to is;
-when a certain type of data comes in, look for a change.
-trigger: if the current value is different, update it.
-since we had to update the local db, then we need to update the remote db.
-look for a record by an ID on server12.
-if it doesn't exist, do nothing, the app will take care of it.
-if it does exist, update only one column of that record.
end.
All of the questions you've asked in the last comment are things we've already assessed, confirmed and know about.
The question was/is simply if mariadb can not only update a local db but can it also update a remote db based on a trigger.
Again, we've assessed everything, there would be no issues with data integrity. We only want to update one column, nothing more. Meaning that the application dealing with the remote database is already aware that this column could be changed by something external.
I think for something like this, it might be worth doing a replication but that might also be too complex for the size of the task. We might end up writing a cron task or something else that simply updates the remote.
In fact, a simple mysql -u xxx -pxxx -D xxx -h xxx with a little code to read/write is almost all that is needed.
We were hoping to avoid needing something external since mysql is so powerful. Or, I guess it was, before Oracle bought into it and now mariadb seems to have limitations. I don't know, I'm not a db dev.
All we want to is;
-when a certain type of data comes in, look for a change.
-trigger: if the current value is different, update it.
-since we had to update the local db, then we need to update the remote db.
-look for a record by an ID on server12.
-if it doesn't exist, do nothing, the app will take care of it.
-if it does exist, update only one column of that record.
end.
In your case, provided mariadb functions in a similar way to the foundation on which it was built,
You could in a single insert into table values (value,value1,value2,value
You could in a single insert into table values (value,value1,value2,value
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MariaDB
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
The other option the mechanism used to update the mariadb shoukd be the same that updates the remote server.
Application updating two databases is simpler than trying to configure a db to notify another.....