Skip to Content.
Sympa Menu

grouper-users - RE: [grouper-users] Re: Upgrade DB from 2.2 -> 2.3, MySQL issues

Subject: Grouper Users - Open Discussion List

List archive

RE: [grouper-users] Re: Upgrade DB from 2.2 -> 2.3, MySQL issues


Chronological Thread 
  • From: "Hyzer, Chris" <>
  • To: Brett Bieber <>, "Waldbieser, Carl" <>
  • Cc: Gouper Users List <>
  • Subject: RE: [grouper-users] Re: Upgrade DB from 2.2 -> 2.3, MySQL issues
  • Date: Wed, 25 May 2016 16:36:03 +0000
  • Accept-language: en-US
  • Authentication-results: unl.edu; dkim=none (message not signed) header.d=none;unl.edu; dmarc=none action=none header.from=isc.upenn.edu;
  • Spamdiagnosticmetadata: NSPM
  • Spamdiagnosticoutput: 1:23

I think the setting suggested by Carl is the way to go.  Can you please do that?

 

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

 

http://dba.stackexchange.com/questions/49913/specified-key-was-too-long-max-key-length-is-1000-bytes-in-mysql-5-6

 

Please let us know how it goes.

 

Thanks

Chris

 

From: Brett Bieber [mailto:]
Sent: Tuesday, May 24, 2016 5:59 PM
To: Waldbieser, Carl <>; Hyzer, Chris <>
Cc: Gouper Users List <>
Subject: Re: [grouper-users] Re: Upgrade DB from 2.2 -> 2.3, MySQL issues

 

I ran in to the same issue as Carl, the upgrade failed to create a proper ddl upgrade script and failed to complete. Adding -deep didn't seem to do anything different to the check, or the generated ddl sql script. We're using Server version: 5.5.47-MariaDB-log MariaDB Server, InnoDB engine, utf8_bin default collation. I don't see the error message regarding the key length, but I haven't manually messed with the Subject versions in the table.

 

Are there any complete instructions for overcoming this issue?

Thanks,

 

-Brett

 

On Mon, May 16, 2016 at 10:40 AM Waldbieser, Carl <> wrote:

Chris,

No, out database is set to utf8_bin collation by default.
I could be mistaken, but I believe that the index needs to reserve up to 3 bytes per character, and that is what pushes the index over the limit.
We are on a somewhat older MySQL, so I am going to have to wait for a database upgrade sometime this summer before I can increase the index size and upgrade to Grouper v2.3.

Thanks,
Carl

----- Original Message -----
From: "Hyzer, Chris" <>
To: "waldbiec" <>, "Gouper Users List" <>
Sent: Sunday, May 15, 2016 4:22:50 PM
Subject: RE: Upgrade DB from 2.2 -> 2.3, MySQL issues

Not sure why your subject table had a wrong value in there, maybe the original script didn’t finish all the way?

You can add "-deep" to the call which will ignore those version numbers and just interrogate your database.

Is your database utfmb4?  Any chance it can just be utf8 bin?  If not, you can try increasing the max index value, I wasn’t aware you could do that :)

Thanks
Chris

-----Original Message-----
From: [mailto:] On Behalf Of Waldbieser, Carl
Sent: Friday, May 13, 2016 3:34 PM
To: Gouper Users List <>
Subject: Re: [grouper-users] Upgrade DB from 2.2 -> 2.3, MySQL issues


After poking at this issue for a while, I changed the value of "grouper_ddl.db_version" for the row with objectname == "Subject" from 1 to 29.
I have no idea what this means, but after that, `gsh -registry -check` generated a DDL script with the DROPs I'd expect.

My problem now is the following definition:

    CREATE TABLE grouper_QZ_JOB_DETAILS
    (
        sched_name VARCHAR(120) NOT NULL,
        job_name VARCHAR(200) NOT NULL,
        job_group VARCHAR(200) NOT NULL,
        description VARCHAR(250) NULL,
        job_class_name VARCHAR(250) NOT NULL,
        is_durable TINYINT(1) NOT NULL,
        is_nonconcurrent TINYINT(1) NOT NULL,
        is_update_data TINYINT(1) NOT NULL,
        requests_recovery TINYINT(1) NOT NULL,
        job_data LONGBLOB NULL,
        PRIMARY KEY (sched_name, job_name, job_group)
    );

Specifically, I get the following error:

  ERROR 1071 (42000) at line 153: Specified key was too long; max key length is 1000 bytes

Have any other folks using MySQL run into this?  I see there is a StackExchange article [1] that talks about how this limit can be increased to 3072 bytes-- is that what others have done?

Thanks,
Carl Waldbieser
ITS Systems Programmer
Lafayette College

[1] http://dba.stackexchange.com/questions/49913/specified-key-was-too-long-max-key-length-is-1000-bytes-in-mysql-5-6

----- Original Message -----
From: "waldbiec" <>
To: "Gouper Users List" <>
Sent: Friday, May 13, 2016 1:52:28 PM
Subject: [grouper-users] Upgrade DB from 2.2 -> 2.3, MySQL issues

I tried upgrading my development instance of Grouper today from v2.2 to v2.3.  The software upgrade went pretty smoothly.

The generated SQL script is another story.  The first statement in it is `CREATE TABLE grouper_ddl`.  This table already exists in my Grouper database, so that's going to cause an error.

Is there something I am missing that has caused the SQL script that was generated to *not* recognize a bunch of tables in my database?  It appears as though there are many CREATE TABLE statements in the script that already exist in my database.

Any advice would be appreciated.

Thanks,
Carl Waldbieser
ITS Systems Programmer
Lafayette College




Archive powered by MHonArc 2.6.16.

Top of Page