MySQL Bugs: #41635: Create table returns errno 150 when you include a FK in the table definition

Bug #41635	Create table returns errno 150 when you include a FK in the table definition
Submitted:	19 Dec 2008 12:17	Modified:	4 Mar 2009 19:26
Reporter:	Daniel Ellis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.5	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
It is not possible to create a table (with a foreign key) using a script via Connector/Net.

I have discovered this issue whilst testing MySQL 5.1.30.  This issue does not happen when using MySQL 5.0.45 or 5.0.67.

I have raised this issue with Connector/Net, because there is no problem when pasting the example script directly into the mysql command line.

The sample script that can be used to test the issue is:

--------------------------------
CREATE DATABASE ConnectorTestDB;

USE ConnectorTestDB;

CREATE TABLE `test1` (`id` int NOT NULL PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE `test2`
(
`id` int NOT NULL PRIMARY KEY,
`id_ref` int,
CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id_ref`) REFERENCES `test1` (`id`)
) ENGINE=InnoDB;

DROP DATABASE ConnectorTestDB;
--------------------------------

The test passes when you remove the constraint.

How to repeat:
Sample C# code for a test case:-

DbProviderFactory factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
DbConnection con = factory.CreateConnection();

DbConnectionStringBuilder builder = new DbConnectionStringBuilder();
builder.Add("Data Source", "localhost");
builder.Add("User Id", "root");
builder.Add("Password", "toor");
builder.Add("Allow User Variables", true);
builder.Add("Allow Batch", true);
con.ConnectionString = builder.ConnectionString;

try
{
    con.Open();

    using (DbCommand command = con.CreateCommand())
    {
        command.CommandText = 
@"
CREATE DATABASE ConnectorTestDB;

USE ConnectorTestDB;

CREATE TABLE `test1` (`id` int NOT NULL PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE `test2`
(
`id` int NOT NULL PRIMARY KEY,
`id_ref` int,
CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id_ref`) REFERENCES `test1` (`id`)
) ENGINE=InnoDB;

DROP DATABASE ConnectorTestDB;
";

        command.ExecuteNonQuery();
    }

    MessageBox.Show("Test successful.");
}
catch (Exception ex)
{
    MessageBox.Show("Test failed:\n\n" + ex.Message);
}
finally
{
    if (con.State == ConnectionState.Open)
    {
        con.Close();
    }
}

Hi Daniel and thanks for your report.

Truly what happens is exactly what you described... Error 150 with message can't create table1, weird. At the same time, same command passes in mysql cl client.

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=****;PORT=****;Allow Batch=True;logging=True;Allow User Variables=True";
            conn.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = "USE test; DROP TABLE IF EXISTS `test2`; DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1`(`id` int NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE `test2`(`id` int NOT NULL PRIMARY KEY,`id_ref` int, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id_ref`) REFERENCES `test1`(`id`)) ENGINE=InnoDB;";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            Console.ReadLine();

This could be related to Bug#1080 in case c/NET adds some constraints.

This is not a bug.  The MySQL command line client breaks up commands on the delimiter.  So the first create table is executed and then the second.  Connector/net doesn't do that.  It recognizes that the server supports batching and sends all the commands at once.  This fails in this case.  The use has two options.  Either execute the two create table statements separately or use the new MySQLScript class.

Hi,

I don't agree that this is not a bug. I don't see any mention of any such limitations in the manual. Even more, the manual [1] even gives an example of using CREATE TABLE with multi-statements.

Example of the problem using the CLI client:

DELIMITER $
CREATE TABLE a (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE b (aId INT, FOREIGN KEY (aId) REFERENCES a(id) ON DELETE SET NULL) ENGINE=InnoDB;
$
>ERROR 1005 (HY000): Can't create table 'jaka_sandbox2.a' (errno: 150)

From SHOW ENGINE INNODB STATUS:
> 090405  1:50:12 Error in foreign key constraint of table jaka_sandbox2/a:
> FOREIGN KEY (aId) REFERENCES a(id) ON DELETE SET NULL) ENGINE=InnoDB:
> Cannot resolve column name close to:
> ) REFERENCES a(id) ON DELETE SET NULL) ENGINE=InnoDB

[1] http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html