22 April 2009, 17:53

Upgrade from v1.0.9 to v1.0.10 earlier this afternoon was successfully uneventful.

Attempted upgrade to v1.1.0 failed with error:

:( Could not execute command: CREATE TABLE grouptype_event_subscription ( id BIGINT(10) NOT NULL auto_increment, plugin VARCHAR(255) NOT NULL, event VARCHAR(50) NOT NULL, callfunction VARCHAR(255) NOT NULL, CONSTRAINT PRIMARY KEY (id), CONSTRAINT grouevensubs_plu_fk FOREIGN KEY (plugin) REFERENCES grouptype_installed (name), CONSTRAINT grouevensubs_eve_fk FOREIGN KEY (event) REFERENCES event_type (name) )TYPE=innodb

Call stack (most recent first):

• execute_sql("CREATE TABLE grouptype_event_subscription (
  id...", true) at /srv/www/mahara-1.1.0/htdocs/lib/dml.php:1360
• execute_sql_arr(array(size 4), true, true) at /srv/www/mahara-1.1.0/htdocs/lib/ddl.php:599
• create_table(object(XMLDBTable)) at /srv/www/mahara-1.1.0/htdocs/lib/db/upgrade.php:368
• xmldb_core_upgrade("2008040220") at /srv/www/mahara-1.1.0/htdocs/lib/upgrade.php:271
• upgrade_core(object(stdClass)) at /srv/www/mahara-1.1.0/htdocs/admin/upgrade.json.php:71

 php.log shows what appears to be a series of successful group migrations followed by:

[22-Apr-2009 18:31:43] [DBG] cc (lib/db/upgrade.php:174) Migrating group Test invite group (14)
[22-Apr-2009 18:31:43] [DBG] cc (lib/db/upgrade.php:185)  * new group type is standard
[22-Apr-2009 18:31:43] [DBG] cc (lib/db/upgrade.php:222)  * marked appropriate users as being members
[22-Apr-2009 18:31:43] [DBG] cc (lib/db/upgrade.php:212)  * marked user 52 as having the admin role
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) mysql error: [1005: Can't create table './mahara/grouptype_event_subsc_event_subscription (
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     id BIGINT(10) NOT NULL auto_increment,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     plugin VARCHAR(255) NOT NULL,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     event VARCHAR(50) NOT NULL,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     callfunction VARCHAR(255) NOT NULL,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) CONSTRAINT  PRIMARY KEY (id),
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) CONSTRAINT grouevensubs_plu_fk FOREIGN KEY (plugin) REFERENCES groupty
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) CONSTRAINT grouevensubs_eve_fk FOREIGN KEY (event) REFERENCES event_ty
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) )TYPE=innodb, )
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) Command was: CREATE TABLE grouptype_event_subscription (
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     id BIGINT(10) NOT NULL auto_increment,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     plugin VARCHAR(255) NOT NULL,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     event VARCHAR(50) NOT NULL,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127)     callfunction VARCHAR(255) NOT NULL,
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) CONSTRAINT  PRIMARY KEY (id),
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) CONSTRAINT grouevensubs_plu_fk FOREIGN KEY (plugin) REFERENCES groupty
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) CONSTRAINT grouevensubs_eve_fk FOREIGN KEY (event) REFERENCES event_ty
[22-Apr-2009 18:31:43] [DBG] cc (lib/dml.php:127) )TYPE=innodb
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663) Could not execute command: CREATE TABLE grouptype_event_subscriptio
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663)     id BIGINT(10) NOT NULL auto_increment,
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663)     plugin VARCHAR(255) NOT NULL,
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663)     event VARCHAR(50) NOT NULL,
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663)     callfunction VARCHAR(255) NOT NULL,
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663) CONSTRAINT  PRIMARY KEY (id),
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663) CONSTRAINT grouevensubs_plu_fk FOREIGN KEY (plugin) REFERENCES grou
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663) CONSTRAINT grouevensubs_eve_fk FOREIGN KEY (event) REFERENCES event
[22-Apr-2009 18:31:43] [WAR] cc (lib/errors.php:663) )TYPE=innodb
[22-Apr-2009 18:31:43] Call stack (most recent first):
[22-Apr-2009 18:31:43]   * log_message("Could not execute command: CREATE TABLE grouptype_...", 8, true, true) at /srv/w
[22-Apr-2009 18:31:43]   * log_warn("Could not execute command: CREATE TABLE grouptype_...") at /srv/www/mahara-1.1.0/ht
[22-Apr-2009 18:31:43]   * SQLException->__construct("Could not execute command: CREATE TABLE grouptype_...") at /srv/ww
[22-Apr-2009 18:31:43]   * execute_sql("CREATE TABLE grouptype_event_subscription (
[22-Apr-2009 18:31:43]     id...", true) at /srv/www/mahara-1.1.0/htdocs/lib/dml.php:1360
[22-Apr-2009 18:31:43]   * execute_sql_arr(array(size 4), true, true) at /srv/www/mahara-1.1.0/htdocs/lib/ddl.php:599
[22-Apr-2009 18:31:43]   * create_table(object(XMLDBTable)) at /srv/www/mahara-1.1.0/htdocs/lib/db/upgrade.php:368
[22-Apr-2009 18:31:43]   * xmldb_core_upgrade("2008040220") at /srv/www/mahara-1.1.0/htdocs/lib/upgrade.php:271
[22-Apr-2009 18:31:43]   * upgrade_core(object(stdClass)) at /srv/www/mahara-1.1.0/htdocs/admin/upgrade.json.php:71
[22-Apr-2009 18:31:43]

Searching for details about mysql error 1005 has been annoyingly unproductive.

Help please?

 

Hrynkiw at Kwantlen

22 April 2009, 18:43

Okay, so the first thing you should know is that your database will now be in an inconsistent, half-upgraded state. I hope you took a backup before you began! You will need to try again from that backup point.

But before you do that, could you take that query that is failing, paste it into the mysql shell, run it and see what it says, then try running 'show engine innodb status;' and pasting here what that says?

If you don't have access to a mysql shell, you could try the commands through phpmyadmin or similar, and see what output you get.

ps: I edited your post to remove the img tag that was linking to the 'fail' icon on your server, it was causing the page to hang waiting for it to load.

23 April 2009, 12:27

Hello Nigel and thank-you for your reply.

> Okay, so the first thing you should know is that your database will now be in an inconsistent, half-upgraded state. I hope you took a backup before you began! You will need to try again from that backup point.

That's as I assumed. No problem; have backups.

> But before you do that, could you take that query that is failing, paste it into the mysql shell, run it and see what it says, then try running 'show engine innodb status;' and pasting here what that says?

 (login as user mahara)

mysql> CREATE TABLE grouptype_event_subscription ( id BIGINT(10) NOT NULL auto_increment,
    -> plugin VARCHAR(255) NOT NULL, event VARCHAR(50) NOT NULL, callfunction
    -> VARCHAR(255) NOT NULL, CONSTRAINT PRIMARY KEY (id), CONSTRAINT
    -> grouevensubs_plu_fk FOREIGN KEY (plugin) REFERENCES
    -> grouptype_installed (name), CONSTRAINT grouevensubs_eve_fk FOREIGN KEY
    -> (event) REFERENCES event_type (name) )TYPE=innodb
    -> ;
ERROR 1005 (HY000): Can't create table './mahara/grouptype_event_subscription.frm' (errno: 150)
mysql> show engine innodb status;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

Hold on. Let me login as root and run it again.

mysq> show engine innodb status;
[divider lines trimmed]
=====================================
090423 10:23:03 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 109, signal count 109
Mutex spin waits 0, rounds 320, OS waits 1
RW-shared spins 217, OS waits 108; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
090423 10:13:07 Error in foreign key constraint of table mahara/grouptype_event_subscription:
 FOREIGN KEY
(event) REFERENCES event_type (name) )TYPE=innodb:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 12895
Purge done for trx's n:o < 0 12888 undo n:o < 0 0
History list length 6
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 27160, OS thread id 3021573024
MySQL thread id 11008, query id 251102 localhost root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
311 OS file reads, 6616 OS file writes, 5613 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 380, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 32477834
Log flushed up to   0 32477834
Last checkpoint at  0 32477834
0 pending log writes, 0 pending chkp writes
5405 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 18922142; in additional pool allocated 1041920
Buffer pool size   512
Free buffers       29
Database pages     481
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 367, created 2084, written 6369
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 27160, id 2991815584, state: waiting for server activity
Number of rows inserted 63208, updated 219, deleted 0, read 41229
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |

> ps: I edited your post to remove the img tag that was linking to the 'fail' icon on your server, it was causing the page to hang waiting for it to load.

D'oh on me. Apologies and thank-you for the repair.

Hrynkiw at Kwantlen

23 April 2009, 17:14

For some reason you're missing an index on event_type (name).

'name' is the only column in that table, and it should be the primary key. Perhaps try adding it as the primary key then trying the upgrade?