Mysql Error: Illegal mix of collations

 

Sometimes, when designing queries, we assume that the database and tables inherit the same general settings. Due to this assumption, we run into many different MYSQL errors.

In this post, I will give you some tips to resolve this specific MYSQL error.

Basically, what it means is that you're trying to compare 2 fields with an incompatible collation.

For example,  if you want to run this query:

 SELECT* FROM table_1 WHERE A in (SELECT B FROM table_2)

But keep in mind that column A has utf8_unicode_ci as default collation, and column B has latin1_general_ci as default collation. So, if you run that query, you will get this error: 

 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT)

How can I make it work?

Well, it's very simple. You can use COLLATE to convert a field to  the desired collation.

Example: 

SELECT* FROM table_1 WHERE A in (SELECT B COLLATE utf8_unicode_ci FROM table_2)

Or

 

SELECT* FROM table_1 WHERE A COLLATE latin1_general_ci in (SELECT B FROM table_2)

Both queries will work fine now.

I hope you find it useful. Until the next post!!!

Configuring Capistrano to run migrations on multiple servers

 

A few months ago I found myself in a situation where I had to run the migrations on a second server. After some research, I learned that Capistrano only runs migrations on the first server in the :db group, so other servers with :db will be ignored.

I was very surprised!!!

I kept looking into this and finally found the option I was looking for:

This is an example of the config/deploy/production.rb

 server 'sample-web-server1', user: 'deploy', roles: %w{app web db}
 server 'sample-web-server2', user: 'deploy', roles: %w{app web}
 server 'sample-web-server3', user: 'deploy', roles: %w{app web db}

Now in your config/deploy.rb file add this option:

 set :migration_servers, -> { release_roles(fetch(:migration_role)) }

Now, you just have to deploy.

As a side note:

According to the capistrano changelogs, this option was introduced from version 1.1.7, so if you have capistrano 1.1.7 onwards, you can safely use it. 

You can also check the capistrano documentation here

Useful Mysql Queries

 

There are some situations where your application could crash and sometimes the problem is not really in the source code, but in the database. e.g: you are using wrong collation for a specific table or column or maybe you are using wrong data type etc.

These Mysql statements can help you debug or better understand your current database structure.

Let's begin:

1.  Show all columns for a specific table:

 SHOW COLUMNS FROM users;

output:

and also you can filter by field or type etc.

 SHOW COLUMNS FROM users WHERE field = 'email';

2. Show table details:

 SHOW TABLE STATUS;

output:

and of course you can filter by table name:

 SHOW TABLE STATUS where name = 'users';

Output:

3. Show Mysql variables:

 SHOW variables;

output:

and also you filter by variable name:

 SHOW variables WHERE Variable_name =  'max_allowed_packet';

4. Show Table size:

 SELECT TABLE_NAME AS `users`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_NAME = "users" AND TABLE_SCHEMA = 'my_blog'

Output:

More are coming soon..

Ubuntu: How to install an old Firefox version

If you want to install an old version of Firefox, you just have to follow these steps:

In this case, I had Firefox 4.5.0 and now I want to install Firefox 47.0.1


First, open your terminal

1. Download firefox 47.0.1
   
   wget https://ftp.mozilla.org/pub/firefox/releases/47.0.1/linux-x86_64/en-US/firefox-47.0.1.tar.bz2
2. Unzip Firefox
   
   tar -xjvf firefox-47.0.1.tar.bz2
3. We remove the old version
   
   sudo rm -rf /opt/firefox*
4. we move the unzip firefox 47.0.1 to the firefox folder
   
   sudo mv firefox /opt/firefox
5. create symbolic link
   
   sudo ln -sf /opt/firefox/firefox /usr/bin/firefox

How to edit a commit message

what you need to do is go to your repository  and type this in your command line:



  git commit --amend



Then in your text editor you can put the new commit message.

Good Luck And Happy Hacking!!!

Git push force

Sometimes, it is  necessary to performed a push force in our repositories, but you need to be really careful because this action will overwrite all, so you need to know what you are doing.

This is the code:

git push -f <remote> <branch>

e.g
git push -f origin my_new_feature

Marvel Studios' Avengers: Infinity War Official Trailer