Thomas Johnson wrote:
> I have MS DTS package that pulls records from one MySQL server and puts them into an
> identical table on another MySQL server. The Query is like the following:
> Select * from scheme.table where modify between @a and @b;
> Modify is a TimeStamp field that has default and on Update set to CurrentTimeStamp.
> We always pull records from the last pull (@a) to the present (@b = Now()) which are set
> at the beginning of the DTS package and stored in a local table on the source DB. We pull
> 6 tables in this package. Initially we have done all 6 in parallel but we have rewritten
> them to be sequential. When we run them together, 90% of the time, one DATA PUMP gets
> zero rows even when there is data. The Data Pump does not report an error. When we go
> back into the design and execute the Data Pump manually it works. Is there a MySQL
> setting we should have set that may be causing this? We are using ODBC 3.51.12 with MySQL
> 5.0.24. It seems to fail for the same table consistently and then after few attempts will
> switch and start to fail for a different table, but always work when we execute just that
> ONE Data Pump manually through the designer.
If it *always* works from the DTS designer, but sometimes fails when
simply executed, then this sounds like a DTS problem to me. Try turning
on ODBC logging and triggering a failed execution. Then see if you can
get an ODBC log for a successful execution.
Personally I've found DTS to be pretty flaky - especially when dealing
with non-Microsoft products. Maybe you'd be better off doing the
transfer some other way? You could always use mysqldump to dump your
table from one server and then import it into another server. You could
do a really simple script that does the mysqldump, then hits a web page
on the destination server, which triggers the import.
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989