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

it gets stuck on database error #339

Closed
trico opened this Issue Feb 11, 2016 · 6 comments

Comments

Projects
None yet
2 participants
@trico

trico commented Feb 11, 2016

pgloader version "3.3.bfdbb21"
compiled with SBCL 1.1.14.debian

It gets stuck when a database error occurs, like with unexpected column name:

pgloader plusfresc.vouchers.201602104559.load

debugger invoked on a CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION in thread
#<THREAD "lparallel" RUNNING {10096EE793}>:
  Database error 42703: column "voucheable_type" of relation "vouchers" does not exist

debugger invoked on a CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION in thread
#<THREAD "lparallel" RUNNING {10096ED743}>:
  Database error 42703: column "voucheable_type" of relation "vouchers" does not exist
2016-02-11T11:16:59.943000+01:00 ERROR Database error 42703: column "voucheable_type" of relation "vouchers" does not exist
2016-02-11T11:16:59.943000+01:00 ERROR Database error 42703: column "voucheable_type" of relation "vouchers" does not exist
2016-02-11T11:16:59.943000+01:00 ERROR Database error 42703: column "voucheable_type" of relation "vouchers" does not exist
2016-02-11T11:16:59.944000+01:00 ERROR Database error 42703: column "voucheable_type" of relation "vouchers" does not exist

the execution never ends

@dimitri

This comment has been minimized.

Show comment
Hide comment
@dimitri

dimitri Feb 11, 2016

Owner

Can you provide me with a test case so that I can reproduce the error? maybe here some SQL command to install the MySQL source database would be enough? Also, a --debug log would be useful.

Owner

dimitri commented Feb 11, 2016

Can you provide me with a test case so that I can reproduce the error? maybe here some SQL command to install the MySQL source database would be enough? Also, a --debug log would be useful.

@trico

This comment has been minimized.

Show comment
Hide comment
@trico

trico Feb 11, 2016

Load file:

LOAD CSV
FROM './tmp/plusfresc.motivators.201602104924.load.csv'
HAVING FIELDS
(
id,motivator_type_id,code,created_at,available_from,available_to,name,description,numeration,when,active,static_code,type,company_id
)
INTO postgres://OMITTED?sslmode=require&tablename=plusfresc.motivators
TARGET COLUMNS
(
id,motivator_type_id,code,created_at,available_from,available_to,name,description,numeration,when,active,static_code,type,company_id
)
WITH fields terminated by ';',
skip header = 1
;

the output with --debug

2016-02-11T15:38:07.119000+01:00 INFO Starting monitor
2016-02-11T15:38:07.125000+01:00 LOG Main logs in '/tmp/pgloader/pgloader.log'
2016-02-11T15:38:07.125000+01:00 LOG Data errors in '/tmp/pgloader/'
2016-02-11T15:38:07.125000+01:00 LOG Parsing commands from file #P"/opt/pgloader-ms/plusfresc.motivators.201602104924.load"
2016-02-11T15:38:07.527000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {1007508053}>
2016-02-11T15:38:07.527000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:07.928000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {1007508053}>
2016-02-11T15:38:07.929000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:07.929000+01:00 DEBUG SET search_path TO '';
2016-02-11T15:38:07.929000+01:00 DEBUG
select i.relname,
       indrelid::regclass,
       indrelid,
       indisprimary,
       indisunique,
       pg_get_indexdef(indexrelid),
       c.conname,
       pg_get_constraintdef(c.oid)
  from pg_index x
       join pg_class i ON i.oid = x.indexrelid
       left join pg_constraint c ON c.conindid = i.oid
 where indrelid = 'plusfresc.motivators'::regclass
