Solved

Grails app MYSQL database problem

Posted on 2014-12-31
11
266 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 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
aws pricing 2 45
Insert values are dynamic 11 41
Detailed steps to upload 6 37
BACKUP of mysql database from mysql server - using Coldfusion 9 37
A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

770 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