With this Database
/*Database Information For - billeder*/
---------------------------------------
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
---------------- ------ ------- ---------- ------ -------------- ----------- --------------- ------------ --------- -------------- ------------------- ----------- ---------- ----------------- -------- -------------- ---------------------
currentusers InnoDB 9 Redundant 0 0 16384 0 0 0 (NULL) 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
databaseinfo InnoDB 9 Redundant 1 16384 16384 0 0 0 (NULL) 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
findcriteria InnoDB 10 Compact 0 0 16384 0 0 0 (NULL) 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
foundlists InnoDB 10 Compact 0 0 16384 0 0 0 (NULL) 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
foundthumbs InnoDB 10 Compact 0 0 16384 0 0 0 (NULL) 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
gallery InnoDB 10 Compact 0 0 16384 0 16384 0 1 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
gallerythumb InnoDB 10 Compact 0 0 16384 0 32768 0 (NULL) 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
keyword InnoDB 10 Compact 0 0 16384 0 16384 0 2 2004-10-18 16:30:41 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
logins InnoDB 10 Compact 0 0 16384 0 32768 0 (NULL) 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
path InnoDB 10 Compact 46 1424 65536 0 81920 0 3 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
tempuserfields InnoDB 10 Compact 0 0 16384 0 0 0 (NULL) 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
thumbnail InnoDB 10 Compact 8378 4077 34160640 0 770048 0 4 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
thumbnailkeyword InnoDB 10 Compact 0 0 16384 0 65536 0 (NULL) 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
userfields InnoDB 10 Compact 3369 68 229376 0 0 0 (NULL) 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
userfieldsinfo InnoDB 10 Compact 1 16384 16384 0 0 0 (NULL) 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
volume InnoDB 10 Compact 3 5461 16384 0 0 0 5 2004-10-18 16:30:42 (NULL) (NULL) latin1_swedish_ci (NULL) InnoDB free: 76800 kB
/*End of Database Information For - billeder*/ (sorry but I couldn't figure out how to produce a better layout)
the Sqlyog program seems to hang after copying tables "thumbnail" (and if I omit "thumbnail" then after copying "userfields" ). Note that they take of the most space. Thumbnail takes around 34 MB (BLOB of .jpg-data) and Userfields takes around 23 KB. The other fields are much smaller (almost empty). However the column is copied OK, before the program (seems to) hang. If I omit the two problematic colums everything is OK ...
I can open another Sqlyog session and watch the table being built and after the last record is added in "thumbnail" the first instance of sqlyog becomes irresponsive. However If I wait for about 10-15 minutes everything finishes, sqlyog (first instance) "wakes up" and informs me that all was copied OK - and it is. Since an SQL-dump is loaded in a few seconds why must this take so long time ?? I'm copying between two tables on local server.
I have dumped and reloaded the DB (using SQLyog), but that does not change anything.
I don't know if it is an SQLYOG issue or an issue with MySQLserver 5.0.4 or just my INNODB settings or a combination of them. Or maybe just another problem with Sqlyog not being multithreaded ?
The DB is used by the program ThumbsPlus www.cerious.com and MUST use INNODB for a least some of the tables since it uses FK's. And I can't change anything in the definitions, because then the ThumbsPlus program won't accept the database (that is a archive of photographs)
Definitions for the two colums
thumbnail
********
CREATE TABLE `thumbnail` (
`idThumb` int(11) NOT NULL auto_increment,
`idPath` int(11) default '0',
`idFiletype` int(11) default '0',
`file_time` int(11) default '0',
`thumbnail_time` int(11) default '0',
`filesize` int(11) default '0',
`checksum` int(11) default '0',
`width` int(11) default '0',
`height` int(11) default '0',
`horiz_res` int(11) default NULL,
`vert_res` int(11) default NULL,
`colortype` smallint(6) default NULL,
`colordepth` smallint(6) default NULL,
`gamma` smallint(6) default NULL,
`thumbnail_width` smallint(6) default NULL,
`thumbnail_height` smallint(6) default NULL,
`thumbnail_type` int(11) default NULL,
`thumbnail_size` int(11) default NULL,
`name` varchar(255) default NULL,
`thumbnail` longblob,
`annotation` text,
`metric1` blob,
`metric2` blob,
`metric3` blob,
PRIMARY KEY (`idThumb`),
UNIQUE KEY `PathName` (`idPath`,`name`),
KEY `{A8761EBB-6021-4395-8C72-303AED531D83}` (`idPath`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
userfields
********
CREATE TABLE `userfields` (
`idThumbUDF` int(11) default NULL,
`uf_Original_DateTime` varchar(23) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1