search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.32k stars 539 forks source link

[mysql]MATERIALIZE VIEWS problem #949

Open m-ilk opened 5 years ago

m-ilk commented 5 years ago

hello dimitri, i saw a few issues that has the similar output, my pgloader is already up-to-date however it still has the MySQL server version for the right syntax to use near '(NIL . error

LOAD DATABASE
    FROM    mysql://***
    INTO    postgresql://localhost/youtube_service
WITH data only
MATERIALIZE VIEWS 'channels' AS $$
    select channelId
    from channels;
$$
INCLUDING ONLY TABLE NAMES MATCHING 'channels';

 pgloader version 3.6.1
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/var/folders/8l/6dy6cqv15v32c_yxykrxc7j80000gp/T/"
2019-04-22T16:33:43.007000-07:00 NOTICE Starting pgloader, log system is ready.
2019-04-22T16:33:43.018000-07:00 INFO Starting monitor
2019-04-22T16:33:43.021000-07:00 LOG pgloader version "3.6.1"
2019-04-22T16:33:43.021000-07:00 LOG Data errors in '/private/tmp/pgloader/'
2019-04-22T16:33:43.021000-07:00 LOG Parsing commands from file #P"/Users/michaellee/Desktop/youtube-service/data_entry.load"
2019-04-22T16:33:43.093000-07:00 INFO Parsed command:
LOAD DATABASE
    FROM    ***
    INTO    postgresql://localhost/youtube_service
MATERIALIZE VIEWS jobs AS $$ SELECT channelId,title FROM channels;$$;

