Comment failed to work after deployment?

[JTP-123]

At first, the question is that I can not log in after deployment on PythonAnywhere. I used flask db migrate on backend:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [flask_migrate] Error: Target database is not up to date.

I guess the problem is current migration edition is not consistent with head mirgation edition, so i did this although i don't know why two different editions happened:

>>>flask db heads
ceca8f22389c (head)
>>>flask db current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
dc96d9b0d0bf

I then use flask db stamp heads to mark the migration edition:

flask db stamp heads
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision dc96d9b0d0bf -> ceca8f22389c

And check it:

flask db current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ceca8f22389c (head)

then run:
flask db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

but it seems still not work, so i run command again:

flask db migrate
    INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
    INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
    INFO  [alembic.autogenerate.compare] Detected added table 'message'
    INFO  [alembic.autogenerate.compare] Detected added index 'ix_message_timestamp' on '['timestamp']'
    INFO  [alembic.autogenerate.compare] Detected added table 'notification'
    INFO  [alembic.autogenerate.compare] Detected added index 'ix_notification_name' on '['name']'
    INFO  [alembic.autogenerate.compare] Detected added index 'ix_notification_timestamp' on '['timestamp']'
    INFO  [alembic.autogenerate.compare] Detected added table 'comment'
    INFO  [alembic.autogenerate.compare] Detected added index 'ix_comment_timestamp' on '['timestamp']'
    INFO  [alembic.autogenerate.compare] Detected added column 'post.language'
    INFO  [alembic.autogenerate.compare] Detected added column 'post.body_html'
    INFO  [alembic.autogenerate.compare] Detected type change from VARCHAR(length=140) to Text() on 'post.body'
    INFO  [alembic.autogenerate.compare] Detected added column 'user.token'
    INFO  [alembic.autogenerate.compare] Detected added column 'user.token_expiration'
    INFO  [alembic.autogenerate.compare] Detected added column 'user.last_message_read_time'
    INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_token' on '['token']'
      Generating /home/JTPing/microblog/migrations/versions/e6990a7ae19f_.py ...  done
    (venv) 01:50 ~/microblog (main)$ flask db upgrade
    INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
    INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
    INFO  [alembic.runtime.migration] Running upgrade 9d026987d6f2 -> e6990a7ae19f, empty message

It works and i can log in now, but unexpected things occurs: all previous user data is gone , so i registered again, but i find that i can't not comment and the error is same as the above "The administrator has been notified. Sorry for the inconvenience!" Also, i clicked the file(文件 in chinese) tag, the error was same. i do not know why.

I used cd migrations/versions and ls to list all subfile:

05eba44a406a_notifications.py             b07ebb5fe6e0_fix_markdown_rendering_of_comments.py
14ba2865cec3_add_comments.py              ceca8f22389c_user_tokens.py
3d95c0a8c088_users_table.py               dc96d9b0d0bf_followers.py
6ee987d26551_posts_table.py               e6990a7ae19f_.py
92a7096214ea_new_fields_in_user_model.py  e949960c17e9_let_user_be_able_to_follow_and_unfollow.py
9389414e987c_enable_markdown_editor.py    f3cff53a2dcf_add_language_to_posts.py
9d026987d6f2_.py                          fd8a7d4f9bd4_private_messages.py
__pycache__

Compare the above with my local file, I found two extra file e6990a7ae19f_.py and 9d026987d6f2_.py, and the latter is the last version which produced logging problems, the former is now database stand on.

I guess the problem lies in the last generated migrations file: e6990a7ae19f_.py , so I cat it.

