MySQL case sensitivity

[joseph]

Walking through the Quick-start guide I got the Demo Application running and then moved on to the Admin Tool where I encountered a problem with MySQL table names on Linux hosts being case sensitive. The queries used in the Admin tool seem to reference lowercase table names. MySQL has an option that can be turned on:

Code: Select all

lower_case_table_names=1

however the tables were created as Uppercase already during start up of the app and would need to be altered to lowercase for this option to work.

Has anyone encountered this and got it working?

[shaggydog97]

If you open up BroadleafCommerceAdminWeb/config/spring/applicationContext-security.xml you will find a hand full of queries. Just capitalize the table names in the queries. Run ant deploy-exploded-war again, and the admin login should work. At least it did for me on Ubuntu.

[bradford.taylor]

Joseph,

ShaggyDog97 is correct.

ShaggyDog97,

Thanks for submitting the patch. I will be applying and checking it in today.

Bradford Taylor

[joseph]

Thanks for pointing out the needed changes. I am up and running now.

[pokemon007]

I had it work, but somehow it's broken due to table name case sensitivity. Not working on my MacBook or Linux. The problem is that at start up, it always looks for lower case table named "sequence_generator" while I want to use upper case cross the board and had converted all lower case table names to upper case (in admin security xml).

Code: Select all

[ERROR] 01:49:14 JDBCExceptionReporter - Table 'commerce2.sequence_generator' doesn't exist
[ERROR] 01:49:49 ContextLoader - Context initialization failed
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
   at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
   at org.broadleafcommerce.common.persistence.SequenceGeneratorCorruptionDetection.onApplicationEvent(SequenceGeneratorCorruptionDetection.java:125)
   at org.broadleafcommerce.common.persistence.SequenceGeneratorCorruptionDetection.onApplicationEvent(SequenceGeneratorCorruptionDetection.java:1)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
   at $Proxy233.onApplicationEvent(Unknown Source)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:97)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:324)
   at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:929)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:467)
   at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:385)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:284)
   at org.broadleafcommerce.common.web.extensibility.MergeContextLoaderListener.contextInitialized(MergeContextLoaderListener.java:42)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3934)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4429)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)


Somehow in SequenceGeneratorCorruptionDetection.onApplicationEvent the table name got converted to lower case. Debugging it, but the conversion doesn't happen in broadleaf's code.

Anyone has any idea how to fix it?

Thank you!

-Charlie

[pokemon007]

Forgot to mention, the reason I want to keep mysql db case sensitive is that I'm hosting the site with Plesk installed that uses case sensitive db table name. And I want to make schema created on different system transferrable. I'm using InnoDB. It's working on Mac if I set lower_case_table_name=0, but still has this problem on Linux. I've tried lower_table_name=0 and 2, but keep getting this error. I can't set it to 1 as that breaks Plesk Panel. Any idea?

Thank you!

-Charlie

[pokemon007]

[Update] I tried to force all table names to lower case, and set lower_case_table_name=2. Now this time it tries to find table SEQUENCE_GENERATOR:

Code: Select all

[ WARN] 14:52:10 AbstractEhcacheRegionFactory - Couldn't find a specific ehcache configuration for cache named [org.hibernate.cache.UpdateTimestampsCache]; using defaults.
[ERROR] 14:52:14 Ejb3Configuration - Container is providing a null PersistenceUnitRootUrl: discovery impossible
[ WARN] 14:52:14 Ejb3Configuration - hibernate.connection.autocommit = false break the EJB3 specification
[ERROR] 14:52:22 Ejb3Configuration - Container is providing a null PersistenceUnitRootUrl: discovery impossible
[ WARN] 14:52:22 Ejb3Configuration - hibernate.connection.autocommit = false break the EJB3 specification
[ WARN] 14:52:25 JDBCExceptionReporter - SQL Error: 1146, SQLState: 42S02
[ERROR] 14:52:25 JDBCExceptionReporter - Table 'commerce2.SEQUENCE_GENERATOR' doesn't exist
[ERROR] 14:52:25 ContextLoader - Context initialization failed
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
   at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
   at org.broadleafcommerce.common.persistence.SequenceGeneratorCorruptionDetection.onApplicationEvent(SequenceGeneratorCorruptionDetection.java:125)
   at org.broadleafcommerce.common.persistence.SequenceGeneratorCorruptionDetection.onApplicationEvent(SequenceGeneratorCorruptionDetection.java:1)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:616)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)


It seems we have two different code paths, one uses lower and one uses upper case? Anyone have any idea what's going?

Thank you!

-Charlie

[pokemon007]

Back to this issue again. Somehow the auto created tables have mixed cases. Most tables are created with upper case names, but some tables are created with lower case names. It's interesting that the result from auto creation is different on MacBook and on Linux (CentOs6):

On my MacBook, following tables are created with lower case names:
| blc_admin_permission |
| blc_admin_role |
| blc_admin_user |
| blc_bank_account_payment |
| blc_content |
| blc_credit_card_payment |
| blc_data_drvn_enum |
| blc_email_tracking |
| blc_gift_card_payment |
| blc_media |
| blc_offer |
| blc_offer_audit |
| blc_rating_summary |
| blc_role |
| blc_sandbox |
| blc_search_intercept |
| blc_search_synonym |
| blc_shipping_rate |
| blc_sku_availability |
| blc_static_asset_strg |
| blc_store |
| blc_target_content |
| blc_zip_code |

However, on Linux, only sequence_generator is in lower case. The funny thing is upper case table name SEQUENCE_GENERATOR is created as well. No wonder either lower case nor upper case table name of this table will work at startup as apparently both tables are needed at start up at least on Linux unless lower_case_table_names is set to 1. That's the way to make it work on Linux. In my case as I can't set it to 1, the workaround is to use the schema generated on the same machine for the machine. So schema is not transferable.

Anyone has any idea why it's like this?

Thanks.

-Charlie