-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<[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
smime.p7s
Description: S/MIME Cryptographic Signature