"""empty message
Revision ID: e6990a7ae19f
Revises: 9d026987d6f2
Create Date: 2023-12-09 01:50:25.406017
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'e6990a7ae19f'
down_revision = '9d026987d6f2'
branch_labels = None
depends_on = None
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('message',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('sender_id', sa.Integer(), nullable=True),
    sa.Column('recipient_id', sa.Integer(), nullable=True),
    sa.Column('body', sa.String(length=140), nullable=True),
    sa.Column('timestamp', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['recipient_id'], ['user.id'], ),
    sa.ForeignKeyConstraint(['sender_id'], ['user.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('message', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_message_timestamp'), ['timestamp'], unique=False)
    op.create_table('notification',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=128), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('timestamp', sa.Float(), nullable=True),
    sa.Column('payload_json', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('notification', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_notification_name'), ['name'], unique=False)
        batch_op.create_index(batch_op.f('ix_notification_timestamp'), ['timestamp'], unique=False)
    op.create_table('comment',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('body', sa.String(length=140), nullable=True),
    sa.Column('timestamp', sa.DateTime(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('post_id', sa.Integer(), nullable=True),
    sa.Column('body_html', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['post_id'], ['post.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    with op.batch_alter_table('comment', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_comment_timestamp'), ['timestamp'], unique=False)
    with op.batch_alter_table('post', schema=None) as batch_op:
        batch_op.add_column(sa.Column('language', sa.String(length=5), nullable=True))
        batch_op.add_column(sa.Column('body_html', sa.Text(), nullable=True))
        batch_op.alter_column('body',
               existing_type=sa.VARCHAR(length=140),
               type_=sa.Text(),
               existing_nullable=True)
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.add_column(sa.Column('token', sa.String(length=32), nullable=True))
        batch_op.add_column(sa.Column('token_expiration', sa.DateTime(), nullable=True))
        batch_op.add_column(sa.Column('last_message_read_time', sa.DateTime(), nullable=True))
        batch_op.create_index(batch_op.f('ix_user_token'), ['token'], unique=True)
       # ### end Alembic commands ###
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_user_token'))
        batch_op.drop_column('last_message_read_time')
        batch_op.drop_column('token_expiration')
        batch_op.drop_column('token')
    with op.batch_alter_table('post', schema=None) as batch_op:
        batch_op.alter_column('body',
               existing_type=sa.Text(),
               type_=sa.VARCHAR(length=140),
               existing_nullable=True)
        batch_op.drop_column('body_html')
        batch_op.drop_column('language')
    with op.batch_alter_table('comment', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_comment_timestamp'))
    op.drop_table('comment')
    with op.batch_alter_table('notification', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_notification_timestamp'))
        batch_op.drop_index(batch_op.f('ix_notification_name'))
    op.drop_table('notification')
    with op.batch_alter_table('message', schema=None) as batch_op:
        batch_op.drop_index(batch_op.f('ix_message_timestamp'))
    op.drop_table('message')
    # ### end Alembic commands ###

Also, I checked history and maybe it is helpful.

(venv) 12:28 ~/microblog (main)$ flask db history
9d026987d6f2 -> e6990a7ae19f (head), empty message
ceca8f22389c -> 9d026987d6f2, empty message
b07ebb5fe6e0 -> ceca8f22389c, user tokens
05eba44a406a -> b07ebb5fe6e0, fix markdown rendering of comments
fd8a7d4f9bd4 -> 05eba44a406a, notifications
9389414e987c -> fd8a7d4f9bd4, private messages
14ba2865cec3 -> 9389414e987c, enable markdown editor
f3cff53a2dcf -> 14ba2865cec3, add comments
e949960c17e9 -> f3cff53a2dcf, add language to posts
dc96d9b0d0bf -> e949960c17e9, let user be able to follow and unfollow
92a7096214ea -> dc96d9b0d0bf, followers
6ee987d26551 -> 92a7096214ea, new fields in user model
3d95c0a8c088 -> 6ee987d26551, posts table
<base> -> 3d95c0a8c088, users table

[miguelgrinberg]

This:

>>>flask db current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
dc96d9b0d0bf

means that your database does not have all the migrations and needs to be updated. The database is loaded up to the change I indicate below:

(venv) 12:28 ~/microblog (main)$ flask db history
9d026987d6f2 -> e6990a7ae19f (head), empty message
ceca8f22389c -> 9d026987d6f2, empty message
b07ebb5fe6e0 -> ceca8f22389c, user tokens
05eba44a406a -> b07ebb5fe6e0, fix markdown rendering of comments
fd8a7d4f9bd4 -> 05eba44a406a, notifications
9389414e987c -> fd8a7d4f9bd4, private messages
14ba2865cec3 -> 9389414e987c, enable markdown editor
f3cff53a2dcf -> 14ba2865cec3, add comments
e949960c17e9 -> f3cff53a2dcf, add language to posts
dc96d9b0d0bf -> e949960c17e9, let user be able to follow and unfollow
92a7096214ea -> dc96d9b0d0bf, followers                                 <--- your database is here
6ee987d26551 -> 92a7096214ea, new fields in user model
3d95c0a8c088 -> 6ee987d26551, posts table
<base> -> 3d95c0a8c088, users table

What you should have done at this point is to upgrade the database:

flask db upgrade

So that all the migrations above the one that is current in your database are applied. After the database is upgraded you should be able to create new migrations without problem.

The stamp command should only be used when you know exactly what you are doing. It is unlikely to be the solution to any problem unless you know that you are working in a strange or non-standard way.

Unfortunately I cannot suggest how to fix your database, because you have done a few destructive operations now while trying to fix the problem. If you have a back up before you started to make these changes, restore the back up and try the upgrade that I suggested. If you don't have a backup then I'm not sure what to suggest other than starting from scratch. Consider this a lesson and make a back up the next time you are going to make potentially destructive changes to your database.

[JTP-123]

I do not have a backup unless the local migration file. Starting from scratch indeed is a good way but it will take a long time. I do not have much free time after work. So I try to find a way to solve it as much as possible. Thank you for your help and advice.