Pgloader: Importing from SQLite fails
Trying to import SQLite database. No luck so far.
I try pgloader --verbose pgloader-simple.test
and that file is:
root@jessie-db:/home/sysman# cat pgloader-simple.test
LOAD DATABASE
FROM sqlite:///home/sysman/laskutus-ilmanlogoa.sqlite
INTO postgresql:///postgres@localhost/satatuulilaskutus;
root@jessie-db:/home/sysman#
As far as I can see this should work, mostly copied it from man page and or web site.
Results:
root@jessie-db:/home/sysman# pgloader --verbose pgloader-simple.test
An unhandled error condition has been signalled:
Could not parse subexpression ";" when parsing
Expression PGLOADER.PARSER::COMMANDS
Subexpression (+ PGLOADER.PARSER::COMMAND)
Subexpression PGLOADER.PARSER::COMMAND
Subexpression (AND
(OR PGLOADER.PARSER::LOAD-ARCHIVE
PGLOADER.PARSER::LOAD-CSV-FILE
PGLOADER.PARSER::LOAD-FIXED-COLS-FILE
PGLOADER.PARSER::LOAD-DBF-FILE
PGLOADER.PARSER::LOAD-IXF-FILE
PGLOADER.PARSER::LOAD-MYSQL-DATABASE
PGLOADER.PARSER::LOAD-SQLITE-DATABASE
PGLOADER.PARSER::LOAD-SYSLOG-MESSAGES)
PGLOADER.PARSER::END-OF-COMMAND)
Subexpression PGLOADER.PARSER::END-OF-COMMAND
Subexpression (AND PGLOADER.PARSER::IGNORE-WHITESPACE #\;
PGLOADER.PARSER::IGNORE-WHITESPACE)
Subexpression ";"
Encountered at:
LOAD DATABASE
FROM sq
^ (Line 1, Column 0, Position 0)
If I try from command line, I get:
root@jessie-db:/home/sysman# pgloader ./laskutus-ilmanlogoa.sqlite postgresql:///satatuulilaskutus
An unhandled error condition has been signalled:
:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite"
{10084D8903}>:
the octet sequence #(219 0) cannot be decoded.
root@jessie-db:/home/sysman#
There might be some strange character in the database, typing error perhaps. I make an SQL dump, that is a text file, and tried to find that character in there. No luck.
I do not understand why the command file fails so miserably. Is the Encountered at position completely wrong? The syntax seems OK to me, at least it should work a little bit.
tapio-lehtonen
All 22 comments
Can you retry with --debug --verbose flags please?
dimitri
on 16 Jun 2015
With flags coming up. Also, I forgot to say this is running on Debian Jessie and version:
root@jessie-db:/home/sysman# pgloader --version
pgloader version "3.1.0"
compiled with SBCL 1.2.3.debian
root@jessie-db:/home/sysman# pgloader --debug --verbose ./laskutus-ilmanlogoa.sqlite postgresql:///satatuulilaskutus
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
An unhandled error condition has been signalled:
:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite"
{10084D8D33}>:
the octet sequence #(219 0) cannot be decoded.
Date/time: 2015-06-17-08:21An unhandled error condition has been signalled:
:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM
for "file /home/sysman/laskutus-ilmanlogoa.sqlite"
{10084D8D33}>:
the octet sequence #(219 0) cannot be decoded.
Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {100849E703}>
0: ((LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX))
1: (SB-IMPL::CALL-WITH-SANE-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {10088500DB}>)
2: (SB-IMPL::%WITH-STANDARD-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {10088500AB}>)
3: (PRINT-BACKTRACE :STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {10002A5A03}> :START 0 :FROM :DEBUGGER-FRAME :COUNT 4611686018427387903 :PRINT-THREAD T :PRINT-FRAME-SOURCE NIL :METHOD-FRAME-STYLE NIL)
4: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {10002A5A03}>)
5: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<SB-INT:STREAM-DECODING-ERROR {10084D9BD3}> :OUTPUT #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {10002A5A03}> :IF-EXISTS :APPEND :VERBOSE T)
6: (SIGNAL #<SB-INT:STREAM-DECODING-ERROR {10084D9BD3}>)
7: (ERROR #<SB-INT:STREAM-DECODING-ERROR {10084D9BD3}>)
8: (SB-IMPL::STREAM-DECODING-ERROR-AND-HANDLE #<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite" {10084D8D33}> 2)
9: (SB-IMPL::FD-STREAM-READ-N-CHARACTERS/UTF-8 #<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite" {10084D8D33}> "SQLite format 3@ !SQLite format 3@ !" 4 508 NIL)
10: (SB-INT:FAST-READ-CHAR-REFILL #<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite" {10084D8D33}> NIL :EOF)
11: (SB-IMPL::ANSI-STREAM-READ-CHAR #<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite" {10084D8D33}> NIL :EOF #<unavailable argument>)
12: (SB-IMPL::ANSI-STREAM-READ-SEQUENCE "SQLite format 3@ !2015-06-17T05:21:40.038000Z LOG Starting pgloader, log system is ready.
2015-06-17T05:21:40.077000Z INFO Starting monitor
2015-06-17T05:21:40.096000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2015-06-17T05:21:40.097000Z LOG Data errors in '/tmp/pgloader/'
2015-06-17T05:21:40.097000Z LOG Data errors in '/tmp/pgloader/'
ader.log'
2015-06-17T05:21:40.098000Z FATAL We have a situation here.
" #<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite" {10084D8D33}> 0 NIL)
13: (READ-SEQUENCE "SQLite format 3@ !" #<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite" {10084D8D33}> :START 0 :END NIL)
14: (PGLOADER.UTILS:SLURP-FILE-INTO-STRING #P"/home/sysman/laskutus-ilmanlogoa.sqlite")
15: (PGLOADER.PARSER::PARSE-COMMANDS-FROM-FILE #P"/home/sysman/laskutus-ilmanlogoa.sqlite")
16: (PGLOADER.PARSER:RUN-COMMANDS #P"/home/sysman/laskutus-ilmanlogoa.sqlite" :START-LOGGER NIL :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)
17: ((FLET #:CLEANUP-FUN-232 :IN PGLOADER::MAIN)) [cleanup]
18: (PGLOADER::MAIN ("pgloader" "--debug" "--verbose" "./laskutus-ilmanlogoa.sqlite" "postgresql:///satatuulilaskutus"))
19: ((LAMBDA NIL :IN "/tmp/buildd/pgloader-3.1.0+dfsg/dumper-2SKVI5f7.lisp"))
20: ((FLET #:WITHOUT-INTERRUPTS-BODY-89 :IN SAVE-LISP-AND-DIE))
21: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))
debugger invoked on a SB-INT:STREAM-DECODING-ERROR in thread
#<THREAD "main thread" RUNNING {100849E703}>:
:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file /home/sysman/laskutus-ilmanlogoa.sqlite"
{10084D8D33}>:
the octet sequence #(219 0) cannot be decoded.
Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.
restarts (invokable by number or by possibly-abbreviated name):
0: [ABORT] Exit application
debugger invoked on a TYPE-ERROR in thread
#<THREAD "main thread" RUNNING {100849E703}>:
The value INVOKE-DEBUGGER is not of type SB-DI:FRAME.
Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.
restarts (invokable by number or by possibly-abbreviated name):
0: [ABORT] Exit application
(SB-DEBUG::FRAME-CALL INVOKE-DEBUGGER :METHOD-FRAME-STYLE :NORMAL :REPLACE-DYNAMIC-EXTENT-OBJECTS NIL)
Log file:
root@jessie-db:/home/sysman# cat /tmp/pgloader/pgloader.log
2015-06-17T05:21:40.038000Z LOG Starting pgloader, log system is ready.
2015-06-17T05:21:40.077000Z INFO Starting monitor
2015-06-17T05:21:40.096000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2015-06-17T05:21:40.097000Z LOG Data errors in '/tmp/pgloader/'
2015-06-17T05:21:40.098000Z LOG Parsing commands from file #P"/home/sysman/laskutus-ilmanlogoa.sqlite"
2015-06-17T05:21:40.098000Z FATAL We have a situation here.
root@jessie-db:/home/sysman#
That is the only file in directory /tmp/pgloader.
tapio-lehtonen
on 17 Jun 2015
Thanks. Still not quite enough information tho. Is it possible for you to hand me the sqlite file (e.g. by private email) for me to debug?
dimitri
on 17 Jun 2015
OK, sending.
tapio-lehtonen
on 17 Jun 2015
Thanks, here's what I get with current version of pgloader. Can you try building the current version and trying again?
2015-06-17T16:55:38.003000+02:00 NOTICE Starting pgloader, log system is ready.
2015-06-17T16:55:38.000000+02:00 NOTICE Starting pgloader, log system is ready.
2015-06-17T16:55:38.008000+02:00 NOTICE DROP then CREATE TABLES
2015-06-17T16:55:38.008000+02:00 WARNING Postgres warning: table "product" does not exist, skipping
2015-06-17T16:55:38.008000+02:00 WARNING Postgres warning: table "bank_account" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 WARNING Postgres warning: table "customer" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 WARNING Postgres warning: table "invoice" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 WARNING Postgres warning: table "invoice_item" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 WARNING Postgres warning: table "payment" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 WARNING Postgres warning: table "print_script" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 WARNING Postgres warning: table "settings" does not exist, skipping
2015-06-17T16:55:38.009000+02:00 NOTICE COPY product
2015-06-17T16:55:38.210000+02:00 ERROR Database error 22P02: invalid input syntax for integer: "false"
CONTEXT: COPY product, line 1, column vat_excluded: "false"
2015-06-17T16:55:38.210000+02:00 ERROR Database error 22P02: invalid input syntax for integer: "true"
CONTEXT: COPY product, line 1, column vat_excluded: "true"
2015-06-17T16:55:38.210000+02:00 NOTICE COPY bank_account
2015-06-17T16:55:38.211000+02:00 NOTICE COPY customer
2015-06-17T16:55:38.211000+02:00 NOTICE COPY invoice
2015-06-17T16:55:38.211000+02:00 NOTICE CREATE INDEX invoice_number_idx ON invoice (number)
2015-06-17T16:55:38.211000+02:00 NOTICE CREATE INDEX invoice_date_idx ON invoice (date)
2015-06-17T16:55:38.211000+02:00 NOTICE CREATE INDEX invoice_due_date_idx ON invoice (due_date)
2015-06-17T16:55:38.211000+02:00 NOTICE CREATE INDEX invoice_balance_idx ON invoice (balance)
2015-06-17T16:55:38.211000+02:00 NOTICE COPY invoice_item
2015-06-17T16:55:38.211000+02:00 ERROR Database error 22P02: invalid input syntax for integer: "true"
CONTEXT: COPY invoice_item, line 1, column vat_excluded: "true"
2015-06-17T16:55:38.212000+02:00 NOTICE CREATE INDEX invoice_item_invoice_idx ON invoice_item (invoice_id)
2015-06-17T16:55:38.212000+02:00 NOTICE COPY payment
2015-06-17T16:55:38.212000+02:00 NOTICE CREATE INDEX payment_invoice_idx ON payment (invoice_id)
2015-06-17T16:55:38.212000+02:00 NOTICE CREATE INDEX payment_bank_account_idx ON payment (bank_account_id)
2015-06-17T16:55:38.212000+02:00 NOTICE COPY print_script
2015-06-17T16:55:38.212000+02:00 NOTICE COPY settings
table name read imported errors time
---------------------- --------- --------- --------- --------------
fetch 0 0 0 0.000s
fetch meta data 11 11 0 0.005s
create, truncate 0 0 0 0.067s
---------------------- --------- --------- --------- --------------
product 2 0 2 0.200s
bank_account 0 0 0 0.010s
customer 1 1 0 0.012s
invoice 1 1 0 0.011s
invoice_item 1 0 1 0.007s
payment 0 0 0 0.006s
print_script 0 0 0 0.006s
settings 1 1 0 0.010s
index build completion 0 0 0 0.135s
---------------------- --------- --------- --------- --------------
Create Indexes 7 7 0 0.033s
Reset Sequences 0 0 0 0.048s
---------------------- --------- --------- --------- --------------
Total streaming time 6 3 3 0.517s
2015-06-17T16:55:38.413000+02:00 NOTICE Reset sequences
NIL
I get same errors with the .deb file from http://pgloader.io/download.html, the pgloader_3.2.0+dfsg-1_amd64.deb.
Trying to compile from cloned git repository, I get errors:
[package pgloader.parser].........................
[package pgloader]................................
..................................................
..................................................
...;
; caught ERROR:
; READ error during COMPILE-FILE:
;
; Symbol "*EXTERNAL-FORMAT*" not found in the DB3 package.
;
; Line: 37, Column: 32, File-Position: 1337
;
; Stream: #<SB-SYS:FD-STREAM
; for "file /home/sysman/src/debian/pgloader/src/sources/db3/db3.lisp"
; {1004193FD3}>
debugger invoked on a UIOP/LISP-BUILD:COMPILE-FILE-ERROR in thread
#<THREAD "main thread" RUNNING {10039CEE63}>:
COMPILE-FILE-ERROR while
compiling #<CL-SOURCE-FILE "pgloader" "src" "sources" "db3" "db3">
. . .
; compilation unit aborted
; caught 3 fatal ERROR conditions
; caught 1 ERROR condition
Tried the compile with command make pgloader.
tapio-lehtonen
on 19 Jun 2015
The cl-db3 package in the debian repository is too old for this more recent version of pgloader. Please try compiling from sources instead.
dimitri
on 19 Jun 2015
I thought I was compiling from sources?
tapio-lehtonen
on 19 Jun 2015
Oh. Waking up. Get back to you later ;-)
dimitri
on 19 Jun 2015
Can you try to make clean then make again and paste the build logs if they still fail?
dimitri
on 24 Jun 2015
sysman@jessie-db:~/src/debian/pgloader$ pwd
/home/sysman/src/debian/pgloader
sysman@jessie-db:~/src/debian/pgloader$ ls -lh
yhteensä 252K
-rw-r--r-- 1 sysman sysman 371 kesä 19 11:10 bootstrap-centos7.sh
-rw-r--r-- 1 sysman sysman 831 kesä 19 11:10 bootstrap-centos.sh
-rw-r--r-- 1 sysman sysman 1,9K kesä 19 11:10 bootstrap-debian.sh
drwxr-xr-x 4 sysman sysman 4,0K kesä 19 11:38 build
drwxr-xr-x 2 sysman sysman 4,0K kesä 19 11:10 conf
drwxr-xr-x 3 sysman sysman 4,0K kesä 19 11:10 debian
-rw-r--r-- 1 sysman sysman 4,5K kesä 19 11:10 INSTALL.md
-rw-r--r-- 1 sysman sysman 7,0K kesä 19 11:10 Makefile
-rw-r--r-- 1 sysman sysman 83K kesä 19 11:10 pgloader.1
-rw-r--r-- 1 sysman sysman 79K kesä 19 11:10 pgloader.1.md
-rw-r--r-- 1 sysman sysman 8,3K kesä 19 11:10 pgloader.asd
-rwxr-xr-x 1 sysman sysman 642 kesä 19 11:10 pgloader.lisp
-rw-r--r-- 1 sysman sysman 2,2K kesä 19 11:10 pgloader.spec
-rw-r--r-- 1 sysman sysman 5,5K kesä 19 11:10 README.md
drwxr-xr-x 7 sysman sysman 4,0K kesä 19 11:10 src
drwxr-xr-x 6 sysman sysman 4,0K kesä 19 11:10 test
-rw-r--r-- 1 sysman sysman 2,1K kesä 19 11:10 TODO.md
-rw-r--r-- 1 sysman sysman 1,6K kesä 19 11:10 Vagrantfile
drwxr-xr-x 6 sysman sysman 4,0K kesä 19 11:10 web
sysman@jessie-db:~/src/debian/pgloader$
sysman@jessie-db:~/src/debian/pgloader$ script
Script started, file is typescript
sysman@jessie-db:~/src/debian/pgloader$ make
mkdir -p build
curl -o build/quicklisp.lisp http://beta.quicklisp.org/quicklisp.lisp
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 57144 100 57144 0 0 35724 0 0:00:01 0:00:01 --:--:-- 35737
sbcl --no-sysinit --no-userinit --load build/quicklisp.lisp \
--load src/getenv.lisp \
--eval '(quicklisp-quickstart:install :path "build/quicklisp" :proxy (getenv "http_proxy"))' \
--eval '(quit)'
This is SBCL 1.2.4.debian, an implementation of ANSI Common Lisp.
More information about SBCL is available at <http://www.sbcl.org/>.
SBCL is free software, provided as is, with absolutely no warranty.
It is mostly in the public domain; some portions are provided under
BSD-style licenses. See the CREDITS and COPYING files in the
distribution for more information.
==== quicklisp quickstart 2015-01-28 loaded ====
To continue with installation, evaluate: (quicklisp-quickstart:install)
For installation options, evaluate: (quicklisp-quickstart:help)
WARNING: Making quicklisp part of the install pathname directory
; Fetching #<URL "http://beta.quicklisp.org/client/quicklisp.sexp">
; 0.82KB
==================================================
838 bytes in 0.01 seconds (136.39KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/client/2015-06-16/quicklisp.tar">
; 240.00KB
==================================================
245,760 bytes in 0.64 seconds (374.41KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/client/2014-01-28/setup.lisp">
; 4.96KB
==================================================
5,078 bytes in 0.00 seconds (0.00KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/asdf/2.26/asdf.lisp">
; 194.07KB
==================================================
198,729 bytes in 0.22 seconds (902.66KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/dist/quicklisp.txt">
; 0.40KB
==================================================
408 bytes in 0.01 seconds (66.41KB/sec)
Installing dist "quicklisp" version "2015-06-08".
; Fetching #<URL "http://beta.quicklisp.org/dist/quicklisp/2015-06-08/releases.txt">
; 300.87KB
==================================================
308,088 bytes in 0.11 seconds (2710.52KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/dist/quicklisp/2015-06-08/systems.txt">
; 220.82KB
==================================================
226,119 bytes in 0.26 seconds (852.58KB/sec)
==== quicklisp installed ====
To load a system, use: (ql:quickload "system-name")
To find systems, use: (ql:system-apropos "term")
To load Quicklisp every time you start Lisp, use: (ql:add-to-init-file)
For more information, see http://www.quicklisp.org/beta/
git clone https://github.com/dimitri/cl-ixf.git build/quicklisp/local-projects/cl-ixf
Cloning into 'build/quicklisp/local-projects/cl-ixf'...
remote: Counting objects: 51, done.
remote: Total 51 (delta 0), reused 0 (delta 0), pack-reused 51
Unpacking objects: 100% (51/51), done.
Checking connectivity... done.
git clone https://github.com/dimitri/cl-db3.git build/quicklisp/local-projects/cl-db3
Cloning into 'build/quicklisp/local-projects/cl-db3'...
remote: Counting objects: 10, done.
remote: Total 10 (delta 0), reused 0 (delta 0), pack-reused 10
Unpacking objects: 100% (10/10), done.
Checking connectivity... done.
git clone https://github.com/AccelerationNet/cl-csv.git build/quicklisp/local-projects/cl-csv
Cloning into 'build/quicklisp/local-projects/cl-csv'...
remote: Counting objects: 487, done.
remote: Total 487 (delta 0), reused 0 (delta 0), pack-reused 487
Receiving objects: 100% (487/487), 150.30 KiB | 0 bytes/s, done.
Resolving deltas: 100% (309/309), done.
Checking connectivity... done.
git clone https://github.com/qitab/qmynd.git build/quicklisp/local-projects/qmynd
Cloning into 'build/quicklisp/local-projects/qmynd'...
remote: Counting objects: 1450, done.
remote: Total 1450 (delta 0), reused 0 (delta 0), pack-reused 1450
Receiving objects: 100% (1450/1450), 270.23 KiB | 0 bytes/s, done.
Resolving deltas: 100% (861/861), done.
Checking connectivity... done.
git clone -b wip-better-errors https://github.com/scymtym/esrap.git build/quicklisp/local-projects/esrap
Cloning into 'build/quicklisp/local-projects/esrap'...
remote: Counting objects: 643, done.
remote: Total 643 (delta 0), reused 0 (delta 0), pack-reused 643
Receiving objects: 100% (643/643), 337.37 KiB | 0 bytes/s, done.
Resolving deltas: 100% (396/396), done.
Checking connectivity... done.
sbcl --no-sysinit --no-userinit --load build/quicklisp/setup.lisp \
--eval '(push "/home/sysman/src/debian/pgloader/" asdf:*central-registry*)' \
--eval '(ql:quickload "pgloader")' \
--eval '(quit)'
This is SBCL 1.2.4.debian, an implementation of ANSI Common Lisp.
More information about SBCL is available at <http://www.sbcl.org/>.
SBCL is free software, provided as is, with absolutely no warranty.
It is mostly in the public domain; some portions are provided under
BSD-style licenses. See the CREDITS and COPYING files in the
distribution for more information.
To load "pgloader":
Load 1 ASDF system:
pgloader
; Loading "pgloader"
.....To load "mssql":
Load 3 ASDF systems:
cffi iterate parse-number
Install 2 Quicklisp releases:
cl-mssql garbage-pools
; Fetching #<URL "http://beta.quicklisp.org/archive/garbage-pools/2013-07-20/garbage-pools-20130720-git.tgz">
; 43.17KB
==================================================
44,210 bytes in 0.03 seconds (1660.53KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/archive/cl-mssql/2013-10-03/cl-mssql-20131003-git.tgz">
; 15.04KB
==================================================
15,398 bytes in 0.11 seconds (141.86KB/sec)
; Loading "mssql"
..................................................
[package garbage-pools]...........................
[package mssql].......
; Loading "pgloader"
To load "uuid":
Load 1 ASDF system:
ironclad
Install 2 Quicklisp releases:
trivial-utf-8 uuid
; Fetching #<URL "http://beta.quicklisp.org/archive/trivial-utf-8/2011-10-01/trivial-utf-8-20111001-darcs.tgz">
; 5.91KB
==================================================
6,055 bytes in 0.00 seconds (5913.09KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/archive/uuid/2013-08-13/uuid-20130813-git.tgz">
; 21.71KB
==================================================
22,226 bytes in 0.02 seconds (1276.77KB/sec)
; Loading "uuid"
..................................................
[package trivial-utf-8]...........................
[package uuid]..
; Loading "pgloader"
To load "quri":
Load 3 ASDF systems:
alexandria babel split-sequence
Install 2 Quicklisp releases:
cl-utilities quri
; Fetching #<URL "http://beta.quicklisp.org/archive/cl-utilities/2010-10-06/cl-utilities-1.2.4.tgz">
; 22.46KB
==================================================
22,998 bytes in 0.02 seconds (1403.69KB/sec)
; Fetching #<URL "http://beta.quicklisp.org/archive/quri/2015-06-08/quri-20150608-git.tgz">
; 65.27KB
==================================================
66,836 bytes in 0.08 seconds (777.02KB/sec)
; Loading "quri"
[package cl-utilities]............................
[package quri.port]...............................
[package quri.uri]................................
[package quri.encode].............................
[package quri.error]..............................
[package quri.util]...............................
[package quri.decode].............................
[package quri.uri.ftp]............................
[package quri.uri.http]...........................
[package quri.uri.ldap]...........................
[package quri.uri.file]...........................
[package quri.etld]...............................
[package quri.domain].............................
[package quri.parser].............................
[package quri].
; Loading "pgloader"
..................................................
[package pgloader.params].........................
[package pgloader.transforms].....................
[package pgloader.logs]...........................
[package pgloader.monitor]........................
[package pgloader.utils]..........................
[package pgloader.archive]........................
[package pgloader.parse-date].....................
[package pgloader.connection].....................
[package pgloader.sources]........................
[package pgloader.pgsql]..........................
[package pgloader.queue]..........................
[package pgloader.ini]............................
[package pgloader.sql]............................
[package pgloader.csv]............................
[package pgloader.fixed]..........................
[package pgloader.copy]...........................
[package pgloader.ixf]............................
[package pgloader.db3]............................
[package pgloader.mysql]..........................
[package pgloader.sqlite].........................
[package pgloader.mssql]..........................
[package pgloader.syslog].........................
[package pgloader.parser].........................
[package pgloader]................................
..................................................
..................................................
...;
; caught ERROR:
; READ error during COMPILE-FILE:
;
; Symbol "*EXTERNAL-FORMAT*" not found in the DB3 package.
;
; Line: 37, Column: 32, File-Position: 1337
;
; Stream: #<SB-SYS:FD-STREAM
; for "file /home/sysman/src/debian/pgloader/src/sources/db3/db3.lisp"
; {100521EC93}>
debugger invoked on a UIOP/LISP-BUILD:COMPILE-FILE-ERROR in thread
#<THREAD "main thread" RUNNING {10039CEE63}>:
COMPILE-FILE-ERROR while
compiling #<CL-SOURCE-FILE "pgloader" "src" "sources" "db3" "db3">
Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.
restarts (invokable by number or by possibly-abbreviated name):
0: [RETRY ] Retry
compiling #<CL-SOURCE-FILE "pgloader" "src" "sources" "db3" "db3">.
1: [ACCEPT ] Continue, treating
compiling #<CL-SOURCE-FILE "pgloader" "src" "sources" "db3" "db3">
as having been successful.
2: Retry ASDF operation.
3: [CLEAR-CONFIGURATION-AND-RETRY] Retry ASDF operation after resetting the
configuration.
4: [ABORT ] Give up on "pgloader"
5: [CONTINUE ] Ignore runtime option --eval "(ql:quickload \"pgloader\")".
6: Skip rest of --eval and --load options.
7: Skip to toplevel READ/EVAL/PRINT loop.
8: [EXIT ] Exit SBCL (calling #'EXIT, killing the process).
(UIOP/LISP-BUILD:CHECK-LISP-COMPILE-RESULTS NIL T T "~/asdf-action::format-action/" ((#<ASDF/LISP-ACTION:COMPILE-OP > . #<ASDF/LISP-ACTION:CL-SOURCE-FILE "pgloader" "src" "sources" "db3" "db3">)))
0]
>>>>>>>>>>>>>> Here I Pressed Control-D
compilation unit aborted
; caught 3 fatal ERROR conditions
; caught 1 ERROR condition
touch build/libs.stamp
sbcl --no-sysinit --no-userinit --load build/quicklisp/setup.lisp \
--eval '(ql:write-asdf-manifest-file "build/manifest.ql")' \
--eval '(quit)'
This is SBCL 1.2.4.debian, an implementation of ANSI Common Lisp.
More information about SBCL is available at <http://www.sbcl.org/>.
SBCL is free software, provided as is, with absolutely no warranty.
It is mostly in the public domain; some portions are provided under
BSD-style licenses. See the CREDITS and COPYING files in the
distribution for more information.
mkdir -p build/bin
sbcl --no-sysinit --no-userinit --load build/quicklisp/setup.lisp \
--eval '(ql:quickload "buildapp")' \
--eval '(buildapp:build-buildapp "build/bin/buildapp.sbcl")' \
--eval '(quit)'
This is SBCL 1.2.4.debian, an implementation of ANSI Common Lisp.
More information about SBCL is available at <http://www.sbcl.org/>.
SBCL is free software, provided as is, with absolutely no warranty.
It is mostly in the public domain; some portions are provided under
BSD-style licenses. See the CREDITS and COPYING files in the
distribution for more information.
To load "buildapp":
Install 1 Quicklisp release:
buildapp
; Fetching #<URL "http://beta.quicklisp.org/archive/buildapp/2015-04-07/buildapp-1.5.4.tgz">
; 15.98KB
==================================================
16,367 bytes in 0.05 seconds (332.99KB/sec)
; Loading "buildapp"
[package buildapp]....
;; loading system "buildapp"
[undoing binding stack and other enclosing state... done]
[saving current Lisp image into /home/sysman/src/debian/pgloader/build/bin/buildapp.sbcl:
writing 5680 bytes from the read-only space at 0x20000000
writing 3120 bytes from the static space at 0x20100000
writing 66846720 bytes from the dynamic space at 0x1000000000
done]
mkdir -p build/bin
build/bin/buildapp.sbcl --logfile /tmp/build.log \
--require sb-posix --require sb-bsd-sockets --require sb-rotate-byte \
--sbcl sbcl \
--asdf-path . \
--asdf-tree build/quicklisp/local-projects \
--manifest-file build/manifest.ql \
--asdf-tree build/quicklisp/dists \
--asdf-path . \
--load-system pgloader \
--load src/hooks.lisp \
--entry pgloader:main \
--dynamic-space-size 4096 \
--compress-core \
--output build/bin/pgloader.tmp
;; loading system "pgloader"
;; loading file #P"/home/sysman/src/debian/pgloader/src/hooks.lisp"
[undoing binding stack and other enclosing state... done]
[saving current Lisp image into build/bin/pgloader.tmp:
writing 5680 bytes from the read-only space at 0x20000000
compressed 32768 bytes into 1962 at level -1
writing 4656 bytes from the static space at 0x20100000
compressed 32768 bytes into 1155 at level -1
writing 143294464 bytes from the dynamic space at 0x1000000000
compressed 143294464 bytes into 25628907 at level -1
done]
# that's ugly, but necessary when building on Windows :(
mv build/bin/pgloader.tmp build/bin/pgloader
sysman@jessie-db:~/src/debian/pgloader$
sysman@jessie-db:~/src/debian/pgloader$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 8.1 (jessie)
Release: 8.1
Codename: jessie
sysman@jessie-db:~/src/debian/pgloader$ apt-cache policy cl-db3
cl-db3:
Asennettu: 20131111-1
Ehdokas: 20131111-1
Versiotaulukko:
*** 20131111-1 0
500 http://approx.koti.taleman.fi:9999/debian/ jessie/main amd64 Packages
100 /var/lib/dpkg/status
Please apt-get remove --purge cl-db3 (and others) when building from sources, the debian provided asdf will prefer loading the system provided cl-db3 rather than the one downloaded in the make step, apparently. I wonder if I can do something about that from the Makefile itself...
dimitri
on 24 Jun 2015
Seems to remove lots of cl-* packages, or actually after apt-get remove cl-db3 then apt-get autoremove removes 57 packages.
Did make clean again. Then make, and it seems to take quite a bit longer to compile.
Did not see any compile errors this time. GG.
Now this freshly compiled pgloader seems to import stuff and push it to Postgres. But does report ERROR somethings. But this is much better, now I can see what ended up in Postgres and probably fix those things that are wrong.
Thanks.
tapio-lehtonen
on 24 Jun 2015
Cool, I'm closing the issue here. Feel free to re-open in case something related happens, or to open another issue with something more relevant, depending on the case. Have fun with PostgreSQL ;-)
dimitri
on 24 Jun 2015
On Wed, Jun 24, 2015 at 12:30:35PM -0700, Dimitri Fontaine wrote:
Cool, I'm closing the issue here. Feel free to re-open in case
something related happens, or to open another issue with something
more relevant, depending on the case. Have fun with PostgreSQL ;-)
The issue is closed, OK. But it seems to me there is a bug in jessie
pgloader, since it could not handle the command file. It just made
strange errors that seemed like the syntax was wrong.
Reply to this email directly or view it on GitHub:
https://github.com/dimitri/pgloader/issues/248#issuecomment-114987336
Tapio Lehtonen
tapio.[email protected]
http://www.iki.fi/tapio.lehtonen
tapio-lehtonen
on 25 Jun 2015
There's an extra slash in the beginning of the PostgreSQL connection string, when removed should work just fine:
LOAD DATABASE
FROM sqlite:///home/sysman/laskutus-ilmanlogoa.sqlite
INTO postgresql://postgres@localhost/satatuulilaskutus;
This issue appears to be years old, but I'm also getting this error with the latest pgloader version:
$ pgloader --version
pgloader version "3.3.2"
compiled with SBCL 1.3.1.debian
$ pgloader --debug --verbose db.sqlite3.20170314_212518
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2017-05-09T06:08:23.017000Z NOTICE Starting pgloader, log system is ready.
2017-05-09T06:08:23.033000Z INFO Starting monitor
2017-05-09T06:08:23.038000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2017-05-09T06:08:23.039000Z LOG Data errors in '/tmp/pgloader/'
2017-05-09T06:08:23.039000Z LOG Parsing commands from file #P"/home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518"
2017-05-09T06:08:23.039000Z FATAL Date/time: 2017-05-08-23:08An unhandled error condition has been signalled: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}>: the octet sequence #(128) cannot be decoded.
Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {1009074683}>
0: ((LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX))
1: (SB-IMPL::CALL-WITH-SANE-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {10090BFECB}>)
2: (SB-IMPL::%WITH-STANDARD-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {10090BFE9B}>)
3: (PRINT-BACKTRACE :STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {10090AF3E3}> :START 0 :FROM :DEBUGGER-FRAME :COUNT 4611686018427387903 :PRINT-THREAD T :PRINT-FRAME-SOURCE NIL :METHOD-FRAME-STYLE NIL)
4: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {10090AF3E3}>)
5: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<SB-INT:STREAM-DECODING-ERROR {10090AF273}> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL)
6: ((FLET #:H0 :IN PGLOADER::MAIN) #<SB-INT:STREAM-DECODING-ERROR {10090AF273}>)
7: (SIGNAL #<SB-INT:STREAM-DECODING-ERROR {10090AF273}>)
8: (ERROR #<SB-INT:STREAM-DECODING-ERROR {10090AF273}>)
9: (SB-IMPL::STREAM-DECODING-ERROR-AND-HANDLE #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> 1)
10: (SB-IMPL::FD-STREAM-READ-N-CHARACTERS/UTF-8 #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> "SQLite format 3@ oSQLite format 3@ o" 4 508 NIL)
11: (SB-INT:FAST-READ-CHAR-REFILL #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> NIL)
12: (SB-IMPL::ANSI-STREAM-READ-STRING-FROM-FRC-BUFFER "SQLite format 3@ o" #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> 0 NIL)
13: (SB-IMPL::ANSI-STREAM-READ-SEQUENCE "SQLite format 3@ o" #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> 0 NIL)
14: (READ-SEQUENCE "SQLite format 3@ o" #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> :START 0 :END NIL)
15: (ALEXANDRIA.0.DEV:READ-STREAM-CONTENT-INTO-STRING #<SB-SYS:FD-STREAM for "file /home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" {10090AA393}> :BUFFER-SIZE 4096)
16: (ALEXANDRIA.0.DEV:READ-FILE-INTO-STRING #P"/home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" :BUFFER-SIZE 4096 :EXTERNAL-FORMAT NIL)
17: (PGLOADER.PARSER:PARSE-COMMANDS-FROM-FILE #P"/home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518")
18: (PGLOADER:RUN-COMMANDS #P"/home/devuser/sqlite_to_pg_migration/db.sqlite3.20170314_212518" :START-LOGGER NIL :FLUSH-SUMMARY T :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)
19: (PGLOADER::PROCESS-COMMAND-FILE ("db.sqlite3.20170314_212518") :FLUSH-SUMMARY T)
20: ((FLET #:CLEANUP-FUN-289 :IN PGLOADER::MAIN)) [cleanup]
21: (PGLOADER::MAIN ("pgloader" "--debug" "--verbose" "db.sqlite3.20170314_212518"))
22: ((LAMBDA NIL :IN "/build/pgloader-Jl621G/pgloader-3.3.2+dfsg/dumper-2SKVI5f7.lisp"))
23: ((FLET #:WITHOUT-INTERRUPTS-BODY-82 :IN SAVE-LISP-AND-DIE))
24: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))
Any suggestions?
PaulMest
on 9 May 2017
Yeah, pgloader reads the file name as being a command file, expected as text in utf8. Please use either the --type sqlite command line option or rather the sqlite:// prefix to the file location.
dimitri
on 10 May 2017
👍1
@dimitri I was following the example in your README.md.
For example, for a full migration from SQLite:
$ createdb newdb
$ pgloader ./test/sqlite/sqlite.db postgresql:///newdb
Perhaps you could update it with the recommended syntax?
PaulMest
on 10 May 2017
You are not following the example from the README, because you are giving a single argument to the command line, which is interpreted as being a command file. Please use the 2 arguments command line where the first argument is a source file and the second one a target database URI.
$ pgloader --help
./build/bin/pgloader [ option ... ] command-file ...
./build/bin/pgloader [ option ... ] SOURCE TARGET
...
Oh, I see. Sorry, I pasted the wrong example... when I ran the official command, it seemed like there was nothing useful in the output...
# This doesn't work
$ pgloader --debug --verbose db.sqlite3.20170509_154401 postgres://user:[email protected]/database
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2017-05-11T22:18:15.023000Z NOTICE Starting pgloader, log system is ready.
2017-05-11T22:18:15.036000Z INFO Starting monitor
2017-05-11T22:18:15.042000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2017-05-11T22:18:15.043000Z LOG Data errors in '/tmp/pgloader/'
2017-05-11T22:18:15.043000Z INFO SOURCE: "db.sqlite3.20170509_154401"
2017-05-11T22:18:15.043000Z INFO TARGET: "postgres://user:[email protected]/database"
2017-05-11T22:18:15.044000Z INFO Stopping monitor
However, when I use --type sqlite, it seems to work. But since the database takes hours to import, I didn't run it all the way through.
# This works!
$ pgloader --debug --verbose --type sqlite db.sqlite3.20170509_154401 postgres://user:[email protected]/database
👍2
Yes, see https://github.com/dimitri/pgloader/blob/master/src/parsers/command-parser.lisp#L135 where we define some filename pattern matching against potential source types we expect. Your filename doesn't match any known pattern, so you have to spell out --type.
With just pushed fix, it now looks like this:
~/dev/pgloader ./build/bin/pgloader /tmp/db.sqlite3.20170509_154401 postgresql:///newdb
2017-05-12T13:07:42.021000+02:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log'
2017-05-12T13:07:42.024000+02:00 LOG Data errors in '/private/tmp/pgloader/'
2017-05-12T13:07:42.024000+02:00 FATAL Failed to parse "/tmp/db.sqlite3.20170509_154401" as a source URI.
2017-05-12T13:07:42.024000+02:00 LOG You might need to use --type.
👍1
Was this page helpful?
0 / 5 - 0 ratings
Related issues
toobulkeh
·
3Comments
0xbzho
·
3Comments
richardkmichael
·
6Comments
fabswt
·
5Comments
aa8y
·
7Comments
Most helpful comment
Oh, I see. Sorry, I pasted the wrong example... when I ran the official command, it seemed like there was nothing useful in the output...
However, when I use
--type sqlite, it seems to work. But since the database takes hours to import, I didn't run it all the way through.