overflow during ODBC export

[Edward Reid]

OK, I've been watching my DB grow and have known for a long time I
would have to move the back end, but of course I waited until I
started getting odd behavior (at about 1.92GB). So today I installed
MySQL and (after a bit too much time getting the data source working)
started exporting tables. I successfully exported eight of my nine
tables, and can view them from a linked Access front end.

But when I tried to export the final table, which is probably about
90% of the space in the DB, it chugged away for an hour or so and
finally told me "not enough space on temporary disk". Well of course
this is nonsense (there's over 200GB free space), and the JETxxx.tmp
file is almost 2GB, so I gather the real problem is that JET isn't
sending directly to the ODBC interface but rather is constructing a
temporary file, and that even the temporary file has a 2GB limit.

And this just when life seemed like it might be easy.

What's the easiest way around this? I'm sure I could use various forms
of export to flat files, selecting a subset of the table, and import
these into MySQL. That just seems so clumsy compared with the ODBC
export, which although it requires several non-obvious clicks for each
table, once I learned what those clicks were, it seems to have just
worked, though I expect I'll have to learn a bit about MySQL to clean
stuff up.

I just noticed that I can export query via ODBC. Is that going to
create a table? If so, will that table get all the properties that an
export from a table gets? This could be the easiest (just set up a
query which selects part of the table, and run it for each section),
but my operational capability on MySQL is yet very limited and so I'm
concerned that I might find something very important missing after
I've put several hours of work into it.

This is in Access 2003 under XP, if it matters.

Thanks,

Edward

[Bruce]

On Aug 15, 10:43*pm, Edward Reid <[e-mail]:

OK, I've been watching my DB grow and have known for a long time I
would have to move the back end, but of course I waited until I
started getting odd behavior (at about 1.92GB). So today I installed
MySQL and (after a bit too much time getting the data source working)
started exporting tables. I successfully exported eight of my nine
tables, and can view them from a linked Access front end.

But when I tried to export the final table, which is probably about
90% of the space in the DB, it chugged away for an hour or so and
finally told me "not enough space on temporary disk". Well of course
this is nonsense (there's over 200GB free space), and the JETxxx.tmp
file is almost 2GB, so I gather the real problem is that JET isn't
sending directly to the ODBC interface but rather is constructing a
temporary file, and that even the temporary file has a 2GB limit.

And this just when life seemed like it might be easy.

What's the easiest way around this? I'm sure I could use various forms
of export to flat files, selecting a subset of the table, and import
these into MySQL. That just seems so clumsy compared with the ODBC
export, which although it requires several non-obvious clicks for each
table, once I learned what those clicks were, it seems to have just
worked, though I expect I'll have to learn a bit about MySQL to clean
stuff up.

I just noticed that I can export query via ODBC. Is that going to
create a table? If so, will that table get all the properties that an
export from a table gets? This could be the easiest (just set up a
query which selects part of the table, and run it for each section),
but my operational capability on MySQL is yet very limited and so I'm
concerned that I might find something very important missing after
I've put several hours of work into it.

This is in Access 2003 under XP, if it matters.

Thanks,

Edward

Have you considered trying this using VBA (via DAO or ADO)? Might be
a bit slower, but might get past whatever problem you're having.

Bruce

[Edward Reid]

Bruce wrote:

Have you considered trying this using VBA (via DAO or ADO)? Might be
a bit slower, but might get past whatever problem you're having.

Thanks. I think I could get the segmented query working faster,
assuming that method works. I'll keep VBA in mind.

I suppose probably I could export as CSV or tab-delimited and import
into MySQL from that.

Edward

[Edward Reid]

I found two workable methods to solve my problem.

First method: set up a query which selects a subset of the table to be
exported (I just used a range on the artificial primary key). Export
the first half of the table to the destination table. Change the query
and export the second half of the table to a temporary destination
table. Move the rows from the temporary table to the permanent one
(insert in permtable select * from temptable) and drop the temporary
table.

Second method: Use the MySQL Migration Tool! Would not have done it
any other way if I'd discovered this first. It just did the job and
moved all the tables without intervention. It did fail on creation of
one of the tables -- don't know why, I just copied the SQL and
executed it manually and it worked and I went on. The tool is free.

I did it by the first method, discovered the second method, discarded
my initial results and used the second. Therefore I don't know what
problems I might have encountered with the first method. I figured
that a tool specifically designed to migrate a DB would likely be more
thorough.

I think the first method was a little faster, but not much and it
certainly didn't make up for the extra intervention required. I don't
think it created all the indices. The resulting DB was the same size
as the JET DB to within 1%.

I'm pretty sure the second method created all the indices on the fly,
which could account for it taking longer. The resulting DB was almost
twice as large as the JET DB. I don't know why. It's possible that it
ended up in Unicode, but that should make little difference, as very
little of the data is character. I haven't tried to figure out why,
since it doesn't matter to me. I ended up, unintentionally, with
everything in InnoDB instead of MyISAM. Doesn't matter much, probably
preferable, but that means it's all in one file and so harder to tell
how much space the indices occupy.

Thanks,

Edward