Extremly slow Join with 'OR'
The main problem - if add in Join on `OR`-condition, select become VERY slow. I realy have to use this condition. -- -- -- `tree_data` -- CREATE TABLE IF NOT EXISTS `tree_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=869 ; testiong on 800 rows Simple tree join query First QUERY is: SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id`) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id`) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id`) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id`) WHERE `l0`.`pid` =0 id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ref pid pid 4 test.l0.id 9 1 SIMPLE l2 ref pid pid 4 test.l1.id 9 1 SIMPLE l3 ref pid pid 4 test.l2.id 9 1 SIMPLE l4 ref pid pid 4 test.l3.id 9 time execution is 0.0069. result 207 rows looking at other query SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id` or `l1`.`pid`=100) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id` or `l2`.`pid`=200) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id` or `l3`.`pid`=300) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id` or `l4`.`pid`=400) WHERE `l0`.`pid` =0 And now execution time is 0.1455!!! profiling gives: Sending data 0.137295 And analize: id select_type table type possible_keys key key_len ref rows Extr 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ALL pid NULLNULLNULL800 1 SIMPLE l2 ALL pid NULLNULLNULL800 1 SIMPLE l3 ALL pid NULLNULLNULL800 1 SIMPLE l4 ALL pid NULLNULLNULL800 And what will happen if there will be 1 rows? (In real i using this `or` condition for selecting to the tree nodes for current element id and id can be id from other nodes.)
Re: Extremly slow Join with 'OR'
You may want to split of your or conditions into a separate query, and use UNION. On Tue, Aug 17, 2010 at 11:22 AM, Влад Р vul...@gmail.com wrote: The main problem - if add in Join on `OR`-condition, select become VERY slow. I realy have to use this condition. -- -- -- `tree_data` -- CREATE TABLE IF NOT EXISTS `tree_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=869 ; testiong on 800 rows Simple tree join query First QUERY is: SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id`) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id`) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id`) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id`) WHERE `l0`.`pid` =0 id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ref pid pid 4 test.l0.id 9 1 SIMPLE l2 ref pid pid 4 test.l1.id 9 1 SIMPLE l3 ref pid pid 4 test.l2.id 9 1 SIMPLE l4 ref pid pid 4 test.l3.id 9 time execution is 0.0069. result 207 rows looking at other query SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id` or `l1`.`pid`=100) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id` or `l2`.`pid`=200) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id` or `l3`.`pid`=300) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id` or `l4`.`pid`=400) WHERE `l0`.`pid` =0 And now execution time is 0.1455!!! profiling gives: Sending data 0.137295 And analize: id select_type table type possible_keys key key_len ref rowsExtr 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ALL pid NULLNULLNULL800 1 SIMPLE l2 ALL pid NULLNULLNULL800 1 SIMPLE l3 ALL pid NULLNULLNULL800 1 SIMPLE l4 ALL pid NULLNULLNULL800 And what will happen if there will be 1 rows? (In real i using this `or` condition for selecting to the tree nodes for current element id and id can be id from other nodes.) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Extremly slow Join with 'OR'
use UNION ALL ..instead of UNION for better performance... On Tue, Aug 17, 2010 at 5:01 PM, Johan De Meersman vegiv...@tuxera.bewrote: You may want to split of your or conditions into a separate query, and use UNION. On Tue, Aug 17, 2010 at 11:22 AM, Влад Р vul...@gmail.com wrote: The main problem - if add in Join on `OR`-condition, select become VERY slow. I realy have to use this condition. -- -- -- `tree_data` -- CREATE TABLE IF NOT EXISTS `tree_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=869 ; testiong on 800 rows Simple tree join query First QUERY is: SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id`) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id`) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id`) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id`) WHERE `l0`.`pid` =0 id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ref pid pid 4 test.l0.id 9 1 SIMPLE l2 ref pid pid 4 test.l1.id 9 1 SIMPLE l3 ref pid pid 4 test.l2.id 9 1 SIMPLE l4 ref pid pid 4 test.l3.id 9 time execution is 0.0069. result 207 rows looking at other query SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id` or `l1`.`pid`=100) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id` or `l2`.`pid`=200) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id` or `l3`.`pid`=300) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id` or `l4`.`pid`=400) WHERE `l0`.`pid` =0 And now execution time is 0.1455!!! profiling gives: Sending data 0.137295 And analize: id select_type table type possible_keys key key_len ref rowsExtr 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ALL pid NULLNULLNULL800 1 SIMPLE l2 ALL pid NULLNULLNULL800 1 SIMPLE l3 ALL pid NULLNULLNULL800 1 SIMPLE l4 ALL pid NULLNULLNULL800 And what will happen if there will be 1 rows? (In real i using this `or` condition for selecting to the tree nodes for current element id and id can be id from other nodes.) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Extremly slow Join with 'OR'
Only if you want to see duplicate rows :-) On Tue, Aug 17, 2010 at 2:21 PM, Ananda Kumar anan...@gmail.com wrote: use UNION ALL ..instead of UNION for better performance... On Tue, Aug 17, 2010 at 5:01 PM, Johan De Meersman vegiv...@tuxera.be wrote: You may want to split of your or conditions into a separate query, and use UNION. On Tue, Aug 17, 2010 at 11:22 AM, Влад Р vul...@gmail.com wrote: The main problem - if add in Join on `OR`-condition, select become VERY slow. I realy have to use this condition. -- -- -- `tree_data` -- CREATE TABLE IF NOT EXISTS `tree_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=869 ; testiong on 800 rows Simple tree join query First QUERY is: SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id`) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id`) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id`) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id`) WHERE `l0`.`pid` =0 id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ref pid pid 4 test.l0.id 9 1 SIMPLE l2 ref pid pid 4 test.l1.id 9 1 SIMPLE l3 ref pid pid 4 test.l2.id 9 1 SIMPLE l4 ref pid pid 4 test.l3.id 9 time execution is 0.0069. result 207 rows looking at other query SELECT `l0`.`id` AS 'l0id', `l0`.`pid` AS `l0pid` , `l1`.`id` AS 'l1id', `l1`.`pid` AS `l1pid` , `l2`.`id` AS 'l2id', `l2`.`pid` AS `l2pid` , `l3`.`id` AS 'l3id', `l3`.`pid` AS `l3pid` , `l4`.`id` AS 'l4id', `l4`.`pid` AS `l4pid` FROM `tree_data` AS `l0` LEFT JOIN `tree_data` as `l1` on (`l1`.`pid`=`l0`.`id` or `l1`.`pid`=100) LEFT JOIN `tree_data` as `l2` on (`l2`.`pid`=`l1`.`id` or `l2`.`pid`=200) LEFT JOIN `tree_data` as `l3` on (`l3`.`pid`=`l2`.`id` or `l3`.`pid`=300) LEFT JOIN `tree_data` as `l4` on (`l4`.`pid`=`l3`.`id` or `l4`.`pid`=400) WHERE `l0`.`pid` =0 And now execution time is 0.1455!!! profiling gives: Sending data 0.137295 And analize: id select_type table type possible_keys key key_len ref rowsExtr 1 SIMPLE l0 ref pid pid 4 const 4 1 SIMPLE l1 ALL pid NULLNULLNULL800 1 SIMPLE l2 ALL pid NULLNULLNULL800 1 SIMPLE l3 ALL pid NULLNULLNULL800 1 SIMPLE l4 ALL pid NULLNULLNULL800 And what will happen if there will be 1 rows? (In real i using this `or` condition for selecting to the tree nodes for current element id and id can be id from other nodes.) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Problem with mytop
If sombody can help me I'll be gratefull I have a problem with mytop1.6 on Mandriva 2010.1, when I install it on a Slave server mytop works, but when I install it on a Master Server don't works, don't display the queries. I use mysql version 5.0.77 and linux mandriva 2010.1 Very Thanks Carlos Caldi - DBA
MySQL 5.1 config for approx 100 concurrent users
I'm looking to implement a new website, which will have on average around 100 approx users at any one time. Is there anything I need to be aware of when setting up MySQL in terms of the server configuration ? Regards Neil
Re: Problem with mytop
Carlos, Have you tried innotop instead? It's a better replacement for mytop. (I wrote it.) - Baron On Tue, Aug 17, 2010 at 8:50 AM, Carlos Eduardo Caldi ce_ca...@hotmail.com wrote: If sombody can help me I'll be gratefull I have a problem with mytop1.6 on Mandriva 2010.1, when I install it on a Slave server mytop works, but when I install it on a Master Server don't works, don't display the queries. I use mysql version 5.0.77 and linux mandriva 2010.1 Very Thanks Carlos Caldi - DBA -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
max_seeks_for_key
Hi, We have myisam tables which has round 10 lakhs of records in each tables.I want to search the records based on index. What should be the value of max_seeks_for_key as it is set at default 4294967295. Thanks in advance. Jeetendra Ranjan MySQL DBA
Can InnoDB do without doublewrite
Hi all, I don't fully understand the importance of doublewrite in InnoDB. (1) Performance wise. Performance wise I can understand that, doublewrite can coalesce many dirty pages into a big buf chunk, and upon flush, first write this big buf chunk to tablespace + fsync(), then write those individual dirty pages followed by a single fsync(). So that we can reduce number of fsync() calls. This makes perfect sense for a hard disk. But if I use a high-end SSD with O_DIRECT ( so that, random write performance is similar to sequential write ), and given the simulated aio used by InnoDB, we cannot get much benefits of the extra coalesced big write. (2) For data integrity. I understand that, doublewrite can guarantee we always have an full valid page (even though it might be dated ) in case of crashes, power failures, etc. But even if we have a corrupted page (without doublewrite), we should still be able to recover thanks to the log. Like every DB, InnoDB logs every update to a log file before the changes are applied to data pages, and the logs are flushed to stable disk at commit before the changed data pages are stored to stable disk. So upon a failure we can always replay the log to restore partial pages. I haven't grasped full knowledge of doublewrite and InnoDB yet. Is there anything wrong with my above comments? Why do we need the extra doublewrite? -Neutron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: max_seeks_for_key
It's not really necessary for you to adjust that variable. -Original Message- From: jitendra ranjan [mailto:jitendra_ran...@yahoo.com] Sent: Tuesday, August 17, 2010 7:52 AM To: mysql@lists.mysql.com Subject: max_seeks_for_key Hi, We have myisam tables which has round 10 lakhs of records in each tables.I want to search the records based on index. What should be the value of max_seeks_for_key as it is set at default 4294967295. Thanks in advance. Jeetendra Ranjan MySQL DBA This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On Wed, 2010-08-11 at 14:23 -0400, Shawn Green (MySQL) wrote: On 8/9/2010 5:27 PM, Yves Goergen wrote: What's that supposed to mean? If there's no way to force the connection into SSL, it is entirely useless. Anyone on the wire could simply pretend that the server doesn't support SSL and so deny the encryption and the client wouldn't even care... If you don't want to require SSL on the local connections then don't set the flag on the @localhost account. If you want the SSL required on the other connections, then set it on the @'...' version of the account that the remote users login through. Excuse me, but isn’t Yves exactly right here? None of the client-side options (I tried --ssl, --ssl-ca=…, --ssl-verify-server-cert, --ssl-key=…, --ssl-cipher=…) can currently be used to force an SSL connection to be used. And requiring SSL from the server side does nothing to stop man-in-the-middle attacks. (Suppose Bob the SQL server grants some privileges to Alice the user with SSL required. Now Alice can log in with her password over SSL and gets denied over non-SSL. Great. But now Mallory comes along and intercepts a connection from Alice intended for Bob. Even if Bob would have claimed that he requires SSL, nothing stops Mallory from claiming that she doesn’t require SSL. Because Alice cannot force the use of SSL from the client side, Alice will make a successful unencrypted connection to Mallory. Then Mallory can accept the connection, ignoring Alice’s authentication, and steal Alice’s data; or Mallory can make a separate SSL connection to Bob, forward Alice’s authentication over it, then take over and issue evil commands to Bob.) This same issue was reported back in 2004 and ignored: http://bugs.mysql.com/bug.php?id=3138 I think this is a serious security problem that demands more attention than dismissal as documented behavior. To solve it, there needs to be a way to force the use of SSL from the client side. Anders -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On 18.08.2010 00:02 CE(S)T, Anders Kaseorg wrote: This same issue was reported back in 2004 and ignored: http://bugs.mysql.com/bug.php?id=3138 Oh dear, 2004... I think this is a serious security problem that demands more attention than dismissal as documented behavior. To solve it, there needs to be a way to force the use of SSL from the client side. I have another suggestion: remove SSL support from MySQL alltogether and declare the protocol as unsafe and only use it over secure networks like VPN. Since MySQL is now Oracle and it's not Oracle's main business, regarding recent bad news about Oracle, we can imagine what will happen this time. Exactly! Nothing. (Oh look, the MySQL guy already has an oracle.com e-mail address...) -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
On Wed, 18 Aug 2010, Yves Goergen wrote: Since MySQL is now Oracle […] Yves, I think we should be treating this security issue seriously, and working with Shawn and the MySQL team towards solving it constructively, instead of just taking potshots at their new company. Anders -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Updatable View limited by current user
Hello, I would like to have an updatable view that only shows the records in a table that have been created by the current user. By restricting regular users to this view (rather than the underlying table) they could only look at and modify their own data; more privileged users could look at anyone's data. Something like this seems to work: CREATE SQL SECURITY INVOKER VIEW `sessionviewbyuser` AS select `session`.`sessionid` AS `sessionid`, `session`.`Folder` AS `Folder`, `session`.`User` AS `User`, `session`.`Path` AS `Path`, `session`.`IP` AS `IP` from `session` where (`session`.`User` = SUBSTRING_INDEX(USER(),'@',1); Is this a reasonable approach? Are there any potential gotchas? Are their better ways to achieve this? With many thanks for any suggestions/pointers, Greg. -- Gregory Jefferis, PhD Division of Neurobiology MRC Laboratory of Molecular Biology, Hills Road, Cambridge, CB2 0QH, UK. http://www2.mrc-lmb.cam.ac.uk/group-leaders/h-to-m/g-jefferis http://www.neuroscience.cam.ac.uk/directory/profile.php?gsxej2 http://flybrain.stanford.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
OpenOffice, Go-OO, ODBC, Offline Data Entry
Hello, I am new to the mailing list here, so I'd like to introduce myself quickly. My name is Casey Quibell, and I am also rather new to the concept of programming a database (Or in this case, more specifically using an ODBC connector to tie OpenOffice into it.) Just a bit of technical preamble. While I am currently implementing this solution using Go-OO and MySQL, I am not tied to them. I like PostgreSQL as well, and there is little wrong with OpenOffice. Hell, if there are recommendations to be made even beyond these software selections I am listening. I have a project I have been commissioned to complete, and while the majority of it seems very simple and straight-forward to me, there remains an issue that has me concerned. Simply put, this agency which needs a database, has a large number of paper forms they have to fill out on a regular basis. Many of these forms ask the same questions over and over again. Using an ODBC connector and OpenOffice, I am able to create .pdf files that resemble very closely the forms they are already used to. By entering information into these forms, they are able to have the information parse directly into a SQL database which they can then create reports from later (A very important ability for them). In addition they are able to bring up an empty form to fill out, and if the relevant individual being processed is already in the system, it will fill out most of the 2nd form's fields for them; leaving only the remaining fields which are new and specific to the 2nd form. All of this is a massive time saver for them. Here is the catch. The SQL database itself exists at a 'home office', and these workers are wanting to be able to enter this data remotely; in an 'offline mode' as it were. Because they are required to fill out these forms away from the office, I have a logistical problem. If they could have WIFI or Internet access while away from the office, I would simply configure a VPN solution for them. However this is not something I can do; where they must go frequently has no Internet access at all, and a cell phone tether with a dataplan would just be inordinately expensive for this particular group. What options do I have available to me? I realize that while these workers are on-site, there is likely very little that can be done as far as the forms 'pulling' data from the office SQL database to auto-fill fields, but what about the other way around? I.e. Them going off-site, filling out the necessary forms, and then once they can get back to the office, upload the data to the database then? I don't know if maybe having a local SQL database running on each worker laptop which could somehow 'sync' the data would be a viable option or not... That is something I have never tried before. One option as far as that route goes that I have found is an application called Pervasync: http://www.pervasync.com/ which claims to be able to sync database material in this manner. However, it is commercial, and I am quite devoted to using only Open Source software. So far, it almost seems like I might have to tell the clients that it is simply not even possible what they are asking for. That they may have to enter the data twice: Once on-site, and a 2nd time when they get back to the office. Essentially copying the offline data, into the 'live' forms essentially. Thank you for your time and consideration. I appologize if my post was a little long, but I really wanted to try and be as clear as I could be as to my over-all intent, as the possible solutions are so very vague to me. Regards, Casey Quibell, lordd...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
