MySQL character set error

Ok, so I finally got my Linux VPS running Railo 3.3.1.000. I downloaded the latest ColdBox 3.5 and ContentBox from GitHub and dropped them in the webroot.

I have set my dialect to “MySQLwithInnoDB” and dbcreate option to “dropcreate” and created a data source called “contentbox” which points to any empty MySQL 5 database.

I am getting the error below and I’m not sure what to make of it. It’s late and my eyes are starting to close on me so I haven’t dug in too deep yet. Originally I had my data source set up as a generic JDBC connection to run it through SeeFusion’s logging. I also tried it as type “MySQL” and it didn’t change anything. The error seems to happen before any CF code is reached and Hibernate is just starting up. Do I need to set a default character set for my database?

Thanks!

~Brad

Variable ‘character_set_client’ can’t be set to the value of 'NULL’Variable ‘character_set_client’ can’t be set to the value of ‘NULL’

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936):936
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934):2934
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616):1616
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708):1708
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249):3249
at com.mysql.jdbc.Connection.execSQL(Connection.java:3178):3178
at com.mysql.jdbc.Statement.execute(Statement.java:711):711
at railo.runtime.orm.hibernate.HibernateSessionFactory.executeSQLScript(HibernateSessionFactory.java:209):209
at railo.runtime.orm.hibernate.HibernateSessionFactory.schemaExport(HibernateSessionFactory.java:168):168
at railo.runtime.orm.hibernate.HibernateSessionFactory.createConfiguration(HibernateSessionFactory.java:153):153
at railo.runtime.orm.hibernate.HibernateORMEngine.getSessionFactory(HibernateORMEngine.java:256):256
at railo.runtime.orm.hibernate.HibernateORMEngine.init(HibernateORMEngine.java:120):120
at railo.runtime.config.ConfigImpl.getORMEngine(ConfigImpl.java:3055):3055
at railo.runtime.config.ConfigImpl.resetORMEngine(ConfigImpl.java:3041):3041
at railo.runtime.orm.ORMUtil.resetEngine(ORMUtil.java:45):45
at railo.runtime.listener.ModernAppListener.initApplicationContext(ModernAppListener.java:369):369
at railo.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:96):96
at railo.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:23):23
at railo.runtime.PageContextImpl.execute(PageContextImpl.java:1991):1991
at railo.runtime.PageContextImpl.execute(PageContextImpl.java:1958):1958
at railo.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:297):297
at railo.loader.servlet.CFMLServlet.service(CFMLServlet.java:32):32
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722):722
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304):304
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210):210

yea never seen that before. I would go with utf8

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

Yeah, but I don’t know how to set it. I googled setting character sets for JDBC URLs and didn’t find anything right off. When I set up the datasource as MySQL I put in UTF8, but that didn’t change anything. I’m not sure how to debug the actual SQL being ran, but I have a feeling Hibernate is actually trying to run a SQL statement that sets = null, but I don’t know where it’s getting the null value from.

~Brad

Yes I don’t know how to reprodu e it or go after it.

Ok, I’ll hit the Railo list with it and see if maybe it’s just a general Railo/Hibernate/ORM thing. If this was SQL Server, I’d just run profiler but I’m a little out of my element in MySQL.

~Brad

For what it’s worth, I asked over in Railo-land. Oguz had some settings for me to change but they didn’t seem to work. After messing for a bit, unable to get any pages to load yet, I looked in the database and noticed that the tables had been created and populated even though it was still throwing the error. I changed “dropcreate” to “update” and everything started working.

I’m ignoring this for now, but if I change it back to dropcreate, the error still happens.

~Brad

Interesting.

Played around with this some more this morning. I figured out why I couldn’t get Oguz’s JDBC URL params to work. Apparently as soon as you start including a query string in your JDBC URL, the username and password fields in the Railo data source admin are ignored and you have to use the &user=myUser&password=myPass syntax. (I also locked myself out of Mysql and realized I had forgotten the root password, which was fun)

Regardless, even after I got ?useUnicode=true&characterEncoding=UTF-8 working, it still doesn’t make the error go away when using dropcreate.

I did narrow it down to the sqlScript setting which is causing the error. If I remove this line at the bottom of the “contentbox_data.sql” script:

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

The error goes away. The variable @OLD_CHARACTER_SET_CLIENT is set at the top of the script with this line:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

The only thing I can think of is that @@CHARACTER_SET_CLIENT is returning null, but when I run “SELECT @@CHARACTER_SET_CLIENT” on the database, I get “latin1” which is also very weird, since I’ve set the default character set to UTF-8 in about 70 different places.

Thanks!

~Brad

Ahh got it, thanks Brad.

Yes, that data.sql will disappear once the installer is complete. So no worries there.

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano