Too Little Exported?

My message board reports that there are 54,600 articles.

Yet, HeidieSQL v. 4 rev. 2405 reports that only 33,345 posts were exported.

Shouldn't there actually be 54,600 posts exported, instead?

- j

Cannot reproduce that here. Could you please update to 5.0 beta or the latest build, which has a rewritten SQL exporter?

I installed v. 5.0.0.3031.

I downloaded rev. heidisql.r3034.exe.

I tried installing heidisql.r3034.exe, but it starts like an independante version of Heidisql.

When I click "open", an error message reads: Error: Access violation at address 00000000. Read of address 00000000.

According to the bug report:

date/time : 2010-01-31, 12:03:11, 187ms
computer name : FOCUS-74ZZNB4WB
user name : Delco Club <admin>
registered owner : Focus Group
operating system : Windows XP Service Pack 3 build 2600
system language : English
system up time : 4 hours 50 minutes
program up time : 1 minute 20 seconds
processors : 2x Intel(R) Pentium(R) 4 CPU 3.00GHz
physical memory : 165/1023 MB (free/total)
free disk space : (C:) 4.21 GB
display mode : 1024x768, 32 bit
process id : $284
allocated memory : 7.40 MB
executable : heidisql.r3034.exe
exec. date/time : 2010-01-31 11:55
version : 4.0.0.3034
compiled with : Delphi 2010
madExcept version : 3.0k
callstack crc : $00000000, $b5c3de94, $b5c3de94
count : 2
exception number : 1
exception class : EAccessViolation
exception message : Access violation at address 00000000. Read of address 00000000.

main thread ($1270):
00000000 +000 ???
00643ef7 +047 heidisql.r3034.exe mysql_connection 257 +5 TMySQLConnection.SetActive
0064468c +044 heidisql.r3034.exe mysql_connection 358 +2 TMySQLConnection.Query
00647716 +026 heidisql.r3034.exe mysql_connection 1127 +1 TMySQLQuery.Execute
00645123 +057 heidisql.r3034.exe mysql_connection 541 +5 TMySQLConnection.GetResults
006456ab +02f heidisql.r3034.exe mysql_connection 678 +1 TMySQLConnection.GetCol
0068463f +09b heidisql.r3034.exe Main 5989 +9 TMainForm.DBtreeInitChildren
005e3750 +018 heidisql.r3034.exe VirtualTrees TBaseVirtualTree.DoInitChildren
005e7588 +028 heidisql.r3034.exe VirtualTrees TBaseVirtualTree.InitChildren
005f39d6 +432 heidisql.r3034.exe VirtualTrees TBaseVirtualTree.ToggleNode
005e7678 +0c0 heidisql.r3034.exe VirtualTrees TBaseVirtualTree.InitNode
005ed919 +081 heidisql.r3034.exe VirtualTrees TBaseVirtualTree.GetFirst
00673d9b +17b heidisql.r3034.exe Main 1657 +22 TMainForm.DoAfterConnect
00672de1 +5e9 heidisql.r3034.exe Main 1522 +120 TMainForm.Startup
007148c9 +0cd heidisql.r3034.exe heidisql 63 +17 initialization

thread $1004 (TWorkerThread):
7c90df58 +0a ntdll.dll NtWaitForSingleObject
7c8025d5 +85 kernel32.dll WaitForSingleObjectEx
7c80253d +0d kernel32.dll WaitForSingleObject
005cc90d +19 heidisql.r3034.exe VirtualTrees TWorkerThread.Execute
0046085f +2b heidisql.r3034.exe madExcept HookedTThreadExecute
0049790e +42 heidisql.r3034.exe Classes ThreadProc
004067f8 +28 heidisql.r3034.exe System 1125 +0 ThreadWrapper
00460741 +0d heidisql.r3034.exe madExcept CallThreadProcSafe
004607ab +37 heidisql.r3034.exe madExcept ThreadExceptFrame
>> created by main thread ($1270) at:
005cc802 +16 heidisql.r3034.exe VirtualTrees TWorkerThread.Create



Incidentally, prior to trying rev. heidisql.r3034.exe, v. 5.0.0.3031 would not download my posts. Instead, the program would just say: "Skipped 300.5 mb" - or something like that.



What should I do?

- j

You saved heidisql.r3034.exe in a different directory. If you update to a build file, just use the "Check for update" menu item in the help menu. Don't save the .exe somewhere else and try to run it.

If you see "Skipped..." in the log list, please look at the limiter at the bottom left of the dialog, saying "Skip tables larger than: [] MB". Set it to "0" to process all tables.

I followed your advice and, so, now I'm running 5.0 r. 3034.

Heidisql isn't finished backing up my database, yet, but I noticed that my post table's backup is automatically listing 33,345.

So, I guess the program is backing up my full posts table, but I can't tell, yet, because all it shows is the number 33,345.

