[daisy] Re: database upgrade error, 2.1 to 2.2-RC upgrade

[Jano Kula]

Hello Karel,

Karel Vervaeke wrote:
> Just dumping the trivial stuff here, maybe it will ring a bell and
> someone will be able to give more information.
> 
> This is line 82 from the db upgrade script:
> alter table document_versions add FOREIGN KEY (last_modifier) REFERENCES
> users (id);
> 
> This page gives more information about the errno 150 message:
> http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
> 
> It still does not explain what exactly went wrong.  If we can reproduce
> it, perhaps running the first 82 lines from the mysql commandline
> followed by "SHOW ENGINE INNODB STATUS" could help

Thank you for the debuging hint. Here is the ERROR part of the output 
after restoring the database and running the script to the line 82:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
080304 22:01:34 Error in foreign key constraint of table 
daisyrepositorycsds/#sql-427_2b:
FOREIGN KEY (last_modifier) REFERENCES users (id):
Syntax error close to:
s


"s" at the end belongs to the output. There is a semicolon in the script 
at the end of the line 82, not a colon as it could look like from the 
output. Running the script to the line 81 doesn't produce error. The 
whole output is at the end of this message.

Jano

> On Tue, 2008-03-04 at 15:28 +0100, Jano Kula wrote:
>> Hi,
>>
>> while upgrading daisyrepository database with:
>>
>> $>mysql -Ddaisyrepositorycsds -u<user> -p<password> < daisy-2_1-to-2_2.sql
>>
>> I've got the following error:
>>
>> ERROR 1005 (HY000) at line 82: Can't create table 
>> './daisyrepositorycsds/#sql-427_12.frm' (errno: 150)
>>
>> My daisyrepository database name is different from usual 
>> "daisyrepository", but there is no database name in the upgrade script. 
>> Access is OK; e.g. new "acl_accessdetail" table was already created. 
>> Could you help me to resolve the issue? Thank you.
>>
>> Jano
>>
>> mysql server version: 5.0.32-Debian_7etch5-log Debian etch distribution


=====================================
080304 22:01:41 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1320, signal count 1320
Mutex spin waits 0, rounds 6920, OS waits 10
RW-shared spins 2218, OS waits 1105; RW-excl spins 213, OS waits 205
------------------------
LATEST FOREIGN KEY ERROR
------------------------
080304 22:01:34 Error in foreign key constraint of table 
daisyrepositorycsds/#sql-427_2b:
FOREIGN KEY (last_modifier) REFERENCES users (id):
Syntax error close to:
s
------------
TRANSACTIONS
------------
Trx id counter 0 7666653
Purge done for trx's n:o < 0 7666510 undo n:o < 0 0
History list length 71
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 1063, OS thread id 3019074480
MySQL thread id 44, query id 2061392 localhost jano
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 0, not started, process no 1063, OS thread id 3018668976
MySQL thread id 32, query id 2061345 localhost daisy
--------
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
7577 OS file reads, 7503 OS file writes, 5151 OS fsyncs
5.20 reads/s, 19534 avg bytes/read, 15.07 writes/s, 6.53 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
2480 inserts, 2480 merged recs, 255 merges
Hash table size 34679, used cells 1511, node heap has 3 buffer(s)
5886.21 hash searches/s, 1905.21 non-hash searches/s
---
LOG
---
Log sequence number 0 247288062
Log flushed up to   0 247288062
Last checkpoint at  0 242080869
0 pending log writes, 0 pending chkp writes
2991 log i/o's done, 7.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 18014930; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       1
Database pages     508
Modified db pages  205
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 8626, created 3291, written 6500
6.20 reads/s, 14.60 creates/s, 17.47 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 1063, id 2991184816, state: sleeping
Number of rows inserted 93575, updated 18156, deleted 23, read 682085
1664.56 inserts/s, 604.63 updates/s, 0.00 deletes/s, 2278.38 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================