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 <[email protected]> wrote: > Please open a Jira for the INTERVAL keyword issue, we can get that fixed in > 4.0.4. > > 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). > > -Eric > > On 2/9/12 10:43 AM, Steve Swinsburg wrote: >> Hi, >> >> 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.: >> >> …. unique(PORTAL_COOKIE_ID, COOKIE_NAME(50)) >> >> Or MD5 the long columns. >> >> 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. >> >> cheers, >> Steve >> >> >> >> >> On 09/02/2012, at 6:56 PM, Fabrice Marchon wrote: >> >>> 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 >>> I encounter problems with the 'ant dbtest' target: it can create some >>> 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: >>> INFO HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect >>> [java] ERROR HHH000388: Unsuccessful: create table >>> UP_PORTLET_COOKIES (PORTLET_COOKIE_ID bigint not null, COOKIE_COMMENT >>> 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, >>> portalCookie_PORTAL_COOKIE_ID bigint, PORTAL_COOKIE_ID bigint, primary >>> key (PORTLET_COOKIE_ID), unique (PORTAL_COOKIE_ID, COOKIE_NAME)) >>> ENGINE=InnoDB >>> [java] ERROR Specified key was too long; max key length is 767 bytes >>> ... >>> >>> >>> 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 >>> UP_EVENT_AGGR_CONF_INTRVL_EXC (UP_EVENT_AGGR_CONF_INTRVL_ID bigint not >>> null, INTERVAL varchar(255)) ENGINE=InnoDB >>> [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 >>> use near 'INTERVAL varchar(255) >>> [java] ) ENGINE=InnoDB' at line 3 >>> ... >>> >>> >>> I followed up the online guides for building uPortal with MySQL: >>> - >>> 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. >>> >>> >>> P.S.: I run the same build and deployment processes with a Postgres 8.2 >>> database and it works perfectly!! >>> >>> -- >>> Fabrice Marchon >>> >>> -- >>> You are currently subscribed to [email protected] as: >>> [email protected] >>> 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 > > 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. > > -Eric > -- You are currently subscribed to [email protected] as: [email protected] To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/uportal-dev