So, I suppose that I won't see any action until after 33,345 posts have been copied; then, I guess, the number will increase, as in #####/100%.

I'll give it an hour and than see if there is a change.

- j

After more than 1/2 an hour had passed, I turned off Heidisql, because I could not tell if it was working.

The 33,345 had turned into 11,172 / 34% and my cursor had become an hour glass.

Further, the 11,172 / 34% did not give me optimism that the program was going to export 54,600 posts instead of just 33,345.

So, I started Heidisql up, again, and tables seemed to export all right - that is, until the program ran into my post table. When that happened, my cursor turned back into an hour glass. When I tried moving it, however, it changed into a vertical arrow which pointed in both directions.

So, my screen was unresponsive.

So, I pressed alt + ctrl + delete and stopped Heidisql, again.

This is where things stand.

Any suggestions?

- j

I started heidisql, again, and, my cursor changed into the hourglass, as the row column filled in.

The column would be blank until it filled in with numbers, as in ##### / 100%.

Yet, the column for my post table would start, at 33,343, and would, apparently, remain that way until filling in with other numbers, in ##### / ###% format.

I suspect there is a memory problem, because, after starting Heidisql, much of my screen becomes unresponsive and moving my notepad around leaves a trail of previous notepads visible.

Yet, there should not be a memory problem; my xp has 2 gig ram and these problems clear up as soon as I switch Heidisql off.

Similarly, when I press my firefox button (at bottom of screen), the browser appears right away. Same for my windows live mail program. Yet, when I press my heidisql button, nothing happens. I end up waiting for a long time, before even part of Heidisql appears - and this part is unresponsive.

- j

HeidiSQL loads ~100MB of data rows per loop into your memory while exporting SQL. So, even if that's increased to 150M or so that should be very ok with 2GB RAM. Don't know what's happening there, I cannot reproduce this slow behaviour.

But you're sure you are using the new version's SQL exporter on the "Table tools" dialog?

1. Incidentally, I just shut down the other programs and tried running heidisql, alone, but this made no difference.

2. Here are the steps I took:

I pressed Start.
I clicked the Heidisql icon.
I clicked open.
I clicked the upper menu's export button.
I clicked export database as sql.
I checked all the table boxes, but unchecked search_wordlist and search_wordmatch.
I checked drop create, for databases.
I checked drop create, for tables.
I chose replace existing data.
I typed in a filename.
I replaced the 20 with 0, in the skip tables larger than...box (bottom left corner of screen).
I clicked the export button (bottom right of screen).

Immediately, my cursor changed into an hourglass.
As soon as heidisql got down to my post table, the row column said 33,343.

So, what did I get wrong?

- j

So, what's wrong with that 33,343? I'm unsure if you mean HeidiSQL is still running or gets to an end after this incomplete message?

First, the 33,343 seems inconsistent with Heidisql's previous behavior; it was not 33,343 / 100%. It was just 33,343.

Secondly, the previous row column cells stayed empty until they filled with quantities of a percentage, like ### / 34%, etc.

Third, heidisql did not proceed onto other rows. They were available, just not listed.

Forth, the program became unresponsive - and that can't be good.

Fifth, the unresponsiveness of heidisql degraded how the rest of my computer worked.

Sixth, as a result of the I still haven't got an up-to-date backup of my database.

Seventh, as I said in my first post, the 33,343 should have been more like 54,600, because there were, at least, 54,600 posts.

- j

How many MB has your posts table?

I just exported a 180MB table, which was processed in 2 loops (100M + 80M). No problem, apart from a cosmetic one - after the first loop it told me it already had 100% exported, which was not true. But then the second loop went on, and the export finished well.

I appreciate your effort.

Really!

So, I apologize if I sounded disrespectful.

As noted, however, my table exceeds 300 mb and this seems to be a problem, for heidisql.

I don't know why.

Perhaps this reflects a problem with the 'skip tables larger than...' box (bottom left corner of screen).

- j

You didn't sound disrepectful to me. Just I didn't really get what the problem was, which I now understand.

Only I cannot reproduce that, which is bad as this lowers the chance that I'll be able to fix that. Sounds like a problem specific to your table. Would be cool if I got a copy of your whole table, probably as ZIP updloaded somewhere. Is that possible ?

I don't think that's possible.

I guess that I'm just stuck with the way things are, for now.

V. 4 handled the issue fine, except that it, too, apparently did not download the entire table. Yet, for months (?), everything was working smoothly. So, I assumed, incorrectly, that everything was being exported.

I'm going to try v. 5, again, and be more patient, this time.

Later, I'll let you know how it went.

- j

THEORY:

The 'unlimited' value, at Heidisql's 'Skip tables larger than...' box (bottom left corner of screen) is arbitrarily-capped, at 33,345, and this is causing many of the problems I have been encountering.


OBSERVATIONS:

My database is 327.55 MB.

Prior to starting Heidisql, I typed 0 into the 'Skip tables larger than...' box, but, later, ran into all the usual difficulties.

So, before trying Heidisql, again, I typed 500 into the box.

Yet, when Heidisql got to my post table's line, the program just skipped over the associated database table ("Skipped - 300.5 MB").

So, before starting Heidisql, for a third time, I used the box's up arrow to increment the value to 500 mb.

This was when I discovered that the box's value was capped, at 100 mb.

So, before starting Heidisql, I used the down arrow to take the value down to 0 (for "unlimited").

Then, during export, I noticed that the row cell, for my post table, started again, with 33,345. But, as in previous attempts, this was confusing, because I knew that 33,345 posts had not, yet, been exported.

So, until more than 0 posts have been exported, then I think that the related cell should start with 0.

After about ten minutes (I have a busy website), however, the cell switched, from "33,345" to "11,172 / 34%" (no quotes).

40 minutes later, however, the cell changed, from "11,172 / 34%" to "22,344 / 67%" (no quotes & still a far cry from the 54,724 posts that were actually available).

About 20 minutes after that, the cell's amount incremented to "33,345 / 100%" (no quotes), but this was still far short of 54,724.

A while later, Heidisql finished exported the rest of the tables.

It seems clear that Heidisql is not functioning as intended.

- j

THEORY:

The 'unlimited' value, at Heidisql's 'Skip tables larger than...' box (bottom left corner of screen) is arbitrarily-capped, at 33,345, and this is causing many of the problems I have been encountering.


OBSERVATIONS:

My database is 327.55 MB.

Prior to starting Heidisql, I typed 0 into the 'Skip tables larger than...' box, but, later, ran into all the usual difficulties.

So, before trying Heidisql, again, I typed 500 into the box.

Yet, when Heidisql got to my post table's line, the program just skipped over the associated database table ("Skipped - 300.5 MB").

So, before starting Heidisql, for a third time, I used the box's up arrow to increment the value to 500 mb.

This was when I discovered that the box's value was capped, at 100 mb.

So, before starting Heidisql, I used the down arrow to take the value down to 0 (for "unlimited").

Then, during export, I noticed that the row cell, for my post table, started again, with 33,345. But, as in previous attempts, this was confusing, because I knew that 33,345 posts had not, yet, been exported.

So, until more than 0 posts have been exported, then I think that the related cell should start with 0.

After about ten minutes (I have a busy website), however, the cell switched, from "33,345" to "11,172 / 34%" (no quotes).

40 minutes later, however, the cell changed, from "11,172 / 34%" to "22,344 / 67%" (no quotes & still a far cry from the 54,724 posts that were actually available).

About 20 minutes after that, the cell's amount incremented to "33,345 / 100%" (no quotes), but this was still far short of 54,724.

A while later, Heidisql finished exporting the rest of the tables.

It seems clear that Heidisql is not functioning as intended.

- j

Thanks a lot for being so detailed. Only I'm afraid I cannot fix anything if I don't have that table+data here. I'm still unable to reproduce here. Sorry.

Maybe I was wrong, about HeidiSQL.

My message board was reporting that there were 54761 posts and a database size of 300.5 mb.

Yet, HeidiSQL was only exporting (downloading) 33,345 posts.

So, I assumed that approximately 21,000 posts were being missed.

So, today, I went into phpmyadmin and started taking steps
for a mysqldump.

Yet, then I noticed that in the Records # column, for my database's posts, only
33,345 records were listed - despite that 54761 posts and a database size of
300.5 mb were being reported.

So, now the new theory is that some of the posts appeared 'missing', because they were replies that were appended to the bottom of earlier posts and, so, this caused about 21,000 posts to be 'grandfathered' under their respective initial post.

At least, that's the new theory and it seems to explain a lot.

I'll let you know what I find, though.

Just thought I would mention this apparent change of events, however, in case you were going nuts while looking for a bug that might not be there.

- j

Hehe, thanks very much, that removes a big question mark from above my head.

In addition to the forgoing, looks like some of the numbers generated by the database are out-dated.

Here's what a knowledgeable person said about the "new theory" in my previous post: "Not sure as I'm not too familiar with the way phpBB stores it's posts/threads/replies, however if you're using for instance an ID column as your tally for post count then quite likely that is off as the ID is auto-incremented and will continue to grow even if older posts/replies are removed from the database."

So, everything might be okay - just slow to update.

This might explain why the maximum number of posts reported by Heidisql and phpmyadmin is still 33,345 - despite that 33,345 was was maximum number reported, on January 30, 2010 (see my first post, above).

So, eventually, I guess, phpmyadmin's underlying software will automatically increment and cause Heidisql to do similar.

It seems to make sense.

I'll let you know if I come by something different.

- j