• From: Indonesia
  • Registered: 04/07/2011
  • Posts: 29

Topic: SQL trail: inaccurate error message?

Hi all,

I activated SQL trail option ($sql_trail = 1), and found that some (or maybe all) of error messages are inaccurate.
For instance, I added a new customer. The new customer data was successfully saved. But in the sql_trail table I found these rows:

=================
sql | result |msg

BEGIN | 1 | could not start a transaction

INSERT INTO debtors_master (name, debtor_ref, address, tax_id, dimension_id, dimension2_id, curr_code, credit_status, payment_terms, discount, pymt_discount,credit_limit, sales_type, notes) VALUES ('Dummy Customer', 'Dummy Customer', 'Jakarta', '','0', '0', 'IDR', '1', '2', 0, 0, 1000, '1', '') | 1 | The customer could not be added

COMMIT | 1 | could not commit a transaction
=================

I saw that the new customer data existed in debtors_master table.
So why did the SQL trail tell me that the query was failed?

Was it because the debtors_master table is MyISAM?

Then I tried to do another transaction with InnoDB tables. I added a new item tax type (the item_tax_types table is of InnoDB type). The new data was saved correctly (I think so). But still, I got similar error message in sql_trail table:

=================
BEGIN     1     could not start a transaction
INSERT INTO item_tax_types (name, exempt) VALUES ('No tax','1')     1     could not add item tax type
COMMIT     1     could not commit a transaction
=================

Is there any body can help me to explain this?
Thanks in advance.

Albert

apprayo's Website

  • Registered: 12/25/2007
  • Posts: 2,448

Re: SQL trail: inaccurate error message?

The messages are included in the table just as a comment helpfull in recognizing where the query was issued. If you want to find real problems, just make a query like:

SELECT * FROM 0_sql_trail where result=0
  • From: Indonesia
  • Registered: 04/07/2011
  • Posts: 29

Re: SQL trail: inaccurate error message?

Thanks for the response.

So, as long as the result is 1, I can ignore the error message?

apprayo's Website

  • tclim
  • Senior Member
  • Offline
  • From: Malaysia
  • Registered: 03/16/2011
  • Posts: 111

Re: SQL trail: inaccurate error message?

Hi All,

  Beware of using SQL Trail.

  I have some issue when using SQL trail for debugging. I have spend quite some time to understand how it work. And finally, I have turn off this features and everything work fine.

The scenario was:

  1) Create a new customer branch
  2) Add Branch Contacts and here it hit an error. (The branch id should be pass to the branch contact when you create a new contact information in branch). When you activate the SQL trail. The branch id doesn't return but it return the wrong id when you execute the sql statement to retrieve the branch id using db_insert_id();

This db_insert_id(); should return the branch id but rather it return the wrong id from 0_sql_trail -> id.

For example: Insert branch return 100;
When you activate the SQL trail, it execute another sql statement to insert the debugging message in 0_sql_trail. and the sql_trail -> id now = 101.
When you execute the db_insert_id(), you expect the returning id is from branch id but instead it return 101.

When I turn off the SQL trail, and re-create the branch and branch contact. It works fine. The returning branch id is correct.

Suggestion: Please change these SQL Trail features to store the message in text file rather then in database. it will help a lot and save time for new developer like me who are trying to change FrontAccounting module. If you have any better idea, please share it with me. Thanks you.

tclim's Website

  • From: Indonesia
  • Registered: 04/07/2011
  • Posts: 29

Re: SQL trail: inaccurate error message?

Thanks tclim!
I think your explanation related with my other issue here: http://mantis.frontaccounting.com/view.php?id=709.

apprayo's Website

  • tclim
  • Senior Member
  • Offline
  • From: Malaysia
  • Registered: 03/16/2011
  • Posts: 111

Re: SQL trail: inaccurate error message?

Hi apprayo,

  I was unable to view your issued. Anyway, if you have any good suggestion, kindly let me know.

Thanks.

tclim's Website

  • From: Indonesia
  • Registered: 04/07/2011
  • Posts: 29

Re: SQL trail: inaccurate error message?

Let me copy my explanation about the issue here:

0000709: Cannot view GRN transaction detail


Each time I click the link of GRN transaction number (to view the transaction detail) in Inventory Item Movement report or Supplier Transaction Inquiry, the new window open with error message:
======================
DATABASE ERROR : FATAL : duplicate purchase order found
sql that failed was :
======================

There is no additional information of failed SQL.

After some search in the PHP code, it seems this is because the content of grn_batch.purch_order_no field doesn't match with the field purch_orders.order_no.

The purch_orders.order_no is auto-increment generated, while grn_batch.purch_order_no contains big numbers (I haven't found where are they taken from).

I tried another new installation, and I got similar issue.

I tested further after I read your post, and found that I got the issue when I turned on the SQL trail. The issue was gone after I turned of the SQL trail.

Hope this helps.

apprayo's Website

  • tclim
  • Senior Member
  • Offline
  • From: Malaysia
  • Registered: 03/16/2011
  • Posts: 111

Re: SQL trail: inaccurate error message?

Hi apprayo,

   Great to know that you have solve your issue. This forum is great to share our experience and knowledge with each others. Hope you enjoy it. big_smile

tclim's Website

  • From: Indonesia
  • Registered: 04/07/2011
  • Posts: 29

Re: SQL trail: inaccurate error message?

smile

But still, we have another discovered issue (SQL trail issue that you found) to be solved.

apprayo's Website

  • Registered: 12/25/2007
  • Posts: 2,448

Re: SQL trail: inaccurate error message?

Hi guys. Thank you both for tracking down the problem. I couldn't reproduce the bug 709, now I know why. The fix will be included in next (2.3.4) release.

Janusz