0

I have a table with two columns col1, col2 which are composite primary keys. Will, that table supports group replication in MySQL InnoDB clustering?

Error message:

org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [/* insert collection row com.domain.BatchClass.documentTypes */ insert into batch_class_document_type (batch_class_id, document_type_id) values (?, ?)]; SQL state [HY000]; error code [3098]; The table does not comply with the requirements by an external plugin.; nested exception is

SHOW CREATE TABLE:

CREATE TABLE `batch_class_document_type` (
    `batch_class_id` bigint(20) NOT NULL, 
    `document_type_id` bigint(20) NOT NULL, 
    PRIMARY KEY (`batch_class_id`,`document_type_id`), 
    KEY `FKB72AFCD2997AC796` (`document_type_id`), 
    KEY `FKB72AFCD2C473EFCA` (`batch_class_id`), 
    CONSTRAINT `FKB72AFCD2997AC796` FOREIGN KEY (`document_type_id`)
                 REFERENCES `document_type` (`id`), 
    CONSTRAINT `FKB72AFCD2C473EFCA` FOREIGN KEY (`batch_class_id`)
                 REFERENCES `batch_class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Rick James
  • 135,179
  • 13
  • 127
  • 222
harish chava
  • 252
  • 2
  • 19
  • It would be mighty lame if it did not support such. – Rick James Feb 15 '19 at 01:40
  • Actually, the above table is relational mapping table with many-to-many association which is created from hibernate(manyto many annotation). When doing that getting the error the table is not compatible for external plugin group replication. – harish chava Feb 15 '19 at 04:47
  • Please provide `SHOW CREATE TABLE` and the specific error message. – Rick James Feb 15 '19 at 05:53
  • @RickJames, provided the requested artifacts. – harish chava Feb 15 '19 at 11:02
  • Was there nothing after "nested exception is " ? – Rick James Feb 15 '19 at 23:28
  • Please file a bug report at bugs.mysql.com . – Rick James Feb 15 '19 at 23:39
  • The requirements are documented here: https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html and it says the table must have a PRIMARY KEY (or non-NULL UNIQUE KEY), but no mention of lack of support for composite PK. I didn't find a bug about this in bugs.mysql.com. Just several cases of the same error for tables that have no PK, or tables that are MyISAM. – Bill Karwin Feb 15 '19 at 23:44

1 Answers1

0

You have an error when updating, not when creating the table right?

insert into batch_class_document_type (batch_class_id, document_type_id) values (?, ?)

My guess is that you are running your group in multi primary mode and as stated here:

https://dev.mysql.com/doc/refman/5.7/en/group-replication-deploying-in-multi-primary-or-single-primary-mode.html

If a transaction executes against a table that has foreign keys with cascading constraints, then the transaction fails to commit when synchronizing itself with the group.

You are hitting this limitation. My suggestion is to try to run it in Single Primary mode.

Pedro Gomes
  • 111
  • 1
  • 4
  • I have created MySQL sandbox cluster with 3 nodes, 1 primary instance and 2 secondary instances. So, my current cluster is already in single primary mode. – harish chava Feb 21 '19 at 13:25
  • I tested that table with the previous creation of simple document_type and batch_class tables with "id" keys. In my tests with 5.7.25 I can insert into the batch_class_document_type table with no errors. So I assume maybe the issue is with the other tables? Add the table structure for them. – Pedro Gomes Feb 21 '19 at 16:37