0x0BCA
commented
4 years ago This is because there are foreign key Integer to int(11) - which are different types. You can use the following create statements as a workaround (not sure this causes issues else where though):
CREATE TABLE inbox_messages ( date_created DATETIME, id int(11) NOT NULL AUTO_INCREMENT, message_id TEXT, result_id TEXT, record_count INTEGER, partial_count BOOL, subscription_collection_name TEXT, subscription_id TEXT, exclusive_begin_timestamp_label DATETIME, inclusive_end_timestamp_label DATETIME, original_message MEDIUMBLOB NOT NULL, content_block_count INTEGER, destination_collections TEXT, service_id VARCHAR(150), PRIMARY KEY (id), CHECK (partial_count IN (0, 1)), FOREIGN KEY(service_id) REFERENCES services (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE subscriptions ( date_created DATETIME, id VARCHAR(150) NOT NULL, collection_id int(11), params TEXT, status VARCHAR(150), service_id VARCHAR(150), PRIMARY KEY (id), FOREIGN KEY(collection_id) REFERENCES data_collections (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(service_id) REFERENCES services (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE service_to_collection ( service_id VARCHAR(150) NOT NULL, collection_id int(11) NOT NULL, PRIMARY KEY (service_id, collection_id), FOREIGN KEY(service_id) REFERENCES services (id) ON DELETE CASCADE, FOREIGN KEY(collection_id) REFERENCES data_collections (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
cohenshimi
derekzhouzhili
hey, i try to install TAXII-MISP-SERVER,
When I run this command : opentaxii-sync-data config/data-configuration.yaml
i get the error :
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1005, 'Can\'t create table
taxiipersist.service_to_collection(errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: CREATE TABLE service_to_collection ( service_id VARCHAR(150) NOT NULL, collection_id INTEGER NOT NULL, PRIMARY KEY (service_id, collection_id), FOREIGN KEY(service_id) REFERENCES services (id) ON DELETE CASCADE, FOREIGN KEY(collection_id) REFERENCES data_collections (id) ON DELETE CASCADE )its look like bug , any help ?
here is all the log :
Traceback (most recent call last): File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1278, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 259, in query _mysql.connection.query(self, query) MySQLdb._exceptions.OperationalError: (1005, 'Can\'t create table
taxiipersist.service_to_collection(errno: 150 "Foreign key constraint is incorrectly formed")')The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/local/bin/opentaxii-sync-data", line 5, in
from opentaxii.cli.persistence import sync_data_configuration
File "/usr/local/lib/python3.6/dist-packages/opentaxii/cli/init.py", line 10, in
server = TAXIIServer(config)
File "/usr/local/lib/python3.6/dist-packages/opentaxii/server.py", line 36, in init
server=self, api=initialize_api(config['persistence_api']))
File "/usr/local/lib/python3.6/dist-packages/opentaxii/utils.py", line 38, in initialize_api
instance = cls(params)
File "/usr/local/lib/python3.6/dist-packages/opentaxii/persistence/sqldb/api.py", line 46, in init
self.db.create_all_tables()
File "/usr/local/lib/python3.6/dist-packages/opentaxii/sqldb_helper.py", line 60, in create_all_tables
self.metadata.create_all(bind=self.engine)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/schema.py", line 4547, in create_all
ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 2098, in _run_visitor
conn._run_visitor(visitorcallable, element, kwargs)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1657, in _run_visitor
visitorcallable(self.dialect, self, kwargs).traverse_single(element)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single
return meth(obj, kw)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 787, in visit_metadata
_is_metadata_operation=True,
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single
return meth(obj, **kw)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 832, in visit_table
include_foreign_key_constraints, # noqa
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1014, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1076, in _execute_ddl
compiled,
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1318, in _execute_context
e, statement, parameters, cursor, context
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1512, in _handle_dbapi_exception
sqlalchemy_exception, with_traceback=excinfo[2], from=e
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1278, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/usr/local/lib/python3.6/dist-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/usr/local/lib/python3.6/dist-packages/MySQLdb/connections.py", line 259, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1005, 'Can\'t create table
taxiipersist.service_to_collection(errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: CREATE TABLE service_to_collection ( service_id VARCHAR(150) NOT NULL, collection_id INTEGER NOT NULL, PRIMARY KEY (service_id, collection_id), FOREIGN KEY(service_id) REFERENCES services (id) ON DELETE CASCADE, FOREIGN KEY(collection_id) REFERENCES data_collections (id) ON DELETE CASCADE )] (Background on this error at: http://sqlalche.me/e/13/e3q8)
Thanks!! 👍