Useful commands for the MariaDB/Mysql DB

So, today I was caught up in a problem where, I was using a migration generator in Python. I accidentally made some mistakes in one of my models, for which, the migration generator lost it's track and subsequently, was generating a duplicate column issue.

Also, another problem was the use of a non UTF-8 collation type. So, I had a pretty rough time trying to solve these things.

I will list down some of the things that I did, not in any useful order, to solve these problems.

#1 I was using SQLAlchemy with Alembic.
Here's the migration that went berserk

def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.add_column('bot_article', sa.Column('blogs_id', sa.Integer(), nullable=True)) #op.drop_constraint('bot_article_ibfk_1', 'bot_article', type_='foreignkey') op.create_foreign_key('bot_article_ibfk_1', 'bot_article', 'blogs', ['blogs_id'], ['id']) # ### end Alembic commands ### def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.drop_constraint('bot_article_ibfk_1', 'bot_article', type_='foreignkey') # op.create_foreign_key('bot_article_ibfk_1', 'bot_article', 'blogs', ['blogs_id'], ['id']) op.drop_column('bot_article', 'blogs_id') # ### end Alembic commands ###

So, as you can see from the commands, there was a foreign key constraint for the column that I had deleted. So I had to first delete the creation process of that column. Which subsequently failed since I had to actually also remove the constraint that involved that column. But how to do that?


alter table bot_article drop foreign key bot_article_ibfk_3;

This command above removes that constraint from the table. Now I was free to use the proper column and customize my Alembic script, which is what you just saw.

#2 Fixing column collation.
My problem in this case was that had to fix the collation of a column that was using a latin collations. For this reason, I could not insert my unicode encoded strings. But how did I find out what collation was this using, or even if it was using a wrong collation?


SHOW FULL COLUMNS from bot_article;

This command, shows whatever settings you have for each column. So I had a latin collation. I had to convert it to 'utf8_general_ci'. Way to do that?

ALTER DATABASE flask_app CHARACTER SET utf8 COLLATE utf8_general_ci;

#3 Finding out what foreign key constraints are in effect in columns
This one's pretty useful. To find out everything you need to type to create a column (intact with all it's settings) you have to do the following

show create table bot_article;

#4 How to add collation using SQLAlchemy

db.Column(db.Text(collation='utf8_general_ci'), nullable=True)

It's possible that Alembic will not detect this using the migrate command, you have to manually add this in a migration file.