Forum: Other

Post: trouble with restore and policy

trouble with restore and policy
Ilia
Jun 26, 2015
Hello, guys.
I use VMware image voltdb and That’s my problem, look at:
1.Voltdb create
2.Sqlcmd
<1 Create table a( name varchar(10));
<2 Show tables;
59
I see it.
Then
3.Voltadmin save
61
Ok, allright. Next I wanna restore this snapshot.
4.I do new DB: contrl+C
5.voltdb create
6.Voltdb recover
60
She write success, but when I wanna see my tables with sqlcmd show tables; its null wtf?
Voltdb recover working right but voltadmin restore not working. What's my problem?

Another problem.

Deployment.xml fight
<security enabled="true"/>
<users>
<user name="admin"
password="superman"
roles="administator"/>
<user name="mitty"
password="thurber"
roles="user"/>
</users>

1.Voltdb create –d deployment.xml
2.Voltadmin shutdown –u admin –p superman working
3.Sqlcmd –u –p not working INVALID ARGUMENT realy?

Please help me with this two problems, thank you.
pzhao
Jun 26, 2015
Ilia,

For your first question, there seems to be a failure that occurs prior to results being displayed. Verify that you are allowed to write files in that directory as well as /home/voltdb/examples/voltdbroot/snapshots exists.

As for your second problem, the sqlcmd options for password is '--password=superman' and username is '--user=admin". For more information, try 'sqlcmd --help'.

Peter Zhao
Ilia
Jun 27, 2015
Ilia,

For your first question, there seems to be a failure that occurs prior to results being displayed. Verify that you are allowed to write files in that directory as well as /home/voltdb/examples/voltdbroot/snapshots exists.

As for your second problem, the sqlcmd options for password is '--password=superman' and username is '--user=admin". For more information, try 'sqlcmd --help'.

Peter Zhao

Peter i atach srenshots for all my action, directory is right, and i have snapshot on there.
second: i read options for sqlcmd and my action is right, but ty, i try it more
Ilia
Jun 29, 2015
Peter can u help me understood how right do snapshot save and restore.
My example:
voltdb create
sqlcmd
create table a(name varchar(5));
voltadmin save DIRECTORY "snap"
then
voltdb create
sqlcmd
voltadmin restore DIRECTORY "snap", and bd empty.
What i do wrong. I read manual and dont understand for what need voltdb compile and then voltdb create catalog.jar, when i do this options i can restore snap right, can u explain it me.
Then about security in deployment.xml we create users and roles, but we can create only role=administator and user, not other how 12.3 UsingVoltDB. i dont understand it. how i can create users with different roles for procedures, example: user=watcher can use only select, user=reader can use procedures insert,select. How i can do it.
And last how to use <export>.
pzhao
Jun 29, 2015
Ilia,

You need to reload the schema prior to doing a 'voltadmin restore' of the data. Your schema should exist prior to doing a restore. There's no data in your tables to save which in turn restores no data. Try inserting a row. More documentation in chapter 13, saving and restoring a voltdb database.

Therefore your sequence of events should be:
voltdb create
sqlcmd
create table a(name varchar(5));
insert into a values ('a');
voltadmin save DIRECTORY "snap"
then
voltdb create
sqlcmd
create table a(name varchar(5));
voltadmin restore DIRECTORY "snap"

Regarding export, we have a detailed chapter in our documents. Please take a look here.
pzhao
Jun 29, 2015
Ilia,

Regarding security, we have a chapter that explains this here. In a nutshell, you need to enable security, and create users/password/roles in the deployment file. Then, assign permissions and access to the roles in your schema.

In your case, your deployment.xml should look something like this:
<deployment>
...
<security enabled="true' />
<users>
<user name="watcher" password="password" roles="selectonly" />
<user name="reader" password="password" roles="dbadmin" />
</users>
...
</deployment>

Add ddl to your database via sqlcmd:
...
create role selectonly WITH sqlread;
create role dbadmin WITH sql;
CREATE PROCEDURE ALLOW sql FROM CLASS MyProc1;
CREATE PROCEDURE ALLOW dbadmin FROM CLASS MyProc2;
...

Let me know how this works out.

Peter Zhao
Ilia
Jun 29, 2015
Ilia,

<deployment>
...
<security enabled="true' />
<users>
<user name="watcher" password="password" roles="selectonly" />
<user name="reader" password="password" roles="dbadmin" />
</users>
...
</deployment>

its wrong, roles can be only administator or user, if i write selectonly she writes warrning
look at screen62
pzhao
Jun 29, 2015
Ilia,

