Barely Functional Keystone Deployment with Docker

My eventual goal is to deploy Keystone using Kubernetes. However, I want to understand things from the lowest level on up. Since Kubernetes will be driving Docker for my deployment, I wanted to get things working for a single node Docker deployment before I move on to Kubernetes. As such, you’ll notice I took a few short cuts. Mostly, these involve configuration changes. Since I will need to use Kubernetes for deployment and configuration, I’ll postpone doing it right until I get to that layer. With that caveat, let’s begin.
Continue reading

Keystone, MySQL and Fedora 18

It looks like the access model for MySQL has changed between F17 and F18.

openstack-db fails with a permission on the root user.  However, the following works:

  1. As the keystone user (I suspect the openstack-db call made the keystone user, or maybe that is done by the RPM install?)
  2. run mysql,  (no params, using the default identification, which I assume is PAM based?)
  3. create a user named keystone.
  4. and grant that user perms to create a db.
su - keystone
mysql
create user 'keystone'@'localhost' identified by 'keystone';
grant all  PRIVILEGES on *.* to 'keystone'@'localhost';

exit mysql and log in as that user:

mysql --user=keystone --password=keystone

Create the keystone database:

create database keystone;

Log out and run the dbsync

keystone-manage db_sync

Obviously, this leaves the DB User with too many permissions, but it is a start.

If I now try to run the command

openstack-db --service glance --init
Please enter the password for the 'root' MySQL user:

Even setting the password in MySQL doesn’t work

UPDATE mysql.user SET Password=PASSWORD('keystone') WHERE User='root' AND Host='localhost';
[root@f18-keystone mysql]# openstack-db --service glance --init
Please enter the password for the 'root' MySQL user:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Failed to connect to the MySQL server.  Please check your root user credentials.

I tried it with the unix password as well.

Note that I can connect using the following SQL Alchemy URL:

connection = mysql://keystone:keystone@localhost/keystone?unix_socket=/var/lib/mysql/mysql.sock

I think this is preferable to exposing TCP sockets around in the case that the Keystone server and MySQL server are co-located.

Map Reduce is kinda like “Normalize on the Fly”

One undervalued aspect of Data modeling is that you actually get time to consider the form of the data before you get the data. In a Map reduce job, you kow that your map phase is going to get the data, and that it is not going to be normalized . I could have said, not likely to be normalized, but the reality is that if you are using Map-Reduced, you are not going to get structured data.

Continue reading

Immutability in Databases and Database Access

If we are to follow the advice of Joshua Bloch in Effective Java, we should minimize the mutability of our objects. How does this apply to data access layers, and databases in general?

A good rule of thumb for databases is that if it is important enough to record in a database, it is important enough not to delete from your database…at least, not in the normal course of events. If Databases tables are primarily read only, then then the action of reading the current item will be “select * from table where key =  max (key)”.  Deletes indicate an error made. And so on.  Business objects are then required to provide the rule to select which is the current record for a given entity.

A good example is the Physical fitness test given in the Army (the APFT).  A soldier takes this test at least once per year, probably more.  In order to be considered “in good standing” they have to score more than the minimum in push ups and sit-ups, and run two miles in less than the maximum time, all scored according to age.  The interesting thing is that the active record for a soldier may not be the latest record, but merely the highest score inside of a time range.  Failing an APFT only puts a solider in bad standing if  they do not have another test scored in the same time period that is above the minimum standards.  A soldier might take the APFT for some reason beyond just minimum qualifications, such as for entry into a school or for a competition.

As an aside, notice that the tests are scored based on age.  Age should not be recorded, rather calculated from the date of the test and the soldiers birth date.   Never record what you can calculate, especially if the result of the calculation will change over time.  Although in this case, it would be OK to record the Age of the soldier at the time of the test as a performance optimization, providing  said calculation was done by the computer and not the person entering the scores.  Note, however, that doing so will prevent adjustments like  recalculating the scores if we find out a soldier lied about his birthday.

Relations are tricky in this regard.  for instance, should removing an item from a shopping cart in an eCommerce application be recorded directly or IAW the “No-delete” rule?  If possible, go with the no-delete, as it allows you to track the addto, remove from cart actions of the shopper, something that the marketing side probably wants to know.  For a performance optimization, you can delete the relation, but make sure you send the events to some other backing store as well.

My Ideal Technology Setup for work

“Since I’m dreaming, I’d like a pony” –Susie, in Calvin and Hobbes.

“I’m not just the President of the Hair Club for Men, I’m also a client.” –President of the Hair Club for Men

