Forums

This is the alembic file for the buggy migration :

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('assessments', 'creation_date',
           existing_type=mysql.DATETIME(),
           nullable=False,
           existing_server_default=sa.text('CURRENT_TIMESTAMP'))
op.create_foreign_key(None, 'assessments', 'users', ['creator_id'], ['identifier'])

This is the model :

from itertools import groupby
from operator import attrgetter
from pandas import read_excel
from sqlalchemy import Column
from sqlalchemy import Integer, Float, ForeignKey, String, DateTime
from sqlalchemy.orm import relationship
from sqlalchemy.sql.functions import current_timestamp
from werkzeug.datastructures import FileStorage
from linnote.core.user import Student
from linnote.core.utils.database import BASE

class Assessment(BASE):
"""Evaluation of students knowledge."""

__tablename__ = 'assessments'
identifier = Column(Integer, primary_key=True)
creator = relationship('User', uselist=False)
creation_date = Column(DateTime, nullable=False, server_default=current_timestamp())
title = Column(String(250), nullable=False, index=True)
coefficient = Column(Integer, nullable=False)
precision = Column(Integer, nullable=False, default=3)
results = relationship('Mark', cascade="all")
reports = relationship('Report', back_populates="assessment", cascade="all")

creator_id = Column(Integer, ForeignKey('users.identifier'))

And finally I'm stuck with that on PA : sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1067 (42000): Invalid default value for 'creation_date' [SQL: 'ALTER TABLE assessme nts MODIFY creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP'] (Background on this error at: http://sqlalche.me/e/f405)

I've tried locally against a MySQL database and everything was working properly... Any idea why this is bugging ?

It looks like your MySQL database is on one of our older servers, which is running a version of the server software that only allows the default CURRENT_TIMESTAMP on TIMESTAMP fields, not on DATETIMEs. Would you like us to move your data over to a newer server where it should work?

Would really love that indeed! Should I perform a database dump before you make the migration ?

Yes, that's probably a good idea -- we'll do a dump too, but it's best for you to have your own backups just in case... Let us know when you've done that, and we'll do the migration.

I’ve made the backup, you can go on!

OK, you should be all set now. One thing -- the migration changed your MySQL password; you can change it back from the "Databases" page.

I've reset my old MySQL password and retry the migration, but the command line still prompt the same error... :/

What does this say when you run it in a MySQL console?

SHOW VARIABLES LIKE "%version%";

In particular, the "version" record.

This is what I got :

• innodb_version: 5.6.27
• protocol_version: 10
• slave_type_conversions
• version: 5.6.27-log
• version_comment: MySQL Community Server (GPL)
• version_compile_machine: x86_64
• version_compile_os: Linux

Ok, so your database is actually running on the new server. This page seems to indicate that it should work, so I'm not sure what might be happening. Are you sure you're connecting to the same MySQL server as the console? Are you sure that it's the same error message?

After further investigations the bug was caused by the migration file (autogenerated). Now everything is workin well.

P.S. : I don't know why it was working on my dev machine...

Glad you got it working! I can't see any reason why it was working on dev and not here either. Maybe a different MySQL version? I can imagine that perhaps the migrations generated are specific to the server you run them against...?