|
The "create database xxx" do not fail if the database is already present on the slave.
1- you have a schema present on a slave but not on master
2- create the same database on master does not break the replication
- the binary logs present clear the the TWO COMMAND of create database, coming from different GTID ( server-ids).
Behind this bug behavior could face a big discrepancy of data , cause the schema contain tables!
how to reproduce:
MAXSCALE topology:
maxctrl list servers
|
┌──────────────────────┬──────────────────────┬──────┬─────────────┬─────────────────┬──────────────────────────────────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │
|
├──────────────────────┼──────────────────────┼──────┼─────────────┼─────────────────┼──────────────────────────────────┤
|
│ cs-node-2.cs-cluster │ cs-node-2.cs-cluster │ 3306 │ 0 │ Slave, Running │ │
|
├──────────────────────┼──────────────────────┼──────┼─────────────┼─────────────────┼──────────────────────────────────┤
|
│ cs-node-1.cs-cluster │ cs-node-1.cs-cluster │ 3306 │ 0 │ Slave, Running │ │
|
├──────────────────────┼──────────────────────┼──────┼─────────────┼─────────────────┼──────────────────────────────────┤
|
│ cs-node-0.cs-cluster │ cs-node-0.cs-cluster │ 3306 │ 0 │ Master, Running │ │
|
└──────────────────────┴──────────────────────┴──────┴─────────────┴─────────────────┴──────────────────────────────────┘
|
|
MariaDB [(none)]> select @@hostname;
------------
------------
------------
1 row in set (0.000 sec)
MariaDB [(none)]> create database mynewdbonslave;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> show databases;
---------------------
---------------------
| allen |
| calpontsys |
| columnstore_info |
| infinidb_querystats |
| information_schema |
| mynewdbonslave |
| mysql |
| performance_schema |
---------------------
now lets move to the master:
MariaDB [(none)]> select @@hostname;
------------
------------
------------
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
---------------------
---------------------
| calpontsys |
| columnstore_info |
| infinidb_querystats |
| information_schema |
| mysql |
| performance_schema |
---------------------
MariaDB [(none)]> create database mynewdbonslave;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> SHOW DATABASes;
---------------------
---------------------
| calpontsys |
| columnstore_info |
| infinidb_querystats |
| information_schema |
| mynewdbonslave |
| mysql |
| performance_schema |
now we should have replication broken on the slave cs-node-1 but we dont:
MariaDB [(none)]> select @@hostname; show slave status \G
------------
------------
------------
1 row in set (0.000 sec)
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: cs-node-0.cs-cluster
|
Master_User: skysql_replication
|
Master_Port: 3306
|
Connect_Retry: 10
|
Master_Log_File: mariadb-bin.000003
|
Read_Master_Log_Pos: 493
|
Relay_Log_File: mariadb-relay.000002
|
Relay_Log_Pos: 794
|
Relay_Master_Log_File: mariadb-bin.000003
|
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: 493
|
Relay_Log_Space: 1101
|
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: 597100
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 597100-597100-73
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: optimistic
|
SQL_Delay: 0
|
SQL_Remaining_Delay: NULL
|
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
|
Slave_DDL_Groups: 1
|
Slave_Non_Transactional_Groups: 0
|
Slave_Transactional_Groups: 0
|
1 row in set (0.000 sec)
|
|
|
|
As for the binary log of the slave (which means all has been already apply ad for slave_log_update):
#220314 14:01:32 server id 597101 end_log_pos 509 CRC32 0x63577417 Query thread_id=443 exec_time=0 error_code=0
|
SET TIMESTAMP=1647266492/*!*/;
|
SET @@session.pseudo_thread_id=443/*!*/;
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/;
|
SET @@session.sql_mode=1411383296/*!*/;
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
/*!\C utf8 *//*!*/;
|
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
|
SET @@session.lc_time_names=0/*!*/;
|
SET @@session.collation_database=DEFAULT/*!*/;
|
create database mynewdbonslave
|
/*!*/;
|
# at 509
|
#220314 14:03:09 server id 597100 end_log_pos 551 CRC32 0xa13225cd GTID 597100-597100-73 ddl
|
/*!100001 SET @@session.gtid_domain_id=597100*//*!*/;
|
/*!100001 SET @@session.server_id=597100*//*!*/;
|
/*!100001 SET @@session.gtid_seq_no=73*//*!*/;
|
# at 551
|
#220314 14:03:09 server id 597100 end_log_pos 658 CRC32 0xfec5a878 Query thread_id=452 exec_time=0 error_code=0
|
SET TIMESTAMP=1647266589/*!*/;
|
create database mynewdbonslave
|
/*!*/;
|
DELIMITER ;
|
# End of log file
|
|
two different server_id , same statement, not replication broken.
|