Rafael,

At 03:43 PM 19/02/2004 -0300, you wrote:

>Helen,
>
>I can reproduce the problem inserting 1.000.000 of records with a program in
>Java.
>
>DDL:
>
>---
>
>SET SQL DIALECT 3;
>
>SET NAMES ISO8859_1;
>
>CREATE DATABASE 'c:\temp\webmarc.fdb'
>USER 'SYSDBA' PASSWORD 'masterkey'
>PAGE_SIZE 8192
>DEFAULT CHARACTER SET ISO8859_1;
>
>CREATE TABLE BSMST (
> MFN INTEGER NOT NULL,
> TAG INTEGER NOT NULL,
> OCC INTEGER NOT NULL,
> SUB CHAR(1) NOT NULL,
> VAL BLOB SUB_TYPE 1 NOT NULL);
>
>---
>
>Java program:
>
>---
>
>import java.io.*;
>import java.util.*;
>import java.sql.*;
>
>public class Teste {
>
> public static void main(String[] args) throws ClassNotFoundException,
> SQLException, IOException {
>
> java.util.Properties propsConexao = new java.util.Properties();
> propsConexao.put("user", "SYSDBA");
> propsConexao.put("password", "masterkey");
> propsConexao.put("lc_ctype", "ISO8859_1");
>
> Class.forName("org.firebirdsql.jdbc.FBDriver");
>
> String dbURL = "jdbc:firebirdsql:localhost:c:\\temp\\webmarc.fdb";
> Connection conn = DriverManager.getConnection(dbURL, propsConexao);
>
> String sql = "INSERT INTO BSMST (MFN,OCC,SUB,TAG,VAL) VALUES " +
> "(1,1,'*',1,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" +
> "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')";
> PreparedStatement pstmt = conn.prepareStatement(sql);
> for (int k = 0; k <= 999999; k++) {
> pstmt.execute();
> System.out.println(k);
> }
> }
>
>}
>
>---
>
>After this, I get the following error with gbak -backup -v webmarc.fdb
>webmarc.fbk
>
>...
>gbak: 140000 records written
>gbak: error accessing BLOB column VAL -- continuing
>gbak: ERROR: BLOB not found
>gbak: 160000 records written
>...
>
>I think that this is a bug. What do you think ?

I think you have more investigation to do and I can only offer some thoughts:

The first thing to do for your test (and in production!!) is to break the
operation into batches and commit after each 20,000 rows. There's an upper
limit on how many rows you should insert in a single transaction (in all
events!). You were lucky to get 159,999 good writes. A million is a
far-far stretch.

You have extra overhead on the server with respect to your blobs because
you are passing a string, which has to be converted to a blob before the
server can begin laying it down in segments. Since the data are identical
for every insert, you can discount invalid data. After 159,999 inserts you
began to get garbage (cross-linking of blob-ids or similar).

On the 160,000th write, the server created a blob-id but was unable to
store the blob (for whatever reason: memory? not enough disk space to
allocate another page?). You should have got an exception during the
insert operation. If you didn't, I'd consider that a bug.

I simply don't believe you ever successfully did this test on Fb 1.0.3 with
a million inserts in a single transaction.

I think you are not helping yourself by refusing to raise this problem on
the Java list.

You asked me what I think. That's what I think.

/heLen