ApplySchema: Cannot add foreign key constraint

[Jun Zhang]

HI, 

I follow the guild at http://vitess.io/user-guide/schema-management.html.

When I created the fourth table "items", vitess returned ExecutorErr:

"ExecutorErr\": \"rpc error: code = 2 desc = \\\"TabletManager.PreflightSchema on test-0000000100 error: /usr/bin/mysql: ERROR 1215 (HY000) at line 3: Cannot add foreign key constraint\\\\n\\\"\",\n  \"TotalTimeSpent\": 46970588\n}\n"

The SQLs are :

CREATE TABLE idcs(

   id INT NOT NULL AUTO_INCREMENT,

   name VARCHAR(100) NOT NULL UNIQUE,

   cname VARCHAR(100) NOT NULL UNIQUE,

   update_ts TIMESTAMP default CURRENT_TIMESTAMP,

   PRIMARY KEY (name)

) Engine=InnoDB;

CREATE TABLE slaves(

   id INT NOT NULL AUTO_INCREMENT,

   name VARCHAR(100) NOT NULL,

   ip VARCHAR(20) NOT NULL,

   lastseen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

   enabled TINYINT(2) DEFAULT 0, 

   idcname VARCHAR(100),

   PRIMARY KEY (id),

   FOREIGN KEY (idcName) REFERENCES idcs(name)

) Engine=InnoDB;

CREATE TABLE hosts(

   id INT NOT NULL AUTO_INCREMENT,

   name VARCHAR(100) NOT NULL,

   ip VARCHAR(20) NOT NULL, 

   department VARCHAR(50),

   iloip VARCHAR(20),

   iloport INT DEFAULT 163,

   status TEXT, -- OK or Error Message

   idcname VARCHAR(100),

   slaveid INT,

   PRIMARY KEY (id, name),

   FOREIGN KEY (idcname) REFERENCES idcs(name),

   FOREIGN KEY (slaveid) REFERENCES slaves(id)

) Engine=InnoDB;

CREATE TABLE items(

   id INT NOT NULL AUTO_INCREMENT,

   class VARCHAR(100) NOT NULL,

   section TEXT,

   bad TEXT,

   good TEXT,

   enabled TINYINT(2) DEFAULT 0,

   PRIMARY KEY (id)

) Engine=InnoDB;

Thanks,

Jun Zhang

[Anthony Yeh]

My guess is that the "CREATE TABLE idcs" is failing because the AUTO_INCREMENT column "idcs.id" does not have an index. The "Cannot add foreign key constraint" error is then caused by the fact that the "idcs" table doesn't exist.

With that said, we don't recommend using foreign key constraints with Vitess because:

1) They don't work across shards, since each shard is an independent MySQL database.

2) Vitess features like resharding are not designed to handle side-effects from foreign key constraints.

The main reason for (2) is that MySQL-enforced foreign key constraints tend to not perform well at large scale anyway, so it's usually more efficient to enforce them as needed at the app level.

--
You received this message because you are subscribed to the Google Groups "vitess" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vitess+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jun Zhang]

Thanks Anthony!

I successfully created three tables, but then,  when I apply any sql to shema, vitss always returns "Cannot add foreign key constraint", even I drop these created tables. eg.:

vitess@da1ae05f0e70:/vt/src/github.com/youtube/vitess/examples/local$ vtctlclient -server localhost:15999 applyschema -sql "drop table items;" test_keyspace
E0112 03:38:32.332120    3782 main.go:51] Remote error: rpc error: code = 2 desc = "Schema change failed, ExecuteResult: {\n  \"FailedShards\": null,\n  \"SuccessShards\": null,\n  \"CurSQLIndex\": 0,\n  \"Sqls\": [\n    \"drop table items\"\n  ],\n  \"ExecutorErr\": \"rpc error: code = 2 desc = \\\"TabletManager.PreflightSchema on test-0000000100 error: /usr/bin/mysql: ERROR 1215 (HY000) at line 5: Cannot add foreign key constraint\\\\n\\\"\",\n  \"TotalTimeSpent\": 18092635\n}\n"


