0

so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables are opening The innodb tables are listed but when I click on any of them on the left tab (phpmyadmin) I get this error: #1146 - Table 'databasename.users' doesn't exist this would be for the users table. Other innodb tables are having the same issue. I would like to know how to remedy this issue. I logged in to mysql directly through ssh as root, when I do : SHOW TABLES; the table users and the other innodb tables are all listed. When I do SHOW COLUMNS FROM users; I get :

mysql> show columns from article;
ERROR 1146 (42S02): Table 'sitecontent.article' doesn't exist

any ideas please ? (issue resides in innodb tables only!)

0

You cannot copy a database folder that has InnoDB tables because InnoDB storage engine maintains a symbiotic relationship between the InnoDB tables' physical files (frm and .ibd) and the data dictionary.

InnoDB Architecture

The data dictionary is inside the system tablespace file known as ibdata1.

You cannot copy a data folder and expect it to be independent of ibdata1.

SHOW TABLES; works because it only scans for .frm files and does not access metadata.

You could do one of two things

GIVE IT A TRY !!!

  • I copied the ibdata1 as well ... pretty much moved the mysql folder to a different server.Some databases are showing others arent – nodejsj Nov 6 '14 at 20:38
  • You cannot move ibdata1 from on server to another because the data dictionary is shared by all subfolders in the old server. Any references to databases in the target server are now inaccessible. You will have to reattach tables in the target server. LESSON: If you move ibdata1, everything should comes with it. In the future (MySQL 5.7), InnoDB will no longer need a data dictionary. – RolandoMySQLDBA Nov 6 '14 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.