Importing MySQL data from a .sql file #944
Description
I have an existing MySQL database that I have to work with. In development I want to use knex seeds to create this database. I don't HAVE to do this w/ knex (I can use chef if I need to), but it'd be nice to be able to do.
I create a .sql file with the command mysql -u user -p table > dump.sql. The output contains conditional execution statements that look like this:
/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
Knex barfs on these statements. My error output from knex seed:run is
rror: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set' at line 9
at Query.Sequence._packetToError (/vagrant/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
at Query.ErrorPacket (/vagrant/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
at Protocol._parsePacket (/vagrant/node_modules/mysql/lib/protocol/Protocol.js:274:23)
at Parser.write (/vagrant/node_modules/mysql/lib/protocol/Parser.js:77:12)
at Protocol.write (/vagrant/node_modules/mysql/lib/protocol/Protocol.js:39:16)
at Socket.<anonymous> (/vagrant/node_modules/mysql/lib/Connection.js:96:28)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
at TCP.onread (net.js:526:21)
at Protocol._enqueue (/vagrant/node_modules/mysql/lib/protocol/Protocol.js:135:48)
at Connection.query (/vagrant/node_modules/mysql/lib/Connection.js:201:25)
at /vagrant/node_modules/knex/lib/dialects/mysql/index.js:92:18
From previous event:
at Client._query (/vagrant/node_modules/knex/lib/dialects/mysql/index.js:88:12)
at Client.query (/vagrant/node_modules/knex/lib/client.js:127:24)
at Runner.assign.query (/vagrant/node_modules/knex/lib/runner.js:118:24)
From previous event:
at /vagrant/node_modules/knex/lib/runner.js:44:21
From previous event:
at Runner.run (/vagrant/node_modules/knex/lib/runner.js:30:20)
at Raw.Target.then (/vagrant/node_modules/knex/lib/interface.js:27:43)
at Object.exports.seed (/vagrant/workbench-seeds/dev/workbench.js:5:20)
at Object.<anonymous> (/vagrant/node_modules/knex/lib/seed/index.js:110:19)
From previous event:
at /vagrant/node_modules/knex/lib/seed/index.js:109:23
at arrayEach (/vagrant/node_modules/lodash/index.js:1289:13)
at Function.<anonymous> (/vagrant/node_modules/lodash/index.js:3345:13)
at Seeder._waterfallBatch (/vagrant/node_modules/knex/lib/seed/index.js:104:5)
at Seeder.<anonymous> (/vagrant/node_modules/knex/lib/seed/index.js:64:19)
From previous event:
at Seeder.<anonymous> (/vagrant/node_modules/knex/lib/seed/index.js:63:31)
From previous event:
at Seeder._runSeeds (/vagrant/node_modules/knex/lib/seed/index.js:62:82)
at Seeder.<anonymous> (/vagrant/node_modules/knex/lib/seed/index.js:24:17)
From previous event:
at Seeder.<anonymous> (/vagrant/node_modules/knex/lib/seed/index.js:23:38)
From previous event:
at Command.<anonymous> (/usr/local/lib/node_modules/knex/lib/bin/cli.js:134:34)
at Command.listener (/usr/local/lib/node_modules/knex/node_modules/commander/index.js:301:8)
at Command.EventEmitter.emit (events.js:98:17)
at Command.parseArgs (/usr/local/lib/node_modules/knex/node_modules/commander/index.js:610:12)
at Command.parse (/usr/local/lib/node_modules/knex/node_modules/commander/index.js:458:21)
at Liftoff.invoke (/usr/local/lib/node_modules/knex/lib/bin/cli.js:142:13)
at Liftoff.<anonymous> (/usr/local/lib/node_modules/knex/node_modules/liftoff/index.js:181:16)
at module.exports (/usr/local/lib/node_modules/knex/node_modules/liftoff/node_modules/flagged-respawn/index.js:17:3)
at Liftoff.<anonymous> (/usr/local/lib/node_modules/knex/node_modules/liftoff/index.js:174:9)
at /usr/local/lib/node_modules/knex/node_modules/liftoff/index.js:148:9
at /usr/local/lib/node_modules/knex/node_modules/v8flags/index.js:99:14
at /usr/local/lib/node_modules/knex/node_modules/v8flags/index.js:38:7
at process._tickCallback (node.js:415:13)
at Function.Module.runMain (module.js:499:11)
at startup (node.js:119:16)
at node.js:902:3
And my seed file looks like this:
var fs = require('fs');
exports.seed = function(knex, Promise) {
var sql = fs.readFileSync('./data/workbench.sql').toString();
return Promise.join(
knex.raw('DROP DATABASE workbench'),
knex.raw('CREATE DATABASE workbench'),
knex.raw(sql)
);
};
Is there a better way to import a .sql file using knex? Is that not really what knex is for (ie, am I misusing the technology)? For now I'm going to have to work around this problem with chef, but I'm interested in what you guys think about this use case and problem.