Various errors on Windows

[shwivel]

I am having trouble getting any basic load to work using the Windows program (pgloader-3.1.ed853a7.exe) provided at http://pgloader.io/

I go over the details in a question I asked recently at stack overflow:
http://stackoverflow.com/questions/28130419/pgloader-error-sqlite3-dll-but-am-not-using-sqlite

Could you please take a look when you have time and let me know what you think?

Thanks

[dimitri]

So the problem you have is about the dll dependencies of the windows binary I prepared. The way the pgloader code now loads the librairies have changed and it might be that I could produce a better windows executable file, going to try and see.

Meanwhile, as I know nothing about working on windows, maybe you'd want to setup a build environment for pgloader? It must be noted that the current simplest way out for you here might be to just install an linux (ubuntu, say) Virtual Machine and operate pgloader from there.

[shwivel]

Thanks for the quick response. Unfortunately I know very little about using linux and would really like to stick to Windows if you're willing and able to work that out. I'll download their technical preview and put that and a sample database on a VM for you to test with, I don't think there would be licensing issues with that as they've given it out for free for testing.

[dimitri]

Found a windows VM where I could still build pgloader, so let's have a try. Ironically I don't have a freetds.dll lying around nor did I find a quick and easy way to build one, so I removed the MS SQL support from the windows build.

Please try the version from http://pgloader.io/files/pgloader.95961a4.exe

And here's the diff I came up with to have a working binary (quickly tested, even):

diff --git a/pgloader.asd b/pgloader.asd
index 402c91c..3f9244f 100644
--- a/pgloader.asd
+++ b/pgloader.asd
@@ -31,7 +31,7 @@
         #:trivial-backtrace    ; For --debug cli usage
                  #:cl-markdown          ; To produce the website
                  #:metabang-bind        ; the bind macro
-                 #:mssql                ; M$ SQL connectivity
+                 ;#:mssql                ; M$ SQL connectivity
                  #:uuid             ; Transforming MS SQL unique identifiers
         )
     :components
@@ -44,7 +44,8 @@
                (:module "monkey"
                         :components
                         ((:file "bind")
-                         (:file "mssql")))
+                         ;; (:file "mssql")
+                         ))

                (:module "utils"
                         :depends-on ("package" "params")
@@ -158,15 +159,15 @@
                                           :depends-on ("sqlite-cast-rules"
                                                        "sqlite-schema"))))

