Additional Limit Encountered

While exporting my database, I encountered an additional HeidiSQL limit which kept preventing one of my tables, from properly-importing.

The table is a '..._posts_text' table, which HeidiSQL says contains the following columns: "post_id"; "bbcode_uid"; "post_subject"; and, "post_text".

HeidiSQL reports that this table contains only 11,690 records, but, below that, shows 12,065.

Moreover, when examined via a text editor, it is clear that HeidiSQL truncated many of the "post_text" column contents when they exceeded a certain size.

That lead to HeidiSQL inserting additional "INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES" lines - which really messed up this columns results, during importation. Out of 12,065 posts, for instance, only 22 actually made it into the final column/database.

Is there a way to remove that limit or work around this issue?

- jamsession :?

While exporting my database, I encountered an additional HeidiSQL limit which kept preventing one of my tables, from properly-importing.

Are we talking about an SQL export?

HeidiSQL reports that this table contains only 11,690 records, but, below that, shows 12,065.

Sounds like the table has the INNODB engine which reports the recordcount as a rough estimation. But I'm not sure where you see these infos exactly - in the data tab?

That lead to HeidiSQL inserting additional "INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES" lines - which really messed up this columns results, during importation. Out of 12,065 posts, for instance, only 22 actually made it into the final column/database.

Is there a way to remove that limit or work around this issue?

I would be happy to help out, but I need
- some relevant lines of the SQL dump
- the CREATE TABLE statement
to know what you're talking about.

1. I am in the process of transfering my website, from one host to another.

The limitation was encountered, during importation, to the new host.


2. The 11,690 was reported on HeidiSQL's data tab, for table "..._posts_text" ("..." denoted certain information).

The 12,065 posts were listed just below that 11,690 figure.

These numbers are from the "export" side, of HeidiSQL.


3. Following are the first several lines, from the resultant sql file:

"# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: mysql.....com
# Database: ...
# Server version: 4.1.20-standard
# Server OS: pc-linux-gnu
# Target-Compatibility: MySQL 5.0
# max_allowed_packet: 8387584
# HeidiSQL version: x.y
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/;


#
# Dumping data for table '..._posts_text'
#

