Solved

Grails app MYSQL database problem

Posted on 2014-12-31
11
259 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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
 
LVL 25

Author Comment

by:Kyle Hamilton
Comment Utility
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
Comment Utility
Happy New Year Dave!!
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
Comment Utility
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
Comment Utility
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 82

Expert Comment

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

Author Comment

by:Kyle Hamilton
Comment Utility
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
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now