search

dimitri / pgloader

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

(Sqlite) Error: Invalid index NIL for (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (24)), should be a non-negative integer below 24 #1189

Open jjmvee opened 4 years ago

jjmvee commented 4 years ago 
pgloader version "3.6.49e5877"
compiled with SBCL 1.4.5.debian

Yes

Yes. But to no avail.

load database
     from sqlite://./brogmwvolledigeset.gpkg
     into postgresql://agr_dev@localhost/mgr

 with include drop, create tables, create indexes, reset sequences, disable triggers

before load do
    \$\$ drop schema if exists brogmwvolledigeset cascade; \$\$,
    \$\$ create schema if not exists brogmwvolledigeset; \$\$

   cast 
        column gpkg_contents.last_change to timestamptz drop default,
        column gmw_monitoring_well.standardized_location to geometry using convert-mysql-point,
        type text to varchar drop typemod

  set work_mem to '16MB', maintenance_work_mem to '512 MB';

2020-07-15T13:38:28.772000+02:00 ERROR Error while formating a row from "gmw_monitoring_well":
2020-07-15T13:38:28.778000+02:00 ERROR Invalid index NIL for (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (24)), should be a non-negative integer below 24.
2020-07-15T13:38:28.781000+02:00 ERROR The value
  NIL
is not of type
  NUMBER
when binding SB-KERNEL::X

https://mega.nz/file/m9pQVaCJ#cIYTzCOOks2EKs7GHPQlndMclfojoSqCoahg_HtHCdc

(zip file, unpacks to a gpkg/sqlite3 file)

The sqlite3 table 'gmw_monitoring_well' does not load. The other tables do.

2020-07-15T13:47:30.080000+02:00 LOG pgloader version "3.6.49e5877"
2020-07-15T13:47:30.181000+02:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///data/nlextract/pdok/bromgw/./brogmwvolledigeset.gpkg {1007F98793}>
2020-07-15T13:47:30.181000+02:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://agr_dev@localhost:5432/mgr {1007F99BF3}>
2020-07-15T13:47:30.694000+02:00 ERROR Error while formating a row from "gmw_monitoring_well":
2020-07-15T13:47:30.694000+02:00 ERROR Invalid index NIL for (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (24)), should be a non-negative integer below 24.
2020-07-15T13:47:30.698000+02:00 ERROR The value
  NIL
is not of type
  NUMBER
when binding SB-KERNEL::X
2020-07-15T13:47:30.698000+02:00 WARNING PostgreSQL warning: there is no transaction in progress
2020-07-15T13:47:33.135000+02:00 WARNING PostgreSQL warning: identifier "idx_193491669_rtree_gmw_monitoring_well_standardized_location_parent_pkey" will be truncated to "idx_193491669_rtree_gmw_monitoring_well_standardized_location_p"
2020-07-15T13:47:33.282000+02:00 WARNING PostgreSQL warning: identifier "idx_193491657_rtree_gmw_monitoring_well_standardized_location_rowid_pkey" will be truncated to "idx_193491657_rtree_gmw_monitoring_well_standardized_location_r"
2020-07-15T13:47:34.352000+02:00 WARNING PostgreSQL warning: identifier "idx_193491663_rtree_gmw_monitoring_well_standardized_location_node_pkey" will be truncated to "idx_193491663_rtree_gmw_monitoring_well_standardized_location_n"
2020-07-15T13:47:34.445000+02:00 WARNING PostgreSQL warning: identifier "idx_193491669_rtree_gmw_monitoring_well_standardized_location_parent_pkey" will be truncated to "idx_193491669_rtree_gmw_monitoring_well_standardized_location_p"
2020-07-15T13:47:34.446000+02:00 WARNING PostgreSQL warning: identifier "idx_193491657_rtree_gmw_monitoring_well_standardized_location_rowid_pkey" will be truncated to "idx_193491657_rtree_gmw_monitoring_well_standardized_location_r"
2020-07-15T13:47:34.452000+02:00 WARNING PostgreSQL warning: identifier "idx_193491663_rtree_gmw_monitoring_well_standardized_location_node_pkey" will be truncated to "idx_193491663_rtree_gmw_monitoring_well_standardized_location_n"
2020-07-15T13:47:34.468000+02:00 ERROR PostgreSQL Database error 23503: insert or update on table "gmw_monitoring_tube" violates foreign key constraint "gmw_monitoring_tube_monitoring_well_id_fkey"
DETAIL: Key (monitoring_well_id)=(1) is not present in table "gmw_monitoring_well".
QUERY: ALTER TABLE gmw_monitoring_tube ADD FOREIGN KEY(monitoring_well_id) REFERENCES gmw_monitoring_well(monitoring_well_id) ON UPDATE NO ACTION ON DELETE NO ACTION
2020-07-15T13:47:34.477000+02:00 ERROR PostgreSQL Database error 23503: insert or update on table "gmw_event" violates foreign key constraint "gmw_event_monitoring_well_id_fkey"
DETAIL: Key (monitoring_well_id)=(1) is not present in table "gmw_monitoring_well".
QUERY: ALTER TABLE gmw_event ADD FOREIGN KEY(monitoring_well_id) REFERENCES gmw_monitoring_well(monitoring_well_id) ON UPDATE NO ACTION ON DELETE NO ACTION
2020-07-15T13:47:34.524000+02:00 LOG report summary reset
                                            table name     errors       read   imported      bytes      total time       read      write
