Not the answer you need?
Register and ask your own question!

Error after changing datadir

phiberphiber EntrantInactive User Role Beginner
I added 2 SSDs in RAID 1 configuration and mounted them as /ssd.
At first, it was very important that two tables from a single database are moved to faster drives so I used the pt-online-schema-change tool to change their datadir to /ssd/mysql

After some time (about 24 hrs) we were able to bring the server down for a couple of minutes so I:
1. stopped the server
2. copied /var/lib/mysql to /ssd/mysql, keeping file and directory ownership of the mysql user
3. changed the datadir in my.cnf to /ssd/mysql
4. started the server and got errors on tables that were previously moved with pt-online-schema-change

Here is the error log:
2017-08-30 12:55:22 2384 [ERROR] InnoDB: A tablespace for dbname/table_name has been found in multiple places;
2017-08-30 12:55:22 2384 [ERROR] InnoDB: Default location; ./dbname/table_name.ibd, LSN=0, Space ID=806060, Flags=1024
2017-08-30 12:55:22 2384 [ERROR] InnoDB: Dictionary location; /ssd/mysql/dbname/table_name.ibd, LSN=0, Space ID=806060, Flags=1024
2017-08-30 12:55:22 2384 [ERROR] InnoDB: Will not open the tablespace for 'dbname/table_name'
2017-08-30 12:55:22 7f8967ff2700 InnoDB: cannot calculate statistics for table "dbname"."table_name" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2017-08-30 12:55:23 7f8967ead700 InnoDB: cannot calculate statistics for table "dbname"."table_name2" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

and so on.

what should I do? Move those tables back to the original location with pt-online-schema-change and repeat the steps above?

Comments

  • modern zoromodern zoro Entrant Inactive User Role Beginner
    Hi,
    did you checked ownership of new dir ?
    try
    chown -p mysql:mysql /ssd/*
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.