DB审核查询平台Archery--安装部署可能遇到的问题

Archery介绍

Archery是archer的分支项目，定位于SQL审核查询平台，旨在提升DBA的工作效率，支持多数据库的SQL上线和查询，同时支持丰富的MySQL运维功能，所有功能都兼容手机端操作。

功能清单

数据库类型	查询	审核	执行	备份	数据字典	慢日志	会话管理	账号管理	参数管理	数据归档
MySQL	√	√	√	√	√	√	√	√	√	√
MsSQL	√	×	√	×	×	×	×	×	×	×
Redis	√	×	√	×	×	×	×	×	×	×
PgSQL	√	×	√	×	×	×	×	×	×	×
Oracle	√	×	√	√	×	×	×	×	×	×
MongoDB	√	×	√	×	×	×	×	×	×	×

因基础环境不同，在安装部署中可能遇到的异常，如下：

1.libmysqlclient.so.20 异常

Watching for file changes with StatReloader
Exception in thread django-main-thread:
Traceback (most recent call last):
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/__init__.py", line 18, in <module>
    from . import _mysql
ImportError: libmysqlclient.so.20: cannot open shared object file: No such file or directory

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.6/threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/runserver.py", line 110, in inner_run
    autoreload.raise_last_exception()
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 76, in raise_last_exception
    raise _exception[1]
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 357, in execute
    autoreload.check_errors(django.setup)()
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "/usr/local/lib/python3.6/site-packages/django/apps/registry.py", line 114, in populate
    app_config.import_models()
  File "/usr/local/lib/python3.6/site-packages/django/apps/config.py", line 211, in import_models
    self.models_module = import_module(models_module_name)
  File "/usr/lib64/python3.6/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 994, in _gcd_import
  File "<frozen importlib._bootstrap>", line 971, in _find_and_load
  File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 665, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/usr/local/lib/python3.6/site-packages/django/contrib/auth/models.py", line 2, in <module>
    from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
  File "/usr/local/lib/python3.6/site-packages/django/contrib/auth/base_user.py", line 48, in <module>
    class AbstractBaseUser(models.Model):
  File "/usr/local/lib/python3.6/site-packages/django/db/models/base.py", line 122, in __new__
    new_class.add_to_class('_meta', Options(meta, app_label))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/base.py", line 326, in add_to_class
    value.contribute_to_class(cls, name)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/options.py", line 206, in contribute_to_class
    self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())
  File "/usr/local/lib/python3.6/site-packages/django/db/__init__.py", line 28, in __getattr__
    return getattr(connections[DEFAULT_DB_ALIAS], item)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 214, in __getitem__
    backend = load_backend(db['ENGINE'])
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 111, in load_backend
    return import_module('%s.base' % backend_name)
  File "/usr/lib64/python3.6/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 15, in <module>
    import MySQLdb as Database
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/__init__.py", line 24, in <module>
    version_info, _mysql.version_info, _mysql.__file__
NameError: name '_mysql' is not defined

解决方案：

因为我们本机安装了mysql，本地由libmysqlclient.so.20文件包，所以简单做个软链接即可。

ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20

2.安装安装pyodbc报错

Using legacy 'setup.py install' for pyodbc, since package 'wheel' is not installed.
Installing collected packages: pyodbc
    Running setup.py install for pyodbc ... error
    ERROR: Command errored out with exit status 1:
     command: /usr/bin/python3 -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-jys5r5zz/pyodbc_bXXXXXd/setup.py'"'"'; __file__='"'"'/tmp/pip-install-jys5r5zz/pyodbc_b2bXXXX4d/setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__file__) if os.path.exists(__file__) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record /tmp/pip-record-4o7hogt9/install-record.txt --single-version-externally-managed --compile --install-headers /usr/local/include/python3.6m/pyodbc
         cwd: /tmp/pip-install-jys5r5zz/pyodbc_b2bc9990d79a4871bf031ae2d0c51d4d/
    Complete output (18 lines):
    /usr/local/lib/python3.6/site-packages/setuptools/dist.py:726: UserWarning: Usage of dash-separated 'library-dirs' will not be supported in future versions. Please use the underscore name 'library_dirs' instead
      % (opt, underscore_opt)
    running install
    /usr/local/lib/python3.6/site-packages/setuptools/command/install.py:37: SetuptoolsDeprecationWarning: setup.py install is deprecated. Use build and pip and other standards-based tools.
      setuptools.SetuptoolsDeprecationWarning,
    running build
    running build_ext
    building 'pyodbc' extension
    creating build
    creating build/temp.linux-x86_64-3.6
    creating build/temp.linux-x86_64-3.6/src
    gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -DPYODBC_VERSION=4.0.30 -I/usr/include/python3.6m -c src/buffer.cpp -o build/temp.linux-x86_64-3.6/src/buffer.o -Wno-write-strings
    In file included from src/buffer.cpp:12:0:
    src/pyodbc.h:56:17: fatal error: sql.h: No such file or directory
     #include <sql.h>
                     ^
    compilation terminated.
    error: command 'gcc' failed with exit status 1
    ----------------------------------------