The error your seeing is stating there needs to be a user with administrator role:
ERROR: Error validating deployment configuration: Cannot enable security without defining at least one user in the built-in ADMINISTRATOR role in the deployment file.
FATAL: Error validating deployment configuration: Cannot enable security without defining at least one user in the built-in ADMINISTRATOR role in the deployment file.

You'll need to add this line to the list of users:
<user name="admin" password="password" roles="administrator" />
Then you can use the accounts you've specified to access sqlcmd:
sqlcmd --user=reader --password=password
SQL Command :: reader@localhost:21212
1> exit

if i write selectonly she writes warrning

This warning is letting you know that there are users being defined and assigned roles that do not exist:
WARN: User "watcher" is assigned to non-existent role "selectonly" and may not have the expected database permissions.
WARN: User "reader" is assigned to non-existent role "dbadmin" and may not have the expected database permissions.
Database does not have any prior knowledge of the existence of the roles 'selectonly' and 'dbadmin', thus the warning being presented. They will exist when execute the ddl I've stated earlier.

Peter Zhao
pzhao
Jun 29, 2015
Ilia,

I noticed you edited a prior post and I am not sure if you still have a problem.
Please feel free to ask any questions.

Peter Zhao
Ilia
Jun 30, 2015
yes i understand how restore snapshot and policy work, another question: can i store procedures and ROLES with CREATE ROLES on storage? or i can do it only ddl.sql file? and i dont understand how export work, look at my sreen:6465 i dont see export csv file in disk
and we have only web interface are localhost:8080?
pzhao
Jul 2, 2015
Ilia,

Sorry for the delay in response.
can i store procedures and ROLES with CREATE ROLES on storage? or i can do it only ddl.sql file?

Schema definition needs to be specified in ddl.sql.

For your export issue, it looks like you have a typo in 'period' property. Added ensure your ddl.sql has a table defined for export as well as export table. http://docs.voltdb.com/UsingVoltDB/ExportProjectFile.php

we have only web interface are localhost:8080?

Yes. This connects you to our VoltDB Management Center, VMC, to monitor and administrator your cluster.

Peter Zhao
Ilia
Jul 2, 2015
can someone answer my question about export?
pzhao
Jul 2, 2015
Ilia,

Were you able to try my suggestion in the prior post?

Peter Zhao
Ilia
Jul 2, 2015
Ty, Peter, i try it more.
One more question, look my sreen i experement with policy and dont understand for what need defaultprocread and defaultproc and why i can use proc with allow roles and non priviligens. 66 u can see deployment file. shcema file and result on excel.
And one more, if u wanna update deployment i must use voltadmin update catalog.jar -d deployment.xml command, but i must configure deploymnet on shcema=catalog in cluster graf and cant create table in base, cant write .sql file, if i wanna create table and procedure in db i cant update?
i can update shema and deployment with catalog OR i cant update my deployment but can change shcema in DB? Right? how i can update deployment file only voltadmin update?
Ilia
Jul 4, 2015
can u answer on question with policy test?
one more question how i can save my currenst schema. In web censter:8080 i have buttom save ddl schema, how i can do it in terminal?
Have VoltDB possibility of audit, for example, i want now how user what do, create table, delete and other sql query, can Log4j do this?
pzhao
Jul 8, 2015
Ilia,

One more question, look my sreen i experement with policy and dont understand for what need defaultprocread and defaultproc and why i can use proc with allow roles and non priviligens. u can see deployment file. shcema file and result on excel.

Unfortunately, I can't see your attachment to clearly to help you. Defaultprocread and defaultproc are special permissions you can assign to roles. Full details can be found here.

And one more, if u wanna update deployment i must use voltadmin update catalog.jar -d deployment.xml command, but i must configure deploymnet on shcema=catalog in cluster graf and cant create table in base, cant write .sql file, if i wanna create table and procedure in db i cant update?

Deployment file can be updated via 'voltadmin update'. 'schema=catalog' is used for backward compatibility with application catalog in v4 and lower. Updating your deployment file will not affect your ability to use live ddl, i.e. create table and procedures. This is explained here.

i can update shema and deployment with catalog OR i cant update my deployment but can change shcema in DB? Right? how i can update deployment file only voltadmin update?

Update your deployment file with 'voltadmin update' and use our live ddl with sqlcmd to change the schema. The use of application catalogs is deprecated and may be removed in a future release of VoltDB.

one more question how i can save my currenst schema. In web censter:8080 i have buttom save ddl schema, how i can do it in terminal?

Unfortunately, there is no terminal method. Utilizing our VMC isthe most convenient way to get the current schema.

