Re: [uportal-dev] uPortal 4.0.3 building with MySQL

[Eric Dalquist]

Well if we can get some verification that the new option fixes the index issue than we will need to document that fix. If it doesn't we'll need to review our indicies and columns to see if we can avoid the problem for at least those using as latin charset. It would probably be good to state that we do not recommend using MySQL if you need UTF-8 character support due to the index constraints imposed by the database.

-Eric

On 2/9/12 4:17 PM, Laura McCord wrote:

Is this something that needs to be added as a warning in the manual?

Laura


On 2/9/12 10:14 AM, Eric Dalquist wrote:

Awesome, thanks Kevin. This issue is going to become even more problematic as people want to allow UTF-8 characters as that limits our effective index width to 191 characters total. It would be unfortunate if we had to remove a bunch of indexes just because of a MySQL limitation.

-Eric

On 2/9/12 3:25 PM, kwilkins wrote:

I'm going to re init our uP4 instance today or tomorrow and try out the suggested MySQL option. Will let you know.

--Kevin Wilkinson
Sent from my mobile

On Feb 9, 2012, at 5:12 AM, Eric Dalquist<eric.dalqu...@doit.wisc.edu> wrote:

Please open a Jira for the INTERVAL keyword issue, we can get that fixed in 4.0.4.

-Eric

On 2/9/12 10:43 AM, Steve Swinsburg wrote:

As for the index length, this is probably the most frustrating limitation of MySQL. What I would like is for someone using MySQL to try out the sysvar_innodb_large_prefix option I linked to in the attached email. According to the MySQL docs that allows index lenghts up to 3600 bytes (a little more sane that 767 bytes).

Hi,





…. unique(PORTAL_COOKIE_ID, COOKIE_NAME(50))

Or MD5 the long columns.



cheers,
Steve




On 09/02/2012, at 6:56 PM, Fabrice Marchon wrote:

The first error is because MySQL has a size limit on the index and since multiple columns re involved it adds their lengths together. Develoeprs need to be aware of this when creating column lengths and then indexes. Does the cookie name really need to be allocated as a varchar(500)? If so, how much of it is unique enough to index off? You can specify a subset of the column to create the index, e.g.: The second is because INTERVAL is a reserved word in MySQL, but it has been used for a column name. That will need to be changed.

Hi,

Could someone please help me concerning installing uPortal 4.0.3 on
Tomcat 6 using a MySQL database?


1) Part 1 with MySQL 5.1

tables (about 73), but not all.

Here is my first MySQL environment:
- MySQL 5.1.57 (default storage InnoDB, lower_case_table_names=1,
org.jasig.portal.io.threadPool.maxThreads=1)

Here is my error:

     [java] ERROR HHH000388: Unsuccessful: create table

varchar(1000), COOKIE_DOMAIN varchar(500), ENTITY_VERSION bigint,
EXPIRES datetime not null, COOKIE_NAME varchar(500) not null,
COOKIE_PATH varchar(1000), SECURE boolean not null, COOKIE_VALUE
varchar(1000) not null, VERSION integer not null,

key (PORTLET_COOKIE_ID), unique (PORTAL_COOKIE_ID, COOKIE_NAME))
ENGINE=InnoDB

...


2) I try the same test with another version of MySQL 5.5
Here is my second MySQL environment:
- MySQL 5.5.19 (default storage InnoDB, lower_case_table_names=1,
org.jasig.portal.io.threadPool.maxThreads=1)

Here is my error:
ERROR HHH000388: Unsuccessful: create table

null, INTERVAL varchar(255)) ENGINE=InnoDB

use near 'INTERVAL varchar(255)
     [java]     ) ENGINE=InnoDB' at line 3
...


I followed up the online guides for building uPortal with MySQL:
-

I encounter problems with the 'ant dbtest' target: it can create some INFO HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect UP_PORTLET_COOKIES (PORTLET_COOKIE_ID bigint not null, COOKIE_COMMENT portalCookie_PORTAL_COOKIE_ID bigint, PORTAL_COOKIE_ID bigint, primary [java] ERROR Specified key was too long; max key length is 767 bytes UP_EVENT_AGGR_CONF_INTRVL_EXC (UP_EVENT_AGGR_CONF_INTRVL_ID bigint not [java] ERROR You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to https://wiki.jasig.org/pages/viewpage.action?pageId=42696767#Building%26DeployinguPortal-uPortalFullSourceVersion

- https://wiki.jasig.org/display/UPM40/MySQL

Full stack traces of compile and dbtest are attached for both tests.



database and it works perfectly!!

--
Fabrice Marchon

--

P.S.: I run the same build and deployment processes with a Postgres 8.2 You are currently subscribed to uportal-dev@lists.ja-sig.org as: steve.swinsb...@gmail.com To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/uportal-dev<uportal_403_compile_mysql55.txt><uportal_403_dbtest_mysql55.txt><uportal_403_dbtest_mysql51.txt><uportal_403_compile_mysql51.txt>

So a bane of my interactions trying to help people use MySQL has been the rather small 767 byte limit on index lengths (UTF8 characters in MySQL take 4 bytes) after doing some more digging today I found: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix

-Eric

Anyone out there using MySQL 5.5 or later want to give this a try and see if it resolves the index-length problems that have been reported in uP4? If we can get this verified we'll add it to the manual for setting up MySQL.

smime.p7s
Description: S/MIME Cryptographic Signature