ERROR: Command errored out with exit status 1: /usr/bin/python3 -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-jys5r5zz/pyodbc_XXXXX/setup.py'"'"'; __file__='"'"'/tmp/pip-install-jys5r5zz/pyodbc_XXXXXXX/setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__file__) if os.path.exists(__file__) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record /tmp/pip-record-4o7hogt9/install-record.txt --single-version-externally-managed --compile --install-headers /usr/local/include/python3.6m/pyodbc Check the logs for full command output.

解决方案：

先安装unixODBC-devel，执行命令如下：

 yum install unixODBC-devel

在此安装就好好了。

如果没有解决，一直是类似如下错误:

creating build/temp.linux-x86_64-3.6
  creating build/temp.linux-x86_64-3.6/src
  gcc -pthread -Wno-unused-result -Wsign-compare -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -DPYODBC_VERSION=4.0.30 -I/data/aliyunniubi/include -I/usr/include/python3.6m -c src/buffer.cpp -o build/temp.linux-x86_64-3.6/src/buffer.o -Wno-write-strings -DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/include
  In file included from src/buffer.cpp:12:0:
  src/pyodbc.h:45:20: fatal error: Python.h: No such file or directory
   #include <Python.h>
                      ^
  compilation terminated.
  error: command 'gcc' failed with exit status 1
  ----------------------------------------
  ERROR: Failed building wheel for pyodbc

呵呵。。。，可以尝试以下方案：

yum install python-devel

或

yum install python3-devel

根据版本决定。

3.安装django-auth-ldap报错

In file included from Modules/LDAPObject.c:3:0:
  Modules/common.h:9:20: fatal error: Python.h: No such file or directory
   #include "Python.h"
                      ^
  compilation terminated.
  error: command 'gcc' failed with exit status 1
  ----------------------------------------
  ERROR: Failed building wheel for python-ldap
Failed to build python-ldap
ERROR: Could not build wheels for python-ldap, which is required to install pyproject.toml-based projects

解决方案：

需要先安装pip3 install python-ldap，而安装之前需要安装yum install openldap-devel。

安装上面两个插件之后，再此安装就报错了。

参考：https://www.cnblogs.com/wangjian941118/p/13518515.html

4.项目启动报错

python manage.py runserver

 File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (1049, "Unknown database 'archery'")

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

Traceback (most recent call last):
  File "/usr/lib64/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "/usr/lib64/python3.6/threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/utils/autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/runserver.py", line 121, in inner_run
    self.check_migrations()


--------------------------------
File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
django.db.utils.OperationalError: (1049, "Unknown database 'archery'")

解决方案

系统默认的数据库是archery。

我们可以先手动创建创建这个数据库。

5.未初始化导致报错

You have 31 unapplied migration(s). Your project may not work properly until you apply the migrations for app(s): admin, auth, contenttypes, django_q, sessions.
Run 'python manage.py migrate' to apply them.

解决方案：

执行初始化命令

python manage.py makemigrations
python manage.py migrate

6.执行初始化时报错，不能创建外键

File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 245, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 229, in apply_migration
    migration_recorded = True
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 115, in __exit__
    self.execute(sql)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.IntegrityError: (1215, 'Cannot add foreign key constraint')

解决方案：

set global FOREIGN_KEY_CHECKS=0

参考：

https://stackoverflow.com/questions/12966626/mysql-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails

再次初始化，不再报错。

7.启动还是报错，此次错误说缺少表

[ERROR]- 获取系统配置信息失败:(1146, "Table 'archery.sql_config' doesn't exist")Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1146, "Table 'archery.sql_config' doesn't exist")

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

Traceback (most recent call last):
  File "/data/scripts/Archery-master/common/config.py", line 25, in get_all_config
    for items in all_config:
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 287, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 1308, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 111, in __iter__
    for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'archery.sql_config' doesn't exist")

解决方案：

step 1.将之前存在的库删除掉：

drop database archery

step 2.先初始化sql应用的脚本

python manage.py makemigrations sql

python manage.py migrate sql

step 3 再初始化整个项目的脚本

python manage.py makemigrations

python manage.py migrate

注意上面的顺序。

到此，此项目再正是部署完成，才算基本调通。

8.服务启动，网址可访问

login会显示输入账号，此时需要进行数据初始化。

# 数据初始化
python3 manage.py dbshell<sql/fixtures/auth_group.sql
python3 manage.py dbshell<src/init_sql/mysql_slow_query_review.sql

# 创建管理用户
python3 manage.py createsuperuser