subnet service types not working with sqlite3 version 3.7.17

[venkata anil]

subnet service types not working with sqlite3 version 3.7.17. But it works from sqlite3 version 3.8.0 and above versions.

Because of this, subnet service type unit tests failing in sqlite3 version 3.7.17.

Captured traceback:
~~~~~~~~~~~~~~~~~~~
    Traceback (most recent call last):
      File "neutron/tests/base.py", line 125, in func
        return f(self, *args, **kwargs)
      File "neutron/tests/unit/extensions/test_subnet_service_types.py", line 245, in test_create_port_no_device_owner_no_fallback
        self.test_create_port_no_device_owner(fallback=False)
      File "neutron/tests/base.py", line 125, in func
        return f(self, *args, **kwargs)
      File "neutron/tests/unit/extensions/test_subnet_service_types.py", line 242, in test_create_port_no_device_owner
        self._assert_port_res(port, '', subnet, fallback)
      File "neutron/tests/unit/extensions/test_subnet_service_types.py", line 173, in _assert_port_res
        self.assertEqual(error, res['NeutronError']['type'])
    KeyError: 'NeutronError'

_query_filter_service_subnets [1] is behaving differently in 3.7.17 and 3.8.0 for these tests
[1] https://github.com/openstack/neutron/blob/master/neutron/db/ipam_backend_mixin.py#L597

I have seen this on centos7 setup, which by default uses sqlite3 version 3.7.17.

[venkata anil (anil-venkata)]

sqlite 3.7.15 - 3.7.17 has this issue. _query_filter_service_subnets is behaving as expected for Sqlite 3.7.14(and below versions) and 3.8.0(and above versions).

[OpenStack Infra (hudson-openstack)]

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

[venkata anil (anil-venkata)]

Below example python program can reproduce this issue. I am using pysqlite[1] python wrapper to access sqlite. Pysqlite version 2.6.0 will work with both sqlite 2.7.17 and 2.8.0.

from pysqlite2 import dbapi2 as sqlite3
conn = sqlite3.connect('example.db')

c = conn.cursor()
c.execute('''CREATE TABLE subnets
             (subnet_id integer, name text)''')
c.execute('''CREATE TABLE subnet_service_types
             (subnet_id integer, service_type text)''')
c.execute('''CREATE TABLE dnsnameservers
             (subnet_id integer, dns_order integer)''')
c.execute("INSERT INTO subnets VALUES (123, 'subnet1')")
c.execute("INSERT INTO subnet_service_types VALUES (123, 'compute:foo')")
conn.commit()

query = ('''SELECT subnets.subnet_id, subnets.name, subnet_service_types.service_type
         FROM subnets LEFT OUTER JOIN dnsnameservers
         ON subnets.subnet_id = dnsnameservers.subnet_id
         LEFT OUTER JOIN subnet_service_types
         ON subnets.subnet_id = subnet_service_types.subnet_id
         WHERE subnets.subnet_id = 123 AND (subnet_service_types.service_type IS NULL OR subnet_service_types.service_type = '')
         ORDER BY dnsnameservers."dns_order"''')
for row in c.execute(query):
   print row
conn.close()

The output of above program in 2.7.17 is
[vagrant@devstack ~]$ python mysqlite.py
(123, u'subnet1', None)

The output of above program in 2.8.0 doesn't generate any records i.e
[vagrant@devstack ~]$ python mysqlite.py

note: need to remove example.db file before running the program.
[1] https://github.com/ghaering/pysqlite

[venkata anil (anil-venkata)]

The below sqlalchemy query
    def _query_filter_service_subnets(self, query, service_type):
        ServiceType = sst_model.SubnetServiceType
        query = query.add_entity(ServiceType)
        query = query.outerjoin(ServiceType)
        query = query.filter(or_(ServiceType.service_type.is_(None),
                                 ServiceType.service_type == service_type))

