'Specified key was too long' error persists after updating MariaDB to10.1.22

[misp]

'Specified key was too long' error persists after updating MariaDB to10.1.22

[mcamachotw]

@FloatingGhost I understand, thank you for your quickly reply.

[davidljohnson]

I also tried recreating my taxiiauth and taxiipersist databases after updating mariadb to the new version, but no dice...

[FloatingGhost]

You will probably chastise me for using python 2.7

you're damn right I will

use real python!

that's how my environment was initially set up when I installed MISP.

you can run multiple python versions at once y'know

Try installing under py3 and see if you get anything good - this error was certainly an old mysql thingy, idk why you're getting it under 10.1.22

[davidljohnson]

Thanks for the feedback -- unfortunately, I'm still receiving the same error. But at least now, it feels good to be using a version of python that's actually still supported. I went ahead and rebuilt the taxiiauth an taxiipersist databases and performed a 'sudo apt-get update && sudo apt-get upgrade' to ensure I'm using the latest versions of my software packages. Also, when I built this using python3, I had to install mysqldb via the command 'sudo apt-get install python3-mysqldb'. Not sure what is wrong. :(

/var/git/MISP-Taxii-Server/config$ opentaxii-create-services -c config/services.yaml
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 226, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 217, in execute
res = self._query(query)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 378, in _query
rowcount = self._do_query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 341, in _do_query
db.query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 280, in query
_mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 767 bytes')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/local/bin/opentaxii-create-services", line 9, in
load_entry_point('opentaxii==0.1.9a1', 'console_scripts', 'opentaxii-create-services')()
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 542, in load_entry_point
return get_distribution(dist).load_entry_point(group, name)
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 2569, in load_entry_point
return ep.load()
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 2229, in load
return self.resolve()
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 2235, in resolve
module = import(self.module_name, fromlist=['name'], level=0)
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/cli/init.py", line 10, in
server = TAXIIServer(config)
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/server.py", line 38, in init
server=self, api=initialize_api(config['persistence_api']))
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/utils.py", line 36, in initialize_api
instance = cls(**params)
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/persistence/sqldb/api.py", line 43, in init
self.db.create_all_tables()
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/sqldb_helper.py", line 60, in create_all_tables
self.metadata.create_all(bind=self.engine)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/schema.py", line 3885, in create_all
tables=tables)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1929, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1538, in _run_visitor
**kwargs).traverse_single(element)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 733, in visit_metadata
_is_metadata_operation=True)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 772, in visit_table
self.traverse_single(index)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 791, in visit_index
self.connection.execute(CreateIndex(index))
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
compiled
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/SQLAlchemy-1.1.9-py3.5-linux-x86_64.egg/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 226, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 217, in execute
res = self._query(query)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 378, in _query
rowcount = self._do_query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 341, in _do_query
db.query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 280, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes') [SQL: 'CREATE UNIQUE INDEX ix_data_collections_name ON data_collections (name)']

[FloatingGhost]

It's a long shot, but can you try running

SET @@global.innodb_large_prefix = 1;

on your sql server?

[davidljohnson]

Here's some information on the character sets of my tables.

MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "taxiipersist";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4 |
+----------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "taxiiauth";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4 |
+----------------------------+
1 row in set (0.00 sec)

[davidljohnson]

It's a long shot, but can you try running

SET @@global.innodb_large_prefix = 1;

No dice :(

[davidljohnson]

Do I need to change this to a 3-byte character set? I think utf8mb4 is 4 bytes. I'll give it a shot and update this thread accordingly.

[davidljohnson]

Dang! That didn't work either. Both databases are in 'utf8', but still receiving the same error.

[FloatingGhost]

Do dee dooooo!

ALTER DATABASE taxiipersist CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER DATABASE taxiiauth CHARACTER SET latin1 COLLATE latin1_general_ci;

run that!

[davidljohnson]

Thank God you figured that out. Now I'm getting a different error:

/var/git/MISP-Taxii-Server/config$ opentaxii-create-services -c config/services.yaml
2017-04-05T15:02:59.792149Z [opentaxii.utils] info: api.initialized {api=opentaxii.persistence.sqldb.SQLDatabaseAPI, event=api.initialized, logger=opentaxii.utils, level=info, timestamp=2017-04-05T15:02:59.792149Z}
2017-04-05T15:02:59.832473Z [opentaxii.utils] info: api.initialized {api=opentaxii.auth.sqldb.SQLDatabaseAPI, event=api.initialized, logger=opentaxii.utils, level=info, timestamp=2017-04-05T15:02:59.832473Z}
Traceback (most recent call last):
File "/usr/local/bin/opentaxii-create-services", line 9, in
load_entry_point('opentaxii==0.1.9a1', 'console_scripts', 'opentaxii-create-services')()
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 542, in load_entry_point
return get_distribution(dist).load_entry_point(group, name)
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 2569, in load_entry_point
return ep.load()
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 2229, in load
return self.resolve()
File "/usr/lib/python3/dist-packages/pkg_resources/init.py", line 2235, in resolve
module = import(self.module_name, fromlist=['name'], level=0)
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/cli/init.py", line 10, in
server = TAXIIServer(config)
File "/usr/local/lib/python3.5/dist-packages/opentaxii-0.1.9a1-py3.5.egg/opentaxii/server.py", line 45, in init
importlib.import_module(signal_hooks)
File "/usr/lib/python3.5/importlib/init.py", line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
ImportError: No module named 'misp_taxii_hooks'

[FloatingGhost]

ah, that one's easy! You didn't run sudo python3 setup.py install from the MISP-Taxii-Server folder!

[davidljohnson]

Great, that worked! I guess I was confused about the order of the instructions and thought I needed to run these commands before running sudo python3 setup.py install in the parent directory.

opentaxii-create-services -c config/services.yaml
opentaxii-create-collections -c config/collections.yaml
opentaxii-create-account -u root -p root.

Now I'm getting a certificate verification failure with my MISP instance, but that's a different issue that I should be able to resolve myself (hopefully).

Thanks very much!

[FloatingGhost]

pymisp.exceptions.PyMISPError: Unable to connect to MISP (http://192.168.1.106:9000). Please make sure the API key and the URL are correct (http/https is required): HTTPConnectionPool(host='192.168.1.106', port=9000):

Seriously!? You're asking for support on an issue that explains itself so very clearly?

Jesus merciful christ end my suffering.

It's saying that it can't connect to MISP on port 9000 on that address, with the API key you provided.

Config should be

misp:
  url: WHEREVER YOUR MISP INSTANCE IS LOCATED
  api: WHATEVER YOUR API KEY IS ON THAT INSTANCE