SQLAlchemy db interactions fail depending how I run the python script

Question

This seems to be the most obscure issue I have dealt with so far. I am struggling hard.

I have an application that collects remote data from some hardware and saves it to a database. I renamed one of the columns using alembic. I have a development database and a testing database. Both are on the same server (MySQL via MariaDB on CentOS 7).

I interact with the development server via the app running on my laptop and the testing db is interacted with via the app running on a clone of the production server.

All servers and databases are set up using Ansible, so differences are limited to usernames and passwords.

Below is the ansible snippet from the upgrade script

def upgrade():
    op.alter_column('units', 'ip_address', new_column_name='ipv4', existing_type=sa.String(100))

If I run the app from my laptop (using my IDE), the data is saved ok.

If I run the script on the testing server manually (env)$ python app.py, the data is saved ok.

But, here's the problem, If I run the script using supervisord, I get an SQLAlchemy error. (excerpts below, full traceback)

...
File "thefile.py", line 64, in run
    self.data['my_wellsites'][w.name]['ipv4'] = wellsite.unit.ipv4
...
sqlalchemy.exc.InternalError: (InternalError) (1054, u"Unknown column 'units.ipv4' in 'field list'") 'SELECT units.id AS units_id, units.unittype_id AS units_unittype_id, units.serial AS units_serial, units.ipv4 AS units_ipv4, units.mac_address AS units_mac_address, units.engine_hours AS units_engine_hours, units.gen_odometer AS units_gen_odometer, units.gen_periodic AS units_gen_periodic, units.notes AS units_notes \nFROM units \nWHERE units.id = %s' (1,)
...

SQLAlchemy models..

class Unit(Model):
    __tablename__ = 'units'
    __table_args__ = {'mysql_engine': 'InnoDB'}

    id = Column(Integer, Sequence('unit_id_seq'), primary_key=True)
    wellsites = relationship('Wellsite', order_by='Wellsite.id', backref='unit')
    ipv4 = Column(String(16), unique=True)
    ...

class Wellsite(Model):
    __tablename__ = 'wellsites'
    __table_args__ = {'mysql_engine': 'InnoDB'}

    id = Column(Integer, Sequence('wellsite_id_seq'), primary_key=True)
    unit_id = Column(Integer, ForeignKey('units.id'), nullable=False)
    ...

etc/supervisord.conf..

...
[program:datacollect]
command = /home/webdev/mydevelopment/git/ers_data_app/env/bin/python /home/webdev/mydevelopment/git/ers_data_app/data_monitoring/collection_manager.py
stdout_logfile=/home/webdev/logs/datacollect.log
stderr_logfile=/home/webdev/logs/datacollecterr.log
autostart=true
autorestart=unexpected
startsecs=10

I tried running an additional alembic upgrade with

op.create_unique_constraint('uq_ipv4', 'units', ['ipv4'])

No dice.

The traceback is identical (using diff program) except timestamps

Here are the two database descriptions of the units table (identical)

MariaDB [ers_DEV]> show columns from units;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| unittype_id  | int(11)      | YES  | MUL | NULL    |                |
| serial       | varchar(10)  | YES  | UNI | NULL    |                |
| ipv4         | varchar(100) | YES  | UNI | NULL    |                |
| mac_address  | varchar(17)  | YES  | UNI | NULL    |                |
| engine_hours | int(11)      | YES  |     | NULL    |                |
| gen_odometer | tinyint(1)   | YES  |     | NULL    |                |
| gen_periodic | tinyint(1)   | YES  |     | NULL    |                |
| notes        | mediumtext   | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+


MariaDB [ers_TEST]> show columns from units;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| unittype_id  | int(11)      | YES  | MUL | NULL    |                |
| serial       | varchar(10)  | YES  | UNI | NULL    |                |
| ipv4         | varchar(100) | YES  | UNI | NULL    |                |
| mac_address  | varchar(17)  | YES  | UNI | NULL    |                |
| engine_hours | int(11)      | YES  |     | NULL    |                |
| notes        | mediumtext   | YES  |     | NULL    |                |
| gen_odometer | tinyint(1)   | YES  |     | NULL    |                |
| gen_periodic | tinyint(1)   | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

score 0 · Answer 1 · answered Jul 21 '15 at 15:22

The issue was at the top of the /etc.supervisord.conf file. In it, an environment variable is set that was pointing the script to the wrong database, overriding the environment variable that was set and examined from anywhere else. This environment variable is only set when the script is being run from supervisor and was causing the trouble

SQLAlchemy db interactions fail depending how I run the python script

1 Answers1