-                         (:module "mssql"
-                                  :depends-on ("common")
-                                  :components
-                                  ((:file "mssql-cast-rules")
-                                   (:file "mssql-schema"
-                                          :depends-on ("mssql-cast-rules"))
-                                   (:file "mssql"
-                                          :depends-on ("mssql-cast-rules"
-                                                       "mssql-schema"))))
+                         ;; (:module "mssql"
+                         ;;          :depends-on ("common")
+                         ;;          :components
+                         ;;          ((:file "mssql-cast-rules")
+                         ;;           (:file "mssql-schema"
+                         ;;                  :depends-on ("mssql-cast-rules"))
+                         ;;           (:file "mssql"
+                         ;;                  :depends-on ("mssql-cast-rules"
+                         ;;                               "mssql-schema"))))

                          (:module "mysql"
                                   :depends-on ("common")
diff --git a/src/hooks.lisp b/src/hooks.lisp
index 97ec7bc..9f11596 100644
--- a/src/hooks.lisp
+++ b/src/hooks.lisp
@@ -15,7 +15,8 @@
   "Close Foreign libs in use by pgloader at application save time."
   (let (#+sbcl (sb-ext:*muffled-warnings* 'style-warning))
     (mapc #'cffi:close-foreign-library '(cl+ssl::libssl
-                                         mssql::sybdb))))
+                                         ;; mssql::sybdb
+                                         ))))

 (defun open-foreign-libs ()
   "Open Foreign libs in use by pgloader at application start time."
diff --git a/src/parsers/command-mssql.lisp b/src/parsers/command-mssql.lisp
index 622e2c5..45b35dc 100644
--- a/src/parsers/command-mssql.lisp
+++ b/src/parsers/command-mssql.lisp
@@ -117,8 +117,8 @@
                                            (excluding))
   `(lambda ()
      ;; now is the time to load the CFFI lib we need (freetds)
-     (let (#+sbcl(sb-ext:*muffled-warnings* 'style-warning))
-       (cffi:load-foreign-library 'mssql::sybdb))
+     ;; (let (#+sbcl(sb-ext:*muffled-warnings* 'style-warning))
+     ;;   (cffi:load-foreign-library 'mssql::sybdb))

      (let* ((state-before  (pgloader.utils:make-pgstate))
             (*state*       (or *state* (pgloader.utils:make-pgstate)))

[shwivel]

Thanks, I get the same error with the new .exe though. With the libssl32.dll and sqlite3.dll files added my error message has become "The procedure entry point EVP_md2 could not be located in the dynamic link ;ibrary 'my_path/'libssl32.dll." as shown here https://shwivel.in/pgloader/pgloader.html

I made that video when running your initial exe but the same error occurs with the last one.

I sent a link to my full VM to the email address you have on your github.

[shwivel]

I think the error has to do with the dll files that are for whatever reason not present in the postgresql 'portable' app at http://sourceforge.net/projects/postgresqlportable/ and is unrelated to your exe. I just tested your new exe on a full postgresql 9.4 install and don't receive any dll related errors. I did get the sqlite3.dll error but simply putting it in the bin folder resolved that.

I think the error I am getting now has to do with the syntax of my control file. Could you please review the below:

Table is:

create table fish (fish_id int, fish_descr varchar(25));

Control file is:

LOAD CSV  
   FROM 'C:\Users\ShWiVeL\Desktop\more_fish.txt'
        HAVING FIELDS  
        (  
           fish_id, fish_descr
        )  
   INTO postgresql://postgres:thep@localhost:5432/test?fish
        TARGET COLUMNS
        (  
           fish_id, fish_descr
        )  
   WITH truncate,  
        skip header = 0,  
        fields terminated by '|'

    SET work_mem to '32 MB', maintenance_work_mem to '64 MB';

Windows .bat file to run the control file is:

cd C:\Program Files\PostgreSQL\9.4\bin
pgloader C:\Users\ShWiVeL\Desktop\commands.load

My more_fish.txt file that I'm trying to import is:

2|Sooodles
3|Poodles

This is the error I get:

Thanks

[shwivel]

I fixed it by adding the line separator as shown below, will have to do some more reading of your docs as to writing out the control files but looks like I am all set to go.

I appreciate your help, this will be very useful.

LOAD CSV --
      FROM 'C:\Users\ShWiVeL\Desktop\more_fish.txt' (fish_id, fish_descr) --
      INTO postgresql://postgres:thep@localhost:5432/test?fish (fish_id, fish_descr) --
 --
      WITH truncate, --
           skip header = 0, --
           fields terminated by '|' --
 --
       SET client_encoding to 'latin1', --
           work_mem to '12MB', --
           standard_conforming_strings to 'on';

[dimitri]

Nice to see the issue fixed. FWIW I tried parsing your command without the line ending comment tricks and it worked for me here.

[moshir]

I have the same issue, running on windows 7, 64 bits.
I have a standard postgres (not portable) running on the same host, working fine.

debugger invoked on a SIMPLE-ERROR in thread
#<THREAD "main thread" RUNNING {26922321}>:
  Error opening shared object "sqlite3.dll":
 Le module spécifié est introuvable.

Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.

restarts (invokable by number or by possibly-abbreviated name):
  0: [CONTINUE       ] Skip this shared object and continue.
  1: [RETRY          ] Retry loading this shared object.
  2: [CHANGE-PATHNAME] Specify a different pathname to load the shared object fr
om.

(SB-SYS:DLOPEN-OR-LOSE #S(SB-ALIEN::SHARED-OBJECT :PATHNAME #P"sqlite3.dll" :NAM
ESTRING "sqlite3.dll" :HANDLE NIL :DONT-SAVE NIL))

I previously added the libeay32.dll file, and now its sqlite3.dll missing.

Is there something i'm missing to use pgloader on win64 ?

[dimitri]

You need to have the sqlite dll with the current executable, I will need to either make that dependency optional like the others or provide for it in the next release.

[moshir]

Ok, so now libssl32.dll is missing.

debugger invoked on a SIMPLE-ERROR in thread
#<THREAD "main thread" RUNNING {26C882B9}>:
  Problem running initialization hook #<FUNCTION OPEN-FOREIGN-LIBS>:
  Unable to load foreign library (LIBSSL).
  Error opening shared object "libssl32.dll":
 Le module spécifié est introuvable.

Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.

restarts (invokable by number or by possibly-abbreviated name):
  0: [CONTINUE] Skip this initialization hook.

(SB-INT:CALL-HOOKS "initialization" (#<FUNCTION OPEN-FOREIGN-LIBS> SB-BSD-SOCKET
S-INTERNAL::CALL-WSA-STARTUP SB-WIN32::INITIALIZE-CONSOLE-CONTROL-HANDLER) :ON-E
RROR :ERROR)

[dimitri]

Rinse, repeat.The OpenSSL should be only loaded opportunitiscally, at the connection driver's request.

[cssaunders]

I just wanted to update this to provide a brief guide of the problems I encountered, and how they were fixed, for anyone else who needs assistance with the issues in this thread.

This is for using Pgloader, in Windows (my install is Windows 8.1 64-bit), and I was specifically migrating from MySQL to Postgres in my usage.

1. Get the PGLoader.exe which is linked to above on this page from dimitri. http://pgloader.io/files/pgloader.95961a4.exe

2. Put the dll files 'sqlite3.dll' and 'libssl32.dll' into the same folder as the pgloader executable.

3. Make sure to include the line separators "--" in your .load file, as shown by shwivel above.

4. Now, when you run pgloader, you will still see some errors pop up, but worry not. The pop up window will open, complaining about EVP_md2. Just close it. Now, in your command line, enter "0" as your response to continue proceeding with pgloader execution. I had to enter "0" twice.

5. Sit back and watch pgloader work its magic.

[dimitri]

oh wow, thanks for that feedback. We should probably find out what's about that EVP_md2 and avoid pushing users through the SBCL interactive debugger too... Building on windows should be simple enough nowadays if you can provide the freetds dll, or again using the patch above. Any chance you would try building from sources?

[cssaunders]

OK, so I've given building a bit of a shot, but without success. Primarily, I work with databases and scripting languages doing for data analytics, so compiling a Lisp application isn't exactly my strong suit.

I have a relatively new computer here, so I cloned a copy of pgloader, and then made sure I had installed Gnu make, SBCL, and cURL on the machine, since it is a Windows box.

I ran the makefile, though, and it still hits a bit of a snag during compilation. I get the following response:

To load "pgloader":
  Load 1 ASDF system:
    pgloader
; Loading "pgloader"
....................
debugger invoked on a CFFI:LOAD-FOREIGN-LIBRARY-ERROR in thread
#<THREAD "main thread" RUNNING {1002DA7B13}>:
  Unable to load foreign library (LIBSSL).
  Error opening shared object "libssl32.dll":
 The specified module could not be found.

I tried copying the 'libssl32.dll' file to the main pgloader folder (same as the makefile), to the src folder, and to the build folder. Each of the three cases still throws the same error during compilation. Not sure where exactly to go from there.

[dimitri]

I am not versed at all on the windows platform, I would still try copying the ddl into the same folder as where the sbcl binary is to be found, as it is this executable which is trying to load it when compiling...

[cssaunders]

Just to let you know, I gave compilation a shot, trying to fix the libssl32.dll issues, but no matter where I put the file during compilation, I always got an error saying the specified module could not be found. I'm just in over my head on this particular problem...

[mdanciu]

I've also tested pgloader on a windows virtual box (after temporary solving the issues with missing dlls using the above tips), but it is not as fast as I expected, in fact for my table ~ 3 million records it is slower than the standard [copy to] command (7 min vs 4 min). I relay think that parallel batch processing/loading is needed. Any idea of how to increase the performance of the actual pgloader?

One of the scripts used for testing is:

pgloader.95961a4.exe -q --type csv --field "sdate,stime,accnr,modalities,refphyname" --with "batch rows = 25000" --with "batch size = 64MB" --with "batch concurrency = 15" --with "skip header = 1" --with "fields terminated by '|'" c:\pgloader\legacy_stulev.csv postgresql://postgres:pass@localhost:5432/db_test?tablename=legacy.stulev

[dimitri]

pgloader uses the COPY protocol and can not be faster than COPY with the current solution. The only way to beat COPY is to handle more than one COPY process to load a single data source, as you're saying.

pgloader is what to use to improve error handling and data processing, not for improving raw performances of data loading.

[mdanciu]

thanks for the quick response.
"pgloader is what to use to improve error handling and data processing" --that is grate because It happened to me that after several hours or days of loading with copy command to receive an error and than to have to start again the loading process.

"handle more than one COPY process to load a single data source" -- is this on your to do list, do yo have an ETA?

I really need a solution to load big data (ex: 1 billion rows) into postgres and not waiting for days to finish the loading. Do you have a suggestion/tips?

[dimitri]

No ETA yet, I intend to implement single-reader multiple-writers architecture in pgloader at some point. Of course if you're interested about sponsoring the feature, we can work on a proper schedule.

Meanwhile, is it possible for you to somehow split the data in the source?

[mdanciu]

"Of course if you're interested about sponsoring the feature, we can work on a proper schedule." -- I'm not in this position to decide about sponsoring but we will see.

"Meanwhile, is it possible for you to somehow split the data in the source?" -- I think this could be a solution.

[StephenChan]

Just thought I'd share my experience with Windows 7 64-bit, using the 95961a4 pgloader .exe linked early in this thread.

1. Error opening shared object "sqlite3.dll": The specified module could not be found.
   Fixed by getting sqlite3.dll 32-bit from here and putting the file in the same directory as the pgloader .exe.
2. Error opening shared object "libssl32.dll": The specified module could not be found.
   Fixed by getting libssl32.dll from here ("Binaries" download).
3. The procedure entry point EVP_md2 could not be located in the dynamic link library LIBEAY32.dll.
   (This appeared in an error dialog.) Fixed by getting libeay32.dll from the same link as libssl32.dll.
4. Could not parse WHITESPACE. Expected: ...
   Fixed by ensuring that my command file only had \n (line feed) linebreaks, not \r\n (carriage return + line feed). To do this in Notepad++ for example, go to Edit -> EOL Conversion, and pick UNIX/OSX Format.

From there, I was able to run pgloader, porting a relatively simple Django database from MySQL 5.6.12 to PostgreSQL 9.5.1. (The only thing I needed the command file for was WITH quote identifiers.)

I can't comment on building pgloader from source, I'm afraid. I didn't feel that the latest pgloader was necessary for me, since my Windows use case is only for my development machine (not production), and my project's database usage isn't that specialized.

[dimitri]

Thanks for your feedback, I could test with tweaking a test file to have windows-style end-of-lines and sure enough could reproduce (then fix) the bug here. Build-dependencies shouldn't be a big problem even on windows, if you want to see about that.

[maveriCkharsha]

Hello, Can someone give me the link for Windows version of pgloader, so that I can test and use?� I couldn't find in the pgloader.io website.

[dimitri]

I don't build binaries for windows because I don't own any license for any version of the system. You can build from sources, you need some command line tools and either SBCL or CCL for windows. Best option I think is to grab the latest released bundle and build from there. See https://github.com/dimitri/pgloader/releases.