I need to autonomously convert mysqldump files (.sql file) to SQLite compatible .sql files. I found this script on github which is supposed to be able to do this. If I had an unmodified .sql file from MySQL called test.sql whose database’s name was test and the script mysql2sqlite.sh in a directory, how would I call it.
The script says in the comments how to call it. I believe my scenario matches the first one titled usage because I already have the mysqldump file.
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite # Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
I read in the comments on the github page to change the scripts permissions with chmod +x mysql2sqlite.sh so navigated to the directory with the sql and the script and did that.
Then I tried:
pi@raspberrypi ~ $ ./mysql2sqlite.sh test.sql test | sqlite3 database.sqlite
It failed with the error: -bash: ./mysql2sqlite.sh: /bin/sh^M: bad interpreter: No such file or directory
I then read online that to call an .sh file you type sh myShellFile.sh
So I tried sh mysql2sqlite.sh test.sql test | sqlite3 database.sqlite
and got back:
: not founde.sh: 2: mysql2sqlite.sh: : not founde.sh: 5: mysql2sqlite.sh: : not founde.sh: 8: mysql2sqlite.sh: : not founde.sh: 11: mysql2sqlite.sh: : not founde.sh: 13: mysql2sqlite.sh: : not founde.sh: 14: mysql2sqlite.sh: mysqldump: Got error: 1045: Access denied for user 'pi'@'localhost' (using password: NO) when trying to connect : not founde.sh: 15: mysql2sqlite.sh:
It appears that its trying to connect to a server rather than reading the mysqldump file that I provided. What is the proper way to call this script. I have some experience with Linux and no experience with unix shells.
It may be of some use to read the background of my problem
EDIT: After editing the first line of the script running it again produces the error Error: near line 4: near "Insert": syntax error
Here is the sqldump I’m trying to process:
-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 28, 2015 at 06:00 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `testtable`
--
CREATE TABLE IF NOT EXISTS `testtable` (
`FirstName` varchar(30) NOT NULL,
`LastName` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `testtable`
--
INSERT INTO `testtable` (`FirstName`, `LastName`) VALUES
('', ''),
('Josh', 'Kallus'),
('', ''),
('Josh', 'Kallus');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Advertisement
Answer
You’re misinterpreting the comments in the script. The script is expecting mysql dump options followed by the dbname (not a dump file). If you look at the first line of code you will see how it works.
mysqldump --compatible=ansi --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact "$@" |
The script is expecting the output MySQL data to be using the parameters it also passes, so the best method is probably to use the script as intended.
mysql2sqlite.sh -h my.host.com --single-transaction -umyuser -p mydb | sqlite3 database.sqlite
If you can only do this from an existing dump file, then you could change that first mysqldump line in the code to this
cat $@ |
Then you could do this
mysql2sqlite.sh test.sql | sqlite3 database.sqlite
Keep in mind that this script is not a silver bullet, you may run into other incompatibility issues. You may be able to resolve them by directly editing the dump file itself. Good luck.
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
Edit: As I originally stated, this script is not a magic bullet and you may have to edit your dump to make it work (best compatibility coming from using it’s built in dump function) I’ve edited your script to what you see below and it should work.
-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 28, 2015 at 06:00 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `testtable`
--
CREATE TABLE IF NOT EXISTS `testtable` (
`FirstName` varchar(30) NOT NULL,
`LastName` varchar(30) NOT NULL
);
--
-- Dumping data for table `testtable`
--
INSERT INTO `testtable` (`FirstName`, `LastName`) VALUES('', ''),('Josh', 'Kallus'),('', ''),('Josh', 'Kallus');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;