Grails app MYSQL database problem

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

LVL 25
Kyle HamiltonData ScientistAsked:
Who is Participating?
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Kyle HamiltonData ScientistAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Dave BaldwinFixer of ProblemsCommented:
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
 
Kyle HamiltonData ScientistAuthor Commented:
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
 
Kyle HamiltonData ScientistAuthor Commented:
Happy New Year Dave!!
0
 
Kyle HamiltonData ScientistAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
You might wait and see how your colleague set it up.
0
 
Kyle HamiltonData ScientistAuthor Commented:
well, actually, I spent about 2 hours with him today and he wasn't able to help me.
0
 
Kyle HamiltonData ScientistAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.