Not only do I write software, I use it. A whole bunch. I am a Linux guy, and when ever I end up in a situation where I have to work around a proprietary solution that just doesn’t make sense for what I am trying to do, it ads a point or two to my Diastolic. So here is my dream setup:

First of all, I’d like a company that doesn’t use Exchange. I want to be able to talk to my mail server, and my calendar server using standards based protocols. I want email based notifications of meeting. GMail does nice job of sending them via SMS. I don’t want to have to run Outlook to book a conference room. Is there such a solution out there? Probably. I haven’t looked, but I am sure I could find many. Let’s start with Exim for mail, and then find a decent Calendaring program that talks email and iCal. Squirrelmail is a clunky webmail client, so I hope we could find something better, but it still beats Exchanges webmail. Basically, I want to run the Mozilla tools on my desktop.  An I want to sync mail to my Palm based Cell phone without installin Goodlink.

Ubuntu Linux as the default developer install.  I have never been so happy with my desktop as I am now.  Debian package management rules, and Ubuntu desktop support is the best I’ve worked with in Linux.  Of course, I would probably be just as happy with Fedora, but haven’t used it in a while.  Most of my Red Hat work has been with Red Hat Enterprise and that is a fine, stable server architecture, but doesn’t suite my needs for a developer work station…too stable.

I want an internet proxy that allows me to talk to ports other than 80 and 443. Yes I realize this is a configuration issue. I want to be able to SSH in and out, and check in and out from public CVS, Subversion, and Git repositories. I want to be able to hit a website on port 8080.

For Revision Control, I would like to use Either Subversion or Git. Probably Git mixed with Quilt. Note that this is not just for software, but also document preparation. For document preparation: Open Office across the board. It does what I need for Presentations, Spreadsheets, and Word Processing.

A single unified indexing system for all of the companies information system.

All emails sent to public mailing lists should be indexed.

Blog sites for individual developers. Word Press seems to work nicely, but Drupal was good, too.  The more publically available the information in a development environemnt, the bettwer it supports community type development.

Decent IDE support for Refactoring. Refactoring support it baseline, regardless of language. I was incredibly productive using Eclipse for Java, but have not been able to get it to work well for our C++ projects. XRefactory looks promising, but I haven’t tried it yet. Slick Edit wasn’t able to handle our source setup, either. But whatever we chose, the team needs to support it.

A commitment to open source software.  There are few things more motivating to a developer than knowing that the effort they put into learning a code base will not get flushed when they leave the company…and you might even use this as a way to hire new talent, too.

OVER keyword in Oracle

I’m looking at the SQL Scripts that create and modify the database for VPXD. Specifically, I am looking at the oracle version stats[123] scripts. They included a query that uses the “OVER” keyword and I have not found a decent description of what this keyword does. This is the query:

SELECT
st.counter_id AS COUNTER_ID
,v_time_rowid AS TIME_ID
,ST.STAT_VAL AS STAT_VAL
,DENSE_RANK()

OVER (PARTITION BY ST.COUNTER_ID ORDER BY SM.SAMPLE_TIME DESC) AS DENSE_RANK
FROM vpx_sample_time1 sm,vpx_stat_counter sc, vpx_stat_def sd, vpx_hist_stat1 st
WHERE sm.rollup_counter=to_number(to_char(v_start_time_id)||to_char(v_cnt))
AND st.time_id = sm.time_id
AND st.counter_id=SC.COUNTER_ID
AND SC.STAT_ID=SD.ID
AND ((SC.DEVICE_ID =v_null_device_id and v_stat_rollup_level<3) or (v_stat_rollup_level>=3))
AND SD.STAT_LEVEL<=v_stat_rollup_level
AND SD.ROLLUP_TYPE=3)
WHERE DENSE_RANK=1

;

The one description of the Over command I have found has come from the Microsoft website:

“Determines the partitioning and ordering of the rowset before the associated window function is applied”

OK, so it is a union of two types of functionality: partitioning and ordering. The queries I am looking at use both. Let’s start with partitioning:

PARTITION BY
Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

