Solved

Grails app MYSQL database problem

Posted on 2014-12-31
11
282 Views
Last Modified: 2014-12-31
I have a Grails app that I am trying to get up and running in my local environment. I'm using Spring to run the app. Everything seems to be going well until the part when the database gets created. BTW, I am not the app developer, I just need to have it running locally (This is my first Grails experience. Please be patient).

[main] ERROR [] liquibase [?] - Error executing SQL CREATE TABLE `activity` (`id` BIGINT AUTO_INCREMENT  NOT NULL, `version` BIGINT NOT NULL, `activity_type` VARCHAR(255) NOT NULL, `content` VARCHAR(32000) NOT NULL, `date_created` DATETIME NOT NULL, `grouping_id` BIGINT NOT NULL, `item_id` BIGINT NOT NULL, `item_type` VARCHAR(255) NOT NULL, `related_item_id` BIGINT, `related_item_type` VARCHAR(255), `subject` VARCHAR(255) NOT NULL, `user_id` BIGINT NOT NULL, CONSTRAINT `activityPK` PRIMARY KEY (`id`)) ENGINE=InnoDB
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'content' (max = 21845); use BLOB or TEXT instead

Open in new window


I'm almost a 100% sure the issue is not with the app, but with my local environment. My colleague is able to run it successfully on his Windows PC.

I tried changing the character set from utf-8 to ascii, to latin1, to some others, but then I get different errors.

for example:
> alter database iq4 character set=latin1;

Open in new window

gives me the following error:
[main] ERROR [] liquibase [?] - Error executing SQL INSERT INTO `school_category` (`school_code`, `school_disc`, `school_name`, `version`) VALUES ('', 'Mexico', 'Universidad Nacional Aut?noma de M?xico ', '1')
java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBDnom...' for column 'school_name' at row 1

Open in new window

0
Comment
Question by:Kyle Hamilton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40526287
The last error is because the string starts with a Unicode / UTF-8 byte order mark.  http://en.wikipedia.org/wiki/Byte_order_mark  Note that changing the 'collation' of a table or database does Not change the contents.  UTF-8 and Latin1 are the same in the low 127 (ASCII) characters but are different above that.  You should probably be telling your JAVA code to operate in UTF-8 also.
0
 
LVL 25

Author Comment

by:Kyle Hamilton
ID: 40526291
ok. so before compiling I ran:

export JAVA_TOOL_OPTIONS=-Dfile.encoding=UTF-8

then dropped the database, created a new database, and ran > alter database iq4 character set=utf8;

And I still end up with the "Column length too big" error

what else can I try?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40526304
That isn't necessarily all that's needed.  From this page: http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html
CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

Open in new window

While your post showed a byte order mark, you might try to verify that the source data is actually using UTF-8.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Author Comment

by:Kyle Hamilton
ID: 40526307
I did all that, and still getting the same error. I think I might be chasing the wrong rabbit.

There is a DB script (which I can't find btw - ugh) that attempts to create a column:
`content` VARCHAR(32000) NOT NULL

Open in new window

,

But in the error log it says:
ENGINE=InnoDB: Column length too big for column 'content' (max = 21845); use BLOB or TEXT instead

Open in new window


I looked up varchar and the max appears to be  65,535. "The length can be specified as a value from 0 to 65,535"
http://dev.mysql.com/doc/refman/5.1/en/char.html

since utf8 uses 3 bytes per character, then the max has to be 65,535/3 = 21845, which aligns with the error log. So I'm guessing I need to use a different character set, one that uses a max of 2 bytes per character. But when I try the various character sets (from the list available mysql> SHOW CHARACTER SET ) I get the bad string values error.

not sure what else to do...

Thanks for your patience :)
0
 
LVL 25

Author Comment

by:Kyle Hamilton
ID: 40526335
Happy New Year Dave!!
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 40526337
Happy New Year, Kyle.  The problem is that 'using a different character set' does Not change the data.  It will still have 3 byte characters in it.  You need to follow the recommendation and use BLOB or TEXT instead.
0
 
LVL 25

Author Comment

by:Kyle Hamilton
ID: 40526343
ok. but why was my colleague able to get it up and running? This is an app that hasn't had any changes made to it in a couple of years.. so I was hoping to just get it running..

Anyway, don't worry about it. If you have any other ideas let me know. I'm leaving it be for tonight.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40526357
You might wait and see how your colleague set it up.
0
 
LVL 25

Author Comment

by:Kyle Hamilton
ID: 40526361
well, actually, I spent about 2 hours with him today and he wasn't able to help me.
0
 
LVL 25

Author Closing Comment

by:Kyle Hamilton
ID: 40526368
I got it working.  I couldn't use blobs or text because that created other Grails errors. I ended up changing the max size to 255: varchar(255). I think the data will just get truncated, but I really don't care about that in my local instance.

I'm disappointed that I had to do that, but at least it's working. Now I'm really going to leave it alone. :)

Thanks for your help. Why anyone would ever want to use Grails completely defies (my) reason. It took me two days to get one application up and running, between all the various bits and pieces that had to be just right, (old) versions, plugins, etc...
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40526370
You're welcome.  I've never tried Grails.  I pretty much stick to PHP and MySQL so I don't have to learn too much new stuff anymore.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to implement Singleton Design Pattern in Java.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question