This just absolutely amazes me.

RJ Writes, "When I first began working at the company in question, I was trying to get a handle on the flow of database queries during a fund transfer, so I enabled query logging, and stepped through an example transaction. (Platform, PHP/MySQL on Linux). For any who read this: DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS.

Order of SQL Queries for "Dispatcher sending funds" transaction:

Example transaction: customer sends funds from US location to overseas location in Buenos Aires, Argentina, which will then disburse the money to a 3rd party.

1. user logs in: (POST from /php/dispatcher_log.php to /php/members.php, redirects to /forms/welcome.php upon success) a. select from 'uaccount' b. insert into 'session', 'log' c. select from 'profile' (didn't bother with details on these queries)

2. user clicks on 'Dispatcher' (/forms/dispatcher_menu.php) a. select email, balance,available,type from uaccount where uid=2192 b. select valid from bankinfo where uid=2192 c. select email, balance,available,type from uaccount where uid=2192 d. select date_format(curdate(),'%W, %M %D, %Y')

3. user clicks on 'Sending Money' (/php/dispatcher_tx-1.2.php) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` c. insert into Dispatchers_tmp (tid) values('0351634373'); d. select tid from Dispatchers_tmp where tid='0351634373' e. select * from Dispatchers_tmp where tid='0351634373' f. select * from Dispatchers_tmp where tid='0351634373' g. select disp_rx_id from Dispatchers_tmp where tid='0351634373' h. select email, balance,available,type from uaccount where uid=2192 i. select valid from bankinfo where uid=2192 j. select email, balance,available,type from uaccount where uid=2192 k. select date_format(curdate(),'%W, %M %D, %Y')

4. user enters remote location (ZIP or city!?! -- isn't there a better way?) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` c. select tid from Dispatchers_tmp where tid='0351634373' d. select profile.* from profile left join uaccount on profile.uid = uaccount.uid where (city like '%Buenos Aires%' or zipcode like '%Buenos Aires%') and uaccount.type='102' and uaccount.uid <> 2192 e. select email, balance,available,type from uaccount where uid=2192 f. select valid from bankinfo where uid=2192 g. select email, balance,available,type from uaccount where uid=2192 h. select date_format(curdate(),'%W, %M %D, %Y')

5. user clicks on a remote dispatcher (still at /php/dispatcher_tx-1.2.php) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` c. select tid from Dispatchers_tmp where tid='0351634373' (keeps state of last transaction # even after logout/login) d. update Dispatchers_tmp set disp_rx_id ='2191' where tid='0351634373' e. select profile.* from profile left join uaccount on profile.uid = uaccount.uid where (city like '%%' or zipcode like '%%') and uaccount.type='102' and uaccount.uid <> 2192 f. select email, balance,available,type from uaccount where uid=2192 g. select valid from bankinfo where uid=2192 h. select email, balance,available,type from uaccount where uid=2192 (again?) i. select date_format(curdate(),'%W, %M %D, %Y')

6. (Again!? must be a page refresh here, to dispatcher_tx-1.2.php?action=amount, because everything starts again) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp' c. select tid from Dispatchers_tmp where tid='0351634373' d. select * from Dispatchers_tmp where tid='0351634373' e. select * from Dispatchers_tmp where tid='0351634373' f. select disp_rx_id from Dispatchers_tmp where tid='0351634373' g. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191 h. select amount from Dispatchers_tmp where tid='0351634373' i. select sender_id from Dispatchers_tmp where tid='0351634373' j. select receptor_id from Dispatchers_tmp where tid='0351634373' k. select email, balance,available,type from uaccount where uid=2192 l. select valid from bankinfo where uid=2192 m. select email, balance,available,type from uaccount where uid=2192 n. select date_format(curdate(),'%W, %M %D, %Y')

7. user enters amount, submits (to /php/dispatcher_tx-1.2.php?action=search_sender) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp' c. select tid from Dispatchers_tmp where tid='0351634373' d. select * from Dispatchers_tmp where tid='0351634373' e. select * from Dispatchers_tmp where tid='0351634373' f. select disp_rx_id from Dispatchers_tmp where tid='0351634373' g. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191 h. update Dispatchers_tmp set amount = 34 where tid = '0351634373'; i. select amount from Dispatchers_tmp where tid='0351634373' j. select sender_id from Dispatchers_tmp where tid='0351634373' k. select receptor_id from Dispatchers_tmp where tid='0351634373' l. select email, balance,available,type from uaccount where uid=2192 m. select valid from bankinfo where uid=2192 n. select email, balance,available,type from uaccount where uid=2192 o. select date_format(curdate(),'%W, %M %D, %Y')

8. user enters new uaccount for receptor (submits to /php/dispatcher_tx-1.2.php) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` ... c. select tid from Dispatchers_tmp where tid='0103252648' d. LOCK TABLES uaccount WRITE insert into uaccount values (null,'13jQ8ghw9b','tester.testingperson@notreallythecompany.com','13jQ8ghw9b','N',0,0,100,0,'0.20','0.20','0.02','0.20','') UNLOCK TABLES e. LOCK TABLES profile WRITE insert into profile (uid,fname,lname,address1,address2,city,state,zipcode,countrycode,nphone,secretquestion,secretanswer) values (3278,'tester','testingperson','123 Santander','','Buenos Aires','Argentina','00000','CO','011-456450546','uaccount opened by','2192') UNLOCK TABLES f. LOCK TABLES Dispatchers_tmp WRITE update Dispatchers_tmp set receptor_id ='3278' where tid='0103252648' UNLOCK TABLES h. select * from Dispatchers_tmp where tid='0103252648' i. select disp_rx_id from Dispatchers_tmp where tid='0103252648' j. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191 k. select amount from Dispatchers_tmp where tid='0103252648' l. select sender_id from Dispatchers_tmp where tid='0103252648' m. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3277 n. select receptor_id from Dispatchers_tmp where tid='0103252648' o. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3278 p. select email, balance,available,type from uaccount where uid=2192 q. select valid from bankinfo where uid=2192 r. select email, balance,available,type from uaccount where uid=2192 s. select date_format(curdate(),'%W, %M %D, %Y')

9. (I guess page re-directs, because it starts all over again:) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` ( c. select tid from Dispatchers_tmp where tid='0103252648' d. select * from Dispatchers_tmp where tid='0103252648' e. select * from Dispatchers_tmp where tid='0103252648' f. select disp_rx_id from Dispatchers_tmp where tid='0103252648' g. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191 h. select amount from Dispatchers_tmp where tid='0103252648' i. select sender_id from Dispatchers_tmp where tid='0103252648' j. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3277 k. select receptor_id from Dispatchers_tmp where tid='0103252648' l. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3278 m. select email, balance,available,type from uaccount where uid=2192 n. select valid from bankinfo where uid=2192 o. select email, balance,available,type from uaccount where uid=2192 p. select date_format(curdate(),'%W, %M %D, %Y')

10. (and I guess page re-directs again, ending up at /php/dispatcher_tx-1.2.php?action=confirm) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (... c. select tid from Dispatchers_tmp where tid='0103252648' d. select * from Dispatchers_tmp where tid='0103252648' e. select disp_rx_id from Dispatchers_tmp where tid='0103252648' f. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191 g. select amount from Dispatchers_tmp where tid='0103252648' h. select sender_id from Dispatchers_tmp where tid='0103252648' i. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3277 j. select receptor_id from Dispatchers_tmp where tid='0103252648' k. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3278 l. select email, balance,available,type from uaccount where uid=2192 m. select valid from bankinfo where uid=2192 n. select email, balance,available,type from uaccount where uid=2192 o. select date_format(curdate(),'%W, %M %D, %Y')

11. Now, from here we have the final "sending" confirmation (POSTs either $submit or $delete to //php/dispatcher_tx-1.2.php) (redirects to printing page, and then back to beginning "Send" form.) a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (... c. select tid from Dispatchers_tmp where tid='0103252648' d. select * from Dispatchers_tmp where tid='0103252648' e. select uaccountno from uaccount where uid='2192' f. select uaccountno from uaccount where uid='2191' g. LOCK TABLES trans WRITE insert into trans values(null,'0103252648','6GjSid8KoJ','34f9YC78AZ','0102',42.45,'USD',20020128122249,null,'Y',null) UNLOCK TABLES h. LOCK TABLES uaccount WRITE update uaccount set balance=balance-42.45,available=available-42.45 where uid=2192 UNLOCK TABLES i. LOCK TABLES uaccount WRITE update uaccount set balance=balance+42.45,available=available+42.45 where uid=2191 UNLOCK TABLES j. LOCK TABLES trans WRITE insert into trans values(null,'0103252648','6GjSid8KoJ','34f9YC78AZ','0102',1,'USD',20020128122249,null,'Y',null) UNLOCK TABLES k. LOCK TABLES uaccount WRITE update uaccount set balance=balance-1,available=available-1 where uid=2192 UNLOCK TABLES l. LOCK TABLES uaccount WRITE update uaccount set balance=balance+1,available=available+1 where uid=2191 UNLOCK TABLES m. LOCK TABLES trans WRITE insert into trans values(null,'0103252648','6GjSid8KoJ','TESTAC0002','0102',1,'USD',20020128122249,null,'Y',null) UNLOCK TABLES n. LOCK TABLES uaccount WRITE update uaccount set balance=balance-1,available=available-1 where uid=2192 UNLOCK TABLES o. LOCK TABLES dispatchers_trans WRITE, Dispatchers_tmp READ insert into dispatchers_trans select * from Dispatchers_tmp where tid='0103252648' UNLOCK TABLES p. select * from dispatchers_trans where RemID='123' q. select * from profile where uid=2192 r. select * from profile where uid=2191 s. select * from profile where uid=3277 t. select * from profile where uid=3278 u. select * from uaccount where uid=2192 v. select * from uaccount where uid=2191 w. select * from uaccount where uid=3277 x. select * from uaccount where uid=3278 y. select * from uaccount where uid=3278 z. select date_format('20020128122249','%M %d, %Y %H:%i:%s') zz.select date_format('','%M %d, %Y %H:%i:%s')

12. Re-direct to printing page a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (... c. select tid from Dispatchers_tmp where tid='0103252648' d. select * from Dispatchers_tmp where tid='' e. select * from Dispatchers_tmp where tid='' f. select disp_rx_id from Dispatchers_tmp where tid='' g. select email, balance,available,type from uaccount where uid=2192 h. select valid from bankinfo where uid=2192 i. select email, balance,available,type from uaccount where uid=2192 j. select date_format(curdate(),'%W, %M %D, %Y')

13. Re-direct back to main "Sending" page a. select type from uaccount where uid=2192 b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (... c. select tid from Dispatchers_tmp where tid='0351454703' d. LOCK TABLES Dispatchers_tmp WRITE insert into Dispatchers_tmp (tid) values('0351454703') update Dispatchers_tmp set disp_tx_id ='2192' where tid='0351454703' UNLOCK TABLES e. select * from Dispatchers_tmp where tid='0351454703' f. select * from Dispatchers_tmp where tid='0351454703' g. select disp_rx_id from Dispatchers_tmp where tid='0351454703' h. select email, balance,available,type from uaccount where uid=2192 i. select valid from bankinfo where uid=2192 j. select email, balance,available,type from uaccount where uid=2192 k. select date_format(curdate(),'%W, %M %D, %Y')

Final count: 134 SELECT statements 26 LOCK TABLE statements 11 CREATE TABLE statements 9 INSERT statements 9 UPDATE statements

Note: it might also be amusing to also bear in mind that "tid", or Transaction ID, was not an auto-generated number in the database, since the DB designer elected not to create a table for master records and a sub-table for actual monetary value transfers. Thus, the major 'trans' table ended up with at least 3 rows for any one transaction, ERGO, you could not make "tid" an auto-generated primary key. They settled for a nifty little PHP function which would generate a text string of 10 random digits. Well, actually it was only 9 random digits, because the first digit always returned as "0". By the time I arrived, they were already getting corruption from duplicate transaction IDs.