mahara-contributors team mailing list archive

Thread
Date
[Bug 1514415] Re: [MySQL] Index column size too large. The maximum column size is 767 bytes.

To: mahara-contributors@xxxxxxxxxxxxxxxxxxx
From: Aaron Wells <1514415@xxxxxxxxxxxxxxxxxx>
Date: Mon, 09 Nov 2015 23:28:08 -0000
Reply-to: Bug 1514415 <1514415@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Hi Andreas,

Thanks for the bug report! That is an intriguing one, which I have never
seen before. When I install Mahara, it looks like my mysql setup
automatically puts a 255-character prefix on that index (i.e. only
indexes the first 255 characters of the interaction_forum_post.path
column).

Now that I look into it, I think the particular prefix that is giving
you problems is actually unnecessary anyway. It's an index on
interaction_forum_post.path, which was added for bug 992905, to allow
forum posts to be paginated but still sort correctly on each page, even
if you're using nested replies.

However, we only use interaction_forum_post.path in the "WHERE" section
of a query twice. And in both those cases, we're also filtering by
interaction_forum_post.topic. I think, in typical usage patterns, there
will not be so many forum posts under a single topic, that having a
separate index on the path column will actually improve performance by
much. (You'd need to have a forum topic thousands of pages long for that
to start happening.)

So I'm going to look into just removing this index.

** Changed in: mahara
       Status: New => In Progress

** Changed in: mahara
   Importance: Undecided => Low

** Changed in: mahara
    Milestone: None => 16.04.0

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1514415

Title:
  [MySQL] Index column size too large. The maximum column size is 767
  bytes.

Status in Mahara:
  In Progress

