Model schema column type mismatches

[Mate Lakat]

I ran through a script that uses sqlalchemy's metadata.create_all to make the database, and found out that some column's types are not matching the database type.

Briefly what I have done is tried to create the database with BASE.metadata.create_all() for a more detailed description of the steps taken, please see this:

Here are the steps to
To repro with PostgreSQL:

drop database if exists cinder;
create database cinder with encoding 'utf-8';
grant all on DATABASE cinder to matelakat;

I installed dbbuild (https://github.com/matelakat/ostools/tree/master/dbbuild)

and then:

dbbuild postgresql:///cinder cinder.db.sqlalchemy.models
...
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) foreign key constraint "group_type_projects_group_type_id_fkey" cannot be implemented
DETAIL: Key columns "group_type_id" and "id" are of incompatible types: integer and character varying.
 [SQL: '\nCREATE TABLE group_type_projects (\n\tcreated_at TIMESTAMP WITHOUT TIME ZONE, \n\tupdated_at TIMESTAMP WITHOUT TIME ZONE, \n\tdeleted_at TIMESTAMP WITHOUT TIME ZONE, \n\tid SERIAL NOT NULL, \n\tgroup_type_id INTEGER NOT NULL, \n\tproject_id VARCHAR(255), \n\tdeleted INTEGER, \n\tPRIMARY KEY (id), \n\tCONSTRAINT uniq_group_type_projects0group_type_id0project_id0deleted UNIQUE (group_type_id, project_id, deleted), \n\tFOREIGN KEY(group_type_id) REFERENCES group_types (id)\n)\n\n']

So fixed that, and re-run:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) foreign key constraint "volume_type_projects_volume_type_id_fkey" cannot be implemented
DETAIL: Key columns "volume_type_id" and "id" are of incompatible types: integer and character varying.
 [SQL: '\nCREATE TABLE volume_type_projects (\n\tcreated_at TIMESTAMP WITHOUT TIME ZONE, \n\tupdated_at TIMESTAMP WITHOUT TIME ZONE, \n\tdeleted_at TIMESTAMP WITHOUT TIME ZONE, \n\tid SERIAL
NOT NULL, \n\tvolume_type_id INTEGER NOT NULL, \n\tproject_id VARCHAR(255), \n\tdeleted INTEGER, \n\tPRIMARY KEY (id), \n\tCONSTRAINT uniq_volume_type_projects0volume_type_id0project_id0deleted UNIQUE (volume_type_id, project_id, deleted), \n\tFOREIGN KEY(volume_type_id) REFERENCES volume_types (id)\n)\n\n']

So fixed that one as well.

Here is the patch:

diff --git a/cinder/db/sqlalchemy/models.py b/cinder/db/sqlalchemy/models.py
index f091780a9..541cfbc5b 100644
--- a/cinder/db/sqlalchemy/models.py
+++ b/cinder/db/sqlalchemy/models.py
@@ -418,7 +418,7 @@ class VolumeTypeProjects(BASE, CinderBase):
         name="uniq_volume_type_projects0volume_type_id0project_id0deleted"),
     )
     id = Column(Integer, primary_key=True)
- volume_type_id = Column(Integer, ForeignKey('volume_types.id'),
+ volume_type_id = Column(String, ForeignKey('volume_types.id'),
                             nullable=False)
     project_id = Column(String(255))
     deleted = Column(Integer, default=0)
@@ -440,7 +440,7 @@ class GroupTypeProjects(BASE, CinderBase):
         name="uniq_group_type_projects0group_type_id0project_id0deleted"),
     )
     id = Column(Integer, primary_key=True)
- group_type_id = Column(Integer, ForeignKey('group_types.id'),
+ group_type_id = Column(String, ForeignKey('group_types.id'),
                            nullable=False)
     project_id = Column(String(255))
     deleted = Column(Integer, default=0)

I re-created the database with migrations to see what the type of the columns is:
cinder=# \d volume_type_projects;
                                       Table "public.volume_type_projects"
     Column | Type | Modifiers
----------------+-----------------------------+-------------------------------------------------------------------
 id | integer | not null default nextval('volume_type_projects_id_seq'::regclass)
 created_at | timestamp without time zone |
 updated_at | timestamp without time zone |
 deleted_at | timestamp without time zone |
 volume_type_id | character varying(36) |
 project_id | character varying(255) |
 deleted | integer |
Indexes:
    "volume_type_projects_pkey" PRIMARY KEY, btree (id)
    "volume_type_projects_volume_type_id_project_id_deleted_key" UNIQUE CONSTRAINT, btree (volume_type_id, project_id, deleted)
Foreign-key constraints:
    "volume_type_projects_volume_type_id_fkey" FOREIGN KEY (volume_type_id) REFERENCES volume_types(id)

That seems to be correct (see that volume_type_id is varchar)

The other table:
cinder=# \d group_type_projects;
                                       Table "public.group_type_projects"
    Column | Type | Modifiers
---------------+-----------------------------+------------------------------------------------------------------
 id | integer | not null default nextval('group_type_projects_id_seq'::regclass)
 created_at | timestamp without time zone |
 updated_at | timestamp without time zone |
 deleted_at | timestamp without time zone |
 group_type_id | character varying(36) |
 project_id | character varying(255) |
 deleted | boolean |
Indexes:
    "group_type_projects_pkey" PRIMARY KEY, btree (id)
    "group_type_projects_group_type_id_project_id_deleted_key" UNIQUE CONSTRAINT, btree (group_type_id, project_id, deleted)
Foreign-key constraints:
    "group_type_projects_group_type_id_fkey" FOREIGN KEY (group_type_id) REFERENCES group_types(id)

This is correct as well, see that group_type_id is varchar 36

The same with MYSQL:
MariaDB [cinder]> describe volume_type_projects;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| deleted_at | datetime | YES | | NULL | |
| volume_type_id | varchar(36) | YES | MUL | NULL | |
| project_id | varchar(255) | YES | | NULL | |
| deleted | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

MariaDB [cinder]> describe group_type_projects;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| deleted_at | datetime | YES | | NULL | |
| group_type_id | varchar(36) | YES | MUL | NULL | |
| project_id | varchar(255) | YES | | NULL | |
| deleted | tinyint(1) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/443165

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/443165
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=6d1036f7e7fc4b9b2494fd892c151f67366634ce
Submitter: Jenkins
Branch: master

commit 6d1036f7e7fc4b9b2494fd892c151f67366634ce
Author: Mate Lakat <email address hidden>
Date: Wed Mar 8 15:59:03 2017 +0100

    Fix column types in models.py

    Some columns' type were not matching the one in the database. Not sure
    if that causes any issues, but it's worth to fix the issue.

    Closes-Bug: #1671135
    Change-Id: Idb242d4f1eb4694a3324ca587caf8a8bccfd35ed

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/cinder 11.0.0.0b1 development milestone.