by

Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Andrew Graham-Yooll
    @andrewgy8
    Yeah, thats what I was going to go for if those hooks didnt fire until after the pgloader processes.
    Another issue Im running into is using the type int with extra auto_increment to serial. It does not seem to add that modification to the postgres tables from the sqlite3 tables that have autoincrement.
    Andrew Graham-Yooll
    @andrewgy8
    This is my current load file which doesnt seem to convert auto-incremnt to serial:
    
    LOAD DATABASE
         from ../../data/databases/main_db.sqlite
         into postgresql://postgres:postgres@127.0.0.1:5432/main_db
    
    WITH include drop, 
        create tables, 
        create indexes, 
        reset sequences
    
    CAST type datetime to timestamp drop default drop not null using zero-dates-to-null, 
    type int with extra auto_increment to serial keep typemod keep default keep not null 
    
    SET work_mem to '16MB', 
        maintenance_work_mem to '512 MB';
    Dimitri Fontaine
    @dimitri
    @andrewgy8 please see dimitri/pgloader#556, should be fixed now
    Andrew Graham-Yooll
    @andrewgy8
    Fantastic @dimitri !
    Ill look into the timezone issue. Time is such a strange thing...
    Marcel Hoppe
    @hobbypunk90

    Hello,
    i habe a little problem, i'm using the 3.1 version an a debian machine with this little script:

    LOAD DATABASE
         FROM    mysql://<user>:<password>@localhost/gildamesh_test_db
         INTO postgres:///gildamesh_test
     WITH data only, truncate;

    and i get this very strange error:
    Error on Github

    Dimitri Fontaine
    @dimitri
    3.1 is old now, please upgrade to latest Debian provided package (3.3.2, see https://packages.debian.org/search?keywords=pgloader) or build from current sources ; then try again.
    Patrik Lermon
    @kler
    Is it possible to handle empty dates when converting from dbase files?
    I get the following error: Database error 22007: invalid input syntax for type date: " - - "
    and it seems like casting is not compatible using dbase as source.
    Dimitri Fontaine
    @dimitri
    Is it possible for you to send me the dbase file so that I can debug pgloader with that input?
    Patrik Lermon
    @kler
    Sorry, I can't do that. But basically the date is empty, which I believe is interpreted as the empty string "", while I believe it should be interpreted as null.
    Dimitri Fontaine
    @dimitri
    Can you open an issue on github I'm going to have a look at the situation, I might be able to blind-code a patch for you there, but it would be much easier with a test case
    Dimitri Fontaine
    @dimitri
    ok as I was looking here, I tried something: dimitri/pgloader#589
    Patrik Lermon
    @kler
    I think it's as easy to reproduce to have a dbf database containing dates which are empty (""). I saw you opened and also closed the issue.
    Dimitri Fontaine
    @dimitri
    It sounds easy to reproduce, but how exactly do you generate a dbf file? My guess is that an empty date will not show up the same depending on the tooling used to produce the file, as is often the case... anyway, let me know if the defensive approach I tried solve your issue!
    Patrik Lermon
    @kler
    Hmm... Problem is that I don't program lisp, so I can't really tell.
    And also thx for the effort!
    Dimitri Fontaine
    @dimitri
    Well all you need to do now is to build pgloader from sources, try again, and report success or failure...
    Patrik Lermon
    @kler
    I tested it now, and it seems to have fixed the issue, but I cannot fully test it, because I ran into the next one... :)
    Same error, but for type numeric:
    2017-07-03T07:17:33.101000Z ERROR Database error 22P02: invalid input syntax for type numeric: " "
    CONTEXT: COPY per, line 1, column timezone: " "
    Dimitri Fontaine
    @dimitri
    Can you reopen issue #589 on GitHub with the details and if possible a test case? The dbf numeric field is supposed to host either integers or floats, but I don't know what float look like in this format... Also why do you have a single blank in a Numeric column? I can again blind code something but would really appreciate a test case (that I don't know how to produce).
    Patrik Lermon
    @kler
    I would love to help, but I can't share the database and I don't know how it's created :/
    So unfortunately I see only two options, either you push another fix "blindly" and I'll test it or else I believe we need to leave the issue.
    I solved my problem by writing my own convert script, but if we could improve pgloader that would be great.
    Dimitri Fontaine
    @dimitri
    Ok I see, will push a blind patch
    Dimitri Fontaine
    @dimitri
    pushed now
    Patrik Lermon
    @kler
    Moved discussion over to bug - seems more legit :)
    (and your code is tested btw)
    Dimitri Fontaine
    @dimitri
    Thanks! That way other users withe the same kind of problems will easily find what we did to solve it...
    Irio Musskopf
    @Irio
    Hello, been struggling for a few minutes trying to use the app for migrating a large MySQL database into PostgreSQL. Maybe could get other eyes to help me? 😬
    Tried a few variations of the command, but the output is always the same. I assume I'm missing something obvious...
    Dimitri Fontaine
    @dimitri
    Hi, yes it's pretty obvious. It's missing some error messages tho, which version are you using?
    Your problem is that the PostgreSQL db URI is malformed, it has postgresql:///, and three slashes mean you didn't provide username, password, host, port. The part after the third slash is expected to be the database name. Just as in the MySQL connection string, actually.
    Brian Jensen
    @bjensen
    I am still getting the pgloader failed to find schema in target catalog with the newest build from head..wasnt it suppose to be fixed? in a migration from mysql to 9.6.3 and WITH data only, truncate
    @dimitri :-)
    Brian Jensen
    @bjensen
    nm
    Luke Snape
    @lsnape

    Hello, I'm trying to find a way of casting a mysqlbinary(36)to a postgres uuid type, or, failing that, a varchar(36). I'm frequently getting the error message:

    2017-08-04T16:17:33.884000Z ERROR The value
      #(53 55 101 50 55 49 99 51 45 55 48 98 56 45 52 55 55 52 45 57 97 98 50 45 55
        52 50 50 55 98 57 101 55 56 100 97)
    is not of type
      STRING

    Does anyone know a way of casting the above using the options provided with pgloader, or do I have to write my own using lisp function?

    Luke Snape
    @lsnape

    OK, I got it working with the varbinary-to-string function, but I get lots of these errors for nullable fields:

    2017-08-05T08:15:45.427000Z ERROR NIL fell through ETYPECASE expression. Wanted one of (STRING VECTOR).

    Should varbinary-to-string handle nulls gracefully?

    Dimitri Fontaine
    @dimitri
    @lsnape yeah it can be argued it should, please open an issue at github and I'll fix it!
    @bjensen yes it's supposed to be fixed, do you have a test case for me to reproduce the bug?
    @lsnape or even see about making a Pull Request, it's a very good introduction topic for hacking on pgloader -- see https://github.com/dimitri/pgloader/blob/master/src/utils/transforms.lisp with plenty of examples in there
    Luke Snape
    @lsnape
    Thanks @dimitri! I'll raise a PR.
    Tim Mendez
    @timendez
    Hey gang, if I have a Postgres database, and a similar looking database in MySQL, can I use this tool to plop the contents of the MySQL database in the Postgres one?
    Dimitri Fontaine
    @dimitri
    Sure, try the --with "data only" parameter.
    louiz’
    @louiz
    dimitri/pgloader#556 I’m running into this issue. It’s weird though, because I have pgloader version "3.4.1", wich is supposed to contain the fix…
    My sqlite3 column is “id INTEGER PRIMARY KEY AUTOINCREMENT,” and it’s converted into “id | bigint | not null”, and not serial
    (with or without the --cast 'type int with extra auto_increment to serial' option)
    Dimitri Fontaine
    @dimitri
    Hi @louiz , it seems like your auto_increment property is misspelled...
    louiz’
    @louiz
    With or without the underscore, you mean?