When I try to run db.create_all() to create MySQL tables, I get the following error:
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1071, 'Specified key was too long; max key length is 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 I resolve this error? I'm unsure the source of the issue. I can create_all() the DB in my dev environment, but not on my host. I've assumed it's a column size restriction on the server's MySQL service. Am I encoding the table contents inefficiently? Did I structure the models incorrectly?
I've tried to change my settings.py:
DB_URI = "mysql+pymysql://%s:%s@%s/%s?charset=utf8" % (DB_USERNAME, DB_PASSWORD, DB_HOST, BLOG_DATABASE_NAME)
I also tried SET @@global.innodb_large_prefix = 1; as this question suggested.
Models
from my_app import db, uploaded_images
from 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 is 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
from blog.models import Post
from 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.
