Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Grails app MYSQL database problem

Posted on 2014-12-31
11
Medium Priority
?
296 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
  • 6
  • 5
11 Comments
 
LVL 84

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 84

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 84

Accepted Solution

by:
Dave Baldwin earned 2000 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 84

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 84

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video teaches viewers about errors in exception handling.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Suggested Courses

885 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