Have VoltDB possibility of audit, for example, i want now how user what do, create table, delete and other sql query, can Log4j do this?

Log4j can be configured to log execution of adhoc queries. The default log4j.xml file is located in <voltdbroot>/voltdb/log4j.xml. Within this file, you'll want to uncomment 'COMPILER' and 'SQL' logger. More information can be found here.

Peter Zhao
Ilia
Jul 10, 2015
can voltdb watch activity of user and kill session of users? i dont fint it
bballard
Jul 13, 2015
Hi Ilia,

Yes and no. You can call "exec @Statistics LIVECLIENTS 0;" in sqlcmd or through the web interface to see currently connected clients and some information about their outstanding requests. However, there is no way to kill running transactions, abort outstanding requests, or kill sessions.

-Ben
Ilia
Jul 13, 2015
ty Ben
look if i have 10 of more clients who used VoltDB with 10 users and i wanna kill some aplications what i can do? kill aplications on client? why i cant kill user's session to close the application?
pzhao
Jul 13, 2015
Ilia,

Correct, you can kill the client. On the server side, you can monitor the active client connections. Killing connections is not a feature at this time. We do have a timeout on long running readonly queries or stored procedures.

Peter Zhao
Ilia
Jul 14, 2015
question about type of data. i find table with type of data only in planningguide, and i dont see type of data how date, time and other types. why so little?
bballard
Jul 14, 2015
Hi Ilia,

The supported datatypes are listed on the reference page in Using VoltDB for CREATE TABLE.

-Ben
Ilia
Jul 16, 2015
Hi, question about replica
look at my action:
1.create master.xml with <dr id="1" listen="true" />
2.create replica.xml with <dr id="2" listen="true">
<connection source="192.168.1.1" />
</dr>
3.master:voltdb create -d master.xml -H 192.168.1.1
4.repcila:voltdb create --replica -H 192.168.2.1 -d replica.xml --admin=43434 and other port change for starting replica on 1 local machine, all ok, all worked
then
5.on replica and master same dr table, all ok.
6.all worked, i insert row in master its replicted on replica all ok.
7.then i change master.xml and write listen=false, then voltdadmin update
8. i insert row on master and this row dont replicated on replica, all ok
9.i change master.xml and write listen=true, then voltdadmin update
10. i write select* on replica and dont see last row, w8 some time and dont see last row. its not ok
11. i insert row on master and this row dont replicated on replica, its not ok
12. i shutdonw replica, create new replica, use select* on relica and see all row how on master, all ok
13. if i wanna change replica.xml and update this with voltadmin update he write error, i cant change replica.xml
14. if i update replica.xml with no changes i can do it, if i change replica.xml i cant update.
15. i shutdown master, write voltadmin promote replica, all ok, all work
but now i cant change replica.xml(new master) i want delete <connection source="master" /> on new master, but cant.
and when i create new replica i must change <connection source="newmaster" />, and when i create replica he cant connect.
i change id or ip error.
67

i dont undestand how it works.
pzhao
Jul 16, 2015
Ilia,

Lets take a look at each issue you've encountered.


7.then i change master.xml and write listen=false, then voltdadmin update
8. i insert row on master and this row dont replicated on replica, all ok
9.i change master.xml and write listen=true, then voltdadmin update
10. i write select* on replica and dont see last row, w8 some time and dont see last row. its not ok
11. i insert row on master and this row dont replicated on replica, its not ok
12. i shutdonw replica, create new replica, use select* on relica and see all row how on master, all ok

Here you run into a problem where you've already enabled DR and you turned it off. When you turn DR off in this manner, step 7, and reconnect it, step 9, this is expected output, step 10 and 11. This is not the intended usage of DR. In order to re-establish replication, you'll need to restart the replica, step 12.

13. if i wanna change replica.xml and update this with voltadmin update he write error, i cant change replica.xml
14. if i update replica.xml with no changes i can do it, if i change replica.xml i cant update.
15. i shutdown master, write voltadmin promote replica, all ok, all work
but now i cant change replica.xml(new master) i want delete <connection source="master" /> on new master, but cant.
and when i create new replica i must change <connection source="newmaster" />, and when i create replica he cant connect.
i change id or ip error.

There are restrictions to what can and can not update with the deployment file. Please show us your 'replica.xml' file so we can understand what you are trying to do for both steps 13 and 14.
For step 15, please show us both your 'master.xml' and 'replica.xml'. Is your intent in step 15 to have a master/replica, kill master and promote replica to new master, and then make the old master a new replica? Please refer to here for full documentation of acceptable changes to deployment.xml file.

