Incremental Design error

Ahmet_Fuat_Sung

I am using Vertica 7.0. However I did not encounter this problem in Vertica 6.0, the problem is related to the incremental design from database designer. When I run the incremental design in admintools, after setting related parameter ( input and output file ) the design operations starts but throws an exception immediately: Database Designer started. For large databases a design session could take a long time; allow it to complete uninterrupted. Use Ctrl+C if you must cancel the session. Setting up design session... --- 2014-01-07 13:12:58 --- Raised error: Traceback (most recent call last): File "/opt/vertica/oss/python/lib/python2.7/site-packages/vertica/designer/Designer.py", line 454, in _handle_exception raise pException KeyError: 'DesignPolicy' LOG FILE: /home/dbadmin/dbd_log//designer.log REPORT THIS INFORMATION TO TECHNICAL SUPPORT AND INCLUDE CONTENTS OF THE LOG FILE IN YOUR REPORT --- designer.log content is below ---- Starting database designer ... 2014-01-07 13:12:54 [Designer.run] starting with parameters: 2014-01-07 13:12:54 [Designer._run] Beginning 2014-01-07 13:12:54 [Designer.init] Beginning: self.interactive=True 2014-01-07 13:12:54 Database Designer started. 2014-01-07 13:12:54 [Designer.fPrint] Output: Database Designer started. 2014-01-07 13:12:54 [Designer.validateSearchPath]: Validating path public 2014-01-07 13:12:54 [Designer.send_sql_text] Sending SQL: set search_path=public; 2014-01-07 13:12:54 [Designer.sendCommand] Sending commands in /home/dbadmin/dbd_log//command.sql 2014-01-07 13:12:54 Setting search path: SET search_path=public,public; 2014-01-07 13:12:54 [Designer.sendCommand] Executing: set search_path=public; 2014-01-07 13:12:54 [Designer.sendCommand] Status: 0. Result: ['SET'] 2014-01-07 13:12:55 Schema search path validated: public 2014-01-07 13:12:55 Sending export catalog request 2014-01-07 13:12:55 [Designer.sendCommand] Sending commands in /home/dbadmin/dbd_log//command.sql 2014-01-07 13:12:55 Setting search path: SET search_path=public,public; 2014-01-07 13:12:55 [Designer.sendCommand] Executing: select export_catalog('/home/dbadmin/dbd_log//catalog_dump.sql', 'design' ); 2014-01-07 13:12:55 [Designer.sendCommand] Status: 0. Result: ['Catalog data exported successfully', ''] 2014-01-07 13:12:55 [Designer.sendCommand] Executing: select export_catalog('/home/dbadmin/dbd_log//schema_dump.xml', 'projections'); 2014-01-07 13:12:55 [Designer.sendCommand] Status: 0. Result: ['Catalog data exported successfully', ''] 2014-01-07 13:12:55 For large databases a design session could take a long time; allow it to complete uninterrupted. 2014-01-07 13:12:55 [Designer.fPrint] Output: For large databases a design session could take a long time; allow it to complete uninterrupted. 2014-01-07 13:12:55 Use Ctrl+C if you must cancel the session. 2014-01-07 13:12:55 [Designer.fPrint] Output: Use Ctrl+C if you must cancel the session. 2014-01-07 13:12:55 Setting up design session... 2014-01-07 13:12:55 [Designer.fPrint] Output: Setting up design session... 2014-01-07 13:12:55 [Designer.send_sql_text] Sending SQL: select dbd_create_workspace('inc',True); 2014-01-07 13:12:55 [Designer.sendCommand] Sending commands in /home/dbadmin/dbd_log//command.sql 2014-01-07 13:12:55 Setting search path: SET search_path=public,public; 2014-01-07 13:12:56 [Designer.sendCommand] Executing: select dbd_create_workspace('inc',True); 2014-01-07 13:12:56 [Designer.sendCommand] Status: 0. Result: ['0'] 2014-01-07 13:12:56 cancelExecution called 2014-01-07 13:12:56 [Designer.send_sql_text] Sending SQL: select dbd_remove_design('inc','inc'); 2014-01-07 13:12:56 [Designer.sendCommand] Sending commands in /home/dbadmin/dbd_log//command.sql 2014-01-07 13:12:56 Setting search path: SET search_path=public,public; 2014-01-07 13:12:56 [Designer.sendCommand] Executing: select dbd_remove_design('inc','inc'); 2014-01-07 13:12:56 [Designer.sendCommand] Status: 0. Result: ['ERROR 3480: Given design inc does not exist'] 2014-01-07 13:12:57 [Designer.send_sql_text] Sending SQL: select dbd_drop_design('inc','inc'); 2014-01-07 13:12:57 [Designer.sendCommand] Sending commands in /home/dbadmin/dbd_log//command.sql 2014-01-07 13:12:57 Setting search path: SET search_path=public,public; 2014-01-07 13:12:57 [Designer.sendCommand] Executing: select dbd_drop_design('inc','inc'); 2014-01-07 13:12:57 [Designer.sendCommand] Status: 0. Result: ['ERROR 3480: Given design inc does not exist'] 2014-01-07 13:12:57 [Designer.send_sql_text] Sending SQL: select dbd_drop_workspace('inc'); 2014-01-07 13:12:57 [Designer.sendCommand] Sending commands in /home/dbadmin/dbd_log//command.sql 2014-01-07 13:12:57 Setting search path: SET search_path=public,public; 2014-01-07 13:12:58 [Designer.sendCommand] Executing: select dbd_drop_workspace('inc'); 2014-01-07 13:12:58 [Designer.sendCommand] Status: 0. Result: ['0'] 2014-01-07 13:12:58 --- 2014-01-07 13:12:58 --- Raised error: Traceback (most recent call last): File "/opt/vertica/oss/python/lib/python2.7/site-packages/vertica/designer/Designer.py", line 454, in _handle_exception raise pException KeyError: 'DesignPolicy' LOG FILE: /home/dbadmin/dbd_log//designer.log REPORT THIS INFORMATION TO TECHNICAL SUPPORT AND INCLUDE CONTENTS OF THE LOG FILE IN YOUR REPORT 2014-01-07 13:12:58 [Designer.fPrint] Output: --- 2014-01-07 13:12:58 --- Raised error: Traceback (most recent call last): File "/opt/vertica/oss/python/lib/python2.7/site-packages/vertica/designer/Designer.py", line 454, in _handle_exception raise pException KeyError: 'DesignPolicy' LOG FILE: /home/dbadmin/dbd_log//designer.log REPORT THIS INFORMATION TO TECHNICAL SUPPORT AND INCLUDE CONTENTS OF THE LOG FILE IN YOUR REPORT

