Table does not show tables relations after importing database
Question by indie
I imported a MySQL dump file into my MySQL server using following command.
mysql> create database database_name;
mysql> use database_name;
In Linux command prompt,
$ mysql -u user_name -p database_name < /tmp/test1.dmp
But, while viewing the database using phyMyAdmin the newly created database_name does not show table relations. It shows only the tables, but not relations.
Is there any set up required before importing the database in mysql database?
How to extract the relations between tables?
Answer by Starx
I just went through the exact same problem.
Is there any set up required before importing the database in mysql database?
Not exactly, but it seems LAMP Server installed on Ubuntu or any Linux Distribution uses MyISAM storage engine by default while creating tables. On InnoDB supports Foreign Key relation. [more info]
To change the storage engine afterwards. [Source answer]
You have to add the line default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (
my.cnformy.inidepending on your operation system) and restart the mysqld service. I don’t believe you can change this throughphpMyAdmin.
On ubuntu, my.cnf is located inside /etc/mysql/.
Or, you can use mysql command
mysql> SET storage_engine=InnoDb;
After this, all the tables and database you create after this, will use InnoDB as their default storage engine, thus eliminating the issue afterwards.
How to extract the relations between tables?
After you change default engine of your database. You also have to change the default engine of your tables, because they haven’t been changed yet. Use the syntax below to change the storage engine
ALTER TABLE <table_name> ENGINE = innodb
Using phpMyAdmin
- Go to the operations tab after selecting a table
- Go to the table options [See below]
- You will see an option to change the storage engine of the table
- Change the storage engine to
InnoDband hitGo
After this, export the database using phpMyadmin or dump your database using mysqldump. it will show the relations.
