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

Wed Mar 5 02:11:05 CST 2008

Just a wild idea: the word 'user' is a keyword in Mysql - perhaps the
name 'users' is parsed as 'user' and 's' concatenated, hence the error
message "Syntax error close to: s".

Try quoting the word users (I don't know which quotes, but I would try
`, ' and " in that order).

(I know it seems far-fetched - normally a parser would first make the
word 'users' a token and never be able to make this confusion - but it's
worth a try!)

HTH,
Karel

On Tue, 2008-03-04 at 22:44 +0100, Jano Kula wrote:
> 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
> ============================
> 
> _______________________________________________
> daisy community mailing list
> Professional Daisy support: http://outerthought.org/en/services/daisy/support.html
> mail to: daisy at lists.cocoondev.org
> list information: http://lists.cocoondev.org/mailman/listinfo/daisy