Looks like I actually can't actually try this setting out, we're running a later version of mysql; 5.1.25.
----------------------------- Kevin Wilkinson Student Affairs IT University of California, Irvine (949) 824-0437 Please think of the environment before printing this message. ----------------------------- On Feb 9, 2012, at 8:28 AM, Eric Dalquist wrote: > 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<[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
