| Subject | Re: [firebird-support] Error on backup |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-05-27T23:47:55Z |
At 12:14 AM 28/05/2005 +0200, you wrote:
was defined to be of sufficient length to accommodate a DATE type, i.e. 11
or more characters. However, changing the domain definition does not
convert the data. That is something you will have to do yourself, before
attempting to make a backup that you can restore.
To focus on what happens during backup and restore, for these columns:
At backup, the metadata definitions are stored first and then all data is
copied in "lumps", in a platform-neutral format, separately from the metadata.
At restore, the metadata objects are recreated first; then the "lumps" are
parsed and converted back to their respective data type formats and pumped
into the tables.
The problem you're having is that the parser can't make sense of the old
date strings you had in those fields affected by the domain change.
DATE and TIME types are stored in a numeric format, not as strings. The
engine has a conversion interface that enables you to submit valid
date/time literal strings in SQL when referring to them. During a restore,
as long as your original strings had been in a valid format, you could get
away with hoping that the restore would do the right thing -- not that I
would recommend anyone taking that chance.
For example, if your old field was a varchar(12) that stored dates as
strings of the format 'dd/mm/ccyy', Firebird will throw a conversion error
on a string like '25/12/2004', because slash-separated dates can only be
'mm/dd/ccyy' or 'mm/dd/yy'. There's a fairly large range of date literal
strings that Firebird will accept, but 'dd/mm/ccyy' and 'dd/mm/yy' are not
among them.
needle-in-the-haystack stuff, since it gets back to the need to fix up data
always when you change its domain - preferably BEFORE you change the
domain. You'll bump into another sort of problem, for example, if the old
string data stored an empty string for null dates because the column was
not nullable (or just because your application took this as an easy
route). An empty string doesn't convert to any sort of date...there is no
"zero-date" that you can store in a non-nullable DATE column.
definitions in the belief that the database engine will fix up your
anomalous data, you are in for this nasty surprise.
You should have backed up the database before you did these changes, so you
have something to fall back to.
Don't change domain or field definitions without having a plan in place to
check and fix the data that are affected.
Backup stores the compiled code of your SPs and restore restores
that. Restore doesn't recompile SPs from source code...in fact, people
sometimes remove the source code from their PSQL modules, and these modules
still restore just fine.
If the changed fields have been used in triggers or SPs which have
assumptions about the data types of those fields, then be prepared, as part
of your metadata change plan, to alter those modules to make them correct
for the new data type, e.g. an old varchar(12) local variable that's now
not going to match up with a field that now contains DATE data.
And so on...
./heLen
>I get this errorYes. The domain change would have been allowed if the old varchar column
>
>Arithmetic overflow or division by zero has occurred.
>arithmetic exception, numeric overflow, or string truncation.
>
>When I try to bakup my database, I have tried to google for it but have
>found nothing related to backups.
>
>The database appears to be working well enough but it is a little bit
>scary to not be able to back it up. I don't get any errors when I validate it.
>
>I have made alot of changes since the last backup, such as adding fields
>and changing domains etc. I also noticed that I could change the domain
>of a column from string to date without any errors even though it was
>refferenced from a few SPs but I didn't give it any thought at the time.
>Might it be related in some way?
was defined to be of sufficient length to accommodate a DATE type, i.e. 11
or more characters. However, changing the domain definition does not
convert the data. That is something you will have to do yourself, before
attempting to make a backup that you can restore.
To focus on what happens during backup and restore, for these columns:
At backup, the metadata definitions are stored first and then all data is
copied in "lumps", in a platform-neutral format, separately from the metadata.
At restore, the metadata objects are recreated first; then the "lumps" are
parsed and converted back to their respective data type formats and pumped
into the tables.
The problem you're having is that the parser can't make sense of the old
date strings you had in those fields affected by the domain change.
DATE and TIME types are stored in a numeric format, not as strings. The
engine has a conversion interface that enables you to submit valid
date/time literal strings in SQL when referring to them. During a restore,
as long as your original strings had been in a valid format, you could get
away with hoping that the restore would do the right thing -- not that I
would recommend anyone taking that chance.
For example, if your old field was a varchar(12) that stored dates as
strings of the format 'dd/mm/ccyy', Firebird will throw a conversion error
on a string like '25/12/2004', because slash-separated dates can only be
'mm/dd/ccyy' or 'mm/dd/yy'. There's a fairly large range of date literal
strings that Firebird will accept, but 'dd/mm/ccyy' and 'dd/mm/yy' are not
among them.
>I tried emptying some ofending tables (where the error appeared to happen)It could have been one or more possible anomalies in date string formats -
>and it worked a little (atleas it moved the error tosomewhere else) but
>now I am stuck, it didn't help emptying the T_UPLOADS table.
needle-in-the-haystack stuff, since it gets back to the need to fix up data
always when you change its domain - preferably BEFORE you change the
domain. You'll bump into another sort of problem, for example, if the old
string data stored an empty string for null dates because the column was
not nullable (or just because your application took this as an easy
route). An empty string doesn't convert to any sort of date...there is no
"zero-date" that you can store in a non-nullable DATE column.
>I don't know what information to supply to help in sorting this out butGbak doesn't do any validation during backup; so, if you change metadata
>here are the last rowns of the gbak output:
>
>gbak: writing parameter CARDHOLDER_LAST_NAME for stored procedure
>gbak: writing exceptions
>gbak: writing Character Sets
>gbak: writing Collations
>gbak: writing data for table T_TRANSACTION_ROW_CONTENT
>gbak: 0 records written
>gbak: writing index RDB$PRIMARY7
>gbak: writing data for table T_CART_ROWS
>gbak: 0 records written
>gbak: writing index PK_T_TRANSACTION_ROWS
>gbak: writing data for table T_TRANSACTION_ROWS
>gbak: 0 records written
>gbak: writing data for table T_CART_ROW_CONTENT
>gbak: 0 records written
>gbak: writing index PK_T_UPLOADS
>IBE: Backup completed. Current time: 00:03:46. Elapsed time: 00:04:15
definitions in the belief that the database engine will fix up your
anomalous data, you are in for this nasty surprise.
You should have backed up the database before you did these changes, so you
have something to fall back to.
Don't change domain or field definitions without having a plan in place to
check and fix the data that are affected.
Backup stores the compiled code of your SPs and restore restores
that. Restore doesn't recompile SPs from source code...in fact, people
sometimes remove the source code from their PSQL modules, and these modules
still restore just fine.
If the changed fields have been used in triggers or SPs which have
assumptions about the data types of those fields, then be prepared, as part
of your metadata change plan, to alter those modules to make them correct
for the new data type, e.g. an old varchar(12) local variable that's now
not going to match up with a field that now contains DATE data.
And so on...
./heLen