Re: ERROR 1005 (HY000): Can't create table

[Pete Ditmars]

So, I updated to 8.0.22 and the error message changes slightly... but it still fails on the same table...

sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 1215 (HY000): Cannot add foreign key constraint
[SQL:
CREATE TABLE policy_statements (
id VARCHAR(255) NOT NULL,
`policyId` VARCHAR(255),
`condition` TEXT,
verb VARCHAR(10),
`resourceType` VARCHAR(64),
`compartmentId` VARCHAR(255),
`compartmentRefType` ENUM('ID','NAME'),
`allowAnyGroup` BOOL DEFAULT '0',
PRIMARY KEY (id),
FOREIGN KEY(`policyId`) REFERENCES policies (id) ON DELETE CASCADE,
FOREIGN KEY(`compartmentId`) REFERENCES compartments (id) ON DELETE SET NULL,
CHECK (`allowAnyGroup` IN (0, 1))
)

no additional data from the engine...

mysql> SHOW ENGINE NDBCLUSTER STATUS;
+---------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+---------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ndbclus | connection | cluster_node_id=33, connected_host=pcamn01, connected_port=1186, number_of_data_nodes=3, number_of_ready_data_nodes=3, connect_count=0 |
| ndbclus | NdbTransaction | created=3, free=3, sizeof=392 |
| ndbclus | NdbOperation | created=6, free=6, sizeof=944 |
| ndbclus | NdbIndexScanOperation | created=0, free=0, sizeof=1152 |
| ndbclus | NdbIndexOperation | created=0, free=0, sizeof=952 |
| ndbclus | NdbRecAttr | created=0, free=0, sizeof=88 |
| ndbclus | NdbApiSignal | created=16, free=16, sizeof=144 |
| ndbclus | NdbLabel | created=0, free=0, sizeof=200 |
| ndbclus | NdbBranch | created=0, free=0, sizeof=32 |
| ndbclus | NdbSubroutine | created=0, free=0, sizeof=72 |
| ndbclus | NdbCall | created=0, free=0, sizeof=24 |
| ndbclus | NdbBlob | created=0, free=0, sizeof=592 |
| ndbclus | NdbReceiver | created=0, free=0, sizeof=128 |
| ndbclus | NdbLockHandle | created=0, free=0, sizeof=48 |
| ndbclus | binlog | latest_epoch=441797515935746, latest_trans_epoch=441776041099278, latest_received_binlog_epoch=0, latest_handled_binlog_epoch=441715911557132, latest_applied_binlog_epoch=0 |
+---------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.08 sec)

unlike my attempt in 8.0.20 I can still create a super-simple table, but I'm not sure how relevant that is....

any guesses as to what might be wrong?

again, this works with Innodb as the storage engine.