Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

To solve this error I pulled my hair and scrolled through so many blog articles and forum. Finally today I came up with different solutions either can help you.

Note in Public Interest: MySQL has strange behavior, I don't know which kinda developer working on it? So if you are facing an issue right now may not occur after day or so.

Scenario: Most of our clients using Microsoft SQL Server but two or three client want MySQL Server on their server so we've to work on it. We deployed our code on MySQL Server. Our product has so many Stored Procedures, It was working fine but two days back they were facing same error.

Solution 1: Check your query related column's collation using following query and change as per your requirement.
/* Show all columns and its collation */
SELECT * 
FROM information_schema.columns 
WHERE table_schema = DATABASE();
/* ALTER TABLE  MODIFY  VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci; to change column collation */
ALTER TABLE address MODIFY address1 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 2: Check your table's collation using following query and change as per your requirement.
/* Show all tables and its collation */
SHOW TABLE STATUS;
/* ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE <collation>; to change table collation. */
ALTER TABLE actor CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 3: Check collation of your database and change as per your requirement.
/* To check your database collation */
SELECT * 
FROM information_schema.SCHEMATA S
WHERE schema_name = DATABASE(); 
/* ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE <collation>; To change database collation */
ALTER DATABASE test CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I did above suggested all changes still I was facing same error. I dig down further and found that my connection's collation is utf8_general_ci and database collation is utf8_unicode_ci. To understand the my ground follow below image.

utf8_unicode_ci - utf8_general_ci


Solution 4: Use COLLATE within inline query as follow.
SELECT *
FROM actor 
WHERE name = tmp_VARIABLE
COLLATE utf8_unicode_ci; 

Solution 5: Change parameter's character set in case of Stored Procedure or functions.
CREATE PROCEDURE proc_test (param1 VARCHAR (10) charset utf8, param2 int)

In my case solution 4 and 5 worked like charm. I tried to change collation_connection variable using mysql configuration file but didn't get success to solve this issue. Please share your solution if you have any.

Special Thanks to:
- http://forums.mysql.com/read.php?103,265345,265579#msg-265579
- http://www.excindex.com/mysql/589131.html

MSSQL: Queries that do not return large result sets. Nonclustered Index limitation

What is Nonclustered Index?
A nonclustered index contains the index key values and row locators that point to the storage location of the table data.

Queries that do not return large result sets. - Nonclustered Index Design Guidelines
Source: http://technet.microsoft.com/en-us/library/ms179325(v=sql.105).aspx

We are using hibernate in our project to support multiple dialect. Its our routine process to generate queries that are generated by hibernate. We do analysis on that queries and suggest indexes and other stuffs. We replaced ? in query with 0. We fired that query in Microsoft SQL Server Management Studio and Execution plan suggested index. We created index with bit modification. After executing same query again what we see is index is not used by query.

We were clueless why is database not using index. We went through lots of article but no one wrote specifically for this rule of nonclustered index. We did few more run on different tables and queries. We were not able to derive perfect rule that...

Database will not use Nonclustered Index when Its returning 'n' records.
It's yet to be discovered from my side. If you guys have perfect rule for this do share in comment. I'll put here if its really a case.

Case Scenario:
SELECT * 
FROM s_master this_ 
LEFT OUTER JOIN t_master tlist2_ 
ON this_.id = tlist2_.s_id 
WHERE this_.document_id = 0 
AND this_.library_id = 0
This query returned 2248 records and as Microsoft SQL server design guide line suggests, query won't use index. We replaced 0 with specific value and It started using Index.

Conclusion:
Nonclustered Index is only useful when you are sure that your query won't return much records.

You'd like to read more about index, scroll through this articles...

MSSQL: How to find all database indexes on column?

I was playing with MSSQL system tables for fun and popup one thing that lets try to find all indexes on column. I ended up with one long query so thought let me post it and you guys don't have work for this query again.

Professionals I don't have to explain much about these tables. Beginner try to work around this table. It'll help you lot in future, way to become database expert in other word Database Administrator(DBA).

/* Change table name and column name in where condition */
SELECT distinct ind.name AS [idx_name], t.name AS [table_name], c.name AS [column_name], ic.is_included_column
FROM sys.indexes ind 
RIGHT JOIN sys.tables t 
	ON ind.object_id = t.object_id 
