Tuesday, June 26, 2018

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.

No comments:

Post a Comment

Post your comment here. If you want to say something about programming problems, scripts, software etc, please try to be as descriptive as possible.

Connect Rapoo MT750S with Linux (Tested on Manjaro)

 I bought this obvious copy of MX Master 2S in hopes of having the device switching functionality along with a lightweight body because I ha...