[Deleted User]

Hi Ahmet,

Hm...  I haven't seen this issue before.  Certainly not particularly helpful...

The log appears to start erroring out after reading the file "/home/dbadmin/dbd_log//command.sql".  Does that file contain valid queries?  I'm wondering if something there is amiss, and we're not reporting the error very clearly.

Adam

Ahmet_Fuat_Sung

The query exactly the same as in the comprehensive design. For same query vertica did not throw error in the comprehensive design. But in incremental design it gives that error. 

The query is below:

select * from public.people where id = 6;

If I copy that sql it runs in the vsql. 

By the way I think if the query is invalid then vertica should give true information about the error. As you see in the report it says just the "report this to the vertica support"

Thanks

[Deleted User]

I think you're correct that Vertica should report true information about the error.  And usually it does.  However, in this case it's obviously not reporting true information about the error :-)  So something's going wrong.  I'm trying to figure out what's wrong; once we know that, we can both tell you what to do to avoid the issue now, and update it to give a better error message in the future.

It sounds like the query is fine, so that's not triggering the problem.  Maybe it's one of the options that's being passed to the DBD?  Could you post exactly how you're invoking adminTools?  With what command-line options; what answers you give to each question; etc.

Ahmet_Fuat_Sung

I started to admintools.

Then > Configuration Menu > Run Database Designer > Choose the "testdb as dbadmin"> Typed password > /home/dbadmin/dbd_log for database designer output > MyIncDesign1 > Incremental > Update Statistics ( not choose the deploy ) > /home/dbadmin/test1.sql as input sql file > Then proceed. 

Thats really interesting I could not solve yet. What am I missing I don't know. 

By the way content of the input sql:

[dbadmin@vertica01 ~]$ cat test1.sql

select *  from public.people where id = 6;

[dbadmin@vertica01 ~]$

[Deleted User]

Hm...  That is odd indeed.  I'm able to reproduce it here, though.  Let me poke around a little and see what I can find out.

Joe_Gonzalez

I am interested in the reply, I am experiencing the same issue in Vertica 7.0

Ahmet_Fuat_Sung

Hi Adam,

Any update about the issue?

Joe_Gonzalez

Ahmet -
I just received a response from the Vertica person I am working with.  It is a coding issue that you can fix manually.  Here are the details:

the DBD bug was introduced in Vertica 7.0. Internally, we renamed “Query-specific” to “Incremental”, but there was one line of code that missed the change. It is line 1874 in /opt/vertica/oss/python/lib/python2.7/site-packages/vertica/designer/Designer.py.

 

Old line:

if (self.newParams['DesignType'] == 'Query-specific’):

 

New fix:

if (self.newParams['DesignType'] in ('Query-specific', 'Incremental')):

Good luck!

Joe

RobJuntCrowell

Thanks Joe, I was encountering this bug and your fix took care of the issue for me.

Good thing some parts of Vertica are actually user serviceable...

Ahmet_Fuat_Sung

Thanks Joe, it worked! 

I think that HP will be fixing this bug for incoming release.

Mrao

Incremental and Query Specific mean the same or different?  If different, pls explain the difference.  If same, it was just that option was renamed in vertica 7?

Joe_Gonzalez

From what I am told, they are the same thing.  It was just renamed.