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
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