Bug description:
  Hallo,

  I have tried to install Mahara with MySQL.

  I have installed it in the following way:

  1. I cloned the git repository at https://git.mahara.org/mahara/mahara.git
  2. I built mahara with make css to create all neccessary files
  3. I started the installationn with php admin/cli/install.php --adminpassword='<my-password>' --adminemail=<my-mail>

  After a while the following error appeared:

  "(lib/dml.php:150) mysqli error: [1709: Index column size too large.
  The maximum column size is 767 bytes.]"

  I did some research and found a potential solution for that:

  1. Some entries in my.cnf, which I have already added:

  innodb_large_prefix=on
  innodb_file_format=barracuda
  innodb_file_per_table=true

  
  2. To add "ROW_FORMAT=DYNAMIC;" when creating a table. But I have no idea how to do this.

  
  More information on this error can be found on:

  http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/
  https://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
  https://dev.mysql.com/doc/refman/5.5/en/innodb-row-format-dynamic.html

  
  Here is the log-file

  [INF] 72 (admin/cli/install.php:74) Installing Mahara
  [INF] 72 (lib/mahara.php:228) Installing core
  [INF] 72 (lib/upgrade.php:1364) Enabled 2 HTML filters.
  [INF] 72 (lib/mahara.php:228) Installing firstcoredata
  [INF] 72 (lib/mahara.php:228) Installing localpreinst
  [INF] 72 (lib/mahara.php:228) Installing artefact.plans
  [INF] 72 (lib/mahara.php:228) Installing artefact.blog
  [INF] 72 (lib/mahara.php:228) Installing artefact.internal
  [INF] 72 (lib/mahara.php:228) Installing artefact.comment
  [INF] 72 (lib/mahara.php:228) Installing artefact.file
  [INF] 72 (artefact/file/lib.php:269) Updated filetype list: 142 added.
  [INF] 72 (lib/mahara.php:228) Installing artefact.annotation
  [INF] 72 (lib/mahara.php:228) Installing artefact.resume
  [INF] 72 (lib/mahara.php:228) Installing auth.saml
  [INF] 72 (lib/mahara.php:228) Installing auth.internal
  [INF] 72 (lib/mahara.php:228) Installing auth.ldap
  [INF] 72 (lib/mahara.php:228) Installing auth.browserid
  [INF] 72 (lib/mahara.php:228) Installing auth.none
  [INF] 72 (lib/mahara.php:228) Installing auth.imap
  [INF] 72 (lib/mahara.php:228) Installing auth.webservice
  [INF] 72 (lib/mahara.php:228) Installing auth.xmlrpc
  [INF] 72 (lib/mahara.php:228) Installing notification.email
  [INF] 72 (lib/mahara.php:228) Installing notification.emaildigest
  [INF] 72 (lib/mahara.php:228) Installing notification.internal
  [INF] 72 (lib/mahara.php:228) Installing search.elasticsearch
  [INF] 72 (lib/mahara.php:228) Installing search.internal
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/gallery
  [INF] 72 (lib/mahara.php:228) Installing blocktype.resume/entireresume
  [INF] 72 (lib/mahara.php:228) Installing blocktype.internal/profileinfo
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/image
  [INF] 72 (lib/mahara.php:228) Installing blocktype.internal/textbox
  [INF] 72 (lib/mahara.php:228) Installing blocktype.internal/socialprofile
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/pdf
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/html
  [INF] 72 (lib/mahara.php:228) Installing blocktype.blog/recentposts
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/filedownload
  [INF] 72 (lib/mahara.php:228) Installing blocktype.comment/comment
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/folder
  [INF] 72 (lib/mahara.php:228) Installing blocktype.blog/blog
  [INF] 72 (lib/mahara.php:228) Installing blocktype.resume/resumefield
  [INF] 72 (lib/mahara.php:228) Installing blocktype.blog/taggedposts
  [INF] 72 (lib/mahara.php:228) Installing blocktype.blog/blogpost
  [INF] 72 (lib/mahara.php:228) Installing blocktype.myfriends
  [INF] 72 (lib/mahara.php:228) Installing blocktype.externalvideo
  [INF] 72 (lib/mahara.php:228) Installing blocktype.inbox
  [INF] 72 (lib/mahara.php:228) Installing blocktype.recentforumposts
  [INF] 72 (lib/mahara.php:228) Installing blocktype.plans/plans
  [INF] 72 (lib/mahara.php:228) Installing blocktype.groupmembers
  [INF] 72 (lib/mahara.php:228) Installing blocktype.groupinfo
  [INF] 72 (lib/mahara.php:228) Installing blocktype.googleapps
  [INF] 72 (lib/mahara.php:228) Installing blocktype.file/internalmedia
  [INF] 72 (lib/mahara.php:228) Installing blocktype.text
  [INF] 72 (lib/mahara.php:228) Installing blocktype.externalfeed
  [INF] 72 (lib/mahara.php:228) Installing blocktype.wall
  [INF] 72 (lib/mahara.php:228) Installing blocktype.annotation/annotation
  [INF] 72 (lib/mahara.php:228) Installing blocktype.creativecommons
  [INF] 72 (lib/mahara.php:228) Installing blocktype.groupviews
  [INF] 72 (lib/mahara.php:228) Installing blocktype.myviews
  [INF] 72 (lib/mahara.php:228) Installing blocktype.navigation
  [INF] 72 (lib/mahara.php:228) Installing blocktype.mygroups
  [INF] 72 (lib/mahara.php:228) Installing blocktype.watchlist
  [INF] 72 (lib/mahara.php:228) Installing blocktype.newviews
  [INF] 72 (lib/mahara.php:228) Installing interaction.forum
  [DBG] 72 (lib/dml.php:150) mysqli error: [1709: Index column size too large. The maximum column size is 767 bytes.] in ON interaction_forum_post (path), )
  [DBG] 72 (lib/dml.php:150) Command was: CREATE INDEX inteforupost_pat_ix ON interaction_forum_post (path)
  [WAR] 72 (lib/errors.php:747) Could not execute command: CREATE INDEX inteforupost_pat_ix ON interaction_forum_post (pa
  Call stack (most recent first):
    * log_message("Could not execute command: CREATE INDEX inteforupo...", 8, true, true) at /var/www/mahara.via4all.de/l
    * log_warn("Could not execute command: CREATE INDEX inteforupo...") at /var/www/mahara.via4all.de/lib/errors.php:747
    * SQLException->__construct("Could not execute command: CREATE INDEX inteforupo...") at /var/www/mahara.via4all.de/li
    * execute_sql("CREATE INDEX inteforupost_pat_ix ON interaction_fo...") at /var/www/mahara.via4all.de/lib/dml.php:1390
    * execute_sql_arr(array(size 21)) at /var/www/mahara.via4all.de/lib/ddl.php:752
    * install_from_xmldb_file("/var/www/mahara.via4all.de/interaction/forum/db/in...") at /var/www/mahara.via4all.de/lib/
    * upgrade_plugin(object(stdClass)) at /var/www/mahara.via4all.de/lib/mahara.php:263
    * upgrade_mahara(array(size 70)) at /var/www/mahara.via4all.de/admin/cli/install.php:75

  [DBG] 72 (lib/ddl.php:753) Array
  [DBG] 72 (lib/ddl.php:753) (
  [DBG] 72 (lib/ddl.php:753)     [0] => CREATE TABLE interaction_forum_instance_config (
  [DBG] 72 (lib/ddl.php:753)     forum BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     field TEXT NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     value TEXT NOT NULL,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforuinstconf_for_fk FOREIGN KEY (forum) REFERENCES interaction_instance (id)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [1] => CREATE INDEX inteforuinstconf_for_ix ON interaction_forum_instance_config (forum)
  [DBG] 72 (lib/ddl.php:753)     [2] => CREATE TABLE interaction_forum_topic (
  [DBG] 72 (lib/ddl.php:753)     id BIGINT(10) NOT NULL auto_increment,
  [DBG] 72 (lib/ddl.php:753)     forum BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     sticky TINYINT(1) NOT NULL DEFAULT 0,
  [DBG] 72 (lib/ddl.php:753)     closed TINYINT(1) NOT NULL DEFAULT 0,
  [DBG] 72 (lib/ddl.php:753)     deleted TINYINT(1) NOT NULL DEFAULT 0,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT  PRIMARY KEY (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforutopi_for_fk FOREIGN KEY (forum) REFERENCES interaction_instance (id)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [3] => CREATE INDEX inteforutopi_for_ix ON interaction_forum_topic (forum)
  [DBG] 72 (lib/ddl.php:753)     [4] => CREATE TABLE interaction_forum_post (
  [DBG] 72 (lib/ddl.php:753)     id BIGINT(10) NOT NULL auto_increment,
  [DBG] 72 (lib/ddl.php:753)     topic BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     parent BIGINT(10) DEFAULT NULL,
  [DBG] 72 (lib/ddl.php:753)     poster BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     subject VARCHAR(255) DEFAULT NULL,
  [DBG] 72 (lib/ddl.php:753)     body TEXT NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     ctime DATETIME NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     deleted TINYINT(1) NOT NULL DEFAULT 0,
  [DBG] 72 (lib/ddl.php:753)     sent TINYINT(1) NOT NULL DEFAULT 0,
  [DBG] 72 (lib/ddl.php:753)     path VARCHAR(2048) DEFAULT NULL,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT  PRIMARY KEY (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforupost_top_fk FOREIGN KEY (topic) REFERENCES interaction_forum_topic (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforupost_par_fk FOREIGN KEY (parent) REFERENCES interaction_forum_post (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforupost_pos_fk FOREIGN KEY (poster) REFERENCES usr (id)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [5] => CREATE INDEX inteforupost_pat_ix ON interaction_forum_post (path)
  [DBG] 72 (lib/ddl.php:753)     [6] => CREATE INDEX inteforupost_top_ix ON interaction_forum_post (topic)
  [DBG] 72 (lib/ddl.php:753)     [7] => CREATE INDEX inteforupost_par_ix ON interaction_forum_post (parent)
  [DBG] 72 (lib/ddl.php:753)     [8] => CREATE INDEX inteforupost_pos_ix ON interaction_forum_post (poster)
  [DBG] 72 (lib/ddl.php:753)     [9] => CREATE TABLE interaction_forum_subscription_forum (
  [DBG] 72 (lib/ddl.php:753)     user BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     forum BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     `key` VARCHAR(50) NOT NULL,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT  PRIMARY KEY (user, forum),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforusubsforu_use_fk FOREIGN KEY (user) REFERENCES usr (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforusubsforu_for_fk FOREIGN KEY (forum) REFERENCES interaction_instance (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforusubsforu_key_uk UNIQUE (`key`)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [10] => CREATE INDEX inteforusubsforu_use_ix ON interaction_forum_subscription_forum (us
  [DBG] 72 (lib/ddl.php:753)     [11] => CREATE INDEX inteforusubsforu_for_ix ON interaction_forum_subscription_forum (fo
  [DBG] 72 (lib/ddl.php:753)     [12] => CREATE TABLE interaction_forum_subscription_topic (
  [DBG] 72 (lib/ddl.php:753)     user BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     topic BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     `key` VARCHAR(50) NOT NULL,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT  PRIMARY KEY (user, topic),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforusubstopi_use_fk FOREIGN KEY (user) REFERENCES usr (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforusubstopi_top_fk FOREIGN KEY (topic) REFERENCES interaction_forum_topic (i
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforusubstopi_key_uk UNIQUE (`key`)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [13] => CREATE INDEX inteforusubstopi_use_ix ON interaction_forum_subscription_topic (us
  [DBG] 72 (lib/ddl.php:753)     [14] => CREATE INDEX inteforusubstopi_top_ix ON interaction_forum_subscription_topic (to
  [DBG] 72 (lib/ddl.php:753)     [15] => CREATE TABLE interaction_forum_moderator (
  [DBG] 72 (lib/ddl.php:753)     user BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     forum BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforumode_use_fk FOREIGN KEY (user) REFERENCES usr (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforumode_for_fk FOREIGN KEY (forum) REFERENCES interaction_instance (id)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [16] => CREATE INDEX inteforumode_use_ix ON interaction_forum_moderator (user)
  [DBG] 72 (lib/ddl.php:753)     [17] => CREATE INDEX inteforumode_for_ix ON interaction_forum_moderator (forum)
  [DBG] 72 (lib/ddl.php:753)     [18] => CREATE TABLE interaction_forum_edit (
  [DBG] 72 (lib/ddl.php:753)     user BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     post BIGINT(10) NOT NULL,
  [DBG] 72 (lib/ddl.php:753)     ctime DATETIME NOT NULL,
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforuedit_use_fk FOREIGN KEY (user) REFERENCES usr (id),
  [DBG] 72 (lib/ddl.php:753) CONSTRAINT inteforuedit_pos_fk FOREIGN KEY (post) REFERENCES interaction_forum_post (id)
  [DBG] 72 (lib/ddl.php:753) )ENGINE=innodb
  [DBG] 72 (lib/ddl.php:753)     [19] => CREATE INDEX inteforuedit_use_ix ON interaction_forum_edit (user)
  [DBG] 72 (lib/ddl.php:753)     [20] => CREATE INDEX inteforuedit_pos_ix ON interaction_forum_edit (post)
  [DBG] 72 (lib/ddl.php:753) )
  [DBG] 72 (lib/ddl.php:753)
  [WAR] 72 (lib/errors.php:747) Failed to install (check logs for xmldb errors)
  Call stack (most recent first):
    * log_message("Failed to install (check logs for xmldb errors)", 8, true, true) at /var/www/mahara.via4all.de/lib/err
    * log_warn("Failed to install (check logs for xmldb errors)") at /var/www/mahara.via4all.de/lib/errors.php:747
    * SQLException->__construct("Failed to install (check logs for xmldb errors)") at /var/www/mahara.via4all.de/lib/ddl.
    * install_from_xmldb_file("/var/www/mahara.via4all.de/interaction/forum/db/in...") at /var/www/mahara.via4all.de/lib/
    * upgrade_plugin(object(stdClass)) at /var/www/mahara.via4all.de/lib/mahara.php:263
    * upgrade_mahara(array(size 70)) at /var/www/mahara.via4all.de/admin/cli/install.php:75

  [WAR] 72 (lib/ddl.php:754) Failed to install (check logs for xmldb errors)
  Call stack (most recent first):
    * install_from_xmldb_file("/var/www/mahara.via4all.de/interaction/forum/db/in...") at /var/www/mahara.via4all.de/lib/
    * upgrade_plugin(object(stdClass)) at /var/www/mahara.via4all.de/lib/mahara.php:263
    * upgrade_mahara(array(size 70)) at /var/www/mahara.via4all.de/admin/cli/install.php:75

  A nonrecoverable error occurred. This probably means you have
  encountered a bug in the systemroot

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1514415/+subscriptions
References

[Bug 1514415] [NEW] [MySQL] Index column size too large. The maximum column size is 767 bytes.
From: Andreas Feldmann, 2015-11-09