Imported tables are not showing up in phpmyadmin [closed]

Question

I exported our production database and created a development database called test. Using ssh I imported the tables/data into the development database.

mysql -u root -p -h localhost test < data.sql

I can view the database structure and it returns rows as expected when I use the command line mysql. However, when I open up phpmyadmin, the "test" database is there but it has no tables and I can't select any of the data.

Any idea on how to fix this?

Answer 1

I had a similar problem with one database and I'd like to share with you a possible cause/solution:

I have imported a .sql file as you did and then I went to phpmyadmin, selected the database and surprise: No tables in the database, but I went to mysql command line and did a SHOW TABLES and they do exist.

So this is what happened in my case, the original database had some VIEWS defined by a user that did not exist in my computer's mysql users. Example:

CREATE ALGORITHM=UNDEFINED DEFINER=admin@% SQL SECURITY DEFINER VIEW cantidades AS select (...)

The user admin@% was available on the original server from where I've exported the database, but not on my computer.

So the fix to this solution was either to add that user, or to drop the views and create them again with a existing user.

I have choosen the second option: DROP VIEW cantidades; CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW cantidades AS select (...)

I've used root because that is the default user for my local XAMPP installation, but for security purposes I recommend using the same user you use to connect to that database.

Answer 2

1. Check admin database user name for the database you selected to import
2. Create a user with same name in the newly imported database through phpmyadmin.

Your problem will be fixed.