Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Trivial
-
Resolution: Fixed
-
Affects Version/s: 10.0.10
-
Fix Version/s: 10.0.11
-
Component/s: None
-
Labels:None
Description
Following the documentation here:
https://mariadb.com/kb/en/multi-source-replication/
The replicate- variables do not work if the connection_name has an underscore. Considering the example on the page:
mysqld --main_connection.replicate_do_db=main_database
If that were to be put in a config file, it would not be loaded because of the underscore in 'main_connection'. I have tried quoting numerous ways and was advised to make a bug report, "There is something about replacing _ with - (or vice versa) in option names, probably that is interfering"
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It just appears to be ignored.
[mysqld1]
signup.replicate_wild_do_table=signup.%
signup.replicate_wild_do_table=mysql.%
signup_archive.replicate_wild_do_table=signup_archive.%
signup_archive.replicate_wild_do_table=mysql.%
MariaDB [(none)]> show slave 'signup_archive' status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: hostname
Master_User: slaveuser
Master_Port: port
Connect_Retry: 60
Master_Log_File: mysql-bin.016021
Read_Master_Log_Pos: 32938
Relay_Log_File: relay-bin-signup_archive.001654
Relay_Log_Pos: 33225
Relay_Master_Log_File: mysql-bin.016021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 32938
Relay_Log_Space: 33574
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3632211757
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
MariaDB [(none)]> show slave 'signup' status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: hostname
Master_User: users
Master_Port: port
Connect_Retry: 60
Master_Log_File: mysql-bin.016006
Read_Master_Log_Pos: 765715
Relay_Log_File: relay-bin-signup.001765
Relay_Log_Pos: 246889
Relay_Master_Log_File: mysql-bin.016006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: signup.%,mysql.%,signup.%,mysql.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 765715
Relay_Log_Space: 247187
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3632211740
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
MariaDB [(none)]> select version();
+------------------------------+
| version() |
+------------------------------+
| 10.0.10-MariaDB-1~wheezy-log |
+------------------------------+
1 row in set (0.00 sec)
Still works for me, even with 'wild_do_table' and the wildcard (example below).
You output raises two questions.
First, your cnf section is named 'mysqld1'. Are you sure you run your server with the correct 'prefix' option?
Second, your output for 'signup' shows four wildcard values. At the very least, it means the config is additionally read from somewhere else. But add to that the ignored values for signup_archive and strange config section name, it might also mean that the config is only read from somewhere else.
For starters, could you please put in this very section someting simple and non-default, e.g. lock_wait_timeout=98, and check the value after you restart the mysqld?
[mysqld] signup.replicate_wild_do_table=signup.% signup.replicate_wild_do_table=mysql.% signup_archive.replicate_wild_do_table=signup_archive.% signup_archive.replicate_wild_do_table=mysql.%
************************** 1. row ***************************
Connection_name: signup
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: wheezy-64-relay-bin-signup.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: signup.%,mysql.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 515
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Slave_Pos:
*************************** 2. row ***************************
Connection_name: signup_archive
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 8306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: wheezy-64-relay-bin-signup_archive.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: signup_archive.%,mysql.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 515
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Slave_Pos:
2 rows in set (0.01 sec)
I am using mysqld_multi where you can have N instances managed by it all named mysqld1, mysqld2, mysqld3, etc, etc. (Ref: https://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html)
The duplication of replication filters is something I've taken as a fact of life with MySQL. I'm having a difficult time finding anything documented but it's always done that for as far back as I can remember. I don't think it's an issue, it's also on a functioning slave.
It is reading other values just fine:
MariaDB [(none)]> show global variables like 'lock_wait_timeout'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | lock_wait_timeout | 98 | +-------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global variables like 'slave_parallel_threads'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | slave_parallel_threads | 16 | +------------------------+-------+ 1 row in set (0.00 sec)
Could you please attach all your config files which contain either this-instance-specific or common sections, and the complete output of SHOW VARIABLES and SHOW ALL SLAVES STATUS \G ? (Please remove/obfuscate the passwords and any private info).
And the server command line as returned by ps -ef | grep mysqld or alike.
Thanks.
Ah, looking at ps, I can see the mysqld portion of it looks like this:
--signup-archive.replicate-wild-do-table=mysql.%
You can see the _ has been replaced by a hyphen. The command is listed correctly on the mysqld_safe part of the process:
--signup_archive.replicate-wild-do-table=mysql.%
I think that is fairly telling.
Thanks.
So, the problem is the whole chain.
mysqld_multi converts the pseudo-cnf into a command line and feeds it to mysqld_safe.
mysqld_safe takes the options from the command line and "normalizes" them by replacing _ with -.
mysqld reads the options and acts accordingly.
If it wasn't mysqld_multi's pseudo config, but a normal config, standard server's section, it wouldn't have happened because the options would remain in the cnf file where mysqld would have read them from as they are.
Or, if mysqd_multi was calling mysqld directly rather than via mysqld_safe, it would have worked because mysqld_multi doesn't modify the options, and mysqld is able to process them.
You can use either of that as a workaround.
It does work correctly when using mysql (not mysqld_multi), so I think the problem is there.
This is something for you to decide, I'm not sure what would be a better (or less bad) fix.
The logic which mixes up slave identificators and option names doesn't look great, but since we've let it reach GA, I guess it will have to stay (I really don't remember how it made it there).
The logic which replaces '_' with '-' in mysqld_safe is intentional, it was introduced by the following revision:
revision-id: guilhem@mysql.com-20091118203617-3jfudef971r7bf2y committer: Guilhem Bichot <guilhem@mysql.com> branch nick: mysql-next-mr-bugfixing timestamp: Wed 2009-11-18 21:36:17 +0100 message: Backport of the fix for BUG#40368 "mysqld_safe not honouring underscore same as dash on server options" from 6.0 (revision-id:guilhem@mysql.com-20090505113602-l12kxupeatve18dh). Such bug led "mysqld_safe --core_file_size=#" to not work because mysqld_safe wouldn't recognize that "ulimit -c" is needed; only --core-file-size=# worked. Same for --open_files_limit and other options with _ where mysqld_safe needs to do something more than passing to mysqld. Original fix by Erik Ljungstrom erik at ibiblio dot org ; slightly modified here. Tested on all internally accessible Unix.
So, while technically it's primitive to remove the conversion, probably we'll need to re-do core_file_size and the others. Since the "other options" are not listed, we might have to do that for every option which has special logic in mysqld_safe, and then it might make sense to do it a different way, e.g. to make a local copy of options for mysqld_safe's tampering, but keep the original ones to pass to mysqld, but it makes the change rather big. Or maybe you'll come up with a better idea, I don't like any of those.
Hi,
Could you please specify how exactly it does not get loaded – i.e. attach your cnf file and paste the error that you are getting (or, if it's not an error but the value is just ignored, paste the output of SHOW SLAVE '<main_connection>' STATUS;.
This works just fine for me (no quoting at all):
cat 1.cnf
show slave 'main_connection' status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: root Master_Port: 3308 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: wheezy-64-relay-bin-main_connection.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: main_database Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 1316 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: