[Building Sakai] Latest 2.7.x throw UTF-8 DB Error

[Steve Swinsburg]

You need to create the database with the utf8 character set:

create database sakai default character set utf8;

Check the equivalent syntax when using mysqladmin.

I believe you can change the encoding after the fact, but AFAIK it's not trivial so best to create the db in the right format to start with.

And in your Tomcat connector you need the URIEncoding param:

  <Connector port="8081" maxHttpHeaderSize="8192"
               maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
               enableLookups="false" redirectPort="8443" acceptCount="100"
               connectionTimeout="20000" disableUploadTimeout="true" URIEncoding="UTF-8"/>

It might just be a case of adding this to your Tomcat connector as your system might default to utf8 so teh database might be ok.

cheers,
Steve


On 04/06/2010, at 5:25 PM, Warwick Chapman wrote:

> Hey Steve
> 
> I created the DB with:
> 
> mysqladmin -p create sakai
> 
> The MySQL config in sakai.properties follows:
> 
> vendor at org.sakaiproject.db.api.SqlService=mysql
> driverClassName at javax.sql.BaseDataSource=com.mysql.jdbc.Driver
> hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
> url at javax.sql.BaseDataSource=jdbc:mysql://dbserver:3306/sakai?useUnicode=true&characterEncoding=UTF-8
> validationQuery at javax.sql.BaseDataSource=select 1 from DUAL
> defaultTransactionIsolationString at javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
> 
> -- Warwick Bruce Chapman | +27 83 7797 094 | http://warwickchapman.com
> 
> 
> On Fri, Jun 4, 2010 at 8:54 AM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
> Hi Warwick,
> 
> When you created your database, did you create it with the utf8 character set? Also, does your Tomcat connector have the URIEncoding="UTF-8" parameter added?
> 
> cheers,
> Steve
> 
> 
> On 04/06/2010, at 4:48 PM, Warwick Chapman wrote:
> 
>> Hi All
>> 
>> I'm back on the horse now and trying to install the latest 2.7.x branch (78257) but am getting the following in the catalina.out file:
>> 
>> <log>
>> FATAL main org.sakaiproject.content.impl.DbContentService - Check on Database Failed
>> java.sql.SQLException: Incorrect string value: '\xC2\x80\xC2\x81\xC2\x82...' for column 'XML' at row 1
>> 
>> ...
>> 
>> FATAL main org.sakaiproject.content.impl.DbContentService - WARNING
>>   The connection from this instance of Sakai to the database
>>   has been tested and found to corrupt UTF-8 Data.
>>   In order for Sakai to operate correctly you must ensure that your
>>   database setup is correct for UTF-8 data. This includes both the
>>   JDBC connection to the database and the underlying storage in the
>>   database.
>>   The test that was performed on your database create a table
>>   wrote some data to that table and read it back again. On reading
>>   that data back it found some form of corruption, reported above.
>> 
>>  More information on database setup for sakai can be found at
>>  http://bugs.sakaiproject.org/confluence/display/DOC/Install+Guide+-+DB+(2.4)
>> 
>>  Sakai Startup will continue but you might want to address this issue ASAP.
>> </log>
>> 
>> MySQL is using the InnoDB engine as recommended by Anthony Whyte and I can confirm that the DB was successfully populated upon initial startup.
>> 
>> -- Warwick Bruce Chapman | +27 83 7797 094 | http://warwickchapman.com
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>> 
>> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100604/b644ecae/attachment.html