search

WhyAskWhy / mysql2sqlite

Query MySQL database and mirror relevant tables to a local SQLite database.
GNU General Public License v2.0
0 stars 1 forks source link

MySQLInterfaceError: Table 'mailserver.mail_relay_whitelist' doesn't exist #9

Open deoren opened 6 years ago

deoren commented 6 years ago

Backstory

The output below is from a test run of the mysql2sqlite.py script (2b56d64 as indicated below) against a freshly imported backup production copy of our mailserver database. This older database has limited clients and the sending nodes is a very small list, so I've not opted to use a separate mail relay whitelist for those nodes, but have instead hard-coded them in my prod Postfix configuration.

Here the "wrapper" script from the dev environment is calling the mysql2sqlite.py script to generate a fresh copy of the SQLite database. The test database has a mail_relay_whitelist table that the INI file references, but not my prod db.

The idea was that the user using the mysql2sqlite.py script would first update both INI files (the settings file and the table/queries list) to fit their environment before running the script. If that is the expected workflow, then this error output is probably as expected and not much needs to be done about it, other than to perhaps point the sysadmin back to our documentation explaining how the script and the config files are meant to be modified/configured before first use.

Error output

ubuntu@ubuntu-1604-test:/tmp/mysql2sqlite-dev$ bash bin/create_db.sh

HEAD is now at 2b56d64 Merge pull request #8 from deoren/first-release
Already up-to-date.
ownership of '/var/cache/mysql2sqlite/mailserver.db' retained as ubuntu:ubuntu
ownership of '/var/cache/mysql2sqlite' retained as ubuntu:ubuntu
Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.5/site-packages/mysql/connector/connection_cext.py", line 392, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: Table 'mailserver.mail_relay_whitelist' doesn't exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/tmp/mysql2sqlite/mysql2sqlite.py", line 304, in <module>
    mysql_cursor.execute(query_settings.queries[table]['read'])
  File "/home/ubuntu/.local/lib/python3.5/site-packages/mysql/connector/cursor_cext.py", line 264, in execute
    raw_as_string=self._raw_as_string)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/mysql/connector/connection_cext.py", line 395, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'mailserver.mail_relay_whitelist' doesn't exist
FAILURE to generate SQLite db file!
deoren commented 6 years ago

I started to name this issue, "Queries INI file must be configured before script's first run" as ultimately I think that is the workflow needed here. If that is really what should happen, then we should probably put a flag within the main config file that forces an early exit with verbose instructions until the user flips the flag to off.

Will leave this open until that support is added or until I come up with something better. We could just have the script skip past missing tables, but I think that will likely lead to typos causing a warning/error to be uncaught and source tables to be unprocessed when intended to be otherwise.

deoren commented 6 years ago

Related: #3

By renaming the existing config files and making them templates it should help somewhat with references to tables that do not exist. The hope is that when the template files are copied and modified any invalid table references will be removed and valid ones inserted.

I think we can do further work to better handle missing/invalid table references, but the main issue is mostly addressed by #3.