The window function in this place must somehow refer to DENSE_RANK. To me this looks like it is possibly trying to insert a subselect into the overall select. Here is how MSDN defines DENSE_RANK. I’d like to reiterate that these are Oracle functions I’m trying to understand, and Microsoft is doing all the work explaining them. When I tried executing this SQL Query in SQLPLUS via the Oracle 11 instant client, it balked:
SQL> 2 3 4 5 6 SQL> SP2-0734: unknown command beginning “OVER (PART…” – rest of line ignored.
SQL> SP2-0734: unknown command beginning “FROM vpx_…” – rest of line ignored. It parses fine when I do it as part of a plsql stored procedure.

Time to dig out the slightly larger guns: I picked up a copy of the SQL Cookbook by Anthony Moliaro from Our Friends in Sebastopol, CA. This covered both DENSE_RANK and the OVER keyword.

Over, it turns out, is one of a new set of functions added to SQL 2003 that the Standard calls Windowing functions. This means they operate on a subset of the results returned from the outside query. The best example I saw showed if you are selecting all employees from all departments, and next to each one you wanted to show how many employees were in that department, you could use count(*) with an over clause that specified PARTITION by department. This example is in the Cookbook.

DENSE_RANK () tells you how many rows come before the current row in some ordering scheme. The over clause is used to define that ordering scheme. If two rows have the same rank, they have equivalent values according to DENSE_RANK. For instance, if we have five people with five salaries, their DENSE_RANK of salaies will be 1 through five, highest to lowest. If the top two salaries were identical, top rank would return 1,1,2,3,4.

OK, so it looks like the original query wants to use DENSE_RANK on a subset of the returned rows of the query. The window will be defined by this portion of the clause: PARTITION BY ST.COUNTER_ID

And the order within this window will be determined by: ORDER BY SM.SAMPLE_TIME DESC

OK time to get serious about playing:

I’ve learend a little biut since the above frustrations trying to run the stored procedure. Here’s what I do now:

run sqlplus. For me this is

/home/adyoung/apps/oracle/instantclient_11_1/sqlplus adyoung/adyoung@10.17.195.159/DRORACLE

Now make it so anything I print to the screen is visible:

set serveroutput on

Load my sql file in and execute it.

get myfile.sql

/

OK, I have a development method. To print debugging output:
DBMS_OUTPUT.PUT_LINE(‘Text Here’);
And we can debug. In the time honored tradition of always moving from success, I comment everything out but a simple output statement. In this case, I know that the real code uses a variable for the rollup level, so my File (minus commented out section, looks like this:
create or replace procedure stattest
is
v_stat_rollup_level int;
begin
v_stat_rollup_level := 2;
dbms_output.put_line(‘Value: ‘ || TO_CHAR(v_stat_rollup_level));
end stattest;
Which executes using the above method. Now I start adding in parts of the SELECT.
SELECT st.counter_id AS COUNTER_ID FROM vpx_hist_stat1 st;
Of course, it hates this. When I load it into sqlplus I get:
Warning: Procedure created with compilation errors.
Of course, in order to see these compilation errors, I have to jump through a couple more hoops:
SQL> set arraysize 1
SQL> set MAXDATA 6000
SQL> show errors procedure stattest
LINE/COL ERROR
——– —————————————————————–
6/1 PLS-00428: an INTO clause is expected in this SELECT statement
So I add a variable called maxcounter in the declare section and modify my select:
SELECT
max(st.counter_id) into maxcounter
FROM
vpx_hist_stat1 st;
dbms_output.put_line(‘Maxcounter: ‘ || TO_CHAR(maxcounter));
And I have a procedure that executes and does something. On to the rest of the select. After several iterations of trial and error, I have the SQL query wokring as a cursor. I’ve now figured out even a better way to work. I’ve modified my file like below, and can now cat it into the sqlplus interpreter:
set arraysize 1
set MAXDATA 6000
set serveroutput on

create or replace procedure stattest
is
v_stat_rollup_level int := 2;
maxcounter int := 0;

CURSOR MYCURSOR is SELECT
st.counter_id as COUNTER_ID,
st.STAT_VAL as STAT_VAL
FROM vpx_sample_time1 sm,
vpx_stat_counter sc,
vpx_stat_def sd,
vpx_hist_stat1 st

WHERE
st.time_id = sm.time_id
AND st.counter_id=SC.COUNTER_ID
AND SC.STAT_ID=SD.ID
AND SD.ROLLUP_TYPE=3
AND SD.STAT_LEVEL<=v_stat_rollup_level;

begin

open MYCURSOR;

FOR recname in MYCURSOR
LOOP
dbms_output.put_line(‘Value: ‘ || TO_CHAR(v_stat_rollup_level));
END LOOP;

dbms_output.put_line(‘Maxcounter: ‘ || TO_CHAR(maxcounter));

end stattest;

/

show errors procedure stattest

.

OK, I am going to bail here.  This post has been half written for a while, and is not going to get completed anytime soon.  I think I’d vote “Over” as something that would be useful in the next rev of postgres, if only for simplifying porting from Oracle.

Oracle to Postgresql part 1

Here are the steps I am going through to port some code from Oracle PL/SQL to PostgreSQL PLPGSQL.

Here is the first line in the Oracle version

create or replace procedure stats_rollup1_proc is

This becomes

create or replace function stats_rollup1_proc() returns int as $$

DECLARE

Now Postgres is not my fulltime focus, just a part of the overall job. a PG expert could probably do it better.

The things to note here:

  • procedure is not a keyword in plpgsql. Thus function and returns. I suspect I could return a void type, but haven’t looked that hard.
  • Postgres requires the text of a stored procedure to be qutoed. The $$ is a nice way to deal with the requirement.
  • DECLARE is optional in Oracle, but required in postgreSQL

At the end of the function:

end stats_rollup1_proc;

Becomes

return 0;

end /*stats_rollup1_proc;*/

$$ LANGUAGE plpgsql

I like leaving the comment in there to match the original begin, since the functions get long enough that it is hard to track. There is no harm in returning 0, even if we don’t really use it as a return code. The final $$ closes out the one from the start of the function. We have to specify the language used, as this same mechanism can be used for any of the languages embedded inside PostgreSQL. Yes, even Python.

Ok, for some mechanics.

In the Declare section of the oracle code we have:

cursor time_cur(current_time_in date) IS
select distinct sample_time
from VPX_SAMPLE_TIME1
WHERE ROLLUP_COUNTER IS NULL
AND SAMPLE_TIME < current_time_in-1/24
order by 1 asc;
v_time VPX_SAMPLE_TIME1.sample_time%type;

which is later used like this:

open time_cur(v_rollup_start_time);
loop
fetch time_cur into v_time;
exit when time_cur%notfound;

In PostgreSQL these can be inlined like this:

for v_time in
select distinct sample_time
from VPX_SAMPLE_TIME1
WHERE ROLLUP_COUNTER IS NULL
AND SAMPLE_TIME < current_time_in-1/24
order by 1 asc
loop

Although I have not yet figured out how to handle the notfound.

Oracle ODBC Setup on Ubuntu (32 on 64)

First of all, I am running a 64 bit Ubuntu 7.04 but I need a 32 Bit Oracle for the application I am using. I have a 32 bit chroot setup. The setup for this is beyond the scope of this article. This version of Ubuntu ships with unixodbc version 2.2.11-13 . There is a symbol, SQLGetPrivateProfileStringW, defined in later versions that the Oracle 11g driver requires. This symbol is not defined in unixodbc version 2.2.11-13. Thus, you have to use the 10.2 Oracle Drivers.

I Downloaded 3 files from The Oracle tech download page for 32bit Linux: the instantclient, SQL Plus, and ODBC packages. I unzipped these in my directory ~/apps/oracle32 Which now looks like this:

adyoung@adyoung-laptop$ pwd
/home/adyoung/apps/oracle32/instantclient_10_2
adyoung@adyoung-laptop$ ls
classes12.jar libocci.so.10.1 libsqora.so.10.1 ojdbc14.jar
genezi libociei.so ODBC_IC_Readme_Linux.html sqlplus
glogin.sql libocijdbc10.so ODBCRelnotesJA.htm
libclntsh.so.10.1 libsqlplusic.so ODBCRelnotesUS.htm
libnnz10.so libsqlplus.so odbc_update_ini.sh

I created an entry in /chroot/etc/odbcinst.ini:

[Oracle 10g ODBC driver]
Description = Oracle ODBC driver for Oracle 10g
Driver = /home/adyoung/apps/oracle32/instantclient_10_2/libsqora.so.10.1
Setup =
FileUsage =
CPTimeout =
CPReuse =

And another in /chroot/etc/odbc.ini

[EMO]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 10g ODBC driver
DSN = EMO
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = 10.10.15.15/DRORACLE
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = adyoung
StatementCache=F
CacheBufferSize=20

Once again, DSN and IP Address have been changed to protect the guilty. To test the datasource, run:

sudo dchroot -d LD_LIBRARY_PATH=/home/adyoung/apps/oracle32/instantclient_10_2 DataManagerII

To just test sqlplus connectivity, from inside the chroot, run:

./sqlplus adyoung/adyoung@10.10.15.15/DRORACLE

Note that using the instant client, no TNSNAMES.ORA file is required.