Change CDSW Jobs Base Image via DB.

[#1]

Some customers who upgrade from CDSW 1.5.x to 1.6.x - 1.7.x will have deprecated CDSW jobs which use base image v7 which run containers as root.  Base Image v8 now have enhanced security which disallow containers to be ran as root users, but rather as the cdsw user. 

It is generally advised to re create these jobs again using base image v8 but in certain environments this is not a feasible option.  The following instructions will assist you in changing the CDSW base image for your jobs.  In this case v7 → v8.

To connect to the cdsw database, gain root on the master node and run

kubectl exec -ti `kubectl get pods | grep Running | grep '^db' | awk '{print $1}'` --  psql -U sense

Confirm the engine id

select * from engine_images;

Let's first explore which projects are using which engines. There are three tables involved: the "projects" table, the "engine_images" table, and the one that joins the two together, the "projects_engine_images" table. This sql statement will give you a row per project, and which engine each project is using:

select proj.id, proj.name, eng.id, eng.description, eng.repository, eng.tag
from projects proj inner join projects_engine_images mapping on proj.id = mapping.project_id
inner join engine_images eng on mapping.engine_image_id = eng.id;

You'll want to make sure that every project is using a "tag" of 8. I recommend going into the UI and doing this for each project, unless there are a lot of projects to migrate.

update projects_engine_images set engine_image_id = 8 where engine_image_id != 8;

Next let's look at jobs. Again there are three tables - as before there are the "projects" and "engine_images" tables, and this time we use the "jobs" table. The following sql statement will show the project - job - engine mapping for all jobs:

select proj.id, proj.name, job.id, job.name, job.description, eng.id, eng.description, eng.repository, eng.tag
from projects proj inner join jobs job on proj.id = job.project_id
inner join engine_images eng on job.engine_image_id = eng.id;

Again, you'll want to look at the engine images used, and look for jobs with an engine tag that is not 8.

At this point you have a decision to make. If you just have a few jobs that are not engine version 8, you can go into the UI and delete and re-create the jobs. If you have many jobs, you'll want to edit the database directly.

If you're going to change the jobs in the database, first write down the 'id' (eng.id above) of the engine you want to migrate to. Then run

select proj.id, proj.name, job.id, job.name, job.description, eng.id, eng.description, eng.repository, eng.tag
from projects proj inner join jobs job on proj.id = job.project_id
inner join engine_images eng on job.engine_image_id = eng.id
where eng.id != 8;

I get this as a result:

 id |  name  | id |        name         | description | id | description |                 repository                 | tag 
----+--------+----+---------------------+-------------+----+-------------+--------------------------------------------+-----
  4 | myoder | 33 | test engine 7 job |             | 13 | Seven       | docker.repository.cloudera.com/cdsw/engine | 7

My job is still running engine 7. More simply,

select id, name, description, engine_image_id from jobs where engine_image_id != 8;

We want to change job.engine_image_id to 8.

UPDATE jobs
SET engine_image_id = 8
WHERE engine_image_id != 8;

All the jobs are now moved to use the desired engine image.