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!!!

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..

MYSQL How to prepend a string when updating a column

In order to prepend a string when you are updating a columns is kind of easy, you only need to know the right Mysqsl function to achieve that.

Example:

For any reason, someone requested you to update the user user_login for the user ID:999  with this symbol "@" prefix to each name:

   

update users 
set user_login = concat('@', user_login)
where id = 999;


 

As you can see the function "concat" is prepending the @ to each user login.

The expected output would be

ID	user_login
999	@john.doe

It is important to note that Concat allows you to add a string either at the end or at the beginning.

How to install Mysql on Ubuntu from command line

To install Mysql on Ubuntu what you have to  do is type  the following command:

sudo apt-get install mysql-client mysql-server

and then you will see something like this:

What you have to do there is type the  password for the root user.

and that's all.

Good luck!!!!!

How To Find Duplicate Records in MYSQL

In this tutorial, I will explain how to find duplicate records in MYSQL.
I think the best way to explain is using an example:

This is our table called actors:

As you can see, we have  the name JULIO several times, which is fine because it won't affect anything, but what would happen if someone told you how many JULIO'S  we have in our actors tables?

To do that, we need the next SQL statement:

   

  select count(a.first_name)as 'Duplicate Records', 
        a.actor_id,a. first_name
  from actor a
 group by a.first_name 
  HAVING count(a.first_name) > 1

 

Output:

Basically, we've created  an extra  column  called duplicate Records that will tell us how many time  the name of Julio and the others names are repeated.

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

If you have ever had this error, is because you want to truncate a table that has foreign key in another table. Let me put it this way.

Having this  structure, where "articles" can have multiple users , and you want to delete the table users using the function TRUNCATE, Mysql will throw this error: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

There is a way to avoid this error, but you have to understand,  if you want to delete the  users tables and reset the auto increment field , you have to do the same with the articles table.

This is a basic example, but what would happen if you want to clean an entire database and reset the auto increment? That would be very complicated without the following code.


I know this code could be useful for you:



SYNTAX:

   

     SET foreign_key_checks = 0;

       TRUNCATE  `users`;


       TRUNCATE  `articles`;


     SET foreign_key_checks = 1;

 

Doing that, you will temporarily disabling  referential constraint, and at the end you will enable the referential constraint.

How to Reset auto increment after deleting a table row

To delete data and reset the auto increment field using MYSQL, we have a function called TRUNCATE which will help you to accomplish this task.


Syntax:

   

     TRUNCATE  `articles`;

 

Basically , After the function "TRUNCATE" you have to write your table name , in this case is the table called " ARTICLES" .When you run this SQL sentence you will delete the whole data and the auto increment field will be reset.

MYSQL Save the Last Id Inserted in a Variable

To save the last id inserted in a SQL variable , we need to do this:

INSERT INTO Users (user_name, user_email, user_address, user_city)
VALUES ('bryan.hamilton','Tbha@gmail.com','Av.21','ohio'); 

//we create a new variable called userLastId
 SET @userLastId = LAST_INSERT_ID( );

//now, we can use this varable in another sql sentence

INSERT INTO articles (art_title, art_body, art_user_id, art_created_at)
VALUES ('Geologic time scale','my body ..',@userLastId ,NOW());  

 

Basically, when you save the last id inserted in the variable that value is available to use it in others SQL sentences.

Mysql SQL Error 1093 You can't specify target table

In this post, we will learn how to resolve this "Mysql Error 1093 You can't specify target table"



I was trying to delete some data using  this query:

DELETE  FROM articles
where art_id  in (select a.art_id
                          from articles a
                         where a.art_date <= '2015-05-19'

                        );
 

When I executed this query an error suddenly appeared that was: Mysql Error 1093 you can't specify target table.


What was the problem?
 Basically, the delete statement doesn’t allow to perform a sub-query using the same table.

How can I overcome this?

The solution, that I found was quite easy, is as simple as this: you have to wrap the sub-query in one more select .

Solution:

DELETE FROM articles
 WHERE art_id in (
     SELECT aid FROM(
      SELECT a.art_id FROM articles a WHERE a.art_date <= '2015-05-19'
  ) as a
 );