------------------------------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                                                 fetch          0          0          0                     0.000s    
                                           before load          0          2          2                     0.031s    
                                       fetch meta data          0         40         40                     0.100s    
                                        Create Schemas          0          0          0                     0.001s    
                                      Create SQL Types          0          0          0                     0.015s    
                                         Create tables          0         30         30                     0.139s    
                                        Set Table OIDs          0         15         15                     0.019s    
------------------------------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                                  gpkg_spatial_ref_sys          0          5          5     1.7 kB          0.077s     0.009s  
                                 gpkg_geometry_columns          0          2          2     0.1 kB          0.088s     0.001s  
                                   gmw_monitoring_well          2      23880          0                     0.141s     1.386s  
                                         gpkg_contents          0         14         14     1.5 kB          0.011s     0.002s  
                                       gpkg_extensions          0          1          1     0.1 kB          0.024s     0.002s     0.001s
                                   gmw_monitoring_tube          0      31307      31307     4.7 MB          2.231s     0.990s     2.069s
                                         gmw_electrode          0        237        237     7.9 kB          1.299s     0.002s     0.001s
                                   gmw_event_tube_data          0      43486      43486     3.7 MB          3.276s     0.959s     1.159s
                                     gmw_geo_ohm_cable          0         79         79     1.5 kB          0.015s     0.004s  
                                             gmw_event          0      29527      29527     1.9 MB          0.898s     0.860s     0.460s
       rtree_gmw_monitoring_well_standardized_location          0      23880      23880     2.0 MB          1.163s     0.177s     0.719s
  rtree_gmw_monitoring_well_standardized_location_node          0        719        719     2.4 MB          2.133s     0.010s     1.028s
                              gmw_event_electrode_data          0        237        237     8.0 kB          0.062s     0.005s     0.001s
 rtree_gmw_monitoring_well_standardized_location_rowid          0      23880      23880   219.0 kB          0.434s     0.071s     0.291s
rtree_gmw_monitoring_well_standardized_location_parent          0        718        718     5.4 kB          0.122s     0.003s     0.010s
------------------------------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                               COPY Threads Completion          0          4          4                     4.284s    
                                        Create Indexes          0         16         16                     0.298s    
                                Index Build Completion          0         16         16                     0.059s    
                                       Reset Sequences          0          7          7                     0.025s    
                                          Primary Keys          0         13         13                     0.017s    
                                   Create Foreign Keys          2          9          7                     0.046s    
                                       Create Triggers          0          0          0                     0.000s    
                                      Install Comments          0          0          0                     0.000s    
------------------------------------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                                     Total import time          2     177972     154092    14.9 MB          4.729s    
2020-07-15T13:38:33.163000+02:00 INFO Stopping monitor
phoe commented 4 years ago 
2020-07-15T13:47:30.694000+02:00 ERROR Error while formating a row from "gmw_monitoring_well":
2020-07-15T13:47:30.694000+02:00 ERROR Invalid index NIL for (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (24)), should be a non-negative integer below 24.
2020-07-15T13:47:30.698000+02:00 ERROR The value
  NIL
is not of type
  NUMBER
when binding SB-KERNEL::X

This is a type error on pgloader's side. We will need a backtrace to figure out where exactly it came from.

jjmvee commented 4 years ago

OK. Can I supply that?

The gpkg file is sqlite3 format with extra geospatial features, it has INSERT/UPDATE triggers on the offending table calling geospatial functions. But these should not fire or impact the import (to my knowledge).

phoe commented 4 years ago

I think you can supply that, yes - please run pgloader with the --debug flag.

jjmvee commented 4 years ago

Here it is:

debug_dump.log

phoe commented 4 years ago

Hmmm. No cigar yet; it doesn't print a backtrace.

Let's try even more stuff --debug --verbose?

jjmvee commented 4 years ago

Now with --debug --verbose (38 MB unpacked):

debug_verbose_dump.zip

phoe commented 4 years ago

These seem like two separate errors, one being Invalid index NIL for (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (24)), should be a non-negative integer below 24. and the other being The value NIL is not of type NUMBER when binding SB-KERNEL::X. Still, even with --debug --verbose, the stacktraces still aren't there.

@dimitri - maybe you will be able to help with that one.

abelbinguo commented 1 year ago

I encountered the same error when the integer value of the sqlite field is null or empty. When I change the sqlite integer to a specific value (for example, 0), the error disappears