Peter Zhao
Ilia
Jul 17, 2015
Hi, Peter I understood how it works.
If I have this configurations file all works righ.
7879
About my question for step 13 and 14.
IP master=192.168.1.134, IP replica=192.168.1.135 –replication=6666 and other changed ports
I have this configuration file
8081
Then I do shutdown muster and promote replica

Then I wanna update my rep.xml with no changed, and all ok

And now I want change <connection> on my replica.xml and I take this error
82
Tell me why I cant change source IP on new master, cuz its master I no need source IP? Why I cant.
If I create new replica on old master with depnew.xml all work.

RESULT: If I wanna create master and replica db I must configure this configuration file how 1st and 2 screen.
pzhao
Jul 17, 2015
Ilia,

RESULT: If I wanna create master and replica db I must configure this configuration file how 1st and 2 screen.

Correct. At this time, you can not update connection source in the deployment file but this may change in the future.
Please refer to here for full documentation of acceptable changes to deployment.xml file.

Peter Zhao
Ilia
Jul 23, 2015
Hi all again
question about query and subquery, i see in UsingVoltdb http://docs.voltdb.com/UsingVoltDB/sqlref_select.php description for select. But in my db subquery not worked mb this not supported on community edition? tell me?
that my example:

table

code model price
1 sony 100
2 toshiba 200
3 samsung 300
query:
Select price from table where price < (select max(price) from table);
this query not wokred and write error unsupported.
bballard
Jul 23, 2015
Hi Ilia,

This should work. I tested it on v5.4 (see below). The Release Notes mention that support for this form of scalar subquery was added in v5.3.

SQL Command :: localhost:21212
1> create table foo (code int, model varchar(10), price int);
Command succeeded.
2> insert into foo values (1, 'sony',100);
(Returned 1 rows in 0.08s)
3> insert into foo values (2,'toshiba',200);
(Returned 1 rows in 0.01s)
4> insert into foo values (3, 'samsung', 300);
(Returned 1 rows in 0.01s)
5> select price from foo where price < (select max(price) from foo);
PRICE
------
100
200


-Ben
Ilia
Jul 23, 2015
ty Ben
i tested it on 5.2.2
Ilia
Jul 24, 2015
Hi all, again question about select subquery
This is my test db.
product
maker model type
A 1232 PC
A 1233 PC
A 1276 Printer
A 1298 Laptop
A 1401 Printer
A 1408 Printer
A 1752 Laptop
B 1121 PC
B 1750 Laptop
C 1321 Laptop
D 1288 Printer
D 1433 Printer
E 1260 PC
E 1434 Printer
E 2112 PC
E 2113 PC

pc
code model speed ram hd cd price
1 1232 500 64 5.0 12x 600.0000
10 1260 500 32 10.0 12x 350.0000
11 1233 900 128 40.0 40x 980.0000
12 1233 800 128 20.0 50x 970.0000
2 1121 750 128 14.0 40x 850.0000
3 1233 500 64 5.0 12x 600.0000
4 1121 600 128 14.0 40x 850.0000
5 1121 600 128 8.0 40x 850.0000
6 1233 750 128 20.0 50x 950.0000
7 1232 500 32 10.0 12x 400.0000
8 1232 450 64 8.0 24x 350.0000
9 1232 450 32 10.0 24x 350.0000

Printer
code model color type price
1 1276 n Laser 400.0000
2 1433 y Jet 270.0000
3 1434 y Jet 290.0000
4 1401 n Matrix 150.0000
5 1408 n Matrix 270.0000
6 1288 n Laser 400.0000

Laptop
code model speed ram hd price screen
1 1298 350 32 4.0 700.0000 11
2 1321 500 64 8.0 970.0000 12
3 1750 750 128 12.0 1200.0000 14
4 1298 600 64 10.0 1050.0000 15
5 1752 750 128 10.0 1150.0000 14
6 1298 450 64 10.0 950.0000 12

I wanna find avg price pc and laptop where maker=a. select maker.
Look at this screen where I write 2 query and 1 of this not worked cuz unsupported subquery in IN operator.
83
query.sql:
select avg(price) from(
select avg(price) as price from pc join product on product.model=pc.model where maker='a'
union
select avg(price) as price from laptop join product on product.model=laptop.model where maker='a') ty;

query1.sql:
select avg(price)
from(
select code, price, pc.model, ram, hd
from pc
where model IN(
select model from product where maker='a'
)
union
select code, price, pc.model, ram, hd
from pc
where model IN(
select model from product where maker='a')) ty;