There is an issue for MySQL Support and SQLAlchemy datadefinitions

[mayday6971]

When zato 2.0 is installed and the database is MySQL,the zato create odb command returns an exception.

InternalError: (InternalError) (1074, u"Column length too big for column 'query' (max = 65535); use BLOB or TEXT instead") '\nCREATE TABLE notif_sql (\n\tid INTEGER NOT NULL, \n\tquery VARCHAR(200000) NOT NULL, \n\tdef_id INTEGER NOT NULL, \n\tPRIMARY KEY (id, def_id), \n\tFOREIGN KEY(id) REFERENCES notif (id), \n\tFOREIGN KEY(def_id) REFERENCES sql_pool (id)\n)\n\n' ()

The command I was running:
zato create odb --verbose --store-config --odb_host localhost --odb_port 3306 --odb_user zato --odb_db_name zato --odb_password=zato mysql

To fix this, I had to edit:
/code/zato-common/src/zato/common/odb/model.py

I added a new SQLAlchemy import for Text and adjusted the query column in the notify_sql table to be a TEXT column instead of a varchar(200000).

Here are the edits:

# SQLAlchemy
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Sequence, \
     Text, Boolean, LargeBinary, UniqueConstraint, Enum, SmallInteger
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship

and the table definition change:

class NotificationSQL(Notification):
    """ New in 2.0: Stores SQL notifications.
    """
    __tablename__ = 'notif_sql'
    __mapper_args__ = {'polymorphic_identity': 'sql'}

    id = Column(Integer, ForeignKey('notif.id'), primary_key=True)

    query = Column(Text, nullable=False)

    def_id = Column(Integer, ForeignKey('sql_pool.id'), primary_key=True)
    definition = relationship(SQLConnectionPool, backref=backref('notif_sql_list', order_by=id, cascade='all, delete, delete-orphan'))

Let me know if I can be of more help.

[dsuch]

Thanks @mayday6971 - good timing, this will be included in the upcoming next week's release!

[dsuch]

This is fixed as of 816e3d1 and will be included in Zato 2.0.0.

Again, many thanks for spotting and reporting it!