0

I'm running MySQL in Linux Red Hat 7. I've got an existing database, error.db, which I copied over to my MySQL directory, and now I want to import it into MySQL. I found this guide which recommended using mysqldump to export the database to an .sql file, which I can then source in the MySQL client.

So I tried the following:

$ mysqldump --socket=socket -u root -p error.db > error_db.sql

After entering the password, I received this error:

mysqldump: Got error: 1049: Unknown database 'error.db' when selecting the database

I'm definitely in the correct directory, error.db definitely exists, and I've definitely typed the password in correctly. I've tried adding the -d flag and the --databases flag, and that hasn't worked. I've also tried using error instead of error.db, and that hasn't worked.

I've checked other questions and haven't found a working solution. This one, the problem was caused by a typo (which I know is not the case:) mysql export table but no database.

And this one was solved because the OP was putting the password in the command line, and apparently using the -p flag solved their problem (although as you can see, I'm using the -p flag and it's still not working:) MYSQL DBDump Error message

I'm using a Load Sharing Facility at work to run MySQL, but that hasn't stopped me from using another .sql file to load a database into MySQL, so I'm not sure whether that would make a difference.

EDIT: Per this question, I've tried using mysql instead of mysqldump, but still no luck:

mysql --socket=socket -u root -p error.db > error_db.sql
Enter password: 
ERROR 1049 (42000): Unknown database 'error.db'

Any ideas what might be going wrong here?

EDIT 2: The error.db database is in SQLite3. I had assumed that the database types would be cross-compatible, but probably this was an incorrect assumption. So I guess my question is how to convert an SQLite3 database to MySQL.

Lou
  • 2,200
  • 2
  • 33
  • 66
  • I am about contents of 'error.db'. How was this file created? if it is readable, can you share the first couple of lines of it? – Luuk Sep 14 '20 at 11:38
  • *`error.db` definitely exists* Do you really see `error.db` in the output of `SHOW DATABASES;` command? – Akina Sep 14 '20 at 11:40
  • @Akina - No, it doesn't show up in MySQL using `SHOW DATABASES` - the point is that I'm trying to import it into MySQL, after first exporting it to an .sql file. I mean that the `error.db` file definitely exists in the directory I'm working in, and the directory from which I started MySQL. – Lou Sep 14 '20 at 11:42
  • *I mean that the error.db file definitely exists in the directory I'm working in, and the directory from which I started MySQL.* This file is NOT a database. This is regular file which cannot be dumped via MySQL. use common file copy procedure. – Akina Sep 14 '20 at 11:45
  • @Luuk - The file was originally created for SQLite3 - I forgot to mention. I believe I populated it by parsing a plain text file using Perl, then outputting into an SQLite3 database. – Lou Sep 14 '20 at 11:48
  • @Akina What is "common file copy procedure" here? – Lou Sep 14 '20 at 11:49
  • 1
    If this is SQLite database, and you need to import it to MySQL, you must convert its data from SQLite database format to some format which is clear for MySQL or use the convertor utility/script. There is a lot of questions about converting/migrating from SQLite to MySQL there - search... – Akina Sep 14 '20 at 12:06
  • I can't say that you were 100% cooperative in your approach, but you did technically point me in the direction of the right answer, so +1 and cheers :) – Lou Sep 14 '20 at 14:39
  • 1
    `sqlite3 error.db .dump > error.sql` might get you started to create a file ('error.sql') which migth be importable into Mysql. I think you first need to create ta database in MySQL (`CREATE DATABASE error`) before bein able to do an import. – Luuk Sep 14 '20 at 15:41

2 Answers2

0

So, it turns out the problem was because I was trying to open a database created in SQLite using MySQL. In retrospect I probably should've seen that that would be an issue.

The solution I took was to export the file in SQLite3 to a CSV file, by opening error.db in SQLite3, and using this code:

.headers on
.mode csv
.output error_data.csv

(Source)

Then I opened MySQL using:

mysql --socket=socket -u root -p --local-infile error

I created the database error and the table error, and then ran the following commands, which successfully imported the data from the .csv file into my database:

mysql> LOAD DATA LOCAL INFILE 'error_data.csv'
    -> INTO TABLE error
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
Query OK, 920 rows affected, 627 warnings (0.05 sec)
Records: 920  Deleted: 0  Skipped: 0  Warnings: 627

Thanks to @mpoletto from this question and the OP from this question for helping me figure all that out.

I don't know why there were warnings; I was unable to check them as I had already run a select query before I knew that you had to run SHOW WARNINGS immediately after the command.

Lou
  • 2,200
  • 2
  • 33
  • 66
0

Use this mysqldump --socket=socket -u root -p --databases error.db > error_db.sql command instead of mysqldump --socket=socket -u root -p error.db > error_db.sql

Hassan Shamshir
  • 37
  • 7