#1009 Errors in copying a table from Server A to Server B using two instances of HeidiSQL

[Anonymous]

Originally created by: quo...@gmail.com

Refer to the Alternative solution, Comment 1 by ansgar.becker, Issue 1007,
I have tried it, but similar errors occured.

Here is the situation details:
Server A MySQL V.4.1.13 w/max_allowed_packet = 1047552 (remote server)
Server B MySQL V.5.1.30 w/max_allowed_packet = 1048576 (localhost)
HeidiSQL V4 RC3
Windows XP

The alternative solution for copying a table form server A to server B
provided by ansgar.becker:
"Open two instances of HeidiSQL, one on server A and one on server B. Then,
on server A, use the option "Another host ..." in the export dialog to
export directly from server to server. This time the max-allowed-packet
setting on server B should get detected and respected for the export."

Attached is my desktop dump screen when errors occurred.

I am not sure how HeidiSQL works so I cannot provide any suggestions on how
to fix it.

I can provide the sample table dump file on request.

[Anonymous]

Originally posted by: a...@anse.de

Cannot reproduce that, detection of remote setting for max-allowed-packet works fine
here. Having two servers just like you, one with a lower and one with a larger
allowed packetsize. Exporting from lower one to larger exports a big table in chunks
of nearly the same size than max-allowed-packet.

Would be helpful if your log pane was visible in the screenshots.

And: you attached two screens with different errors. Did you get both a) within the
same export process or b) in two seperate exports? If a) is the case, it's likely
that your connection to server B was idle some time and so disconnected.

Status: NeedInfo

[Anonymous]

Originally posted by: quo...@gmail.com

I got those errors in two separate exports, and I usually got max_allowed_packets
error.

I don't know whether it is important, but I just want to mention that my table
consists of multi-
byte characters.

Attached is the log screen of HeidiSQLs. If it is not what you want, please advice.

I have solved my table transfer problem by using phpMyAdmin to dump the table file
from server A and
then use HeidiSQL to load the dump file to server B.

The following is the log from Server A:
SHOW TABLES FROM `achan6`
SHOW VARIABLES LIKE 'character_set_connection'
SELECT GET_LOCK('HeidiSQL_{1AE3E7C9-2531-4EDF-AE94-323D0DE1AAB1}', 0)
SELECT RELEASE_LOCK('HeidiSQL_{1AE3E7C9-2531-4EDF-AE94-323D0DE1AAB1}')
SHOW CREATE TABLE `cuc_forum_posts`
SHOW FIELDS FROM `cuc_forum_posts`
SHOW TABLE STATUS LIKE 'cuc_forum_posts'
SELECT * FROM `achan6`.`cuc_forum_posts` LIMIT 0, 5000

