On one of my dev sandboxes I use sqlite for fun.
Since this change -
https://review.openstack.org/#/c/10917/2
When I list/filter by name (i.e. nova list --name Apollo:106842) i get the following traceback:
2012-08-15 12:56:04 DEBUG nova.compute.api [req-37ab9251-ae08-4cb8-87c4-efb132b9fc39 account1 account1] Searching by: {'deleted': False, 'project_id': 'account1', 'name': u'Apollo:106842'} from (pid=18032) get_all /opt/nova/nova/compute/api.py:1090
2012-08-15 12:56:05 ERROR nova.api.openstack [req-37ab9251-ae08-4cb8-87c4-efb132b9fc39 account1 account1] Caught error: (OperationalError) no such function: REGEXP u'SELECT instances.created_at AS instances_created_at, instances.updated_at AS instances_updated_at, instances.deleted_at AS instances_deleted_at, instances.deleted AS instances_deleted, instances.id AS instances_id, instances.user_id AS instances_user_id, instances.project_id AS instances_project_id, instances.image_ref AS instances_image_ref, instances.kernel_id AS instances_kernel_id, instances.ramdisk_id AS instances_ramdisk_id, instances.server_name AS instances_server_name, instances.launch_index AS instances_launch_index, instances.key_name AS instances_key_name, instances.key_data AS instances_key_data, instances.power_state AS instances_power_state, instances.vm_state AS instances_vm_state, instances.task_state AS instances_task_state, instances.memory_mb AS instances_memory_mb, instances.vcpus AS instances_vcpus, instances.root_gb AS instances_root_gb, instances.ephemeral_gb AS instances_ephemeral_gb, instances.hostname AS instances_hostname, instances.host AS instances_host, instances.instance_type_id AS instances_instance_type_id, instances.user_data AS instances_user_data, instances.reservation_id AS instances_reservation_id, instances.scheduled_at AS instances_scheduled_at, instances.launched_at AS instances_launched_at, instances.terminated_at AS instances_terminated_at, instances.availability_zone AS instances_availability_zone, instances.display_name AS instances_display_name, instances.display_description AS instances_display_description, instances.launched_on AS instances_launched_on, instances.locked AS instances_locked, instances.os_type AS instances_os_type, instances.architecture AS instances_architecture, instances.vm_mode AS instances_vm_mode, instances.uuid AS instances_uuid, instances.root_device_name AS instances_root_device_name, instances.default_ephemeral_device AS instances_default_ephemeral_device, instances.default_swap_device AS instances_default_swap_device, instances.config_drive AS instances_config_drive, instances.access_ip_v4 AS instances_access_ip_v4, instances.access_ip_v6 AS instances_access_ip_v6, instances.auto_disk_config AS instances_auto_disk_config, instances.progress AS instances_progress, instances.shutdown_terminate AS instances_shutdown_terminate, instances.disable_terminate AS instances_disable_terminate, security_groups_1.created_at AS security_groups_1_created_at, security_groups_1.updated_at AS security_groups_1_updated_at, security_groups_1.deleted_at AS security_groups_1_deleted_at, security_groups_1.deleted AS security_groups_1_deleted, security_groups_1.id AS security_groups_1_id, security_groups_1.name AS security_groups_1_name, security_groups_1.description AS security_groups_1_description, security_groups_1.user_id AS security_groups_1_user_id, security_groups_1.project_id AS security_groups_1_project_id, instance_types_1.created_at AS instance_types_1_created_at, instance_types_1.updated_at AS instance_types_1_updated_at, instance_types_1.deleted_at AS instance_types_1_deleted_at, instance_types_1.deleted AS instance_types_1_deleted, instance_types_1.id AS instance_types_1_id, instance_types_1.name AS instance_types_1_name, instance_types_1.memory_mb AS instance_types_1_memory_mb, instance_types_1.vcpus AS instance_types_1_vcpus, instance_types_1.root_gb AS instance_types_1_root_gb, instance_types_1.ephemeral_gb AS instance_types_1_ephemeral_gb, instance_types_1.flavorid AS instance_types_1_flavorid, instance_types_1.swap AS instance_types_1_swap, instance_types_1.rxtx_factor AS instance_types_1_rxtx_factor, instance_types_1.vcpu_weight AS instance_types_1_vcpu_weight, instance_types_1.disabled AS instance_types_1_disabled, instance_metadata_1.created_at AS instance_metadata_1_created_at, instance_metadata_1.updated_at AS instance_metadata_1_updated_at, instance_metadata_1.deleted_at AS instance_metadata_1_deleted_at, instance_metadata_1.deleted AS instance_metadata_1_deleted, instance_metadata_1.id AS instance_metadata_1_id, instance_metadata_1."key" AS instance_metadata_1_key, instance_metadata_1.value AS instance_metadata_1_value, instance_metadata_1.instance_uuid AS instance_metadata_1_instance_uuid, instance_info_caches_1.created_at AS instance_info_caches_1_created_at, instance_info_caches_1.updated_at AS instance_info_caches_1_updated_at, instance_info_caches_1.deleted_at AS instance_info_caches_1_deleted_at, instance_info_caches_1.deleted AS instance_info_caches_1_deleted, instance_info_caches_1.id AS instance_info_caches_1_id, instance_info_caches_1.network_info AS instance_info_caches_1_network_info, instance_info_caches_1.instance_uuid AS instance_info_caches_1_instance_uuid \nFROM instances LEFT OUTER JOIN security_group_instance_association AS security_group_instance_association_1 ON security_group_instance_association_1.instance_uuid = instances.uuid AND instances.deleted = ? LEFT OUTER JOIN security_groups AS security_groups_1 ON security_groups_1.id = security_group_instance_association_1.security_group_id AND security_group_instance_association_1.deleted = ? AND security_groups_1.deleted = ? LEFT OUTER JOIN instance_types AS instance_types_1 ON instances.instance_type_id = instance_types_1.id LEFT OUTER JOIN instance_metadata AS instance_metadata_1 ON instance_metadata_1.instance_uuid = instances.uuid AND instance_metadata_1.deleted = ? LEFT OUTER JOIN instance_info_caches AS instance_info_caches_1 ON instance_info_caches_1.instance_uuid = instances.uuid \nWHERE instances.deleted = ? AND instances.vm_state != ? AND instances.project_id = ? AND (instances.display_name REGEXP ?) ORDER BY instances.created_at DESC' (0, 0, 0, 0, 0, 'soft-delete', 'account1', 'Apollo:106842')
2012-08-15 12:56:05 TRACE nova.api.openstack Traceback (most recent call last):
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/api/openstack/__init__.py", line 78, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack return req.get_response(self.application)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/request.py", line 1053, in get_response
2012-08-15 12:56:05 TRACE nova.api.openstack application, catch_exc_info=False)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/request.py", line 1022, in call_application
2012-08-15 12:56:05 TRACE nova.api.openstack app_iter = application(self.environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 159, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack return resp(environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 159, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack return resp(environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 159, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack return resp(environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 159, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack return resp(environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/routes/middleware.py", line 131, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack response = self.app(environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 159, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack return resp(environ, start_response)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 147, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack resp = self.call_func(req, *args, **self.kwargs)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 208, in call_func
2012-08-15 12:56:05 TRACE nova.api.openstack return self.func(req, *args, **kwargs)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/api/openstack/wsgi.py", line 864, in __call__
2012-08-15 12:56:05 TRACE nova.api.openstack content_type, body, accept)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/api/openstack/wsgi.py", line 912, in _process_stack
2012-08-15 12:56:05 TRACE nova.api.openstack action_result = self.dispatch(meth, request, action_args)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/api/openstack/wsgi.py", line 1000, in dispatch
2012-08-15 12:56:05 TRACE nova.api.openstack return method(req=request, **action_args)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/api/openstack/compute/servers.py", line 373, in detail
2012-08-15 12:56:05 TRACE nova.api.openstack servers = self._get_servers(req, is_detail=True)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/api/openstack/compute/servers.py", line 443, in _get_servers
2012-08-15 12:56:05 TRACE nova.api.openstack search_opts=search_opts)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/compute/api.py", line 1142, in get_all
2012-08-15 12:56:05 TRACE nova.api.openstack sort_key, sort_dir)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/compute/api.py", line 1164, in _get_instances_by_filters
2012-08-15 12:56:05 TRACE nova.api.openstack sort_dir)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/db/api.py", line 607, in instance_get_all_by_filters
2012-08-15 12:56:05 TRACE nova.api.openstack sort_dir)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/db/sqlalchemy/api.py", line 130, in wrapper
2012-08-15 12:56:05 TRACE nova.api.openstack return f(*args, **kwargs)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/opt/nova/nova/db/sqlalchemy/api.py", line 1602, in instance_get_all_by_filters
2012-08-15 12:56:05 TRACE nova.api.openstack instances = query_prefix.all()
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2115, in all
2012-08-15 12:56:05 TRACE nova.api.openstack return list(self)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2227, in __iter__
2012-08-15 12:56:05 TRACE nova.api.openstack return self._execute_and_instances(context)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances
2012-08-15 12:56:05 TRACE nova.api.openstack result = conn.execute(querycontext.statement, self._params)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1449, in execute
2012-08-15 12:56:05 TRACE nova.api.openstack params)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
2012-08-15 12:56:05 TRACE nova.api.openstack compiled_sql, distilled_params
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
2012-08-15 12:56:05 TRACE nova.api.openstack context)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
2012-08-15 12:56:05 TRACE nova.api.openstack context)
2012-08-15 12:56:05 TRACE nova.api.openstack File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 331, in do_execute
2012-08-15 12:56:05 TRACE nova.api.openstack cursor.execute(statement, parameters)
2012-08-15 12:56:05 TRACE nova.api.openstack OperationalError: (OperationalError) no such function: REGEXP u'SELECT instances.created_at AS instances_created_at, instances.updated_at AS instances_updated_at, instances.deleted_at AS instances_deleted_at, instances.deleted AS instances_deleted, instances.id AS instances_id, instances.user_id AS instances_user_id, instances.project_id AS instances_project_id, instances.image_ref AS instances_image_ref, instances.kernel_id AS instances_kernel_id, instances.ramdisk_id AS instances_ramdisk_id, instances.server_name AS instances_server_name, instances.launch_index AS instances_launch_index, instances.key_name AS instances_key_name, instances.key_data AS instances_key_data, instances.power_state AS instances_power_state, instances.vm_state AS instances_vm_state, instances.task_state AS instances_task_state, instances.memory_mb AS instances_memory_mb, instances.vcpus AS instances_vcpus, instances.root_gb AS instances_root_gb, instances.ephemeral_gb AS instances_ephemeral_gb, instances.hostname AS instances_hostname, instances.host AS instances_host, instances.instance_type_id AS instances_instance_type_id, instances.user_data AS instances_user_data, instances.reservation_id AS instances_reservation_id, instances.scheduled_at AS instances_scheduled_at, instances.launched_at AS instances_launched_at, instances.terminated_at AS instances_terminated_at, instances.availability_zone AS instances_availability_zone, instances.display_name AS instances_display_name, instances.display_description AS instances_display_description, instances.launched_on AS instances_launched_on, instances.locked AS instances_locked, instances.os_type AS instances_os_type, instances.architecture AS instances_architecture, instances.vm_mode AS instances_vm_mode, instances.uuid AS instances_uuid, instances.root_device_name AS instances_root_device_name, instances.default_ephemeral_device AS instances_default_ephemeral_device, instances.default_swap_device AS instances_default_swap_device, instances.config_drive AS instances_config_drive, instances.access_ip_v4 AS instances_access_ip_v4, instances.access_ip_v6 AS instances_access_ip_v6, instances.auto_disk_config AS instances_auto_disk_config, instances.progress AS instances_progress, instances.shutdown_terminate AS instances_shutdown_terminate, instances.disable_terminate AS instances_disable_terminate, security_groups_1.created_at AS security_groups_1_created_at, security_groups_1.updated_at AS security_groups_1_updated_at, security_groups_1.deleted_at AS security_groups_1_deleted_at, security_groups_1.deleted AS security_groups_1_deleted, security_groups_1.id AS security_groups_1_id, security_groups_1.name AS security_groups_1_name, security_groups_1.description AS security_groups_1_description, security_groups_1.user_id AS security_groups_1_user_id, security_groups_1.project_id AS security_groups_1_project_id, instance_types_1.created_at AS instance_types_1_created_at, instance_types_1.updated_at AS instance_types_1_updated_at, instance_types_1.deleted_at AS instance_types_1_deleted_at, instance_types_1.deleted AS instance_types_1_deleted, instance_types_1.id AS instance_types_1_id, instance_types_1.name AS instance_types_1_name, instance_types_1.memory_mb AS instance_types_1_memory_mb, instance_types_1.vcpus AS instance_types_1_vcpus, instance_types_1.root_gb AS instance_types_1_root_gb, instance_types_1.ephemeral_gb AS instance_types_1_ephemeral_gb, instance_types_1.flavorid AS instance_types_1_flavorid, instance_types_1.swap AS instance_types_1_swap, instance_types_1.rxtx_factor AS instance_types_1_rxtx_factor, instance_types_1.vcpu_weight AS instance_types_1_vcpu_weight, instance_types_1.disabled AS instance_types_1_disabled, instance_metadata_1.created_at AS instance_metadata_1_created_at, instance_metadata_1.updated_at AS instance_metadata_1_updated_at, instance_metadata_1.deleted_at AS instance_metadata_1_deleted_at, instance_metadata_1.deleted AS instance_metadata_1_deleted, instance_metadata_1.id AS instance_metadata_1_id, instance_metadata_1."key" AS instance_metadata_1_key, instance_metadata_1.value AS instance_metadata_1_value, instance_metadata_1.instance_uuid AS instance_metadata_1_instance_uuid, instance_info_caches_1.created_at AS instance_info_caches_1_created_at, instance_info_caches_1.updated_at AS instance_info_caches_1_updated_at, instance_info_caches_1.deleted_at AS instance_info_caches_1_deleted_at, instance_info_caches_1.deleted AS instance_info_caches_1_deleted, instance_info_caches_1.id AS instance_info_caches_1_id, instance_info_caches_1.network_info AS instance_info_caches_1_network_info, instance_info_caches_1.instance_uuid AS instance_info_caches_1_instance_uuid \nFROM instances LEFT OUTER JOIN security_group_instance_association AS security_group_instance_association_1 ON security_group_instance_association_1.instance_uuid = instances.uuid AND instances.deleted = ? LEFT OUTER JOIN security_groups AS security_groups_1 ON security_groups_1.id = security_group_instance_association_1.security_group_id AND security_group_instance_association_1.deleted = ? AND security_groups_1.deleted = ? LEFT OUTER JOIN instance_types AS instance_types_1 ON instances.instance_type_id = instance_types_1.id LEFT OUTER JOIN instance_metadata AS instance_metadata_1 ON instance_metadata_1.instance_uuid = instances.uuid AND instance_metadata_1.deleted = ? LEFT OUTER JOIN instance_info_caches AS instance_info_caches_1 ON instance_info_caches_1.instance_uuid = instances.uuid \nWHERE instances.deleted = ? AND instances.vm_state != ? AND instances.project_id = ? AND (instances.display_name REGEXP ?) ORDER BY instances.created_at DESC' (0, 0, 0, 0, 0, 'soft-delete', 'account1', 'Apollo:106842')
2012-08-15 12:56:05 TRACE nova.api.openstack
2012-08-15 12:56:05 INFO nova.api.openstack [req-37ab9251-ae08-4cb8-87c4-efb132b9fc39 account1 account1] http://localhost:8774/v1.1/account1/servers/detail?name=Apollo%3A106842 returned with HTTP 500
2012-08-15 12:56:05 INFO nova.osapi_compute.wsgi.server [req-37ab9251-ae08-4cb8-87c4-efb132b9fc39 account1 account1] 127.0.0.1 - - [15/Aug/2012 12:56:05] "GET /v1.1/account1/servers/detail?name=Apollo%3A106842 HTTP/1.1" 500 335 0.037569
Per my reading sqlite doesn't have native support for REGEX:
http://www.sqlite.org/lang_expr.html#regexp
This attached patch fixes the issue for me.
Another option may be to use sqlite-pcre, but you'll have to load the so for each connection or setup a sqliterc - but, IMHO, that seems like more trouble than it's worth.
When sqlalchemy makes the connect to sqlite it should be adding a PoolListener for regexp. Apparently PoolListeners are deprecated so perhaps it would be best to not maintain regexp support for sqllite as you suggest.