LOCK TABLES `..._posts_text` WRITE;
/*!40000 ALTER TABLE `..._posts_text` DISABLE KEYS*/;
INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES
(1,'..."

The above file is what I import.

- jamsession

To help clear up possible confusion, the limitation was [u:f17ddbd465]noticed[/u:f17ddbd465] during importation.

HeidiSQL, however, had already placed the multiple "INSERT INTO `phpbb2_posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES" lines in the file I was trying to import. So, I guess that plus the truncations must have happened during exportation.

Sorry for any misunderstandings.

- jamsession

I can't see any error in the INSERT statement?

I stand corrected; I was veiwing the file, in Wordpad, and this program truncated some lines, displayed on screen.

For no apparent reason, however, HeidiSQL, still frequently inserts the "INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES" line where it should not be.

Following are a few examples, from the above sql backup file.

# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: mysql.....com
# Database: ...
# Server version: 4.1.20-standard
# Server OS: pc-linux-gnu
# Target-Compatibility: MySQL 5.0
# max_allowed_packet: 8387584
# HeidiSQL version: x.y
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/;


#
# Dumping data for table '..._posts_text'
#

LOCK TABLES `..._posts_text` WRITE;
/*!40000 ALTER TABLE `..._posts_text` DISABLE KEYS*/;
INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES
(1,'...

(1008,'...','Your Cars Electrical System','Your Cars Electrical System \r\nby: Kevin Schappell \r\n\r\n...This article was posted by permission.');
INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES
(1009,'...

(2010,'...','"...Understanding Your Credit Cards"','Rebuild & Keep Good Credit Ratings by Understanding Your Credit Cards \r\nby: David Hall...This article was posted by permission.');
INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES
(2011,'...

(3016,'3921afe992','Scam Alert II: Domain Hijacking','Scam Alert II: Domain Hijacking\r\n by: Douglas Miller...This article was posted by permission.');
INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES
(3019,'...

(4029,'...','Spyware Protection - It\'s Not An Option Any More',/*!40100 _latin1...);
INSERT INTO `..._posts_text` (`post_id`, `bbcode_uid`, `post_subject`, `post_text`) VALUES
(4030,'..."

Is there a way to stop HeidiSQL from repeatedly inserting that line?

They appear to be playing havoc with my results.

- jamsession

Ah, I think I got what you mean. hehe. But that's quite normal: The extended INSERT syntax normally concats multiple VALUES lines after another, seperated by a comma. But for large tables you have to take care of the maximum possible load you can send in one INSERT (variable "max_allowed_packet"). If HeidiSQL wouldn't take care of that, you would get a "Server has gone away" while importing the resulting file.

Hope you understand what I mean?

So, how can I get around this? :?

Around what? The multiple INSERTs is a must, not an error.

How can I completely transfer my data. In sections?

As I said, above, there are over 12,000 records which have to be moved.

Please reply.

- jamsession :?

I have no clue why they are not imported. You told us about these multiple INSERTs which we just discovered as quite normal. So this is not the error. So what's the error?

I slightly guess it's difficult to explain here. So if possible send me the zipped SQL file in a mail, so I can test it and look for errors.

The zipped file is 21.6 mb - too large, for email.

Since this is [u:ac5f8e7223]not[/u:ac5f8e7223] an error, however, then is there a way to instruct HeidiSQL to target and export only specific sections of a table and then to append such data to another database's table?

If that is the only way for me to transfer the complete 12,000+ records, then, fine, I'll do it.

Please reply.

- jamsession :?

The zipped file is 21.6 mb - too large, for email.

Upload it somewhere and send me a link?

Since this is [u:ed6f53e098]not[/u:ed6f53e098] an error, however, then is there a way to instruct HeidiSQL to target and export only specific sections of a table and then to append such data to another database's table?

No, this is not an option. HS should export all or no data (depending on the "Data" option in the Export dialog). There is no section wise export option. If HS does something weird here, I guess we have a bug somewhere. So I would propose I analyze that SQL file.

There might be copyright and licensing problems with sending out a copy of the file, as you suggest.

Further, due to the move, the board's contents have not been checked, in a while. So, there might be something objectionable there that would, otherwise, first be removed. Yet, presently, there is no time for checking that content.

Also, there might be a problem with the 'user/registration' agreement.

How about if I just copy and paste sections of the table's contents into small sql files and import them? Will HeidiSQL append such data to the new database?

- jamsession :?

I keep being unclear about the problem in that file. So, simple and safe workaround for now: Download the MySQL server package (5.0 is stable) from mysql.com and use mysqldump on the commandline to make your backup:

mysqldump.exe -hYOURSERVERIP -uYOURUSERNAME -p --opt --compress DATABASENAME >backup.sql

Well, an incomplete database table is not much good.

That repeated insertion of the "INSERT" command causes the server to overwrite records and this results in a record shortage.

So, I need a way to work around HeidiSQL's repeated insertion of that "INSERT" code - not another backup.

I already suggested a related improvement/new feature.

Any thoughts on this workaround.

- jamsession :?

That repeated insertion of the "INSERT" command causes the server to overwrite records and this results in a record shortage.

Is that a guess or can you verify that? That should definitely not be the case. An "INSERT INTO" can not overwrite some other record, a "REPLACE INTO" can! If you have 2 identical INSERTs for a table which has a primary or unique key, the second INSERT would definitely give you some error message! So, where is this error message?

I already suggested a related improvement/new feature.

Where? Sorry, please point me to this suggestion...

It's a kind of vague situation here. Nobody than you can see your SQL file completely and therefore nobody can help you much. I need error messages and more concrete code if you expect more help. Alone with the description you gave me, I cannot detect any error yet. The multiple INSERTs look very normal as said before.

I finally started successfully importing my large table!

First, I had to remove the complete "LOCK TABLES" & "UNLOCK TABLES;" lines.

Plus, I could only import about 150 records at a time, but, at least, this is working via the "INSERT INTO" command.

So, thanks for all your help!

Seriously!

- jamsession :D

As an additional followup, I discovered that importations were not capped, at 150 records. Instead, it was the overall sql file size that counted; I could import any number of records just so long as the file size did not exceed 1.5 mb.

Otherwise, the receiving server would disconnect.

Again, though, thanks for the help!

- jamsession :D