RIGHT JOIN sys.columns c 
	ON t.object_id = c.object_id
RIGHT JOIN sys.index_columns ic 
	ON ind.index_id = ic.index_id
       AND c.column_id = ic.column_id
WHERE ind.is_primary_key = 0  
  AND ind.is_unique_constraint = 0 
  AND t.is_ms_shipped = 0
  AND ind.name IS NOT NULL
  AND ind.is_unique IN (0,1)
  AND t.name = 'table_name'
  AND c.name = 'column_name'
There ain't much to write for this query. Change table_name and column_name

Execution Plan in MSSQL Server

What is Execution Plan?
As word suggest, Its the plan that shows how actually database executes the query by analyzing available table information. It includes database indexes and statistics on that table.

Execution Plan is tool provided by MSSQL to optimize your queries. Its very popular in community of Database Administrator. Yet so many programmer not really aware of this tool. It helps me to analyze query on daily basis.

Advantages
Execution Plan show us what is cost of executing particular query. Its holds one unique feature that It suggest Index on table and also promising analysis of index compare to MSSQL Profiler. Execution plan we'll show you following statistics on any node.

  • Physical Operation
  • Logical Operation
  • Actual Number of Rows
  • Estimated I/O Cost
  • Estimated CPU Cost
  • Estimated Number of Executions
  • Number of Executions
  • Estimated Operator Cost
  • Estimated Subtree Cost
  • Estimated Number of Rows
  • Estimated Row Size
  • Actual Rebinds
  • Actual Rewinds
  • Ordered
  • NodeID
  • Seek Predicates

How to enable "Execution Plan" in MSSQL Server?
Find Execution Plan icon  in the top toolbar. Click on it to enable and disable or shortcut key Ctrl + m. You've to do it for each query windows.

Execution Plan in the Action
On my database I'm executing following query and lets see what Execution plan show us.
SELECT * 
FROM stage_master sm 
LEFT OUTER JOIN task_master tm 
ON sm.id = tm.stage_id 
WHERE sm.document_id = 4592
AND sm.library_id = 25614
And the Execution Plan

Click to enlarge
Whats it says?
Well I'm going to very brief on this as there are other things here not available in this execution plan like Parallelism other things that differ from query to query.

  • At the top green line says you should have index on table. It'll also generate query to create index. Right click on execution plan area then Missing Index Details. 
  • Thick line shows it is processing lots of information and has much cost on query. 
  • Hover your mouse pointer on any node it'll show you actual statistics as follow...

MySQL: Queries to 'Show All Database Indexes' and 'Drop All Table Indexes'

Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

As described above database index helps only for search operation. Insert, Update and Delete will suffer. Index should be created only selected fields based on your retrieval of data (Columns).

