From: Johan Hallgren (johan.hallgrenbrightpoint.com)
Date: Wed Mar 28 2007 - 15:32:15 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Steffen,

Thanks for your prompt reply. Changing the COLUMNCOMPRESSION parameter
on my 7.6 instance to NO did indeed fix the issue, and I am now able to
load the data without errors. Just to make sure I understand the
process, could you please confirm that I have understood this correctly:

* Is it correct that if I do the export and import as PAGES (as opposed
to RECORDS), I need to make a backup of my target database after loading
the data, before I can make any changes to the loaded tables?
* Is it correct that changing the COLUMNCOMPRESSION to NO will mean that
the storage requirements of the database will be somewhat larger than if
it was set to yes, but it is a setting that is completely transparent to
the user of the database (e.g. I assume that a varchar(10) field still
would behave exactly the same in SQL statements regardless of the
setting of the COLUMNCOMPRESSION parameter)?
* I did have the one issue when loading that because the schema contains
a couple of circular foreign key relationships (where two tables have
FKs that reference each other) I got a referential integrity violation
error when first importing the catalog and then the data. Instead I had
to manually create the schema (minus the foreign keys), load the data,
and then manually create the foreign keys. I assume this is the way it
has to work, and had I not had these circular FKs, I could have imported
the catalog using the loader?

Thanks again for your assistance!

Regards,
Johan H

On Wed, 2007-03-28 at 09:43 +0200, Schildberg, Steffen wrote:
> Hi Johan,
>
> Johan Hallgren [mailto:johan.hallgrenbrightpoint.com] wrote:
> >
> > I am trying to export data using the loadercli from an ASCII database
> > instance running on a 7.4.3 BUILD 027-121-048-452 database kernel, to
> > another ASCII database instance running on a 7.6.00 BUILD
> > 034-121-134-685 database kernel. I am using the loadercli that came
> > installed with the 7.6 database, and am specifically trying to do the
> > following:
> >
> [snipped successful export cmds]
>
> >
> > 2. Import the same catalog and data into the 7.6 database,
> > using a user with the same user name as the one in the 7.4 database:
> >
> > AUTOCOMMIT ON
> > //
> > SQLMODE INTERNAL
> > //
> > SET DATE USA
> > //
> > SET MAXERRORCOUNT 1
> > //
> > IMPORT USER CATALOG INSTREAM 'usercatalog.txt'
> > //
> > IMPORT USER DATA INSTREAM 'userdata.txt'
> >
> > This works fine until the last command, which errors out with:
> >
> > Error during execution
> > -->-25342
> > Error restoring table TABLENAME; table exists but source table schema
> > and target table schema are different
> >
> > At this point, thinking that issue may be that maybe there's a foreign
> > key being applied that makes the import fail due to the
> > tables not being
> > imported in the correct order, I used the exported schema
> > definition to
> > manually create the new schema minus the foreign keys, and then just
> > execute the import data statement. However, this yields the
> > same error.
> > Also, the table on which the first error occurs has no foreign keys
> > defined.
> >
> I'm pretty sure this is a problem of the database parameter COLUMNCOMPRESSION
> which might be set to YES for your 7.6 database but does not exist on your
> 7.4 database. This parameter defines the length type of non primary key columns -
> whether they are variable long or fixed long. To verify this please have a look
> into the log file of the Loader. There should be a more detailed message logged.
> Additionally check the catalog file 'usercatalog.txt' for create table statements.
> Those statements should have the appendix NO FIXED LENGTH. If they do not have
> the appendix you can do the following to overcome the problem:
> - drop the user in your target database and recreate it
> - set the database parameter COLUMNCOMPRESSION (this is a support parameter) to NO
> forcing all newly created tables to have numeric columns and char-columns with length
> less than 31 bytes fixed length; thus the newly created tables will have the same
> internal representation as the tables in your 7.4 database have
> - run the import again
>
> If this fails again I would be interested in the Loader log file and the catalog file.
>
> Best regards,
> Steffen

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]