python - Can't create_all Flask-SQLAlchemy - specified key was too long -
when try run db.create_all() create mysql tables, following error:
sqlalchemy.exc.internalerror: (pymysql.err.internalerror) (1071, 'specified key long; max key length 767 bytes') [sql: '\ncreate table post (\n\tid integer not null auto_increment, \n\tblog_id integer, \n\tuser_id integer, \n\ttitle varchar(80), \n\tbody text, \n\timage varchar(255), \n\tslug varchar(256), \n\tpublish_date datetime, \n\tlive bool, \n\ttag_id integer, \n\tprimary key (id), \n\tforeign key(blog_id) references blog (id), \n\tforeign key(user_id) references user (id), \n\tunique (slug), \n\tcheck (live in (0, 1)), \n\tforeign key(tag_id) references tag (id)\n)\n\n'] how can resolve error? i'm unsure source of issue. can create_all() db in dev environment, not on host. i've assumed it's column size restriction on server's mysql service. encoding table contents inefficiently? did structure models incorrectly? i've tried change settings.py:
db_uri = "mysql+pymysql://%s:%s@%s/%s?charset=utf8" % (db_username, db_password, db_host, blog_database_name)
i tried set @@global.innodb_large_prefix = 1; this question suggested.
models
from my_app import db, uploaded_images datetime import datetime class blog(db.model): id = db.column(db.integer, primary_key=true) name = db.column(db.string(80)) admin = db.column(db.integer, db.foreignkey('user.id')) posts = db.relationship('post', backref='blog', lazy='dynamic') def __init__(self, name, admin): self.name = name self.admin = admin def __repr__(self): return '<name %r>' % self.name class post(db.model): id = db.column(db.integer, primary_key=true) blog_id = db.column(db.integer, db.foreignkey('blog.id')) user_id = db.column(db.integer, db.foreignkey('user.id')) title = db.column(db.string(80)) body = db.column(db.text) image = db.column(db.string(255)) slug = db.column(db.string(256), unique=true) publish_date = db.column(db.datetime) live = db.column(db.boolean) tag_id = db.column(db.integer, db.foreignkey('tag.id')) tag = db.relationship('tag', backref=db.backref('posts', lazy='dynamic')) @property def imgsrc(self): return uploaded_images.url(self.image) def __init__(self, blog, user, title, body, tag, image=none, slug=none, publish_date=none, live=true): self.blog_id = blog.id self.user_id = user.id self.title = title self.body = body self.tag = tag self.image = image self.slug = slug if publish_date none: self.publish_date = datetime.utcnow() self.live = live def __repr__(self): return '<post %r>' % self.title class tag(db.model): id = db.column(db.integer, primary_key=true) name = db.column(db.string(50)) def __init__(self, name): self.name = name def __repr__(self): return self.name from my_app import db blog.models import post datetime import datetime import datetime class user(db.model): id = db.column(db.integer, primary_key=true) fullname = db.column(db.string(80)) email = db.column(db.string(35), unique=true) username = db.column(db.string(25), unique=true) password = db.column(db.string(60)) paid_until = db.column(db.datetime, default=datetime.datetime.utcnow) posts = db.relationship('post', backref='user', lazy='dynamic') def __init__(self, fullname, email, username, password): self.fullname = fullname self.email = email self.username = username self.password = password def __repr__(self): return '<user %r>' % self.username thank you.
well, 1 answered students in course. issue index on slug (when mark unique it's creating index).
because varchar, maximum string length indexed columns in mysql 255. if slug utf8, need assume less length (255/3 = 85 chars max, because each utf8 character 3 bytes).
so reduce varchar size on slug less 256 characters (students mentioned 252 worked).
here's stack overflow discussion on issue.
Comments
Post a Comment