Error when running Vertica Database Designer

Eakan_GK

Hi, I posted this question under another topic (https://community.vertica.com/vertica/topics/error_generating_deploy_in_database_designer) but didn't seem to get any sort of response. so i decided to start a new topic. I keep getting this error every time I run the DB designer with the options mentioned above (pasting below for reference): A query specific design The KSafety for the cluster is 2 Optimization for query performance and gave 4 schemas to generate the designs for. This time i gave it a file with 9 queries (i reduced the number of queries just to figure out which query was causing this problem). And it fails without pointing out any query in particular: 2013-06-28 15:45:57 Query 1 optimization ratio or status is 1 Query 2 optimization ratio or status is 1 Query 3 optimization ratio or status is 1 Query 4 optimization ratio or status is 1 Query 5 optimization ratio or status is 1 Query 6 optimization ratio or status is 1 Query 7 optimization ratio or status is 1 Query 8 optimization ratio or status is 1 Query 9 optimization ratio or status is 1 And somewhere down the log I get this: 2013-06-28 15:47:00 [Designer.sendCommand] Executing: SELECT count(*) from "v_dbd_fma20120628_1_fma20120628_1".output_event_history_report where stage_type IN ('Error during deployment', 'Error during design'); 2013-06-28 15:47:00 [Designer.sendCommand] Status: 0. Result: ['1'] 2013-06-28 15:47:01 [Designer.sendCommand] Executing: SELECT max(current_step_description) from "v_dbd_fma20120628_1_fma20120628_1".output_event_history_report where stage_type IN ('Error during deployment', 'E\ rror during design'); 2013-06-28 15:47:01 [Designer.sendCommand] Status: 0. Result: ['Deployment did not complete successfully. ERROR - Subquery used as an expression returned more than one row'] 2013-06-28 15:47:01 Could not generate default deployment script. I know you would suggest that the queries are not working. But i have verified them manually but the DBD just won't run. Any input would be appreciated.

[Deleted User]

Hi Eakan, If you're running at a K-safety of 2, I assume you're an enterprise customer? (Since K=2 is not useful if you only have three nodes, the community limit; regardless of K-safety you always must have more than half of the nodes up, otherwise the cluster might split in half.) In that case, since you haven't gotten any input, I would encourage you to open a support case. I don't recognize this issue; at the very least it's certainly unfortunate that the error message doesn't tell you what to do... It would be helpful (either there or here) if you could keep trying to narrow down the issue. It sounds like it's not a particular query. Is it something particular about one of your schemas or tables that you're querying? About the data in those tables? Adam

Eakan_GK

Hey Adam, Thanks for responding. I did post part of this in another topic. But i didn't get a response there as it was marked answered previously. https://community.vertica.com/vertica/topics/error_generating_deploy_in_database_designer You can see the very little progress I made there. But I am not sure what the Database Designer does when it is being run and what it checks to stop running.

Eakan_GK

Also wanted to add here the method I used to narrow down the issue. I initially gave the Database designer a file with 69 queries or so. Then I divided the queries to some 7 files and ran database designer multiple times for each of the input files. It seems that some times it worked, other times it failed. I tested the queries several times after failures were limited to some files. Then I identified the problems with having invalid objects like views without underlying tables that might have caused it to fail. I am not even sure if that is some thing that Database designer checks before failing. But for me, it did seem to work for a few more files after that. Is there a way to identify invalid objects in the database by querying some system table?

[Deleted User]

Hi Eakan, Thanks for posting a new topic -- but by "open a support case", I meant to go into http://my.vertica.com/ and set up something through the "Support" section. It's not too surprising to me that you didn't get a response here. It's a tough question; that's why I recommended that you get in touch with Support, so that they can help you. Regarding queries and errors: The DBD does expect you to give it queries that you are running regularly. Which, of course, means valid queries. If your queries error out, then the DBD can error out. Don't bother running the DBD until you have debugged your queries; made sure they are valid, made sure they produce the results you want, make sure they don't fail intermittently if they're operating on a subset of your data (since SQL allows for data-dependent failures), etc. Recall that there's no need to give the DBD every query that people run against your system; just give it the ones that you use regularly and know work. Adam

Eakan_GK

Thanks Adam. I did understand what you meant by setting up something through the support section. i was just telling you that I posted my problem as part of another topic earlier to which I didn't really get a reply. I guess it was because that topic was marked 'Answered' earlier. The invalid database objects I mentioned earlier was not being referred to by any of the queries but they just happen to exist in the database. Yes, I am only giving the database designer a subset of queries that usually get run. We are yet to implement the rest of the queries. We were working on a proof of concept and Vertica seemed to be performing well compared to the other database technology that we use currently. This input set of 64 queries are just a basic set of queries that covers different categories of data. We have to run such queries on a daily basis to extract data from several schemas, all of which we have access to. Thank you for the detailed reply. I shall get in touch with Support as you suggested.