Database crash during object replication
Database crash during object replication
Create simple table
create table product_type ( product_type_id int , product_type_name varchar(255) );
Create sequence + flattened table with nextval(sequence) as default value
create sequence seq_product; create table product ( product_id int default nextval('seq_product') , product_name varchar(255) default '' , product_type_id int default -1 , product_type_name varchar(255) default (select product_type_name from product_type where product_type.product_type_id = product.product_type_id) );
Create copy of flattened table
create table product_copy like product including projections;
This copy loses all default expressions
select export_objects('','product_copy');
CREATE TABLE public.product_copy
(
product_id int,
product_name varchar(255),
product_type_id int,
product_type_name varchar(255)
);
- Setup replication config only for product_copy table and run it
objects = product_copy
Error: SQL command "select object_snapshot('recovery_snapshot_TJB8M57213K3BQ92C77GZG8AWROS6U6O','product_copy','','',false);" failed: ERROR 7832: Table public.product_type is referenced by public.product_copy in a SET USING or DEFAULT expression, but is not included in the snapshot
HINT: Back up the tables together, or alter table removing SET USING/DEFAULT on attributes
As described before product_copy lost "flattened" default, but not completely. This is the first BUG.
In next step append all necessary objects and run replication
objects = product_copy, product_type, product
Run replication AGAIN and check the cluster. All nodes will crash
2018-11-08 09:35:09.528 DistCall Dispatch:7f50267fd700-a0000000000af7 @v_recovery_node0002: VX001/5445: VIAssert(0) failed
DETAIL: /scratch_a/release/svrtar2886/vbuild/vertica/Catalog/Table.cpp: 910
HINT: Please report this error to Vertica; try restating your query
LOCATION: ??, /scratch_a/release/svrtar2886/vbuild/vertica/Basics/VAssert.cpp:22
2018-11-08 09:35:09.537 DistCall Dispatch:7f50267fd700-a0000000000af7 @v_recovery_node0002: VX001/8238: Unable to apply catalog diff. Error: VIAssert(0) failed
LOCATION: dispatchIncomingCallMessage, /scratch_a/release/svrtar2886/vbuild/vertica/Dist/DistCalls.cpp:16917
2018-11-08 09:35:09.758 DistCall Dispatch:7f50267fd700-a0000000000af7 [Main] Wrote backtrace to ErrorReport.txt
Comments
How did you get STEP 2 to run?
Should be:
Per the doc, a CREATE TABLE LIKE statement "replicates the source table definition and any storage policy associated with it. It does not copy table data or expressions on columns."
See:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/CreateTableLike.htm
Per the crash, that's an issue! What version of Vertica are you running?
Fyi ... Vertica 9.0.1-6 has this fix:
9.0.1-6 (03/28/2018)- Running replication on a schema that contains sequences resulted in an error causing the replication to fail. In addition, it caused a node crash on the secondary cluster. This issue has been fixed.
See:
https://www.vertica.com/docs/ReleaseNotes/9.0.x/Vertica_9.0.x_Release_Notes.htm
It's my mistake - code was edited before publishing and not tested.
Original structure was simplier:
create table product ( product_id int default nextval('seq_product') , product_name varchar(255) , product_type_id int , product_type_name varchar(255) default (select product_type_name from product_type where product_type.product_type_id = product.product_type_id) );
Everything else is original and tested.
"create table like" loses all default expressions ONLY if table has identity/sequence expressions.
Version is 9.1.1-0
Please fix it. Replicated database is completely destroyed after such a failure and must be recreated from scratch.
Hi,
The doc says that CREATE TABLE...LIKE copies all table constraints, with one the exception being "Any column that obtains its values from a sequence, including IDENTITY and AUTO_INCREMENT columns. Vertica copies the column values into the new table, but removes the original constraint."
That explains why the DEFAULT with the sequence is not copied, but not why unrelated DEFAULT expressions are not copied. I opened a JIRA to see if this is expected behavior. I will update this tread when I hear back.
Per the crash, can you please open a Vertica Support Case for that? I believe that you need more attention than can be provided via the forum.
Thanks!
We are still using CE license. So I can't.
My crash can be easily reproduced. I also added ErrorReport to the first post.
Hi,
I can not reproduce the cluster failure in your example.
Here is my quick test:
My target cluster DB does not have the three tables:
On my source cluster, I will create the three tables and replicate to the target:
The target cluster (verticademos.com) is still up, and the tables were created:
@vasmez81 - Are your source and target DB running the same version of Vertica?
Please repeat replication. Crash occurs in the second replication run.
First run is always successful. After the second run, it reports "Object replication is complete!", but the DB is falling.
Both DBs has the same version 9.1.1-0
Hi,
I tried replicating 2 more times. The target DB stayed up.
Hello again!
Jim, could You check on cluster with more than one node?
I checked replication on a single node cluster. You are right, the crash is not reproduced in 9.1.1-0.
But in a 3-node cluster the problem is clear.
I was able to reproduce the target failure on a 2 node cluster. I will open a JIRA immediately and will keep this thread updated with its resolution progress. Thanks for letting us know about this!
Fyi:
Thank you for support!
Is there any estimate for fixes?
1. Dropping default for columns after copy_table / create table like
2. Replication of such tables
Hi,
Engineering has identified and fixed the issue. Waiting to hear when the code fix will be available to the community. I will update this thread as soon as I know!