Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
Using different gtid_domain_id are a way to allow a multi purpose database server to define a set of different gtids to assist in parallel replication. Unfortunately since gtid_domain_id can only be set by a super user this restricts its use. It would be useful to have an allocation of gtid_domain_id per user to facilitate application assisted parallel replication.
https://mariadb.com/kb/en/mariadb/documentation/replication/standard-replication/global-transaction-id/#gtid_domain_id (doesn't list it as SUPER only)
mysql> set session gtid_domain_id=59;
|
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
|
If having multiple users set the same gtid_domain_id is risky for some reason consider:
a) an entry in the mysql.user table that defines the default gtid_domain_id for that user; and optionally extra
b) a min/max gtid_domain_id values in the users table to prevent conflicts (min/max available as readonly variable) where a non-super user can only set gtid_domain_id within this range.
b) would be useful if an application support a set of operations (e.g. db cache, batch payments) that it knows are independent.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-
Activity
--init-connect, but on a per-user basis? Then this could be used to set the domain id for a as desired,
but also be used to control any other setting.
The init-connect method is what i used in combination with a user lookup in the blog above and this could also do the same as gtid_domain_id_function.
Do you think it is really useful to set a range of allowed domain ids for a non-superuser? It seems to me that as soon as a user has the ability to set two different domain ids, that user has the ability to break replication anyway, by creating conflicting transactions with different domain ids. So not sure how restricting the range would help in practice.
Yes is only really useful if this is resolved such that breaks only stop the replication of conflicting domains and giving a user the ability to resolve this which I think is about two features beyond what really needs to be considered now.
It also seems that a stored procedure with SQL SECURITY DEFINER...
yes that was done in the blog above too.
I was aiming at a generic interface that allows a Drupal application for instance to keep disparate sections of their code (cache, vs entities vs users vs event logs) to have a different domain-id and hence explicitly paralleling them. Something that is clear and obvious and could be incorporated into the Drupal distribution code as a pseudo data paralleling interface.
So I'll experiment some more with stored procedures, multi source replication (each with domain id filters and the same master) to avoid total replication faults (concept flawed?) maybe. And I'll see how useful that is. Stats on in-order parallel replication will see if there is much to be improved by going explicit.
MDEV-6594 Use separate domain_id for Galera transactions kind of needs to be considered.
Noting the irony of Dan getting allocated to this issue - he himself reported it from Open Query in 2014...
Given this was largely a discussion from years ago, I value the reviews that have taken place. I'll let this issue wait until there's people still in operational roles.
To people that come across this and are interested. Please watch/vote for this issue and perhaps include a few works why its of interest to you.
I think Serg wanted to reply to this, but I guess he has been busy with other
stuff so far (as have I - I apologise for the delay in replying to your
several patches, but things are too busy lately).
Serg and I discussed this briefly, and we both felt that this (adding another
priviliege and new user table columns) does not seem the best way to do
things?
For one, it seems too ad-hoc - there are probably other settings that could be
useful for non-superuser access in some cases, should they also have separate
privileges and columns?
And besides - this method of new privilege does not seem to be the most useful
way for the DBA? One thing that could be really useful would be the ability to
make the domain_id be a hash function of the currently selected database, to
simulate mysql per-database parallelism. Or a hash function of the currently
selected user. Eg. pseudo-code:
SET GLOBAL gtid_domain_id = HASH(CURRENT DATABASE) MOD 10;
or something like that - but the exact syntax needs some more thought to be
flexible and consistent. Maybe just a GLOBAL gtid_domain_id_function variable,
which contains the name of a stored function that sets the value?
Or what if there was a general mechanism to set --init-connect, but on a
per-user basis? Then this could be used to set the domain id for a as desired,
but also be used to control any other setting.
Do you think it is really useful to set a range of allowed domain ids for a
non-superuser? It seems to me that as soon as a user has the ability to set
two different domain ids, that user has the ability to break replication
anyway, by creating conflicting transactions with different domain ids. So not
sure how restricting the range would help in practice.
It also seems that a stored procedure with SQL SECURITY DEFINER could have a
role here? The superuser could define a stored procedure and give a
non-privileged user execute access. Such a procedure could provide the ability
to change the domain_id, with a high degree of flexibility?