will translate to the following sql code in sqlite(while running UT test_create_port_no_device_owner_no_fallback)

    2016-09-30 18:41:34,235 INFO sqlalchemy.engine.base.Engine.sqlite@ptzpgjyreu SELECT subnets.project_id AS subnets_project_id, subnets.id AS subnets_id, subnets.name AS subnets_name, subnets.network_id AS subnets_network_id, subnets.segment_id AS subnets_segment_id, subnets.subnetpool_id AS subnets_subnetpool_id, subnets.ip_version AS subnets_ip_version, subnets.cidr AS subnets_cidr, subnets.gateway_ip AS subnets_gateway_ip, subnets.enable_dhcp AS subnets_enable_dhcp, subnets.ipv6_ra_mode AS subnets_ipv6_ra_mode, subnets.ipv6_address_mode AS subnets_ipv6_address_mode, subnets.standard_attr_id AS subnets_standard_attr_id, subnet_service_types.subnet_id AS subnet_service_types_subnet_id, subnet_service_types.service_type AS subnet_service_types_service_type, subnetpoolprefixes_1.cidr AS subnetpoolprefixes_1_cidr, subnetpoolprefixes_1.subnetpool_id AS subnetpoolprefixes_1_subnetpool_id, standardattributes_1.id AS standardattributes_1_id, standardattributes_1.resource_type AS standardattributes_1_resource_type, standardattributes_1.description AS standardattributes_1_description, standardattributes_1.revision_number AS standardattributes_1_revision_number, standardattributes_1.created_at AS standardattributes_1_created_at, standardattributes_1.updated_at AS standardattributes_1_updated_at, tags_1.standard_attr_id AS tags_1_standard_attr_id, tags_1.tag AS tags_1_tag, subnetpools_1.project_id AS subnetpools_1_project_id, subnetpools_1.id AS subnetpools_1_id, subnetpools_1.name AS subnetpools_1_name, subnetpools_1.ip_version AS subnetpools_1_ip_version, subnetpools_1.default_prefixlen AS subnetpools_1_default_prefixlen, subnetpools_1.min_prefixlen AS subnetpools_1_min_prefixlen, subnetpools_1.max_prefixlen AS subnetpools_1_max_prefixlen, subnetpools_1.shared AS subnetpools_1_shared, subnetpools_1.is_default AS subnetpools_1_is_default, subnetpools_1.default_quota AS subnetpools_1_default_quota, subnetpools_1.hash AS subnetpools_1_hash, subnetpools_1.address_scope_id AS subnetpools_1_address_scope_id, subnetpools_1.standard_attr_id AS subnetpools_1_standard_attr_id, ipallocationpools_1.id AS ipallocationpools_1_id, ipallocationpools_1.subnet_id AS ipallocationpools_1_subnet_id, ipallocationpools_1.first_ip AS ipallocationpools_1_first_ip, ipallocationpools_1.last_ip AS ipallocationpools_1_last_ip, dnsnameservers_1.address AS dnsnameservers_1_address, dnsnameservers_1.subnet_id AS dnsnameservers_1_subnet_id, dnsnameservers_1."order" AS dnsnameservers_1_order, subnetroutes_1.destination AS subnetroutes_1_destination, subnetroutes_1.nexthop AS subnetroutes_1_nexthop, subnetroutes_1.subnet_id AS subnetroutes_1_subnet_id, networkrbacs_1.project_id AS networkrbacs_1...

[venkata anil (anil-venkata)]

Mistakenly mentioned sqlite versions as 2.7.17 and 2.8.0 in comments 3 and 4 instead of 3.7.17 and 3.8.0.

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.openstack.org/381906

[OpenStack Infra (hudson-openstack)]

Change abandoned by Armando Migliaccio (<email address hidden>) on branch: master
Review: https://review.openstack.org/381906
Reason: This review is > 4 weeks without comment, and failed Jenkins the last time it was checked. We are abandoning this for now. Feel free to reactivate the review by pressing the restore button and leaving a 'recheck' comment to get fresh test results.

[Miguel Angel Ajo (mangelajo)]

confirmed here too: http://paste.openstack.org/show/590464/

[OpenStack Infra (hudson-openstack)]

Change abandoned by Armando Migliaccio (<email address hidden>) on branch: master
Review: https://review.openstack.org/381906
Reason: This review is > 4 weeks without comment, and failed Jenkins the last time it was checked. We are abandoning this for now. Feel free to reactivate the review by pressing the restore button and leaving a 'recheck' comment to get fresh test results.

[Brian Haley (brian-haley)]

I'm starting to agree with John's last comment in the patch:

"Short of a performance analysis showing this is better than the existing implementation, I'd rather we remove support for those versions of sqlite to avoid the bug."

Without this I don't think it's a good option to stuff this in rc1.

[Kevin Benton (kevinbenton)]

This bug has had a related patch abandoned and has been automatically un-assigned due to inactivity. Please re-assign yourself if you are continuing work or adjust the state as appropriate if it is no longer valid.

[OpenStack Infra (hudson-openstack)]

Change abandoned by Kevin Benton (<email address hidden>) on branch: master
Review: https://review.openstack.org/380067
Reason: This review is > 4 weeks without comment, and failed Jenkins the last time it was checked. We are abandoning this for now. Feel free to reactivate the review by pressing the restore button and leaving a 'recheck' comment to get fresh test results.

[Ihar Hrachyshka (ihar-hrachyshka)]

[vagrant@localhost ~]$ yum info sqlite
Installed Packages
Name : sqlite
Arch : x86_64
Version : 3.7.17
Release : 8.el7
Size : 795 k
Repo : installed
From repo : anaconda
Summary : Library that implements an embeddable SQL database engine
URL : http://www.sqlite.org/
License : Public Domain
Description : SQLite is a C library that implements an SQL database engine. A large
            : subset of SQL92 is supported. A complete database is stored in a
            : single disk file. The API is designed for convenience and ease of use.
            : Applications that link against SQLite can enjoy the power and
            : flexibility of an SQL database without the administrative hassles of
            : supporting a separate database server. Version 2 and version 3 binaries
            : are named to permit each to be installed on a single host

[vagrant@localhost neutron]$ tox -e py27 test_subnet_service_types
  py27: commands succeeded
  congratulations :)