Copy table ….

[peterlaursen]

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 http://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

[peterlaursen]

I'm sorry .. but I seem to have been confused to one point.

Importing a DUMP also takes a lot of time. Maybe just as much. But SQLyog does not “black out” when importing a DUMP as it does with “copy table”. With the dump there is also a progress bar. I would like that for “copy DB” too and if possible some other indicator (blinking or whatever) that everything is in progres as it should.

[Ritesh]

This is due to the fact that SQLyog is a single threaded app. Behind the scenes it is importing the DUMP but you are not getting any GUI updation.

We plan to make SQLyog multithreaded taking one option at a time.

After Export/Import and Stop Query, we plan to make Copy Table multithreaded.

[peterlaursen]

thanks …

[Author]

Posts