We’ve all seen the error message General error 1267 when working with MySQL, but we usually don’t know what it means or how to fix it. What’s behind this mysterious message? What causes it, and how can you prevent it? In this article, we explain why it happens and how to handle general error 1267 without losing your mind or breaking your server in the process.
What are collations?
Collations are used to compare or sort strings in different languages. You may not even know this but the English alphabet doesn’t always have the same order as a Portuguese alphabet, so they’re important. Illegal mix of collations is a common phrase and an SQL statement that means there’s something wrong with your query and it can’t be executed without errors.
When used in SQL, for example, it may generate a General error 1267 Illegal mix of collations. This statement is an SQL command to let us know there’s something wrong with your query. While trying to execute a query like DROP TABLE on a database that hasn’t been dropped before would result in a general error. But if we were to try executing a query against tables that have already been dropped, we’ll get an illegal mix of collations. So next time you see these errors, remember what it means!
How to Fix General error: 1267
You can sometimes fix General error 1267 by simply removing the column which contains the non-Latin characters. If this doesn’t work, it could be that your MySQL database isn’t configured for the charset for which the table is stored. The best way to see if that’s the case is to look at your column data types in phpMyAdmin or whichever software you’re using to access your database and compare those types with what was used when creating or modifying a table in MySQL. This should help identify any columns of text type containing non-Latin characters and tell you how to modify those columns so they can store text in the proper encoding (as UTF-8 or UTF-16).
well, now when I’ve created a database with utf8_unicode_ci collation by this command “CREATE DATABASE weberrorfinder CHARACTER SET utf8 COLLATE utf8_unicode_ci;” it’s working.
Advantages of a lowercase_table_names setting
The simplest way to avoid general errors is to use a lowercase_table_names setting. This ensures that the table names are all in lowercase. There will be no illegal mix of collations. If this setting is not enabled, then some table names may end up with capital letters and others with lowercase letters. This can lead to confusion and errors when comparing data or queries between tables or databases.
The case sensitivity of column and database identifiers also follows the case sensitivity of their containing database, so enabling this option will ensure consistency between cases across databases, if it’s being used on all servers in a cluster. In other words, it makes the search for any typos less difficult! For example, suppose someone named BJ has a MySQL account with both lowercase_table_names and utf8mb4 enabled; but he tries to connect from an MS SQL Server using lowercase_table_name=false and ‘latin1’ encoding (aka ‘ANSI’). He would see messages like these:
What went wrong? Illegal mix of collations
Disadvantages of a lowercase_table_names setting
A setting for lowercase_table_names could lead to data loss, general errors, and incompatibility issues with the various types of operating systems. A SQL statement executed with a lowercase table name will not return data because the value will be assumed as invalid and returned in an error format. These errors are known as general errors (SQLSTATE HY000) because they affect most if not all database actions when they occur. It is crucial to properly set up your tables before using them so that you do not run into unforeseen problems. Make sure to look up the documentation for your specific database to learn more about the default behavior of table names and how it can affect your work .
The following examples show what happens when table names are used improperly in MySQL and Oracle databases. In MySQL, if a user sets lowercase_table_names = 1 and then queries their database for foobar, the result will be Error! Couldn’t create query because foobar does not exist. In Oracle, there may not even be an error at all due to this feature’s being off by default; thus no sqlstate hy000 would be produced during execution time. When configuring your installation of Oracle or any other DBMS, make sure to read up on any features like this one which might affect what you’re trying to accomplish.
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?