xml地图|网站地图|网站标签 [设为首页] [加入收藏]
SQL基础之外键约束,简单快速的处理方法
分类:数据库

首先创建测试表

use master

 

use test;
create table test01
(
id1 int not null,
id2 int not null
);
create table test02
(
id11 int not null,
id22 int not null
);
alter table test01 add constraint pk_id1
  primary key(id1);

 

Preface

考虑如下关系

alter database mfv408_Tift1 add log file

 

图片 1

(

    Sometimes,the threads(especially the SQL_Thread) will be terminated by accident.The slave will stop until we fix the relevant error or just skip them.There's many methods we can use to solve these kind of issues.For example,we can set "sql_salve_skip_counter" in mysql client and we can set "slave_skip_errors" in my.cnf as well(static parameter,need to restart mysqld).Further more,set "slave_exec_mode=idempotent" may also be useful.It can skip the "duplicate-key" or "no-key-found" errors.It means that we don't need to pay more attention to error 1062 and 1032 any more if we do so.Is there any other method can solve these skipping error issues?Surely there is one.

test02表中的id11依赖于test01中的id1,因此为test02创建外键

name=mfv408_Tift1_log,filename='C:_DISKdata1mfv408_Tift1_log2.ldf',

 

alter table test02 add constraint fk_id11
  foreign key(id11) references test01(id1);

    SIZE = 1024MB,

Introduce

注意:test01表中的id1必须为主键或者唯一索引,否则无法创建基于id1的外键。

    MAXSIZE = UNLIMITED,

 

 

    FILEGROWTH = 10%

*    pt-slave-restart is the one who can skip specific errors and restart the slave automatically.But considering of the consistency of data,we'd better do think twice when skipping errors by it.There're two test below.Let's see how the pt-slave-restart works.
*

创建外键之后,我们将发现无法在test02中输入不在test01的id1范围内的数据

)

 

图片 2图片 3

go

Procedure

insert into test02 values(1,1);

 

 

View Code

 

Usage

图片 4图片 5

DBCC SHRINKFILE('mfv408_Tift1_log',10)

1 pt-slave-restart [OPTIONS] [DSN]
消息 547,级别 16,状态 0,第 1 行
INSERT 语句与 FOREIGN KEY 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test01", column 'id1'。
语句已终止。

 

 

View Code

USE master;

**Common parameter**

如果在创建外键之前就已经在test02中有了不在test01的id1范围内的数据,则创建外键会失败

GO

 1 --always //Start slaves if there's no error,it will prevent to you stop slave manully.
 2 --check-relay-log //Check the relay log file before checking errors on slave.
 3 --error-length //Specify the max length to print.
 4 --error-number //Specify the number of errors you want to make the slave try to restart.
 5 --error-text //Specify the text of errors you want to make the slave try to restart.
 6 --max-sleep //Specify the maximum sleep time after it polling the slave.
 7 --min-sleep //Specify the minimum sleep time after it polling the slave.
 8 --monitor //whether to monitor the slave(default)
 9 --master-uuid //Specify the master-uuid you want to skip,if you have multiple writing node.
10 --recurse //If you want to execute pt-slave-restart on master.It's dispensable option.
11 --recurse-method //This option is used together with "--recurse".It specify the method to find slaves.
12 --sleep //Specify the interval of sleep time in checking the slave.
13 --until-master //run until this master logfile,position then exit.
14 --until-relay //run until this relay logfile,position then exit.

图片 6图片 7

ALTER DATABASE mfv408_Tift1 

 

alter table test02 drop constraint fk_id11;
insert into test02 values(1,1);
alter table test02 add constraint fk_id11
  foreign key(id11) references test01(id1);

REMOVE FILE mfv408_Tift1_log;

Example

View Code

GO

 

图片 8图片 9

 

1.Test of automatically restart slave.

消息 547,级别 16,状态 0,第 1 行
ALTER TABLE 语句与 FOREIGN KEY 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test01", column 'id1'。

 

View Code

Execute pt-slave-restart with "--always".

此时可以通过with nocheck选项,强制跳过现有数据检查

 1 [root@zlm1 12:15:35 ~]
 2 #pt-slave-restart --recurse=1 --always -h192.168.56.101 -P3306 -urepl -prepl4slave
 3 *******************************************************************
 4  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 5  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 6  together with SSL_ca_file|SSL_ca_path for verification.
 7  If you really don't want to verify the certificate and keep the
 8  connection open to Man-In-The-Middle attacks please set
 9  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
10 *******************************************************************
11   at /usr/bin/pt-slave-restart line 3841.
12 *******************************************************************
13  Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
14  is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
15  together with SSL_ca_file|SSL_ca_path for verification.
16  If you really don't want to verify the certificate and keep the
17  connection open to Man-In-The-Middle attacks please set
18  SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
19 *******************************************************************
20   at /usr/bin/pt-slave-restart line 3841.
21 
22 # A software update is available:
23 2018-07-21T12:18:15 P=3306,h=192.168.56.101,p=...,u=repl relay-bin.000030         407 0
24 
25 //It has begun to monitor the slave to automatically startup.
alter table test02 with nocheck
  add constraint fk_id11
  foreign key(id11) references test01(id1);

 

虽然在test01表中id1设置为了主键,不允许null,但是在test02表中的id2可以允许null值

**Check whether the slave replication is normal.**

alter table test02 alter column id11 int null;
insert into test02 values(null,1);
 1 zlm@192.168.56.101:3306 [(none)]>show slave statusG
 2 *************************** 1. row ***************************
 3                Slave_IO_State: Waiting for master to send event
 4                   Master_Host: 192.168.56.100
 5                   Master_User: repl
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000091
 9           Read_Master_Log_Pos: 194
10                Relay_Log_File: relay-bin.000031
11                 Relay_Log_Pos: 407
12         Relay_Master_Log_File: mysql-bin.000091
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 194
25               Relay_Log_Space: 861
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 1003306
43                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
44              Master_Info_File: /data/mysql/mysql3306/data/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627
55             Executed_Gtid_Set: 27af30ca-6800-11e8-ad7e-080027de0e0e:1,
56 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627
57                 Auto_Position: 1
58          Replicate_Rewrite_DB: 
59                  Channel_Name: 
60            Master_TLS_Version: 
61 1 row in set (0.00 sec)

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:SQL基础之外键约束,简单快速的处理方法

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文