Hi All,

I’ve been somewhat successful getting the Locomotive install running on my new iMac. The app is there and I signed in and can login.

My problems arise when trying to import my old MySQL dump into the (empty/default?) SQLite3 db.

When I run: sqlite3 db/tracks-104.db < db/tracks-104-dump.sql

(in terminal from my new tracks-1.043 folder—yes I named my old dump “tracks-104-dump.sql” which I did in YourSQL on my old system)

as per bsag’s post: Updated Tracks 1.043 for installation with Locomotive

I get a big chunk of data and then this error:

SQL error: unrecognized token: “`“
UNLOCK TABLES;
SQL error: near “UNLOCK”: syntax error
CREATE TABLE `todos` (
  `id` int(11) NOT NULL auto_increment,
  `context_id` int(11) NOT NULL default ‘0’,
  `project_id` int(11) default NULL,
  `description` varchar(255) NOT NULL default ‘’,
  `notes` text,
  `done` tinyint(1) NOT NULL default ‘0’,
  `created_at` datetime default NULL,
  `due` date default NULL,
  `completed` datetime default NULL,
  `user_id` int(11) default ‘1’,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SQL error: unrecognized token: “`“
LOCK TABLES `todos` WRITE;
SQL error: unrecognized token: “`“

and then another big chunk and then this error:

UNLOCK TABLES;
SQL error: unrecognized token: “`“
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `login` varchar(80) NOT NULL default ‘’,
  `password` varchar(40) NOT NULL default ‘’,
  `word` varchar(255) default NULL,
  `is_admin` tinyint(1) NOT NULL default ‘0’,
  `preferences` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SQL error: unrecognized token: “`“
LOCK TABLES `users` WRITE;
SQL error: unrecognized token: “`“
INSERT INTO `users` VALUES (1,‘tim’,‘c4b709311c79f499e8d376767ef63eb903c1c91a’,‘22fd8dd501eaa43800309a6130aff04bea8603f0’,1,‘—- \nstaleness_starts: \“180\“\ndate_format: \“%d/%m/%Y\“\nno_completed: \“5\“\nadmin_email: \nweek_starts: \“1\“\ndue_style: \“1\“‘);
SQL error: unrecognized token: “`“
UNLOCK TABLES;
SQL error: near “UNLOCK”: syntax error

So I’m stuck. Can someone help me migrate my database to SQLite3?

As far as converting the boolean fields. Is bsag’s syntax correct (I was getting errors)?

Should:
sqlite> set done = ‘f’
be
sqlite> set done = f

Should
sqlite> where done = 0;
be
sqlite> where done = 0

Any help with this last hurdle would be greatly appreciated so I can et back to getting things done. I can email you mt SQL dump if needed.

I’m three weeks trackless—is there a db guru out there somehwere? Bsag, are you back?

Yes, I am back, though still swamped under a backlog.

The error:

SQL error: unrecognized token: “`”

is telling you that it can’t recognise the backticks (`) in your database dump. I would go through your dump and search and replace backticks for standard double quotes (“) - make sure you do this in a text editor which doesn’t auto-convert your ASCII double quotes for fancy curly quotes.

The replacement syntax for the Boolean fields is fine.

Sorry to take so long to reply!

Hi Bsag,

Welcome back!

Okay, did the find and replace of bacticks (`) for standard double quotes (“) in Textmate. 190 replacements.

Ran: sqlite3 db/tracks-104.db < db/tracks-104-dump.sql

and got big chunk of data more erros another big chunk and more erros. Here they are:

DATADATADATADATA

SQL error: near “,“: syntax error
UNLOCK TABLES;
SQL error: near “UNLOCK”: syntax error
CREATE TABLE “todos” (
  “id” int(11) NOT NULL auto_increment,
  “context_id” int(11) NOT NULL default ‘0’,
  “project_id” int(11) default NULL,
  “description” varchar(255) NOT NULL default ‘’,
  “notes” text,
  “done” tinyint(1) NOT NULL default ‘0’,
  “created_at” datetime default NULL,
  “due” date default NULL,
  “completed” datetime default NULL,
  “user_id” int(11) default ‘1’,
  PRIMARY KEY (“id”)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SQL error: table “todos” already exists
LOCK TABLES “todos” WRITE;
SQL error: near “LOCK”: syntax error

DATADATADATADATA

UNLOCK TABLES;
SQL error: unrecognized token: “\“
CREATE TABLE “users” (
  “id” int(11) NOT NULL auto_increment,
  “login” varchar(80) NOT NULL default ‘’,
  “password” varchar(40) NOT NULL default ‘’,
  “word” varchar(255) default NULL,
  “is_admin” tinyint(1) NOT NULL default ‘0’,
  “preferences” text,
  PRIMARY KEY (“id”)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SQL error: table “users” already exists
LOCK TABLES “users” WRITE;
SQL error: near “LOCK”: syntax error
INSERT INTO “users” VALUES (1,‘tim’,‘c4b709311c79f499e8d376767ef63eb903c1c91a’,‘22fd8dd501eaa43800309a6130aff04bea8603f0’,1,‘—- \nstaleness_starts: \“180\“\ndate_format: \“%d/%m/%Y\“\nno_completed: \“5\“\nadmin_email: \nweek_starts: \“1\“\ndue_style: \“1\“‘);
SQL error: PRIMARY KEY must be unique
UNLOCK TABLES;
SQL error: near “UNLOCK”: syntax error

I’ve emailed you my “tracks-104-dump.sql”

T.