MySQL configuration on ADF Development

I need to share some MySQL problems. When we develop application using ADF within MySQL database, we get following issues.

Issue 1: Our af:query doesn’t work with MySQL database. We just debug that issue; it comes from pure SQL standard. Which means default mysql configuration doesn’t support PIPES.

Solve 1: We configured PIPES_AS_CONCAT in my.cnf (Linux). If you will face that problem in Windows, you configure in my.ini (Windows).

set-variable=sql-mode=PIPES_AS_CONCAT

Issue 2: Sometimes we created table name as lowercase and uppercase in database. So that case MySQL error comes from mostly at Linux. For example: table doesn’t exist. We solved following way.

Solve 2: Same configuration way in my.cnf, it is just ignore case value.

lower_case_table_names=1

Issue 3: When we started new application with ADF & MySQL, we got Unicode problem. So in this case we changed MySQL connection string in JDeveloper.

Solve 3: We entered custom JDBC URL in Database connection editor.

Maybe you have to visit juddi-oracle.blogspot.com blog. He explained very well.

http://juddi-oracle.blogspot.com/2011/08/mysql-unicode-datasource-problem.html

Issue 4: Sometimes we get MySQL connection time out problem. This case we have to change some variable and connection string. We get following error: java.net.SocketException: Broken pipe.
Solve 4: I think, There are two configuration required.
A. We can increase to connection timeout variable to appropriate value.
mysql> SHOW VARIABLES;
mysql>SET GLOBAL connect_timeout=120;
B. We can change connection string. I described some connection string changes above and i added additional property. ( autoReconnect=true )

jdbc:mysql://localhost:3306/mydb?autoReconnect=true&characterSetResults=UTF-8&characterEncoding=UTF-8&useUnicode=yes

Best Regards,

Erdenebayar