2019-04-22T16:33:43.128000-07:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://michaellee@localhost:5432/youtube_service {10064C9613}>
2019-04-22T16:33:43.129000-07:00 DEBUG SET client_encoding TO 'utf8'
2019-04-22T16:33:43.129000-07:00 DEBUG SET application_name TO 'pgloader'
2019-04-22T16:33:43.129000-07:00 LOG Migrating from #<MYSQL-CONNECTION mysql://*** {10064C8423}>
2019-04-22T16:33:43.129000-07:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://michaellee@localhost:5432/youtube_service {10064C9613}>
2019-04-22T16:33:43.287000-07:00 DEBUG CONNECTED TO #<MYSQL-CONNECTION mysql://*** {10064C8423}>
2019-04-22T16:33:43.287000-07:00 INFO MySQL: CREATE VIEW (NIL . jobs) AS SELECT channelId,title FROM channels;
2019-04-22T16:33:43.287000-07:00 SQL MySQL: sending query: CREATE VIEW (NIL . jobs) AS SELECT channelId,title FROM channels;
KABOOM!
FATAL error: MySQL Error [1064]: "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 '(NIL . jobs) AS SELECT channelId,title FROM channels' at line 1"
Date/time: 2019-04-22-16:33An unhandled error condition has been signalled:
                              MySQL Error [1064]: "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 '(NIL . jobs) AS SELECT channelId,title FROM channels' at line 1"

Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {10005805B3}>
0: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {1007B31003}>)
1: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<QMYND:MYSQL-ERROR {1007B30E93}> :OUTPUT NIL :IF-EXISTS :APPEND :VERBOSE NIL)
2: ((FLET "H0" :IN PGLOADER::MAIN) #<QMYND:MYSQL-ERROR {1007B30E93}>)
3: (SB-KERNEL::%SIGNAL #<QMYND:MYSQL-ERROR {1007B30E93}>)
4: (ERROR #<QMYND:MYSQL-ERROR {1007B30E93}>)
5: (QMYND-IMPL:PARSE-RESPONSE #<QMYND-IMPL::MY-PACKET-STREAM 207/207 [2]>)
6: (QMYND:MYSQL-QUERY #<QMYND-IMPL::MYSQL-INET-CONNECTION {1007468193}> "CREATE VIEW (NIL . jobs) AS SELECT channelId,title FROM channels;" :ROW-FN NIL :AS-TEXT T :RESULT-TYPE LIST)
7: (PGLOADER.SOURCE.MYSQL::CREATE-MY-VIEWS (((NIL . "jobs") . "SELECT channelId,title FROM channels;")))
8: ((:METHOD PGLOADER.SOURCES:FETCH-METADATA (PGLOADER.SOURCE.MYSQL:COPY-MYSQL PGLOADER.CATALOG:CATALOG)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {1006D8C1F3}> #1=#S(PGLOADER.CATALOG:CATALOG :NAME #2="data-entry" :SCHEMA-LIST (#S(PGLOADER.CATALOG:SCHEMA :SOURCE-NAME #2# :NAME "\"data-entry\"" :CATALOG #1# :IN-SEARCH-PATH T :TABLE-LIST NIL :VIEW-LIST NIL :EXTENSION-LIST NIL :SQLTYPE-LIST NIL)) :TYPES-WITHOUT-BTREE NIL :DISTRIBUTION-RULES NIL) :MATERIALIZE-VIEWS (((NIL . "jobs") . "SELECT channelId,title FROM channels;")) :ONLY-TABLES NIL :CREATE-INDEXES T :FOREIGN-KEYS T :INCLUDING NIL :EXCLUDING NIL) [fast-method]
9: ((:METHOD PGLOADER.LOAD:COPY-DATABASE (PGLOADER.SOURCES:DB-COPY)) #<PGLOADER.SOURCE.MYSQL:COPY-MYSQL {1006D8C1F3}> :ON-ERROR-STOP T :WORKER-COUNT 4 :CONCURRENCY 1 :MULTIPLE-READERS NIL :MAX-PARALLEL-CREATE-INDEX NIL :TRUNCATE NIL :DISABLE-TRIGGERS NIL :DATA-ONLY NIL :SCHEMA-ONLY NIL :CREATE-SCHEMAS T :CREATE-TABLES T :INCLUDE-DROP T :DROP-SCHEMA NIL :CREATE-INDEXES T :INDEX-NAMES :UNIQUIFY :RESET-SEQUENCES T :FOREIGN-KEYS T :REINDEX NIL :AFTER-SCHEMA NIL :DISTRIBUTE NIL :ONLY-TABLES NIL :INCLUDING NIL :EXCLUDING NIL :SET-TABLE-OIDS T :ALTER-TABLE NIL :ALTER-SCHEMA NIL :MATERIALIZE-VIEWS (((NIL . "jobs") . "SELECT channelId,title FROM channels;"))) [fast-method]
10: ((LAMBDA ()))
11: (PGLOADER:RUN-COMMANDS #P"/Users/michaellee/Desktop/youtube-service/data_entry.load" :START-LOGGER NIL :FLUSH-SUMMARY T :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)
12: (PGLOADER::PROCESS-COMMAND-FILE ("data_entry.load") :FLUSH-SUMMARY T)
13: (PGLOADER.MONITOR::CALL-WITH-MONITOR #<CLOSURE (LAMBDA NIL :IN PGLOADER::MAIN) {1005615C6B}>)
14: (PGLOADER::MAIN ("pgloader" "-d" "data_entry.load"))
15: ((LAMBDA NIL :IN "/private/tmp/pgloader-20190422-19863-14g5q7g/dumper-2SKVI5f7.lisp"))
16: ((FLET SB-UNIX::BODY :IN SAVE-LISP-AND-DIE))
17: ((FLET "WITHOUT-INTERRUPTS-BODY-14" :IN SAVE-LISP-AND-DIE))
18: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))
dimitri commented 5 years ago

Are you sure you're using the pgloader binary that comes from the brew HEAD build? it seems that the version number indicates otherwise...

m-ilk commented 5 years ago

Are you sure you're using the pgloader binary that comes from the brew HEAD build? it seems that the version number indicates otherwise...

hello dimitri, Warning: pgloader HEAD-513455f is already installed and up-to-date this is what i get when i try to install --HEAD

dimitri commented 5 years ago

Please try building from git sources without the help of brew, it might work better?