Mysql syntax issue related to EXCLUDING TABLE NAMES MATCHING

[jberryman]

I'm running

$ sudo -u postgres pgloader commands
2014-08-29T19:29:17.069000Z LOG Starting pgloader, log system is ready.
2014-08-29T19:29:17.111000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2014-08-29T19:29:17.123000Z LOG Data errors in '/tmp/pgloader/'
2014-08-29T19:29:17.123000Z LOG Parsing commands from file #P"/home/me/src/pgl_cmd"
An 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 ''= 'Program'')
order by table_name, ordinal_position' at line 9"

where commands looks like:

load database
    from mysql://user:password@localhost/db
    into postgresql:///db

 WITH include drop, create tables, no truncate,  
      create indexes, reset sequences, foreign keys

  SET maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'db'  

 CAST type datetime to timestamptz  
                drop default drop not null using zero-dates-to-null,  
      type date drop not null drop default using zero-dates-to-null,
      -- Cast binary(16) to uuid:
      type binary when (= precision 16) to uuid drop typemod drop default

 -- 'Program' is unused and conflicts case-wise with 'program' table
 EXCLUDING TABLE NAMES MATCHING 'Program'

 BEFORE LOAD DO  
 $$ create schema if not exists db; $$; 

Possibly related to #109. Finally here is the debug output. Any ideas or workarounds greatly appreciated:

sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2014-08-29T19:30:48.128000Z LOG Starting pgloader, log system is ready.
2014-08-29T19:30:48.225000Z INFO Starting monitor
2014-08-29T19:30:48.243000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2014-08-29T19:30:48.244000Z LOG Data errors in '/tmp/pgloader/'
2014-08-29T19:30:48.245000Z LOG Parsing commands from file #P"/home/me/src/pgl_cmd"
2014-08-29T19:30:48.245000Z DEBUG CONNECT
2014-08-29T19:30:48.246000Z DEBUG SET maintenance_work_mem TO '128MB'
2014-08-29T19:30:48.247000Z DEBUG SET work_mem TO '12MB'
2014-08-29T19:30:48.247000Z DEBUG SET search_path TO 'db'
2014-08-29T19:30:48.248000Z DEBUG BEGIN
An 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 ''= 'Program'')
order by table_name, ordinal_position' at line 9"

Date/time: 2014-08-29-19:30An 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 ''= 'Program'')
order by table_name, ordinal_position' at line 9"


