Bug #93424 MEM upgrade - recreating existing keys and FKs
Submitted: 30 Nov 2018 10:58 Modified: 22 Feb 1:57
Reporter: Rafael Bos Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Enterprise Monitor Severity:S2 (Serious)
Version:4.0.7 OS:Red Hat
Assigned to: MySQL Verification Team 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.

[22 Jan 1:57] MySQL Verification Team
Hi,

I agree the upgrade procedure is not ideal but should finish quickly and should left your DB in a good state. Did you let it finish? After it finish there should be no duplicate keys in the schema.
[23 Feb 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".