vitess@da1ae05f0e70:/vt/src/github.com/youtube/vitess/examples/local$ vtctlclient -server localhost:15999 getschema test-100
{
  "database_schema": "CREATE DATABASE /*!32312 IF NOT EXISTS*/ `{{.DatabaseName}}` /*!40100 DEFAULT CHARACTER SET utf8 */",
  "table_definitions": [
    {
      "name": "hosts",
      "schema": "CREATE TABLE `hosts` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(100) NOT NULL,\n  `ip` varchar(20) NOT NULL,\n  `department` varchar(50) DEFAULT NULL,\n  `iloip` varchar(20) DEFAULT NULL,\n  `iloport` int(11) DEFAULT '163',\n  `user` varchar(20) DEFAULT 'ksyun',\n  `passwd` varchar(20) DEFAULT 'ks3Fsys%',\n  `status` text,\n  `idcname` varchar(100) DEFAULT NULL,\n  `slaveid` int(11) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `idcname` (`idcname`),\n  KEY `slaveid` (`slaveid`),\n  CONSTRAINT `hosts_ibfk_1` FOREIGN KEY (`idcname`) REFERENCES `idcs` (`name`),\n  CONSTRAINT `hosts_ibfk_2` FOREIGN KEY (`slaveid`) REFERENCES `slaves` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8",
      "columns": [
        "id",
        "name",
        "ip",
        "department",
        "iloip",
        "iloport",
        "user",
        "passwd",
        "status",
        "idcname",
        "slaveid"
      ],
      "primary_key_columns": [
        "id"
      ],
      "type": "BASE TABLE",
      "data_length": 16384
    },
    {
      "name": "idcs",
      "schema": "CREATE TABLE `idcs` (\n  `name` varchar(100) NOT NULL,\n  `cname` varchar(100) NOT NULL,\n  `update_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  PRIMARY KEY (`name`),\n  UNIQUE KEY `name` (`name`),\n  UNIQUE KEY `cname` (`cname`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8",
      "columns": [
        "name",
        "cname",
        "update_ts"
      ],
      "primary_key_columns": [
        "name"
      ],
      "type": "BASE TABLE",
      "data_length": 16384
    },
    {
      "name": "slaves",
      "schema": "CREATE TABLE `slaves` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(100) NOT NULL,\n  `ip` varchar(20) NOT NULL,\n  `lastseen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  `status` text,\n  `enabled` tinyint(2) DEFAULT '0',\n  `idcname` varchar(100) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `idcname` (`idcname`),\n  CONSTRAINT `slaves_ibfk_1` FOREIGN KEY (`idcname`) REFERENCES `idcs` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8",
      "columns": [
        "id",
        "name",
        "ip",
        "lastseen",
        "status",
        "enabled",
        "idcname"
      ],
      "primary_key_columns": [
        "id"
      ],
      "type": "BASE TABLE",
      "data_length": 16384
    }
  ],
  "version": "f5354f4eb4c38bb76135e753754bab6d"
}

How can I drop these schemas?

Thans,

Jun Zhang

[Anthony Yeh]

I think I see what's happening. The schema manager is one of the features of Vitess that is not designed to work with foreign key constraints. Since it assumes there are no such constraints, the schema manager thinks that order doesn't matter when creating tables.

When you use the ApplySchema command, Vitess dumps the schema from the real database and re-applies it on an empty, temporary database. It then applies the requested new DDL, to verify that it will be a valid operation on the real database. This is called the preflight schema check.

What's happening for you is that the tables are being re-created during preflight in the wrong order, so the foreign key constraints fail, and the preflight fails.

Although we don't have immediate plans to support foreign key constraints in all of Vitess, I will look into fixing this issue, since there may be other cases when the order of table creation matters.

Thanks for the report!

Anthony

[Jun Zhang]

Thanks Anthony! 

You' re right: It's a problem of wrong order during preflight. I remove all the foreign key constraints, everything goes well ! 

BTW: I doesn't find any clue of such designed features in document, could you please list other known constraints?

[Jun Zhang]

Another question: Does Vitess support cross-shard  PRIMARY KEY/UNIQUE/[NOT] NULL/AUTO_INCREMENT, or only in one shard?

Thanks,

Jun Zhang

[Sugu Sougoumarane]

This is neither yes nor no. Here's a doc that talks about how these issues are going to be addressed in the V3 API: http://vitess.io/doc/VTGateV3Features/.

There are also design docs for these features, but they're worth reading only if you enjoy torturing your brain :).