Backtrace for: #<SB-THREAD:THREAD "main thread" RUNNING {D94C1C9}>
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) {E6A7F1D}>)
2: (SB-IMPL::%WITH-STANDARD-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {E6A7F05}>)
3: (PRINT-BACKTRACE :STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {91A4E49}> :START 0 :FROM :DEBUGGER-FRAME :COUNT 536870911 :PRINT-THREAD T :PRINT-FRAME-SOURCE NIL :METHOD-FRAME-STYLE NIL)
4: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {91A4E49}>)
5: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE #<QMYND:MYSQL-ERROR {E6A4961}> :OUTPUT #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDOUT* {91A4E49}> :IF-EXISTS :APPEND :VERBOSE T)
6: (SIGNAL #<QMYND:MYSQL-ERROR {E6A4961}>)
7: (ERROR #<QMYND:MYSQL-ERROR {E6A4961}>)
8: (QMYND-IMPL:PARSE-RESPONSE #<QMYND-IMPL::MY-PACKET-STREAM 207/207 [2]>)
9: (QMYND:MYSQL-QUERY #<QMYND-IMPL::MYSQL-INET-CONNECTION {E2CF8E1}> "
  select c.table_name, c.column_name,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'db' and t.table_type = 'BASE TABLE'


         and not (table_name '= 'Program'')
order by table_name, ordinal_position" :ROW-FN NIL :AS-TEXT T :RESULT-TYPE LIST)
10: (PGLOADER.MYSQL::LIST-ALL-COLUMNS :DBNAME NIL :TABLE-TYPE :TABLE :ONLY-TABLES NIL :INCLUDING NIL :EXCLUDING ("Program"))
11: (PGLOADER.MYSQL::FETCH-MYSQL-METADATA :STATE #S(PGLOADER.UTILS::PGSTATE :TABLES #<HASH-TABLE :TEST EQUAL :COUNT 2 {DB6E489}> :TABNAMES ("fetch meta data" "before load") :READ 1 :ROWS 1 :ERRS 0 :SECS 0.036) :MATERIALIZE-VIEWS NIL :ONLY-TABLES NIL :INCLUDING NIL :EXCLUDING ("Program"))
12: ((:METHOD PGLOADER.SOURCES:COPY-DATABASE (PGLOADER.MYSQL:COPY-MYSQL)) #<PGLOADER.MYSQL:COPY-MYSQL {E06E3E9}> :STATE-BEFORE #S(PGLOADER.UTILS::PGSTATE :TABLES #<HASH-TABLE :TEST EQUAL :COUNT 2 {DB6E489}> :TABNAMES ("fetch meta data" "before load") :READ 1 :ROWS 1 :ERRS 0 :SECS 0.036) :STATE-AFTER #S(PGLOADER.UTILS::PGSTATE :TABLES #<HASH-TABLE :TEST EQUAL :COUNT 0 {DB6EBC1}> :TABNAMES NIL :READ 0 :ROWS 0 :ERRS 0 :SECS 0.0) :STATE-INDEXES #S(PGLOADER.UTILS::PGSTATE :TABLES #<HASH-TABLE :TEST EQUAL :COUNT 0 {DB6E959}> :TABNAMES NIL :READ 0 :ROWS 0 :ERRS 0 :SECS 0.0) :TRUNCATE NIL :DATA-ONLY NIL :SCHEMA-ONLY NIL :CREATE-TABLES T :INCLUDE-DROP T :CREATE-INDEXES T :RESET-SEQUENCES T :FOREIGN-KEYS T :IDENTIFIER-CASE :DOWNCASE :ONLY-TABLES NIL :INCLUDING NIL :EXCLUDING ("Program") :DECODING-AS NIL :MATERIALIZE-VIEWS NIL) [fast-method]
13: ((LAMBDA ()))
14: (PGLOADER.PARSER:RUN-COMMANDS #P"/home/me/src/pgl_cmd" :START-LOGGER NIL :SUMMARY NIL :LOG-FILENAME NIL :LOG-MIN-MESSAGES NIL :CLIENT-MIN-MESSAGES NIL)
15: ((FLET #:CLEANUP-FUN-200 :IN PGLOADER::MAIN)) [cleanup]
16: (PGLOADER::MAIN ("pgloader" "--debug" "pgl_cmd"))
17: ((LAMBDA NIL :IN "/home/me/src/pgloader/dumper-2SKVI5f7.lisp"))
18: ((FLET #:WITHOUT-INTERRUPTS-BODY-75 :IN SAVE-LISP-AND-DIE))
19: ((LABELS SB-IMPL::RESTART-LISP :IN SAVE-LISP-AND-DIE))


debugger invoked on a QMYND:MYSQL-ERROR in thread
#<THREAD "main thread" RUNNING {D94C1C9}>:
  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 ''= 'Program'')
order by table_name, ordinal_position' at line 9"

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 {D94C1C9}>:
  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)
0[2] 2014-08-29T19:30:48.472000Z DEBUG CONNECT
2014-08-29T19:30:48.474000Z DEBUG SET maintenance_work_mem TO '128MB'
2014-08-29T19:30:48.474000Z DEBUG SET work_mem TO '12MB'
2014-08-29T19:30:48.474000Z DEBUG SET search_path TO 'db'
2014-08-29T19:30:48.474000Z DEBUG BEGIN
2014-08-29T19:30:48.474000Z NOTICE create schema if not exists db;
2014-08-29T19:30:48.474000Z FATAL We have a situation here.

[jberryman]

I should also mention that I couldn't find any variation (quotes/no quotes/regex/etc) that didn't either result in a pgloader parse failure or a mysql parse error. I assume this is related to aad2727 but I don't really understand how string formatting works in this code. Thanks.

[dimitri]

Please test that it's ok now, I've been confused enough with the previous (hasty) version that I needed to brush up my format skills again.