Inconsistent results from all endpoints on MariaDB

[christabor]

I have used this successfully with postgresql on OSX and CentOS 7.2, but when using with MariaDB on CentOS 7.2, the results are seemingly random and inconsistent.

e.g.

curl -XGET http://mywebsite.com/api/resource, or curl -XGET http://mywebsite.com/api/resource/1 will return none or a result(s), non-deterministically. Are there known issues with MariaDB and/or mysql?

[jfinkels]

The unit tests basically only cover SQLite's in-memory database backend, although there are a few tests for some PostgreSQL-specific operators. So the behavior with MySQL is untested (although SQLAlchemy should handle everything without error).

I understand the errors you are seeing are arbitrary, but it would be helpful if you could construct a small self-contained minimal working example that demonstrates the issue.

[christabor]

No problem - I'm a big fan of this project so I'd really like to sort this out! I will get something for you tomorrow.

I would also note that there are no errors as far as I can tell.. Unless they are being gracefully handled and suppressed.

[christabor]

Sorry for the delay. Here's an example app:

"""API App."""

from __future__ import absolute_import

from datetime import datetime as dt
import os

from sqlalchemy import (
    Column,
    DateTime,
    ForeignKey,
    Integer,
    String,
)
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

from flask import Flask
from flask.ext.migrate import Migrate, MigrateCommand
from flask.ext.script import Manager
import flask_restless
from flask_restful import Api


Base = declarative_base()
DB_URI = os.getenv('DB_URI')
ENGINE = create_engine(DB_URI, echo=True)
SESSION = scoped_session(sessionmaker(bind=ENGINE))


class Events(Base):
    """Events table."""

    __tablename__ = 'events'

    id = Column(Integer, autoincrement=True, primary_key=True)
    start = Column(DateTime)
    end = Column(DateTime)
    updates = relationship('Updates', back_populates='event')


class Updates(Base):
    """Updates table."""

    __tablename__ = 'updates'

    id = Column(Integer, autoincrement=True, primary_key=True)
    datetime = Column(DateTime, default=dt.now)
    update = Column(String(5000))
    event_id = Column(Integer, ForeignKey('events.id'))
    event = relationship('Events', back_populates='updates')


app = Flask(__name__)
app.debug = True
api = Api(app)
manager = flask_restless.APIManager(app, session=SESSION)

# Setup Migrations
migrate = Migrate(app, ENGINE)

# Setup script Manager
script_manager = Manager(app)
script_manager.add_command('db', MigrateCommand)

event_api_options = dict(
    methods=['GET', 'POST', 'PUT'],
    results_per_page=100,
)

update_api_options = dict(
    methods=['GET', 'POST', 'PUT'],
    results_per_page=100,
)

manager.create_api(Updates, **update_api_options)
manager.create_api(Events, **event_api_options)


if __name__ == '__main__':
    script_manager.run()

[jfinkels]

Thanks, I'll take a look at this.

[jfinkels]

Hi @christabor! The example you posted has a few seemingly irrelevant things: a Flask-Restful API, a script Manager, a Migrate object, multiple models, etc. If you are able, it would be helpful for me to get a minimal version of this module that demonstrates the problem. Also, can you post the database connection URI that you use?

[christabor]

I cleaned up the above example to remove some extraneous code.

Mysql connection string (env var "DB_URI") is like this: mysql://user:pass@localhost/dbname
I haven't tried using sockets.

[jfinkels]

Thanks, I'll take another look at it.