Skip to main content

How to change mysql table names in linux server to be case insensitive

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. Such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.
The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.
To check what is the value of this variable in you installed MySql, run below command
mysqladmin -u <user name> -p variables
Check the lower_case_table_names in the list. Find more details about this variable here.
If lower_case_table_names is 0, it means your table, functions, triggers names are case sensitive. lower_case_table_names can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_namesis 0. On Windows, the default value is 1. On OS X, the default value is 2.
ValueMeaning
0Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
To change the value of ‘lower_case_table_names’, follow below steps.
  1. Go to my.conf file
  2. look up for: # The MySQL server [mysqld]
  3. add this right below it: lower_case_table_names = 2
  4. save the file and restart MySQL service
Now, fire below command.
mysqladmin -u <user name> -p variables

Comments

Post a Comment

Popular posts from this blog

How to Bind Apache Tomcat to IPv4 (Solved)

Apache Tomcat  is an open source web server and servlet container developed by the  Apache Software Foundation . It implements the Java Servlet, JavaServer Pages (JSP), Java Unified Expression Language and Java WebSocket specifications from Sun Microsystems and provides a web server environment for Java code to run in. Binding Tomcat to IPv4 is necessary if we have our server not working due to the default binding of our tomcat server to IPv6. As we know IPv6 is the modern way of assigning IP address to a device and is not in complete practice these days but may come into practice in soon future. So, currently we don't need to switch our tomcat server to IPv6 due to no use and we should bind it to IPv4. Before thinking to bind to IPv4, we should make sure that we've got tomcat installed in our CentOS 7. Here's is a quick tutorial on  how to install tomcat 8 in CentOS 7.0 Server . 1. Switching to user tomcat First of all, we'll gonna switch user to  tomcat  user. We
1 comment
Read more

IOException parsing XML document from class path resource

Error Exception in thread "main" org . springframework . beans . factory . BeanDefinitionStoreException : IOException parsing XML document from class path resource [ src / main / java / resources / combined2 . xml ]; nested exception is java . io . FileNotFoundException : class path resource [ src / main / java / resources / combined2 . xml ] cannot be opened because it does not exist at org . springframework . beans . factory . xml . XmlBeanDefinitionReader . loadBeanDefinitions ( XmlBeanDefinitionReader . java : 341 ) at org . springframework . beans . factory . xml . XmlBeanDefinitionReader . loadBeanDefinitions ( XmlBeanDefinitionReader . java : 302 ) at org . springframework . beans . factory . support . AbstractBeanDefinitionReader . loadBeanDefinitions ( AbstractBeanDefinitionReader . java : 143 ) at org . springframework . beans . factory . support . AbstractBeanDefinitionReader . loadBeanDefinitions ( AbstractBeanDefinitionReader . java : 178 ) at
Post a Comment
Read more

Linux Command htop vs top

Those who work with Linux servers, but also those who use Linux as a desktop every day knows that it is very useful to have a list of processes running on the machine with a list of resources they are using, to see who is eating all the memory or is using all the CPU, and the ps command is not exactly the most convenient. So we ‘ll see a series of programs that serve to show what’s happening in our system and who is using our resources. The first tool that you can use to get information is the  ps  command, yes i know, it’s not so handy but you are sure to have it installed in every machine and you can get quickly some information on the system: Get the top 10 CPU consuming process ps aux | sort -n -k 3 | tail -10 Get the top 10 memory consuming process ps aux | sort -n -k 4 | tail -10 Top In most Unix-like operating systems, the top command is a system monitor tool that produces a frequently-updated list of processes. By default, the processes are ordered by
Post a Comment
Read more