- Source (WikiPedia: http://en.wikipedia.org/wiki/Database_index)

In my previous article we discussed about MSSQL Indexes . Now we'll see how you can achieve same in MySQL. 

SHOW ALL INDEXES 
/* It'll show all indexes except Primary Key */
SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = (SELECT DATABASE())
AND INDEX_NAME != 'PRIMARY';

DROP ALL TABLE INDEXES
Basic Syntax of dropping index is DROP INDEX INDEX_NAME ON TABLE_NAME; but to drop index in one single shot is not possible. You've to loop through all indexes of database. Thus, We've to use Stored Procedure in MySQL.
DROP PROCEDURE IF EXISTS usp_DropIndexOfTable;
DELIMITER $$
CREATE PROCEDURE usp_DropIndexOfTable(IN TableName VARCHAR(100))
BEGIN
 /**
  * @Created By: Vicky Thakor
  * @Created On: 6th March, 2014
  * This will delete all Indexes of table except Primary Key
         * 
         * @Test: CALL usp_DropIndexOfTable('user_master');
  */
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_index varchar(200) DEFAULT "";
        DEClARE index_cursor CURSOR FOR 
        /* In below query remove TABLE_NAME criteria to remove all indexes */
        SELECT INDEX_NAME
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = (SELECT DATABASE())
  AND TABLE_NAME = TableName
  AND INDEX_NAME != 'PRIMARY';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

 DECLARE exit handler FOR sqlexception
 BEGIN
 -- ERROR
 SHOW ERRORS LIMIT 1;
 ROLLBACK;
 END;

 DECLARE exit handler FOR sqlwarning
 BEGIN
 -- WARNING
 SHOW WARNINGS LIMIT 1;
 ROLLBACK;
 END;

 OPEN index_cursor; 
  get_index: LOOP
   FETCH index_cursor INTO v_index;

   IF v_finished = 1 THEN 
    LEAVE get_index;
   END IF;
  
   SET @v_query = CONCAT('DROP INDEX ',v_index,' ON ', TableName);
   PREPARE stmt FROM @v_query;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END LOOP get_index;
       CLOSE index_cursor;
END$$
DELIMITER ;
Just fire above Stored Procedure in your database and then execute this stored procedure using
CALL usp_DropIndexOfTable('user_master'). You can modify above stored procedure according to your requirement.

java.sql.SQLException: Incorrect syntax near '@P1'.

Scenario: 
We were calling MSSQL Stored Procedure using hibernate. My colleague called me to check out error and even I was clueless. It was working fine at database level. And while checking out calling code it seems alright but it thrown an error as follow.

org.hibernate.exception.SQLGrammarException: could not execute query
  at Incorrect syntax near '@P1'..()
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
  at could not execute query.(Native Method)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.loader.Loader.doList(Loader.java:2223)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
  ... 53 more
Caused by: java.sql.SQLException: Incorrect syntax near '@P1'.
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  ... 50 more

Solution:
My colleague forgot to put comma , after each parameter. Follow below queries one with error and other without.

EXEC MY_STORED_PROC :PARAM1 :PARAM2 /* Query that throwing error because of missing comma. */
EXEC MY_STORED_PROC :PARAM1, :PARAM2 /* Query that works fine. */

How to configure Subdomain in Tomcat for localhost?

In my previous article How to configure Subdomain in Tomcat 7? , I wrote about configuring subdomain on live server with linux environment. Today I'll show how you can configure sub-domain in Apache in local system with Netbeans and without Netbeans environment.

Configure sub-domain using Netbeans environment. 
Netbeans executable bundle has Apache Tomcat server in it. You can install Apache tomcat separately add it to Netbeans. So lets check out how to create sub-domain...

Step 1: Open Services tab in Netbeans. If you can't find click on Window > Services (Ctrl + 5) .

Step 2: Navigate to Servers > Apache Tomcat > Edit server.xml


Step 3: Find an Engine tag and add following line with in Engine tag.
<!-- Changes required in following code. In this example (cdn_2b48cl2gf97hytt58p8g.mydomain.com) is my sub-domain and directory.
        1. appBase: sub-domain directory location in webapps.
        2. name: sub-domain name you want. like(cdn_2b48cl2gf97hytt58p8g.mydomain.com, m.mydomain.com)
        3. docBase: Absolute path/location of your directory. -->
<Host appBase="webapps/cdn_2b48cl2gf97hytt58p8g" autoDeploy="false" name="cdn_2b48cl2gf97hytt58p8g.mydomain.com" unpackWARs="true">
      <Context antiJARLocking="true" docBase="D:\Path_to_project\build\web\cdn_2b48cl2gf97hytt58p8g" path=""/>
</Host>

How to forward domain request using hosts file?
Using hosts file you can forward any domain request to any IP address or hosts. We'll forward sub-domain request to localhost.

Note: For this example I used windows platform. Use sudo vim /etc/hosts command in terminal for linux and mac OR http://www.howtogeek.com/howto/27350/beginner-geek-how-to-edit-your-hosts-file/

Step 1: Navigate to folder C:\Windows\System32\drivers\etc.

Step 2: Open hosts file using any editor like notepad and add line like below. So now your sub-domain will point to localhost.
127.0.0.1 cdn_2b48cl2gf97hytt58p8g.mydomain.com

How to setup sub-domain in Tomcat without Netbeans environment?
I gave you example to create sub-domain using Netbeans but technically you are using Netbeans as an editor nothing else. All you need to do is open server.xml file of your tomcat server in any editor like notepad follow same steps above and you are done.

Finally you need to restart your server and you are done!. Now you can access your sub-domain by url like cdn_2b48cl2gf97hytt58p8g.mydomain.com:8084/. You need to specify your tomcat server port.