2016-02-11T15:38:07.931000+01:00 DEBUG reset search_path;
2016-02-11T15:38:08.131000+01:00 WARNING Target table "plusfresc.motivators" has 1 indexes defined against it.
2016-02-11T15:38:08.132000+01:00 WARNING That could impact loading performance badly.
2016-02-11T15:38:08.132000+01:00 WARNING Consider the option 'drop indexes'.
2016-02-11T15:38:08.162000+01:00 INFO COPY "plusfresc.motivators"
2016-02-11T15:38:08.162000+01:00 DEBUG Reader started for plusfresc.motivators
2016-02-11T15:38:08.162000+01:00 DEBUG Transformer 1 in action for plusfresc.motivators!
2016-02-11T15:38:08.163000+01:00 DEBUG Transformer 2 in action for plusfresc.motivators!
2016-02-11T15:38:08.163000+01:00 INFO Open #P"/opt/pgloader-ms/./tmp/plusfresc.motivators.201602104924.load.csv"
2016-02-11T15:38:08.363000+01:00 DEBUG finish-batch[3] 1 row in 0.116000s
2016-02-11T15:38:08.364000+01:00 DEBUG format-data-to-copy[1] 1 row in 0.001000s
2016-02-11T15:38:08.364000+01:00 INFO Transformer[1] for plusfresc.motivators is done in 0.088000s
2016-02-11T15:38:08.365000+01:00 DEBUG finish-batch[3] 1 row in 0.117000s
2016-02-11T15:38:08.365000+01:00 DEBUG Reader for plusfresc.motivators is done in 0.117000s
2016-02-11T15:38:08.366000+01:00 DEBUG format-data-to-copy[2] 1 row in 0.000000s
2016-02-11T15:38:08.366000+01:00 INFO Transformer[2] for plusfresc.motivators is done in 0.048000s
2016-02-11T15:38:08.367000+01:00 INFO COPY "plusfresc.motivators" done.
2016-02-11T15:38:08.368000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {100A089243}>
2016-02-11T15:38:08.368000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:08.369000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {100A132413}>
2016-02-11T15:38:08.369000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:08.369000+01:00 NOTICE SET search_path TO plusfresc;
2016-02-11T15:38:08.370000+01:00 NOTICE SET search_path TO plusfresc;

debugger invoked on a CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION in thread
#<THREAD "lparallel" RUNNING {10097582F3}>:
  Database error 42703: column "code" of relation "motivators" does not exist

debugger invoked on a CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION in thread
#<THREAD "lparallel" RUNNING {1009727813}>:
  Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.570000+01:00 INFO pgsql:copy-from-queue[6]: plusfresc.motivators ("id" "motivator_type_id" "code"
                                                "created_at" "available_from"
                                                "available_to" "name"
                                                "description" "numeration"
                                                "when" "active" "static_code"
                                                "type" "company_id")
2016-02-11T15:38:08.572000+01:00 INFO pgsql:copy-from-queue[4]: plusfresc.motivators ("id" "motivator_type_id" "code"
                                                "created_at" "available_from"
                                                "available_to" "name"
                                                "description" "numeration"
                                                "when" "active" "static_code"
                                                "type" "company_id")
2016-02-11T15:38:08.574000+01:00 DEBUG BEGIN
2016-02-11T15:38:08.574000+01:00 DEBUG BEGIN
2016-02-11T15:38:08.575000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.576000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.576000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.576000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist

trico commented Feb 11, 2016

Load file:

LOAD CSV
FROM './tmp/plusfresc.motivators.201602104924.load.csv'
HAVING FIELDS
(
id,motivator_type_id,code,created_at,available_from,available_to,name,description,numeration,when,active,static_code,type,company_id
)
INTO postgres://OMITTED?sslmode=require&tablename=plusfresc.motivators
TARGET COLUMNS
(
id,motivator_type_id,code,created_at,available_from,available_to,name,description,numeration,when,active,static_code,type,company_id
)
WITH fields terminated by ';',
skip header = 1
;

the output with --debug

2016-02-11T15:38:07.119000+01:00 INFO Starting monitor
2016-02-11T15:38:07.125000+01:00 LOG Main logs in '/tmp/pgloader/pgloader.log'
2016-02-11T15:38:07.125000+01:00 LOG Data errors in '/tmp/pgloader/'
2016-02-11T15:38:07.125000+01:00 LOG Parsing commands from file #P"/opt/pgloader-ms/plusfresc.motivators.201602104924.load"
2016-02-11T15:38:07.527000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {1007508053}>
2016-02-11T15:38:07.527000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:07.928000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {1007508053}>
2016-02-11T15:38:07.929000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:07.929000+01:00 DEBUG SET search_path TO '';
2016-02-11T15:38:07.929000+01:00 DEBUG
select i.relname,
       indrelid::regclass,
       indrelid,
       indisprimary,
       indisunique,
       pg_get_indexdef(indexrelid),
       c.conname,
       pg_get_constraintdef(c.oid)
  from pg_index x
       join pg_class i ON i.oid = x.indexrelid
       left join pg_constraint c ON c.conindid = i.oid
 where indrelid = 'plusfresc.motivators'::regclass