The following is the log from Server B:
SHOW DATABASES
SELECT VERSION()
SHOW VARIABLES LIKE 'max_allowed_packet'
SELECT GET_LOCK('HeidiSQL_{1AE3E7C9-2531-4EDF-AE94-323D0DE1AAB1}', 0)
SELECT RELEASE_LOCK('HeidiSQL_{1AE3E7C9-2531-4EDF-AE94-323D0DE1AAB1}')
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/
DROP TABLE IF EXISTS `achan6`.`cuc_forum_posts`
CREATE TABLE `achan6`.`cuc_forum_posts` (  `no` bigint(20) unsigned NOT NULL default
'0', 
`permission` int(10) unsigned default NULL,  `hidelevel` tinyint(1) unsigned default
NULL, 
`temppass` varchar(50) default NULL,  `title` varchar(255) default NULL,  `username`
varchar(50)
default NULL,  `userid` bigint(20) unsigned default NULL,  `userip` varchar(50)
default NULL, 
`usertitle` varchar(50) default NULL,  `email` varchar(255) default NULL,  `body`
longtext, 
`prequel` bigint(20) unsigned default NULL,  `inttime` bigint(20) unsigned default
NULL,  `modtime`
bigint(20) unsigned default NULL,  `moduser` varchar(50) default NULL,  `moduserip`
varchar(50)
default NULL,  PRIMARY KEY (`no`),  UNIQUE KEY `no` (`no`) ) ENGINE=MyISAM DEFAULT
CHARSET=utf8
LOCK TABLES `achan6`.`cuc_forum_posts` WRITE
ALTER TABLE `achan6`.`cuc_forum_posts` DISABLE KEYS
INSERT INTO `achan6`.`cuc_forum_posts` (`no`, `permission`, `hidelevel`, `temppass`,
`title`,
`username`, `userid`, `userip`, `usertitle`, `email`, `body`, `prequel`, `inttime`,
`modtime`,
`moduser`, `moduserip`) VALUES ('10891',NULL,NULL,NULL,'我的一點偏見
\n','Raymond\n','1',NULL,NULL,NULL,'實在好討厭0係正經0既 chorus email 入面見到唔應該出現0既
宗教活動宣傳...即使
同音樂、同合唱有少少關係。<br /><br />第一︰最重要當然係我個人偏見，我承認我好憎呢個宗教。教義有咩問
題唔講喇，但我更加討
厭0既係教徒們「信徒的榜樣」，假公濟教，同埋將宗教觀誤以為係普世價值觀，以為個個都會啃得落。<br /><br
/>第二︰contact
list 有晒我地所有人 contact，有興趣宣傳大可以擅用有關資料，但都唔該打正旗號，等我可以避得開。我會假設
用得 "<a
href="mailto:a@gmail.com">a@gmail.com</a>" send 過0黎0既 email 都係同個團直接相關0既事。朱
總有咩私人0野要宣
傳，都係透過私人 email 去做，我覺得係一個好例子。可能唔信某宗教，做事會比較識得顧及其他唔信0既人。<br
/><br />第三︰兩
件涉嫌活動，雖然掛住音樂羊頭，但兩件都係以宗教目的為實。既然係宗教性質壓倒音樂性質，用團0既名義宣傳就有D
唔係咁適合。我相信
我地叫得做 CU Chorus，正正就係想搣甩 choir 呢個字0既宗教色彩，每年聖誕演出亦都特意加入非宗教歌曲 (例
如&nbsp;Have
Yourself A Merry Little Christmas 就係由我入團以來每年都唱0既一首非宗教歌)。希望我地全個團都可以
繼續薪火相傳，保存
我地「政教分離」、宗教中立0既優良傳統。<br /><br />結論︰發完 up 風，封殺我啦。反正某宗教都傾巢而出
支持網絡廿三條。
\n',NULL,'1233345238',NULL,NULL,NULL), ('10890',NULL,NULL,NULL,'Re: 新春快樂
\n','Will\n','1',NULL,NULL,NULL,'我註冊左, 所以都祝大家....嗯....註冊快
樂.\n','10889','1233196759',NULL,NULL,NULL), ('10889',NULL,NULL,NULL,'新春快樂\n','飛
\n','1',NULL,NULL,NULL,'各位豬朋 *COUGH*...團員你地好，首先祝大家豬...牛年快樂。<br /><br />
合唱團全新網頁
*COUGH*...討論區會於一月三十日晚上十時...呀...係香港時間三十一號早上五點啟用。由於上載需時<span
style="background-
color: #000000; color: #000000; font-size: xx-small;">（ITSC，含*BEEP*啦）</span>，所以
會有一個小時...
係...係一個小時<span style="color: #000000; font-size: xx-small;"><span
style="background-color:
#000000;">（得唔得呀...*BEEP*速，真係想死）</span></span>既outage時間。請見諒。<br /><br />
新網頁最影響大家既改
變莫過於請假 *COUGH*...討論區，大家需要登入先至可以請假 *COUGH*...使用討論區。<span
style="font-family: arial
black,avant garde;"><strong><span style="color: #ff0000; font-size: xx-large;">你註冊
左未呀？
</span></strong></span>未就好快Ｄ註冊啦。<br /><br />我知道有Ｄ人註冊左但遲遲都收唔到確認電郵登
入唔到。我估原因有
二，睇下你有無乃野 *COUGH*...err...有無相同情況。<br /><br />1.合唱團拋棄左 *COUGH*...呀...我
既意思係遺留左你既申
請。<br />2.管理果條 *COUGH*...個人劈炮...一時間唔得閒。<span style="background-color:
#000000; col/*
large SQL query, snipped at 2,000 characters */
/* SQL Error: Got a packet bigger than 'max_allowed_packet' bytes */