Mysql: Table 'performance_schema.data_lock_waits' doesn't exist

[rajputvishwas]

Hi,
I have installed mysql 5.7.24 on openshift 3.11 and is working fine. I am trying to create view
which internally using default table performance_schema.data_lock_waits. When I am trying to run this script it ends up with

ERROR 1146 (42S02) at line 20444: Table 'performance_schema.data_lock_waits' doesn't exist

I have seen similar issue #457 and applied the solution given, but again end up with error

mysql_upgrade -u root -p --force
Enter password:
mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) while connecting to the MySQL server
Upgrade process encountered error and will not continue.

What I can see is data_lock_waits table is missing.

Environment:
oc v3.11.0+0cbc58b
kubernetes v1.11.0+d4cacc0
features: Basic-Auth SSPI Kerberos SPNEGO

Server https://xxx.xxx.xxx/
openshift v3.11.88
kubernetes v1.11.0+d4cacc0

MySQL
Your MySQL connection id is 3749
Server version: 5.7.24 MySQL Community Server (GPL)

[wglambert]

Access denied for user 'root'@'localhost'

Was the database initialized and the server ready to accept connections?

$ docker run -d --rm --name mysql -e MYSQL_ROOT_PASSWORD=pass mysql:5.7       
c69e7edd1b323976c24897f6f8960c570b86ffb57efc29aa882eed32feeaa27c

$ docker logs mysql 2>&1 | grep -i "init process done"
2020-02-13 17:42:40+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

$ docker exec -it mysql bash

root@c69e7edd1b32:/# mysql_upgrade -uroot -ppass --force    
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

I can't reproduce an issue with views however

$ docker run -d --rm --name mysql -e MYSQL_ROOT_PASSWORD=pass mysql:5.7
ef4c59b85f881289a811283c49f255a52c84704e61524264bb02f45c7e8ac981

$ docker logs mysql 2>&1 | grep -i "init process done"
2020-02-13 17:59:13+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

 docker exec -it mysql bash                                  
root@ef4c59b85f88:/# mysql -uroot -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host, User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

mysql> CREATE VIEW test AS SELECT Host, User FROM user;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from test;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

You could also try asking over at the Docker Community Forums, Docker Community Slack, or Stack Overflow. Since these repos aren't really a user-help forum

[rajputvishwas]

I am able to connect using

mysql --user=root

and also able to perform db operations such as show databases and tables.

However, I am not able execute

mysql_upgrade -uroot -ppass --force

sh-4.2$ mysql -uroot -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
sh-4.2$ mysql_upgrade -uroot -ppass --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) while connecting to the MySQL server
Upgrade process encountered error and will not continue.
sh-4.2$ mysql --user=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 66882
Server version: 5.7.24 MySQL Community Server (GPL)

[tianon]

That looks like your database was successfully initialized with an empty root password.

[rajputvishwas]

Coming back to the error, is there any way to confirm that the data_lock_waits is part of performance_schema for MySQL 5.7.24.

[yosifkit]

From what I can tell that table does not exist until MySQL 8.0. The documentation about the table doesn't exist for older versions: https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html but I can't find the exact reference in the release notes of the 8.0 series.

I also tested listing tables in the performance_schema database in mysql:5.7 and mysql:8.0 and did not see data_lock_waits (or even data_locks) in 5.7 (5.7.29 to be exact).

Since this doesn't seem to be an issue with the image I will close.

[wglambert]

It looks like in 5.7 mysql uses innodb_lock_waits https://dev.mysql.com/doc/refman/5.7/en/innodb-lock-waits-table.html
And 8.0 uses data_lock_waits https://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html