MySQL Import shows successful but no tables generated

Question

Tried importing a small .sql file (~5MB) via cmd. Within a few seconds the import returns as if it was successful (no errors or warnings), however, when I refresh the schema in workbench or check contents of the folder representation of the schema, the schema appears to be empty.

This is the command I used:

mysql -hlocalhost -uroot -ppassword db_name < C:\sql_input_path\db.sql > C:\error_log_output_path\error.txt

In addition, I tried importing the .sql file using the Workbench import tool, which also shows successful, but nothing seems to be reflected in the actual schema after a refresh.

Any ideas why this is and how to correct this? I have been trying to figure this out for quite some time.

thanks in advance!

Onega

In addition, show processlist command doesn't seem to suggest that MySQL is importing anything during the import and show tables returns no results after the import. – Onega

Check in the db.sql file, if use db_name is included with a different db_name than what you are expecting. – Smithsonite

@Ravinder, thanks for the reply. Don't think this matters, the use dbname is automatically generated with the dump. When you specify a DB name in the cmd import command, it should override the use dbname that is within the .sql file. – Onega

Do you mean the db specified at command line overrides the one specified in file? If yes, my answer is NO. – Smithsonite

+1, Thanks for the reply. You're right. Is there a way to force it to use a different schema, without editing the text in the .sql file? The file I need to import is 40GB+ in size so I cannot open it with a text editor to edit the default schema. – Onega

If you can't edit it, better regenerate the sql file with no use db command included. No other option. – Smithsonite

Is there a way to create a dump via the command line without it? – Onega

Use tools like SQLYog, MySQL Workbench for exporting data into an sql file. You can instruct but optionally, to include use database statement in the dump file. – Smithsonite

Answer 1

Just quoteing Ravinder Reddys:

"Check in the db.sql file, if use db_name is included with a different db_name than what you are expecting. Ravinder Reddy May 26 '14 at 16:38

Worked for me.

Sarrusophone

Answer 2

Im using microsoft SQL. All I had to do is restart the program or just press refresh. It's there you just need to refresh it so it will appear.

Gulp

Answer 3

Check your actual sql file with any editor... this file likely says in the first lines:

Host: localhost Database: yournamedatabase blah blah blah blah blah blah -- Current Database: yournamedatabase

CREATE DATABASE /!32312 IF NOT EXISTS/ yournamedatabase /*!40100 DEFAULT CHARACTER SET latin1 */;

USE yournamedatabase;

This name is likely different from your file name...maybe your file name is mydatabase.sql.... So what you need to create is yournamedatabase with mysql not mydatabase database...or to not complicate yourself just match the names so all of them are identical.

Ap

Answer 4

If you are using transactions, make sure that at the start of the script there is a START TRANSACTION; command and a COMMIT; command at the end of the script.

Itinerate