Bug #93424 MEM upgrade - recreating existing keys and FKs
Submitted: 30 Nov 2018 10:58
Reporter: Rafael Bos Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Enterprise Monitor Severity:S2 (Serious)
Version:4.0.7 OS:Red Hat
Assigned to: CPU Architecture:Any

[30 Nov 2018 10:58] Rafael Bos
Description:
Hi,
after upgrade from 4.0.5/4.0.6 to 4.0.7 MEM server is unresponsive - looks like due to recreating of already existing keys and FKs ...

This is running after tomcat service startup
 /* mem dbpool.default */ alter table `mem__quan`.example_statements add constraint FK_sv9vpfu3pl2a060ip8cuoc3wf foreign key (normalized_statement_by_server_by_schema_id) references `mem__quan`.normalized_statements_by_server_by_schema (id)

| example_statements | CREATE TABLE `example_statements` (
  `round_robin_bin` bigint(20) NOT NULL,
  `bytes` bigint(20) DEFAULT NULL,
  `comments` longtext,
  `connectionId` bigint(20) DEFAULT NULL,
  `errors` bigint(20) DEFAULT NULL,
  `execTime` bigint(20) DEFAULT NULL,
  `hostFrom` varchar(1024) DEFAULT NULL,
  `hostTo` varchar(1024) DEFAULT NULL,
  `noGoodIndexUsed` bigint(20) DEFAULT NULL,
  `noIndexUsed` bigint(20) DEFAULT NULL,
  `rows` bigint(20) DEFAULT NULL,
  `text` longtext,
  `timestamp` bigint(20) NOT NULL,
  `user` varchar(255) DEFAULT NULL,
  `warnings` bigint(20) DEFAULT NULL,
  `normalized_statement_by_server_by_schema_id` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `source_location_id` binary(16) DEFAULT NULL,
  PRIMARY KEY (`round_robin_bin`,`normalized_statement_by_server_by_schema_id`),
  KEY `FKC55122D964ECB741` (`source_location_id`),
  KEY `id_timestamp` (`normalized_statement_by_server_by_schema_id`,`timestamp`),
  KEY `timestamp` (`timestamp`),
  CONSTRAINT `FKC55122D951B2A3DC` FOREIGN KEY (`normalized_statement_by_server_by_schema_id`) REFERENCES `normalized_statements_by_server_by_schema` (`id`),
  CONSTRAINT `FKC55122D964ECB741` FOREIGN KEY (`source_location_id`) REFERENCES `example_source_locations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 |

There are multiple such queries executed sequentially, all with 'alter table' statements adding FKs that are present
Killing those queries results in the same cycle to be started again ...
As these tables are quite big (tens of GBs) this takes lots of time while MEM is down

The mysql-monitor.log reports these
2018-11-14 09:27:45,449  INFO [em-worker-pool-thread-0:com.mysql.etools.concurrent.DebugRwLock] failed to grab READ lock after 10s, [lock=java.util.concurrent.locks.ReentrantReadWriteLock@7908c9d0[Write locks = 1, Read locks = 0]], readers={}, writers={Thread[localhost-startStop-1,5,main]=1}
2018-11-14 09:27:55,452  INFO [em-worker-pool-thread-0:com.mysql.etools.concurrent.DebugRwLock] failed to grab READ lock after 10s, [lock=java.util.concurrent.locks.ReentrantReadWriteLock@7908c9d0[Write locks = 1, Read locks = 0]], readers={}, writers={Thread[localhost-startStop-1,5,main]=1}
2018-11-14 09:29:15,465  INFO [em-worker-pool-thread-0:com.mysql.etools.concurrent.DebugRwLock] (repeated 6 times) failed to grab READ lock after 10s, [lock=java.util.concurrent.locks.ReentrantReadWriteLock@7908c9d0[Write locks = 1, Read locks = 0]], readers={}, writers={Thread[localhost-startStop-1,5,main]=1}

How to repeat:
Run MEM upgrade to 4.0.7 from versions 4.0.5 or 4.0.6

Suggested fix:
Check the table structure and create keys anf FKs only when needed
[30 Nov 2018 10:59] Rafael Bos
Identified schema diffs

Attachment: Bug_93424_MEM_4.0.7_identified_schema_diffs.txt (text/plain), 11.98 KiB.