MySQL Forums
Forum List  »  Install & Repo

Drop and recreate sys schema - no harm ?
Posted by: yvonne kire
Date: February 07, 2019 12:42PM

As I understand sys schema is new in 5.7.7 and above
it's a 'helper' schema to mostly used by performance schema
ie to keep track of historical data - true or false ?

I have MySQL 5.7.22 sys_config TABLESPACE ID miss match problem on many db servers.

I keeps crashing my 5.7 randomly - it tries to access sys.sys_config table and there is a miss match and it crash and restart mysql. I don't know what mysql try to do internaly to the sys schema that caused innodb to crash.

[ERROR] InnoDB: Failed to find tablespace for table `sys`.`sys_config` in the cache. Attempting to load the tablespace with space id 12282
[ERROR] InnoDB: In file './sys/sys_config.ibd', tablespace id and flags are 21 and 33, but in the InnoDB data dictionary they are 12282 and 33.
[ERROR] InnoDB: Operating system error number 2 in a file operation.
[ERROR] InnoDB: The error means the system cannot find the path specified.
[ERROR] InnoDB: Could not find a valid tablespace file for `sys/sys_config`.
[Warning] InnoDB: Cannot calculate statistics for table `sys`.`sys_config` because the .ibd file is missing.

BUT the file is there....

mysql> desc sys_config;
ERROR 1146 (42S02): Table 'sys.sys_config' doesn't exist

# cd /var/lib/mysql/sys/
# /var/lib/mysql/sys]# ls -atlhr sys_config.ibd
-rw-r----- 1 mysql mysql 96K Nov 22 15:05 sys_config.ibd

Current FIX

drop database sys ;
\! rm -fr /var/lib/mysql/sys
source /usr/share/percona-server/mysql_sys_schema.sql
desc sys.sys_config ;
select count(*) from sys.sys_config;

Anyone see any problem with this?

ie drop sys schema
and then create a blank new sys schema
(I know all past data will be lost but I don't need it)

Options: ReplyQuote

Written By
Drop and recreate sys schema - no harm ?
February 07, 2019 12:42PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.