2016-02-11T15:38:07.931000+01:00 DEBUG reset search_path;
2016-02-11T15:38:08.131000+01:00 WARNING Target table "plusfresc.motivators" has 1 indexes defined against it.
2016-02-11T15:38:08.132000+01:00 WARNING That could impact loading performance badly.
2016-02-11T15:38:08.132000+01:00 WARNING Consider the option 'drop indexes'.
2016-02-11T15:38:08.162000+01:00 INFO COPY "plusfresc.motivators"
2016-02-11T15:38:08.162000+01:00 DEBUG Reader started for plusfresc.motivators
2016-02-11T15:38:08.162000+01:00 DEBUG Transformer 1 in action for plusfresc.motivators!
2016-02-11T15:38:08.163000+01:00 DEBUG Transformer 2 in action for plusfresc.motivators!
2016-02-11T15:38:08.163000+01:00 INFO Open #P"/opt/pgloader-ms/./tmp/plusfresc.motivators.201602104924.load.csv"
2016-02-11T15:38:08.363000+01:00 DEBUG finish-batch[3] 1 row in 0.116000s
2016-02-11T15:38:08.364000+01:00 DEBUG format-data-to-copy[1] 1 row in 0.001000s
2016-02-11T15:38:08.364000+01:00 INFO Transformer[1] for plusfresc.motivators is done in 0.088000s
2016-02-11T15:38:08.365000+01:00 DEBUG finish-batch[3] 1 row in 0.117000s
2016-02-11T15:38:08.365000+01:00 DEBUG Reader for plusfresc.motivators is done in 0.117000s
2016-02-11T15:38:08.366000+01:00 DEBUG format-data-to-copy[2] 1 row in 0.000000s
2016-02-11T15:38:08.366000+01:00 INFO Transformer[2] for plusfresc.motivators is done in 0.048000s
2016-02-11T15:38:08.367000+01:00 INFO COPY "plusfresc.motivators" done.
2016-02-11T15:38:08.368000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {100A089243}>
2016-02-11T15:38:08.368000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:08.369000+01:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://OMITTED {100A132413}>
2016-02-11T15:38:08.369000+01:00 DEBUG SET client_encoding TO 'utf8'
2016-02-11T15:38:08.369000+01:00 NOTICE SET search_path TO plusfresc;
2016-02-11T15:38:08.370000+01:00 NOTICE SET search_path TO plusfresc;

debugger invoked on a CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION in thread
#<THREAD "lparallel" RUNNING {10097582F3}>:
  Database error 42703: column "code" of relation "motivators" does not exist

debugger invoked on a CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION in thread
#<THREAD "lparallel" RUNNING {1009727813}>:
  Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.570000+01:00 INFO pgsql:copy-from-queue[6]: plusfresc.motivators ("id" "motivator_type_id" "code"
                                                "created_at" "available_from"
                                                "available_to" "name"
                                                "description" "numeration"
                                                "when" "active" "static_code"
                                                "type" "company_id")
2016-02-11T15:38:08.572000+01:00 INFO pgsql:copy-from-queue[4]: plusfresc.motivators ("id" "motivator_type_id" "code"
                                                "created_at" "available_from"
                                                "available_to" "name"
                                                "description" "numeration"
                                                "when" "active" "static_code"
                                                "type" "company_id")
2016-02-11T15:38:08.574000+01:00 DEBUG BEGIN
2016-02-11T15:38:08.574000+01:00 DEBUG BEGIN
2016-02-11T15:38:08.575000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.576000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.576000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist
2016-02-11T15:38:08.576000+01:00 ERROR Database error 42703: column "code" of relation "motivators" does not exist

@dimitri

This comment has been minimized.

Show comment
Hide comment
@dimitri

dimitri Feb 12, 2016

Owner

Ok, I was wrong to think you were using a database source where pgloader does the introspection and figures out itself automatically the target columns. Here you are providing the column names, and apparently you made some typo or other kind of mistake?

  Database error 42703: column "code" of relation "motivators" does not exist

Do you expect the column code to exist?

Owner

dimitri commented Feb 12, 2016

Ok, I was wrong to think you were using a database source where pgloader does the introspection and figures out itself automatically the target columns. Here you are providing the column names, and apparently you made some typo or other kind of mistake?

  Database error 42703: column "code" of relation "motivators" does not exist

Do you expect the column code to exist?

@trico

This comment has been minimized.

Show comment
Hide comment
@trico

trico Feb 15, 2016

I made the typo intentionally, we integrate pgloader in a kind of automated system and sometimes a column definition error could happen, so after trying to emulate an error it gets stuck and never ends.

trico commented Feb 15, 2016

I made the typo intentionally, we integrate pgloader in a kind of automated system and sometimes a column definition error could happen, so after trying to emulate an error it gets stuck and never ends.

@dimitri

This comment has been minimized.

Show comment
Hide comment
@dimitri

dimitri Feb 15, 2016

Owner

Oh, ok, now I understand the problem, will see about that, thanks.

Owner

dimitri commented Feb 15, 2016

Oh, ok, now I understand the problem, will see about that, thanks.

@dimitri

This comment has been minimized.

Show comment
Hide comment
@dimitri

dimitri Feb 21, 2016

Owner

I believe your case should be fixed now, please confirm or re-open the issue after testing, thanks!

Owner

dimitri commented Feb 21, 2016

I believe your case should be fixed now, please confirm or re-open the issue after testing, thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment