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

Additional Limit Encountered

User, date Message
Written by jamsession
6 years ago
Category: General
120 posts since Sat, 22 Dec 07
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 :?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06

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.
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06
I can't see any error in the INSERT statement?
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06
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?
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
So, how can I get around this? :?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06
Around what? The multiple INSERTs is a must, not an error.
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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 :?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06
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.
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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 :?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06

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.
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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 :?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06
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

Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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 :?
Written by ansgar
6 years ago
4936 posts since Fri, 07 Apr 06

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.
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
6 years ago
120 posts since Sat, 22 Dec 07
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.