Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#23 DatabaseManager: DUMP/RESTORE issues

open-accepted
nobody
None
6
2004-05-29
2004-05-28
Robert Caranica
No

Hello,

I want to report the following issues of the
org.hsqldb.util.DatabaseManager tool:

1) The DUMP feature (Tools->Dump) generates a wrong
type in the CREATE TABLE sql for the IDENTITY
columns.

Example:

- Original CREATE TABLE statement:

CREATE TABLE USER_T(
USER_ID INTEGER NOT NULL IDENTITY PRIMARY KEY,
USER_NAME_TX VARCHAR NOT NULL,
USER_PASSWORD_TX VARCHAR NOT NULL,
USER_EMAIL_TX VARCHAR,
USER_FULLNAME_TX VARCHAR,
USER_DESC_TX VARCHAR,
CONSTRAINT SYS_CT_25 UNIQUE(USER_ID),
CONSTRAINT SYS_CT_26 UNIQUE(USER_NAME_TX)
)

At this point the following query works OK:

insert into user_t(user_name_tx, user_password_tx)
values('aa','bb')

- What DUMP generates:

CREATE TABLE USER_T(
USER_ID INTEGER,
USER_NAME_TX VARCHAR,
USER_PASSWORD_TX VARCHAR,
USER_EMAIL_TX VARCHAR,
USER_FULLNAME_TX VARCHAR,
USER_DESC_TX VARCHAR,
CONSTRAINT SYS_PK_USER_T PRIMARY KEY (USER_ID)
)

So,what about the NOT NULL(s) and, most importantly
where is the IDENTITY gone ?

After RESTORE'ing the database, the following query
(that used to work) doesn't work any more:

insert into user_t(user_name_tx, user_password_tx)
values('aa','bb')

Error is: "Try to insert null into a non-nullable column in
statement [...] Error Code:-10 / State: 23 000"

As no user_id is specified (user_id is supposed to be an
IDENTITY), the query in the second case fails, because
in the second case user_id is not an IDENTITY anymore.

2) The RESTORE feature doesn't restore a DUMP-ed file
with only the INSERTs.

How to reproduce:

1. Create some tables in the database and fill them with
data. Keep the CREATE TABLEs handy, as you'll reuse
them later.
2. Choose Tools->Dump, and for _each_ table in the left
leave only the "Transfer to
destination table" and "Insert into destination" checked.
(As a side note, we should have had a global check for
these, but you have to do it for each table; for few
tables it's not a problem, but if I have a lot of tables,
this becomes a nightmare to uncheck them all)
3. Run the DUMP, which generates only INSERTs like it's
supposed to.
4. DROP all tables and recreate the empty tables by
PASTE'ing the CREATE TABLE SQL statements into the
HSQL Database Manager's Textarea and click "Execute".
5. Then choose Tools->Restore,select the file already
DUMPed and press "Start Restore"
6. The "Transfer finished successfully in: 0.0 sec"
message is displayed but the data isn't loaded!

Btw, a TRANSFER like the one above, between two
databases, which involves only copying of data
(SELECTs and INSERTs) works! (so only only
the "Transfer to destination table" and "Insert into
destination" to be checked in the TRANSFER window)

I hope all the details that I provided will be useful in
tracking down the cause for this behavior.

Thanks,
Robert

PS: Software info:
WindowsXP
J2SE 1.3.1_04
JBoss 3.2.2 (with Jetty 4.2.11 and Hypersonic DB 1.7.1)

Discussion

    • priority: 5 --> 6
     
  • Fred Toussi
    Fred Toussi
    2004-05-29

    • labels: 325100 -->
    • milestone: 116100 -->
    • status: open --> open-accepted
     
  • Fred Toussi
    Fred Toussi
    2004-05-29

    Logged In: YES
    user_id=150940

    Thanks,

    This has been reported before, we need volunteers to
    improve the scripting output.

    Moved to HSQLDB Utilities tracker.

     
  • alan johnson
    alan johnson
    2004-07-19

    Logged In: YES
    user_id=943591

    i think this would be worth
    looking into