Description:
Sys schema is used for monitoring purpose and its common to have some customizations on the same. In cases, any users create tables with same name as views or change views to tables (use event scheduler for data collection and insertion into views) then upgrade operation failes with an error.
[ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2014, 2023, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- View: latest_file_io -- -- Shows the latest file IO, by file / thread. -- -- mysql> select * from latest_file_io limit 5; -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | thread | file | latency | operation | requested | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 9.26 us | write | 124 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 4.00 us | write | 2 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 56.34 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYD | 53.93 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 104.05 ms | delete | NULL | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- CREATE OR REPLACE ALGORITHM = MERGE DEFINER = 'mysql.sys'@'localhost' SQL SECURITY INVOKER VIEW latest_file_io ( thread, file, latency, operation, requested ) AS SELECT IF(id IS NULL, CONCAT(SUBSTRING_INDEX(name, '/', -1), ':', thread_id), CONCAT(user, '@', host, ':', id) ) thread, sys.format_path(object_name) file, format_pico_time(timer_wait) AS latency, operation, format_bytes(number_of_bytes) AS requested FROM performance_schema.events_waits_history_long JOIN performance_schema.threads USING (thread_id) LEFT JOIN information_schema.processlist ON processlist_id = id WHERE object_name IS NOT NULL AND event_name LIKE 'wait/io/file/%' ORDER BY timer_start; ' failed with error code = 1347, error message = ''sys.latest_file_io' is not VIEW'.
How to repeat:
Below is the session record to repeat the issue:
[root@testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer deploy single 8.0.28
Database installed in $HOME/sandboxes/msb_8_0_28
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
[root@testbox ~]#
[root@testbox ~]#
[root@testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer deploy single 8.0.36
Database installed in $HOME/sandboxes/msb_8_0_36
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
[root@testbox ~]#
[root@testbox ~]#
[root@testbox ~]#
[root@testbox ~]# cd sandboxes/msb_8_0_28/
[root@testbox msb_8_0_28]# ./use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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 [localhost:8028] {msandbox} ((none)) > use sys
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 [localhost:8028] {msandbox} (sys) > create table latest_file_io_2 select * from latest_file_io;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8028] {msandbox} (sys) > drop view latest_file_io;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8028] {msandbox} (sys) > rename table latest_file_io_2 to latest_file_io;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8028] {msandbox} (sys) > \q
Bye
[root@testbox msb_8_0_28]#
[root@testbox msb_8_0_28]# cd
[root@testbox ~]#
[root@testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer admin upgrade msb_8_0_28 msb_8_0_36
stop /root/sandboxes/msb_8_0_28
stop /root/sandboxes/msb_8_0_36
Data directory msb_8_0_28/data moved to msb_8_0_36/data
^C
[root@testbox ~]#
[root@testbox ~]# cd sandboxes/msb_8_0_36/
[root@testbox msb_8_0_36]# cat data/msandbox.err
2024-04-07T19:19:51.164670Z 0 [System] [MY-010116] [Server] /root/dbdeployer/8.0.28/bin/mysqld (mysqld 8.0.28) starting as process 9029
2024-04-07T19:19:51.178057Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-07T19:19:51.388720Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-04-07T19:19:51.625281Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-04-07T19:19:51.625353Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-04-07T19:19:51.650393Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 18028, socket: /tmp/mysqlx-18028.sock
2024-04-07T19:19:51.650916Z 0 [System] [MY-010931] [Server] /root/dbdeployer/8.0.28/bin/mysqld: ready for connections. Version: '8.0.28' socket: '/tmp/mysql_sandbox8028.sock' port: 8028 MySQL Community Server - GPL.
2024-04-07T19:23:39.209710Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user msandbox. Shutting down mysqld (Version: 8.0.28).
2024-04-07T19:23:40.061157Z 0 [System] [MY-010910] [Server] /root/dbdeployer/8.0.28/bin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.
2024-04-07T19:23:43.622769Z 0 [System] [MY-010116] [Server] /root/dbdeployer/8.0.36/bin/mysqld (mysqld 8.0.36) starting as process 10053
2024-04-07T19:23:43.631071Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-04-07T19:23:43.859029Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-04-07T19:23:46.306332Z 4 [System] [MY-013381] [Server] Server upgrade from '80028' to '80036' started.
2024-04-07T19:23:51.005990Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2014, 2023, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- View: latest_file_io -- -- Shows the latest file IO, by file / thread. -- -- mysql> select * from latest_file_io limit 5; -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | thread | file | latency | operation | requested | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 9.26 us | write | 124 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 4.00 us | write | 2 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 56.34 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYD | 53.93 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 104.05 ms | delete | NULL | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- CREATE OR REPLACE ALGORITHM = MERGE DEFINER = 'mysql.sys'@'localhost' SQL SECURITY INVOKER VIEW latest_file_io ( thread, file, latency, operation, requested ) AS SELECT IF(id IS NULL, CONCAT(SUBSTRING_INDEX(name, '/', -1), ':', thread_id), CONCAT(user, '@', host, ':', id) ) thread, sys.format_path(object_name) file, format_pico_time(timer_wait) AS latency, operation, format_bytes(number_of_bytes) AS requested FROM performance_schema.events_waits_history_long JOIN performance_schema.threads USING (thread_id) LEFT JOIN information_schema.processlist ON processlist_id = id WHERE object_name IS NOT NULL AND event_name LIKE 'wait/io/file/%' ORDER BY timer_start; ' failed with error code = 1347, error message = ''sys.latest_file_io' is not VIEW'.
2024-04-07T19:23:51.011934Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2024-04-07T19:23:51.011958Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-04-07T19:23:53.471302Z 0 [System] [MY-010910] [Server] /root/dbdeployer/8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
[root@testbox msb_8_0_36]#
[root@testbox msb_8_0_36]#
Suggested fix:
Even its possible to drop and recreate sys schema, it can lead to loss of customizations build on the database. It would be really helpful to include a precheck to verify the object types are accurate to avoid additaiionl downtime during the upgrade.
Example:
-- Even it needs to be updated whenever new objects added to sys schema but it really help to catch issues in sys schema customizations that can impact upgrade operations.
select count(1) from information_schema.tables where table_schema='sys' and table_name in (..) and table_type='VIEW';
Description: Sys schema is used for monitoring purpose and its common to have some customizations on the same. In cases, any users create tables with same name as views or change views to tables (use event scheduler for data collection and insertion into views) then upgrade operation failes with an error. [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2014, 2023, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- View: latest_file_io -- -- Shows the latest file IO, by file / thread. -- -- mysql> select * from latest_file_io limit 5; -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | thread | file | latency | operation | requested | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 9.26 us | write | 124 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 4.00 us | write | 2 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 56.34 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYD | 53.93 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 104.05 ms | delete | NULL | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- CREATE OR REPLACE ALGORITHM = MERGE DEFINER = 'mysql.sys'@'localhost' SQL SECURITY INVOKER VIEW latest_file_io ( thread, file, latency, operation, requested ) AS SELECT IF(id IS NULL, CONCAT(SUBSTRING_INDEX(name, '/', -1), ':', thread_id), CONCAT(user, '@', host, ':', id) ) thread, sys.format_path(object_name) file, format_pico_time(timer_wait) AS latency, operation, format_bytes(number_of_bytes) AS requested FROM performance_schema.events_waits_history_long JOIN performance_schema.threads USING (thread_id) LEFT JOIN information_schema.processlist ON processlist_id = id WHERE object_name IS NOT NULL AND event_name LIKE 'wait/io/file/%' ORDER BY timer_start; ' failed with error code = 1347, error message = ''sys.latest_file_io' is not VIEW'. How to repeat: Below is the session record to repeat the issue: [root@testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer deploy single 8.0.28 Database installed in $HOME/sandboxes/msb_8_0_28 run 'dbdeployer usage single' for basic instructions' . sandbox server started [root@testbox ~]# [root@testbox ~]# [root@testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer deploy single 8.0.36 Database installed in $HOME/sandboxes/msb_8_0_36 run 'dbdeployer usage single' for basic instructions' . sandbox server started [root@testbox ~]# [root@testbox ~]# [root@testbox ~]# [root@testbox ~]# cd sandboxes/msb_8_0_28/ [root@testbox msb_8_0_28]# ./use Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.28 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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 [localhost:8028] {msandbox} ((none)) > use sys 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 [localhost:8028] {msandbox} (sys) > create table latest_file_io_2 select * from latest_file_io; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:8028] {msandbox} (sys) > drop view latest_file_io; Query OK, 0 rows affected (0.01 sec) mysql [localhost:8028] {msandbox} (sys) > rename table latest_file_io_2 to latest_file_io; Query OK, 0 rows affected (0.01 sec) mysql [localhost:8028] {msandbox} (sys) > \q Bye [root@testbox msb_8_0_28]# [root@testbox msb_8_0_28]# cd [root@testbox ~]# [root@testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer admin upgrade msb_8_0_28 msb_8_0_36 stop /root/sandboxes/msb_8_0_28 stop /root/sandboxes/msb_8_0_36 Data directory msb_8_0_28/data moved to msb_8_0_36/data ^C [root@testbox ~]# [root@testbox ~]# cd sandboxes/msb_8_0_36/ [root@testbox msb_8_0_36]# cat data/msandbox.err 2024-04-07T19:19:51.164670Z 0 [System] [MY-010116] [Server] /root/dbdeployer/8.0.28/bin/mysqld (mysqld 8.0.28) starting as process 9029 2024-04-07T19:19:51.178057Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2024-04-07T19:19:51.388720Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2024-04-07T19:19:51.625281Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2024-04-07T19:19:51.625353Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2024-04-07T19:19:51.650393Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 18028, socket: /tmp/mysqlx-18028.sock 2024-04-07T19:19:51.650916Z 0 [System] [MY-010931] [Server] /root/dbdeployer/8.0.28/bin/mysqld: ready for connections. Version: '8.0.28' socket: '/tmp/mysql_sandbox8028.sock' port: 8028 MySQL Community Server - GPL. 2024-04-07T19:23:39.209710Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user msandbox. Shutting down mysqld (Version: 8.0.28). 2024-04-07T19:23:40.061157Z 0 [System] [MY-010910] [Server] /root/dbdeployer/8.0.28/bin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL. 2024-04-07T19:23:43.622769Z 0 [System] [MY-010116] [Server] /root/dbdeployer/8.0.36/bin/mysqld (mysqld 8.0.36) starting as process 10053 2024-04-07T19:23:43.631071Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2024-04-07T19:23:43.859029Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2024-04-07T19:23:46.306332Z 4 [System] [MY-013381] [Server] Server upgrade from '80028' to '80036' started. 2024-04-07T19:23:51.005990Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2014, 2023, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- View: latest_file_io -- -- Shows the latest file IO, by file / thread. -- -- mysql> select * from latest_file_io limit 5; -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | thread | file | latency | operation | requested | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 9.26 us | write | 124 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 4.00 us | write | 2 bytes | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 56.34 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYD | 53.93 us | close | NULL | -- | msandbox@localhost:1 | @@tmpdir/#sqlcf28_1_4e.MYI | 104.05 ms | delete | NULL | -- +----------------------+----------------------------------------+------------+-----------+-----------+ -- CREATE OR REPLACE ALGORITHM = MERGE DEFINER = 'mysql.sys'@'localhost' SQL SECURITY INVOKER VIEW latest_file_io ( thread, file, latency, operation, requested ) AS SELECT IF(id IS NULL, CONCAT(SUBSTRING_INDEX(name, '/', -1), ':', thread_id), CONCAT(user, '@', host, ':', id) ) thread, sys.format_path(object_name) file, format_pico_time(timer_wait) AS latency, operation, format_bytes(number_of_bytes) AS requested FROM performance_schema.events_waits_history_long JOIN performance_schema.threads USING (thread_id) LEFT JOIN information_schema.processlist ON processlist_id = id WHERE object_name IS NOT NULL AND event_name LIKE 'wait/io/file/%' ORDER BY timer_start; ' failed with error code = 1347, error message = ''sys.latest_file_io' is not VIEW'. 2024-04-07T19:23:51.011934Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server. 2024-04-07T19:23:51.011958Z 0 [ERROR] [MY-010119] [Server] Aborting 2024-04-07T19:23:53.471302Z 0 [System] [MY-010910] [Server] /root/dbdeployer/8.0.36/bin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL. [root@testbox msb_8_0_36]# [root@testbox msb_8_0_36]# Suggested fix: Even its possible to drop and recreate sys schema, it can lead to loss of customizations build on the database. It would be really helpful to include a precheck to verify the object types are accurate to avoid additaiionl downtime during the upgrade. Example: -- Even it needs to be updated whenever new objects added to sys schema but it really help to catch issues in sys schema customizations that can impact upgrade operations. select count(1) from information_schema.tables where table_schema='sys' and table_name in (..) and table_type='VIEW';