Jira'd as https://issues.jasig.org/browse/UP-3339
cheers, Steve On 10/02/2012, at 8:54 AM, Kevin Wilkinson wrote: > 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 -- 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
