DTS Sometimes gets 0 rows
in [MyODBC]
|
From: "Thomas Johnson" on 16 Nov 2006 15:58 ------_=_NextPart_001_01C709BC.0F3887A5 Content-Type: text/plain; charset="windows-1250" Content-Transfer-Encoding: quoted-printable 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: =20 Select * from scheme.table where modify between @a and @b; =20 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 =3D 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. =20 -------------------------------------------------------------------------= ----------------------------------------- THOMAS A. JOHNSON | DIRECTOR OF IT APPLICATIONS | PARCELite SOLUTIONS a = division of TMSi -------------------------------------------------------------------------= ----------------------------------------- 1315 West 22nd Street Suite 225 Oak Brook, IL 60523 =20 Phone: 630.214-1057 x206 Mobile: 630.441-8300 Web: HYPERLINK "http://www.PARCELite.com"www.PARCELite.com Email: HYPERLINK "mailto:tjohnson(a)PARCELite.com"tjohnson(a)PARCELite.com =20 The information in this email and in any attachments is confidential and = may be privileged. If you are not the intended recipient, please destroy = this message, delete any copies held on your systems and notify the = sender immediately. You should not retain copy or use this email for any = purpose, nor disclose all or any part of its content to any other = person. =20 --=20 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.430 / Virus Database: 268.14.6/535 - Release Date: = 11/15/2006 3:47 PM =20 ------_=_NextPart_001_01C709BC.0F3887A5--
From: Daniel Kasak on 16 Nov 2006 16:09 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: > > =20 > > Select * from scheme.table where modify between @a and @b; > > =20 > > Modify is a TimeStamp field that has default and on Update set to Curre= ntTimeStamp. We always pull records from the last pull (@a) to the prese= nt (@b =3D Now()) which are set at the beginning of the DTS package and s= tored in a local table on the source DB. We pull 6 tables in this packag= e. Initially we have done all 6 in parallel but we have rewritten them t= o be sequential. When we run them together, 90% of the time, one DATA PU= MP 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 man= ually 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 swi= tch and start to fail for a different table, but always work when we exec= ute just that ONE Data Pump manually through the designer. > =20 If it *always* works from the DTS designer, but sometimes fails when=20 simply executed, then this sounds like a DTS problem to me. Try turning=20 on ODBC logging and triggering a failed execution. Then see if you can=20 get an ODBC log for a successful execution. Personally I've found DTS to be pretty flaky - especially when dealing=20 with non-Microsoft products. Maybe you'd be better off doing the=20 transfer some other way? You could always use mysqldump to dump your=20 table from one server and then import it into another server. You could=20 do a really simple script that does the mysqldump, then hits a web page=20 on the destination server, which triggers the import. --=20 Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: dkasak(a)nusconsulting.com.au website: http://www.nusconsulting.com.au -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dmyodbc(a)freebsd.csie.nctu.edu.tw
From: "Al McNicoll" on 20 Nov 2006 05:04 I have no experience with DTS, but it sounds like it could be a locking issue - it might sound simple, but are you locking the tables before trying to read out all the rows? The lock options you use probably depend on whether you're using InnoDB or MyISAM too. If you look in the Create Backup section of the MySQL Adminstrator GUI tool, under the "Advanced Options" tab, you'll see the recommended options (including their SQL statements) for consistent backups of the various table types... Al McNicoll Integritec Limited -----Original Message----- From: Thomas Johnson [mailto:tjohnson(a)PARCELITE.COM] Sent: 16 November 2006 20:15 To: myodbc(a)lists.mysql.com Cc: Pedram Soheil Subject: DTS Sometimes gets 0 rows 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. -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc?unsub=myodbc(a)freebsd.csie.nctu.edu.tw
From: Mike Harknett on 20 Nov 2006 13:39 Another possibility would be to add both MySQL databases as linked servers and just use a stored procedure to select from one and insert into the other using openquery . Then schedule the procedure as a SQL Server Agent job. This has worked for me in the past. cheers mike Daniel Kasak wrote: > 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. > -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc?unsub=myodbc(a)freebsd.csie.nctu.edu.tw
|
Pages: 1 Prev: MySQL Connector ODBC v5 bug Next: Cannot connect with MyODBC Version 5.0.10 |