distal-attribute
distal-attribute
distal-attribute
distal-attribute

Additional Limit Encountered

jamsession posted 7 years ago in General
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 :?
ansgar posted 7 years ago

jamsession wrote: 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?

jamsession wrote: 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?

jamsession wrote: 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.
jamsession posted 7 years ago
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
jamsession posted 7 years ago
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
ansgar posted 7 years ago
I can't see any error in the INSERT statement?

jamsession posted 7 years ago
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
ansgar posted 7 years ago
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?
jamsession posted 7 years ago
So, how can I get around this? :?
ansgar posted 7 years ago
Around what? The multiple INSERTs is a must, not an error.
jamsession posted 7 years ago
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 :?

ansgar posted 7 years ago
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.
jamsession posted 7 years ago
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 :?
ansgar posted 7 years ago

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



Upload it somewhere and send me a link?

jamsession wrote: 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.
jamsession posted 7 years ago
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 :?
ansgar posted 7 years ago
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

jamsession posted 7 years ago
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 :?
ansgar posted 7 years ago

jamsession wrote: 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?

jamsession wrote: 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.
jamsession posted 7 years ago
I finally started successfully importing my large table! happy

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
jamsession posted 7 years ago
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

Please login to leave a reply, or register at first.