Bug #87773 Table and Database get Locked
Submitted: 15 Sep 2017 9:37 Modified: 11 Nov 2017 13:50
Reporter: kkumar Santosh Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7 OS:CentOS (Version 7)
Assigned to: CPU Architecture:Any
Tags: database lock, table lock

[15 Sep 2017 9:37] kkumar Santosh
Description:
Hello ,

I am new here. I have done the setup of MySQL InnoDB Cluster using 3 Machines. And connect all of them. On one of the machine, i have installed MySQL router.
Now i an trying to connect my application it is working fine for single connection. But when i start Multiple instances the Query in cluster is stuck and queued up.
Ex if i start the application from 10 System with same table access then it will stop to respond.

I have checked the processlistby using.
mysql> show processlist;
This will shows the result as

+-----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id  | User        | Host               | db   | Command | Time   | State                                                  | Info                                                                                                 |
+-----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
|   7 | system user |                    | NULL | Connect | 114540 | executing                                              | NULL                                                                                                 |
|  10 | system user |                    | NULL | Connect | 114540 | Slave has read all relay log; waiting for more updates | NULL                                                                                                 |
| 701 | wise        | 10.32.181.34:37921 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 754 | wise        | 10.32.181.34:37980 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 762 | wise        | 10.32.181.34:37988 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 767 | wise        | 10.32.181.34:37993 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 787 | wise        | 10.32.181.34:38013 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 796 | wise        | 10.32.181.34:38022 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 820 | wise        | 10.32.181.34:38046 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 822 | wise        | 10.32.181.34:38048 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 824 | wise        | 10.32.181.34:38050 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 836 | wise        | 10.32.181.34:38062 | wise | Query   |    697 | query end                                              | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 842 | root        | localhost          | NULL | Query   |    134 | checking permissions                                   | GRANT ALL PRIVILEGES ON *.* TO 'wise'@'%'                                                            |
| 844 | root        | localhost          | NULL | Query   |      0 | starting                                               | show processlist                                                                                     |
+-----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+

Can you please help me to resolve this. As i am not aware why this will happened.
May be i have done some wrong configurations or i need to do some extra configurations.

Please Help...

How to repeat:
Install MysQL InnoDB Cluster.
Steps i Followed:

sudo rpm -i ./mysql57-community-release-el7-10.noarch.rpm
sudo yum update
sudo yum install mysql-community-server mysql-shell
sudo systemctl start mysqld.service
sudo systemctl enable mysqld.service
sudo grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1
ALTER USER 'root'@'localhost' IDENTIFIED BY '######';

===========================================

Configure Cluster

sudo -i mysqlsh
dba.configureLocalInstance();
\q
sudo systemctl restart mysqld.service
mysqlsh
dba.checkInstanceConfiguration('root@localhost:3306');
sudo systemctl restart mysqld.service
mysql -u root -######

===============================================

GRANT PRIVILLAGES

GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO wise@'%' WITH GRANT OPTION;
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT ON performance_schema.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT ON sys.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT ON mysql_innodb_cluster_metadata.* TO wise@'%';
GRANT SELECT ON performance_schema.global_status TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO wise@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO wise@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO wise@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO wise@'%';
GRANT SELECT ON performance_schema.replication_group_members TO wise@'%';
GRANT ALL ON *.* TO 'wise'@'%';
GRANT REPLICATION SLAVE ON *.* TO wise@'%';
FLUSH PRIVILEGES;

SET GLOBAL innodb_strict_mode =0;
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_file_format=Barracuda;

==============================================
Create Cluster and Add instance
[root@hk02mysqlpoc2 testuser]# mysqlsh
mysql-js> shell.connect('wise@10.32.181.31:3306');
mysql-js> var cluster = dba.createCluster('WiseCluster');
mysql-js> cluster.status();
mysql-js> cluster.addInstance('wise@10.32.181.32:3306');
[22 Sep 2017 4:23] kkumar Santosh
Please Help
[11 Oct 2017 13:50] MySQL Verification Team
Hi!

Thank you for your contribution.

Let me clear out for you the purpose of this forum. This forum is for reporting bugs. A report must contain a fully repeatable test case. This test case must be such that we can simply run it and get the same results as you are getting.

It could then be a bug, but it also could be the expected behaviour.

What this forum is not, this is not a forum for free support.

The behaviour that you observe could be due to InnoDB locking or to some error in your Group Replication setup. We can not tell as we can not find a repeatable test case within this report.
[12 Nov 2017 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".