DB審核查詢平臺Archery--安裝部署可能遇到的問題

Archery介紹

Archery是archer的分支專案，定位于SQL審核查詢平臺，旨在提升DBA的作業效率，支持多資料庫的SQL上線和查詢，同時支持豐富的MySQL運維功能，所有功能都兼容手機端操作，

功能清單

因基礎環境不同，在安裝部署中可能遇到的例外，如下：

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

在此安裝就好好了，

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

參考：

1.手動部署--Archery

https://archerydms.com/installation/manual/

2.開源SQL審核平臺——Archery 安裝、部署心得 

https://www.cnblogs.com/David-domain/archive/2019/07/10/11165734.html

3.Archery SQL審核查詢平臺

https://archerydms.com/

標籤：其他

上一篇：PostgreSQL 的欄位型別和表操作筆記

下一篇：MySQL函式1(單行函式)