Possible bug in dbcopy
Hi,
Yesterday, I did a "dbcopy" of some tables of one database to another (same
structures)
The execution raises this error on a table:
Error fetching row! Code=-1263, ISAM=0.
Here is the dbschema:
create table "sistec".actm_documento
(
id_documento serial not null ,
id_inspeccion_det integer,
estado_sincronizacion "informix".boolean,
ruta_local varchar(200),
fecha_creacion datetime year to second,
usuario_crea char(20),
fecha_modificacion datetime year to second,
usuario_modifica char(20),
fecha_eliminacion datetime year to second,
usuario_elimina char(20),
id_empresa smallint not null ,
estado_registro smallint not null
);
revoke all on "sistec".actm_documento from "public" as "sistec";
create index "sistec".idx_actm_documento on "sistec".actm_documento
(id_documento,id_inspeccion_det) using btree ;
create unique index "sistec".p23986_594964 on "sistec".actm_documento
(id_documento) using btree ;
alter table "sistec".actm_documento add constraint primary key
(id_documento) ;
I checked the source data, and I didn't see anything anormal.
But I noticed that the table has a column "boolean". I think this could be the
cause of the error.
Another tables raise errors too. At all cases, the error is:
Error fetching row! Code=-1831, ISAM=0
In this case, I noticed that the tables has columns "lvarchar".
The version of dbcopy es 1.90, with informix 11.70 FC8 and HPUX 11.31
Thanks.
OK, so the -1263 error indicates that one of the datetime columns contains
a value that is not a valid date. The error logging feature won't catch
this since it only traps and logs insert errors. You'll have to find and
fix the rows that have a problem.
The -1831 error is a spurious error (it talks about using array fetching
with deferred prepare and open-fetch-close optimization - something that
dbcopy does not do!). The problem happens when you try to use array
fetching on a table that has LVARCHAR columns. It is a bug in the ESQL/C
libraries that I have reported several times over several years. It first
showed up in engine verseion 11.50 & ESQL/C version 3.50 and has never been
fixed. One less than satisfying work around is to cast the LVARCHAR columns
to CHAR in the select statement that you pass manually into dbcopy. However
that will cause the target column to be space padded on the right. You can
get around that by making the target column type CHAR initially then alter
those columns back to LVARCHAR after the copy completes.
Alternatively, use the dbmove utility for tables with LVARCHAR columns
instead of dbcopy. In fact, this bug is the main reason that I wrote dbmove
in the first place. Until IBM gets around to fixing this bug, there's
nothing I can do to make dbcopy work correctly with LVARCHAR.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Nov 26, 2015 at 5:13 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>
wrote:
> Hi,
>
> Yesterday, I did a "dbcopy" of some tables of one database to another (same
> structures)
>
> The execution raises this error on a table:
> Error fetching row! Code=-1263, ISAM=0.
>
> Here is the dbschema:
>
> create table "sistec".actm_documento
> (
>
> id_documento serial not null ,
>
> id_inspeccion_det integer,
>
> estado_sincronizacion "informix".boolean,
>
> ruta_local varchar(200),
>
> fecha_creacion datetime year to second,
>
> usuario_crea char(20),
>
> fecha_modificacion datetime year to second,
>
> usuario_modifica char(20),
>
> fecha_eliminacion datetime year to second,
>
> usuario_elimina char(20),
>
> id_empresa smallint not null ,
>
> estado_registro smallint not null
> );
>
> revoke all on "sistec".actm_documento from "public" as "sistec";>
> create index "sistec".idx_actm_documento on "sistec".actm_documento
>
> (id_documento,id_inspeccion_det) using btree ;
> create unique index "sistec".p23986_594964 on "sistec".actm_documento
>
> (id_documento) using btree ;
> alter table "sistec".actm_documento add constraint primary key
>
> (id_documento) ;
>
> I checked the source data, and I didn't see anything anormal.
>
> But I noticed that the table has a column "boolean". I think this could be
> the
> cause of the error.
>
> Another tables raise errors too. At all cases, the error is:
>
> Error fetching row! Code=-1831, ISAM=0
>
> In this case, I noticed that the tables has columns "lvarchar".
>
> The version of dbcopy es 1.90, with informix 11.70 FC8 and HPUX 11.31
>
> Thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e014946c218b05805257a560e
Hi,
Regarding the error -1263, I forget to mention that the records are inserted,
except by one.
In my test, the source data has 55 records, but the target table is loaded
with 54 records.
Example:
dbcopy -d dbtecnica -h desaold -H synergia -t actm_documento -T rvi_prueba
Selecting data from dbtecnica@desaold:actm_documento with:
SELECT * FROM actm_documento;
Inserting data to dbtecnica@synergia:rvi_prueba with:
INSERT INTO rvi_prueba (
id_documento,
id_inspeccion_det,
estado_sincronizacion,
ruta_local,
fecha_creacion,
usuario_crea,
fecha_modificacion,
usuario_modifica,
fecha_eliminacion,
usuario_elimina,
id_empresa,
estado_registro
) values (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
Error fetching row! Code=-1263, ISAM=0.
Input: 54 records.
Copied: 54 records to dbtecnica@synergia:rvi_prueba.
Logged: 0 records to error log.
But, with insert into:
insert into rvi_prueba
select * from dbtecnica@desaold:actm_documento
55 row(s) inserted.
I did several test (I created new tables and changed the order of records) and
I noticed that, the "last record" is not inserted.
It is possible, of course, that the error is raised only in my particular
environment, but I'm not sure. I work with engine 11.70FC8W1, csdk 3.70 FC8W1,
HP-UX 11.31 64 bits and dbcopy 11.90.
I'm sending the test data:
1|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/12171615661456335.jpg|2015-1
0-12 17:16:27|lpadilla|||||7|1|
2|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012172917661456335.jpg|
2015-10-12 17:29:38|lpadilla|||||7|1|
3|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012173237661456335.jpg|
2015-10-12 17:32:54|lpadilla|||||7|1|
4|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012173630661456335.jpg|
2015-10-12 17:36:43|lpadilla|||||7|1|
5|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012174046661456335.jpg|
2015-10-12 17:41:31|lpadilla|||||7|1|
6|116|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012174445661456335.jpg|
2015-10-12 17:45:03|lpadilla|2015-10-12 17:45:38|lpadilla|||7|1|
7|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012180202661456335.jpg|
2015-10-12 18:02:14|lpadilla|||||7|1|
8|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012180848661456335.jpg|
2015-10-12 18:09:00|lpadilla|||||7|1|
9|116|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012184233940599770.jpg|
2015-10-12 18:42:58|lpadilla|2015-10-12 18:43:15|lpadilla|||7|1|
10|116|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012190634.jpg|2015-10-
12 19:06:48|lpadilla|2015-10-12 19:07:02|lpadilla|||7|1|
11|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013120940.jpg|2015-10-
13 12:09:59|lpadilla|2015-10-13 19:39:38|lpadilla|||7|1|
12|117|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013141004.jpg|2015-10-
13 14:10:25|lpadilla|||||7|1|
13|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013141847.jpg|2015-10-
13 14:19:06|lpadilla|2015-10-13 19:06:27|lpadilla|||7|1|
14|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013142950.jpg|2015-10-
13 14:30:11|lpadilla|2015-10-13 14:30:26|lpadilla|||7|1|
15|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013171826.jpg|2015-10-
13 17:19:13|lpadilla|2015-10-13 17:19:22|lpadilla|||7|1|
16|117|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013181645.jpg|2015-10-
13 18:17:00|lpadilla|||||7|1|
17|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013181705.jpg|2015-10-
13 18:17:19|lpadilla|2015-10-13 18:17:27|lpadilla|||7|1|
18|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014095614.jpg|2015-10-
14 09:56:33|lpadilla|2015-10-14 10:19:56|lpadilla|||7|1|
19|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014095828.jpg|2015-10-
14 09:58:38|lpadilla|2015-10-14 09:58:52|lpadilla|||7|1|
20|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101137.jpg|2015-10-
14 10:11:49|lpadilla|2015-10-14 10:14:46|lpadilla|||7|1|
21|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101153.jpg|2015-10-
14 10:12:03|lpadilla|2015-10-14 10:15:08|lpadilla|||7|1|
22|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101208.jpg|2015-10-
14 10:12:17|lpadilla|2015-10-14 10:22:26|lpadilla|||7|1|
23|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101224.jpg|2015-10-
14 10:12:34|lpadilla|2015-10-14 10:24:57|lpadilla|||7|1|
24|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014102253.jpg|2015-10-
14 10:23:06|lpadilla|2015-10-14 11:54:13|lpadilla|||7|1|
25|119|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014115116.jpg|2015-10-
14 11:51:33|lpadilla|2015-10-14 11:52:14|lpadilla|||7|1|
26|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014115420.jpg|2015-10-
14 11:54:33|lpadilla|2015-10-14 18:50:10|lpadilla|||7|1|
27|120|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014184326.jpg|2015-10-
14 18:43:38|lpadilla|2015-10-14 18:43:53|lpadilla|||7|1|
28|120|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014184402.jpg|2015-10-
14 18:44:14|lpadilla|2015-10-14 18:49:28|lpadilla|||7|1|
29|121|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014184845.jpg|2015-10-
14 18:48:57|lpadilla|2015-10-14 18:49:05|lpadilla|||7|1|
30|122|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151015141154.jpg|2015-10-
15 14:12:12|lpadilla|||||7|1|
31|123|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151015141430.jpg|2015-10-
15 14:14:42|lpadilla|2015-10-15 14:14:56|lpadilla|||7|1|
32|123|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151015141506.jpg|2015-10-
15 14:15:17|lpadilla|||||7|1|
33|124|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016154828.jpg|2015-10-
16 15:48:51|lpadilla|||||7|1|
34|132|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016162328.jpg|2015-10-
16 16:23:46|lpadilla|2015-10-16 17:29:29|lpadilla|||7|1|
35|132|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016162401.jpg|2015-10-
16 16:24:11|lpadilla|2015-10-16 16:24:30|lpadilla|||7|1|
36|132|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016162447.jpg|2015-10-
16 16:24:57|lpadilla|2015-10-16 16:25:05|lpadilla|||7|1|
37|132|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016162510.jpg|2015-10-
16 16:25:20|lpadilla|||||7|1|
38|132|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016172500.jpg|2015-10-
16 17:25:20|lpadilla|||||7|1|
39|136|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151020164229.jpg|2015-10-
20 16:42:52|lpadilla|||||7|1|
40|136|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151020164501.jpg|2015-10-
20 16:45:18|lpadilla|||||7|1|
41|141|t|/mnt/sdcard/Pictures/ActividadMantenimiento/191004230
_20151021114720.jpg|2015-10-21 11:48:09|lpadilla|2015-10-21
12:15:13|lpadilla|||7|1|
42|141|t|/mnt/sdcard/Pictures/ActividadMantenimiento/191004230
_20151021114856.jpg|2015-10-21 11:49:17|lpadilla|2015-10-21
11:49:52|lpadilla|||7|1|
43|141|f|/mnt/sdcard/Pictures/ActividadMantenimiento/191004230
_20151021115221.jpg|2015-10-21 11:52:32|lpadilla|||||7|1|
44|141|t|/mnt/sdcard/Pictures/ActividadMantenimiento/191004230
_20151021115244.jpg|2015-10-21 11:52:56|lpadilla|2015-10-21
11:53:44|lpadilla|||7|1|
45|145|t|/mnt/sdcard/Pictures/ActividadMantenimiento/08100
Send me a direct email with the data and schema as attachments and Iwill
look into it. It's hard to extract from a forum post.
Art
On Nov 27, 2015 12:52 PM, "ROGER VILCA" <rvilca@luzdelsur.com.pe> wrote:
> Hi,
> Regarding the error -1263, I forget to mention that the records are
> inserted,
> except by one.
>
> In my test, the source data has 55 records, but the target table is loaded
> with 54 records.
>
> Example:
>
> dbcopy -d dbtecnica -h desaold -H synergia -t actm_documento -T rvi_prueba
> Selecting data from dbtecnica@desaold:actm_documento with:
>
> SELECT * FROM actm_documento;>
> Inserting data to dbtecnica@synergia:rvi_prueba with:
>
> INSERT INTO rvi_prueba (>
> id_documento,
>
> id_inspeccion_det,
>
> estado_sincronizacion,
>
> ruta_local,
>
> fecha_creacion,
>
> usuario_crea,
>
> fecha_modificacion,
>
> usuario_modifica,
>
> fecha_eliminacion,
>
> usuario_elimina,
>
> id_empresa,
>
> estado_registro
>
> ) values (
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
>
> Error fetching row! Code=-1263, ISAM=0.
> Input: 54 records.
> Copied: 54 records to dbtecnica@synergia:rvi_prueba.
> Logged: 0 records to error log.
>
> But, with insert into:
>
> insert into rvi_prueba
> select * from dbtecnica@desaold:actm_documento>
> 55 row(s) inserted.
>
> I did several test (I created new tables and changed the order of records)
> and
> I noticed that, the "last record" is not inserted.
>
> It is possible, of course, that the error is raised only in my particular
> environment, but I'm not sure. I work with engine 11.70FC8W1, csdk 3.70
> FC8W1,
> HP-UX 11.31 64 bits and dbcopy 11.90.
>
> I'm sending the test data:
>
>
>
>
1|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/12171615661456335.jpg|2015-1
0-12
> 17:16:27|lpadilla|||||7|1|
>
>
>
2|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012172917661456335.jpg|
2015-10-12
> 17:29:38|lpadilla|||||7|1|
>
>
>
3|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012173237661456335.jpg|
2015-10-12
> 17:32:54|lpadilla|||||7|1|
>
>
>
4|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012173630661456335.jpg|
2015-10-12
> 17:36:43|lpadilla|||||7|1|
>
>
>
5|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012174046661456335.jpg|
2015-10-12
> 17:41:31|lpadilla|||||7|1|
>
>
>
6|116|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012174445661456335.jpg|
2015-10-12
> 17:45:03|lpadilla|2015-10-12 17:45:38|lpadilla|||7|1|
>
>
>
7|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012180202661456335.jpg|
2015-10-12
> 18:02:14|lpadilla|||||7|1|
>
>
>
8|116|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012180848661456335.jpg|
2015-10-12
> 18:09:00|lpadilla|||||7|1|
>
>
>
9|116|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012184233940599770.jpg|
2015-10-12
> 18:42:58|lpadilla|2015-10-12 18:43:15|lpadilla|||7|1|
>
>
>
10|116|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151012190634.jpg|2015-10-
12
> 19:06:48|lpadilla|2015-10-12 19:07:02|lpadilla|||7|1|
>
>
>
11|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013120940.jpg|2015-10-
13
> 12:09:59|lpadilla|2015-10-13 19:39:38|lpadilla|||7|1|
>
>
>
12|117|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013141004.jpg|2015-10-
13
> 14:10:25|lpadilla|||||7|1|
>
>
>
13|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013141847.jpg|2015-10-
13
> 14:19:06|lpadilla|2015-10-13 19:06:27|lpadilla|||7|1|
>
>
>
14|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013142950.jpg|2015-10-
13
> 14:30:11|lpadilla|2015-10-13 14:30:26|lpadilla|||7|1|
>
>
>
15|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013171826.jpg|2015-10-
13
> 17:19:13|lpadilla|2015-10-13 17:19:22|lpadilla|||7|1|
>
>
>
16|117|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013181645.jpg|2015-10-
13
> 18:17:00|lpadilla|||||7|1|
>
>
>
17|117|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151013181705.jpg|2015-10-
13
> 18:17:19|lpadilla|2015-10-13 18:17:27|lpadilla|||7|1|
>
>
>
18|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014095614.jpg|2015-10-
14
> 09:56:33|lpadilla|2015-10-14 10:19:56|lpadilla|||7|1|
>
>
>
19|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014095828.jpg|2015-10-
14
> 09:58:38|lpadilla|2015-10-14 09:58:52|lpadilla|||7|1|
>
>
>
20|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101137.jpg|2015-10-
14
> 10:11:49|lpadilla|2015-10-14 10:14:46|lpadilla|||7|1|
>
>
>
21|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101153.jpg|2015-10-
14
> 10:12:03|lpadilla|2015-10-14 10:15:08|lpadilla|||7|1|
>
>
>
22|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101208.jpg|2015-10-
14
> 10:12:17|lpadilla|2015-10-14 10:22:26|lpadilla|||7|1|
>
>
>
23|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014101224.jpg|2015-10-
14
> 10:12:34|lpadilla|2015-10-14 10:24:57|lpadilla|||7|1|
>
>
>
24|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014102253.jpg|2015-10-
14
> 10:23:06|lpadilla|2015-10-14 11:54:13|lpadilla|||7|1|
>
>
>
25|119|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014115116.jpg|2015-10-
14
> 11:51:33|lpadilla|2015-10-14 11:52:14|lpadilla|||7|1|
>
>
>
26|118|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014115420.jpg|2015-10-
14
> 11:54:33|lpadilla|2015-10-14 18:50:10|lpadilla|||7|1|
>
>
>
27|120|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014184326.jpg|2015-10-
14
> 18:43:38|lpadilla|2015-10-14 18:43:53|lpadilla|||7|1|
>
>
>
28|120|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014184402.jpg|2015-10-
14
> 18:44:14|lpadilla|2015-10-14 18:49:28|lpadilla|||7|1|
>
>
>
29|121|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151014184845.jpg|2015-10-
14
> 18:48:57|lpadilla|2015-10-14 18:49:05|lpadilla|||7|1|
>
>
>
30|122|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151015141154.jpg|2015-10-
15
> 14:12:12|lpadilla|||||7|1|
>
>
>
31|123|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151015141430.jpg|2015-10-
15
> 14:14:42|lpadilla|2015-10-15 14:14:56|lpadilla|||7|1|
>
>
>
32|123|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151015141506.jpg|2015-10-
15
> 14:15:17|lpadilla|||||7|1|
>
>
>
33|124|f|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016154828.jpg|2015-10-
16
> 15:48:51|lpadilla|||||7|1|
>
>
>
34|132|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016162328.jpg|2015-10-
16
> 16:23:46|lpadilla|2015-10-16 17:29:29|lpadilla|||7|1|
>
>
>
35|132|t|/mnt/sdcard/Pictures/ActividadMantenimiento/20151016162401.jpg|2015-10-
16
> 16:24:11|lpadill