Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Column case is ignored #649

Closed
chrisspen opened this issue Oct 19, 2017 · 8 comments
Closed

Column case is ignored #649

chrisspen opened this issue Oct 19, 2017 · 8 comments

Comments

@chrisspen
Copy link

@chrisspen chrisspen commented Oct 19, 2017

I converted a mysql database to postgres by running:

pgloader mysql://olduser:oldpass@oldhost/olddb postgresql://newuser:newpass@newhost/newdb

and the new postgres database is unusable by my application because the case is broken in any column name that used mixed or camel case. e.g. A column called someColumn gets turned into somecolumn, which in postgres are not the same. It appears all columns were converted to lower-case.

The help docs don't mention any option to change this. Is there any way to stop this from happening or is this a limitation of the tool?

@dimitri
Copy link
Owner

@dimitri dimitri commented Oct 19, 2017

The default is to follow PosgreSQL defaults and down case every identifier. Use --with "quote identifiers" at the command line to get the behaviour you're asking for, keeping the exact case of your fields. Note that every use of those column names will have to be double-quoted when using PostgreSQL.

And yes it is documented at https://github.com/dimitri/pgloader/blob/master/pgloader.1.md#load-mysql-database

  - *downcase identifiers*

    When this option is listed, pgloader converts all MySQL identifiers
    (table names, index names, column names) to *downcase*, except for
    PostgreSQL *reserved* keywords.

	The PostgreSQL *reserved* keywords are determined dynamically by
	using the system function `pg_get_keywords()`.

  - *quote identifiers*

	When this option is listed, pgloader quotes all MySQL identifiers so
	that their case is respected. Note that you will then have to do the
	same thing in your application code queries.

@eurobob
Copy link

@eurobob eurobob commented Apr 30, 2020

@dimitri @chrisspen

Sorry to reopen an old issue, but this is not working so well. When I try to add the --with option it doesn't work, no matter if I put it before the mysql & postgres urls, or after them

❯ pgloader --with "quote identifiers" mysql://root@localhost:3306/guguide pgsql://postgres@localhost:5432/guguide
#<CLOSURE (LAMBDA (COMMAND-LINE-ARGUMENTS::VALUE)
            :IN
            COMMAND-LINE-ARGUMENTS::MAKE-OPTION-ACTION) {1004DD0D4B}> fell through ETYPECASE expression.
Wanted one of (NULL KEYWORD SYMBOL CONS).
pgloader [ option ... ] command-file ...
pgloader [ option ... ] SOURCE TARGET
  --help -h                       boolean  Show usage and exit.
  --version -V                    boolean  Displays pgloader version and exit.
  --quiet -q                      boolean  Be quiet
  --verbose -v                    boolean  Be verbose
  --debug -d                      boolean  Display debug level information.
  --client-min-messages           string   Filter logs seen at the console (default: "warning")
  --log-min-messages              string   Filter logs seen in the logfile (default: "notice")
  --summary -S                    string   Filename where to copy the summary
  --root-dir -D                   string   Output root directory. (default: #P"/tmp/pgloader/")
  --upgrade-config -U             boolean  Output the command(s) corresponding to .conf file for
                                           v2.x
  --list-encodings -E             boolean  List pgloader known encodings and exit.
  --logfile -L                    string   Filename where to send the logs.
  --load-lisp-file -l             string   Read user code from files
  --dry-run                       boolean  Only check database connections, don't load anything.
  --on-error-stop                 boolean  Refrain from handling errors properly.
  --no-ssl-cert-verification      boolean  Instruct OpenSSL to bypass verifying certificates.
  --context -C                    string   Command Context Variables
  --with                          string   Load options
  --set                           string   PostgreSQL options
  --field                         string   Source file fields specification
  --cast                          string   Specific cast rules
  --type                          string   Force input source type
  --encoding                      string   Source expected encoding
  --before                        string   SQL script to run before loading the data
  --after                         string   SQL script to run after loading the data
  --self-upgrade                  string   Path to pgloader newer sources
  --regress                       boolean  Drive regression testing
❯ pgloader mysql://root@localhost:3306/guguide pgsql://postgres@localhost:5432/guguide --with "quote identifiers"
KABOOM!
FATAL error: No such file or directory: "mysql://root@localhost:3306/guguide"
   No such file or directory: "pgsql://postgres@localhost:5432/guguide"
   No such file or directory: "--with"
   No such file or directory: "quote identifiers"
An unhandled error condition has been signalled:
   No such file or directory: "mysql://root@localhost:3306/guguide"
   No such file or directory: "pgsql://postgres@localhost:5432/guguide"
   No such file or directory: "--with"
   No such file or directory: "quote identifiers"



2020-04-30T16:17:03.009000+01:00 LOG pgloader version "3.6.d5314a6"

What I am doing here?

No such file or directory: "mysql://root@localhost:3306/guguide"
   No such file or directory: "pgsql://postgres@localhost:5432/guguide"
   No such file or directory: "--with"
   No such file or directory: "quote identifiers"

@phoe
Copy link
Collaborator

@phoe phoe commented Apr 30, 2020

#<CLOSURE (LAMBDA (COMMAND-LINE-ARGUMENTS::VALUE)
            :IN
            COMMAND-LINE-ARGUMENTS::MAKE-OPTION-ACTION) {1004DD0D4B}> fell through ETYPECASE expression.
Wanted one of (NULL KEYWORD SYMBOL CONS).

This is a type error that IMO shouldn't have happened. Reopening.

@phoe phoe reopened this Apr 30, 2020
@pravatk
Copy link

@pravatk pravatk commented May 6, 2020

I am also facing the same issue when running pgloader on a sqlite database. The loaded table names and columns are always in lower case.
But when I run with --with "quote identifiers", I see a different error mentioned below.

pgloader test.db postgresql:///test --with "quote identifiers"
KABOOM!
FATAL error: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file <pathto>/test.db" {10050ED663}>: the octet sequence #(172) cannot be decoded.
An unhandled error condition has been signalled: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file <pathto>/test.db" {10050ED663}>: the octet sequence #(172) cannot be decoded.



2020-05-06T19:05:26.012000+05:30 LOG pgloader version "3.6.2"
2020-05-06T19:05:26.014000+05:30 LOG Data errors in '/private/tmp/pgloader/'
2020-05-06T19:05:26.014000+05:30 LOG Parsing commands from file #P"<pathto>/test.db"

What I am doing here?

:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file <pathto>/test.db" {10050ED663}>:

  the octet sequence #(172) cannot be decoded.

Can someone help?

@phoe
Copy link
Collaborator

@phoe phoe commented May 6, 2020

:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file /Users/pravat/work/repos/skyschedule/utility/data/skyschedule.db" {10050ED663}>:

  the octet sequence #(172) cannot be decoded.

This happens because of an UTF-8 decoding error. Is a .db file readable as UTF-8 though? I thought that SQLite databases contained binary data, at which point reading them via a character stream is an error on pgloader side.

@pravatk
Copy link

@pravatk pravatk commented May 6, 2020

@phoe The loader works fine without the --with "quote identifiers" option though.

@phoe
Copy link
Collaborator

@phoe phoe commented May 6, 2020

Hmmm. It seems that the issue is distinct from the one in the original post.

Please open a new issue with this bugreport. Also, if possible, please attach a minimal reproducible test case - for debugging, we'll need a .db file that generates such an error for you, as small as possible.

@dimitri
Copy link
Owner

@dimitri dimitri commented May 9, 2020

Just tried and failed to reproduce the error with MySQL, and then the error with SQLite is explained in #1142: please use options before arguments in the command line.

@dimitri dimitri closed this May 9, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants