Vote for v1.5 Fixes

PrestaShop Forum: Build a free online shop. Count on the Community!

The best place in the world to ask questions about PrestaShop and get advice from our passionate community!

PrestaShop Forum: Build a free online shop. Count on the Community!

Jump to content

 
Photo

SQL Error setting up tax codes

SQL tax error tax code

  • Please log in to reply
18 replies to this topic
BigDude

    PrestaShop Newbie

  • Members
  • Pip
  • 9 posts

Posted 17 May 2013 - 03:17 PM

I am just getting my store setup and have run into what could be a MAJOR headache. I have managed to solve a couple of other issues, but this one leaves me a bit baffled because I don't know from where it comes or which piece of code I should look at!!

The problem is this:

I have created a new tax rule because I was not getting ANY taxes applied to purchases from my state, which is the ONLY tax I am collecting at the moment. However, when I save the rule, I get conflicting messages. One message says,

"Creation Successful!"

but just below that message I have an error box that pops up with:

"Bad SQL query
Unknown column 'a.active' in 'where clause'"

I would appreciate one of you seasoned folks clearing my brain of the fog I am experiencing with this issue.... :(

Thanks a bunch!

-DON-

vekia

    PrestaShop Legend

  • US Moderators
  • 36129 posts

Posted 17 May 2013 - 10:20 PM

I am just getting my store setup and have run into what could be a MAJOR headache. I have managed to solve a couple of other issues, but this one leaves me a bit baffled because I don't know from where it comes or which piece of code I should look at!!

The problem is this:

I have created a new tax rule because I was not getting ANY taxes applied to purchases from my state, which is the ONLY tax I am collecting at the moment. However, when I save the rule, I get conflicting messages. One message says,

"Creation Successful!"

but just below that message I have an error box that pops up with:

"Bad SQL query
Unknown column 'a.active' in 'where clause'"

I would appreciate one of you seasoned folks clearing my brain of the fog I am experiencing with this issue.... :(

Thanks a bunch!

-DON-


hello

i want to check code, but i must know what prestashop version you use.
You use fresh installation or maybe upgraded version?

free tutorials, free templates, free addons, prestashop modules MndfRK4.png


BigDude

    PrestaShop Newbie

  • Members
  • Pip
  • 9 posts

Posted 17 May 2013 - 10:29 PM

This is a fresh install of code downloaded from Prestashop site 3 days ago.... I thought saying I was just starting would indicate this was a NEW store! Anyway, latest version.

arncus

    PrestaShop Apprentice

  • Certified Partners
  • 78 posts

Posted 18 May 2013 - 01:40 AM

Hi Bigdude!

It always helps to list the version number because we're not always sure what people have downloaded and installed. I have many clients that use various versions, so it helps to know what version I should be testing on. I'm testing this with 1.5.4.1.


I was trying to duplicate the issue that you're getting. Let me list what I did - please let us know if that's what you have done:
  • Clicked LOCALIZATION menu, then clicked on TAX RULES.
  • Clicked on Add New (screen cap 1). Named the new rule.
  • Click ed on Add a new tax rule(screen cap 2). Created the rule.
NOTE: One word of caution for anyone else reading this and trying it - please select the COUNTRY when create the new tax rule - it defaults to ALL COUNTRIES.

I tried this a few times trying different settings. I also get the green bar that states Creation Successful. When I add a new tax rule, I see UPDATE successful. It appears to be working for me. Can you tell us how you were creating the rule so that we can see where the problem might have happened?

Give us a little more information and we'd be happy to help!

Arnel C.
Community Support Team
www.webhostinghub.com

Edited by arncus, 18 May 2013 - 01:42 AM.


BigDude

    PrestaShop Newbie

  • Members
  • Pip
  • 9 posts

Posted 18 May 2013 - 02:48 PM

Arncus,

Thanks for the reply... and I apologize if my tone was too curt, but I have reached the end of my rope having to repeat what I have already said because folks don't READ or LISTEN carefully and completely. I suppose I was too tired and frustrated to have responded to the first message. Again, my apologies.

Now, as to what steps I have taken, EXACTLY the same ones you are taking. I am also on 1.5.4.1.

-DON-

vekia

    PrestaShop Legend

  • US Moderators
  • 36129 posts

Posted 18 May 2013 - 04:05 PM

having to repeat what I have already said because folks don't READ or LISTEN carefully and completely

sorry but you but you didn't mention anywhere about the version of PS that you are using.
I suggest you to read forum rules.

free tutorials, free templates, free addons, prestashop modules MndfRK4.png


arncus

    PrestaShop Apprentice

  • Certified Partners
  • 78 posts

Posted 18 May 2013 - 04:39 PM

My apologies for raising the ire of the moderator. In his defense, BigDude did apologize. It was simply my intent that the version be explicitly said. And Vekia brings up a VERY valid point (and you're not the only one to ignore it, Bigdude/Don) - the rules of the forum do state that we need a little information when you are posting. It simply helps to save time and confusion.

But whatever, it's all water under the bridge, so to speak....let's get to the problem.

A couple of questions so that we can try and narrow this down.

1. When you installed PrestaShop 1.5.4.1 - did you install with the sample data. Did you import any data in? Anything special during the install? Basically, I'm just making sure that nothing abnormal happened during the install.
2. Did you add any modules or add-ons? What were they? Using any special theme?
3. When you were entering the data, what did you enter? Can you provide us a link?

Any detail on what you did would help. So far, you've acknowledged the version and that you followed the same steps as me, but now I'm trying to make sure that we're working with the same setup, and nothing is different. We need to try to duplicate the problem.

I work with a hosting company where many of our clients use different commerce packages. In an effort to help us better understand the product and learn more about it, it helps us to get out there and see what problems peopleare facing. This ultimately lets us help our customers, so we try to help out in forums like this one. I've worked with PrestaShop (in terms of installing and helping out our clients and non-clients who use it) for close to a year now, and I can tell you that the community involved with this ecommerce solution is very responsive and WANT to help. If we can just a get a little information from you, Don, I'm sure we'll be able to help.

Sorry for the speech. Please provide us a little more info, and we'll try our best to solve the problem!

Arnel C.
Community Support Team
Webhostinghub.com

BigDude

    PrestaShop Newbie

  • Members
  • Pip
  • 9 posts

Posted 18 May 2013 - 11:26 PM

Arnel,

Nope, nothing special. I unzipped the package right from the web, copied it over to my machine and went about configuration. After I had set the preliminaries, I went into each section of the catalog and deleted, one by one, the data that was installed - by default - when the program was setup. I have done no imports, no nothing! Just trying to get the pieces nailed down before I attempt a rollout to production. This shop is installed locally on my development machine. Running Mac 10.6.8 with the default SQL and Apache setup. Have had no issues developing straight websites, creating a webstore for my wife, or testing out a couple of other store packages. Each have had their problems along the way, nothing big or insurmountable. This one however seems to have thrown a brick through the window and is preventing ANY tax computation from occuring on the cart side.

If this isn't something that we can readily get to the bottom of, I will just move ahead. I really don't have the time to spend hours upon hours trying to get one little issue resolved.

I appreciate you looking into it for me and I will do what I can to try to get you the information you need, but I also have a business to run... I hope you understand.

Thanks again,
-DON-

patmanami

    PrestaShop Newbie

  • Members
  • Pip
  • 5 posts

Posted 02 June 2013 - 08:20 AM

I'm getting the same exact error that was reported. Did anyone find a solution to it?

Bad SQL query
Unknown column 'a.active' in 'where clause'


Besides editing the default tax rule, I've also tried to create a new tax rule and the same error appears, even though I get the Update Successful message.

This is keeping the tax from displaying anywhere in the cart and is a critical issue. All other tax options for the customer group, product and localization have been set correctly.

Advanced help on this would be awesome. I am using PS 1.5.4.1

Thanks much

rodeotack

    PrestaShop Newbie

  • Members
  • Pip
  • 1 posts

Posted 10 July 2013 - 06:28 AM

I am running 1.5.4.1
I am using the default theme and installed with the samples. I have added paypal and facebook modules. I getthe same message but in configurations, I also have this error - Enable allow_url_fopen (Could this be the problem? And I can't seem to fix that error either.) My host actually loaded PrestaShop for me.

I have added a new tax rule and it says successful but also gives me the error.
The tax code shows up but my site still isn't taxing.

My website is www.rodeotack.com

Thank you!

nizzen

    PrestaShop Newbie

  • Members
  • Pip
  • 13 posts

Posted 04 September 2013 - 06:40 AM

Hi

 

No solution to this yet?

 

I cant use my shop due to this error. Cant get tax calculations to work or change the rules - the error comes up all the time.

 

Using latest presta.

 

Please help.

 

Thanks



mahkali

    PrestaShop Newbie

  • Members
  • Pip
  • 3 posts

Posted 13 October 2013 - 01:29 PM

I'm getting the same exact error that was reported. Did anyone find a solution to it?

Bad SQL query
Unknown column 'a.active' in 'where clause'


Besides editing the default tax rule, I've also tried to create a new tax rule and the same error appears, even though I get the Update Successful message.

This is keeping the tax from displaying anywhere in the cart and is a critical issue. All other tax options for the customer group, product and localization have been set correctly.

Advanced help on this would be awesome. I am using PS 1.5.4.1

Thanks much

I'm getting this error too. Any luck so far?

 

Many thanks,
Maarten



vekia

    PrestaShop Legend

  • US Moderators
  • 36129 posts

Posted 13 October 2013 - 03:48 PM

turn on error reporting please then you will see full error code

open find this line
define('_PS_MODE_DEV_', false);
change to look like this:
define('_PS_MODE_DEV_', true);

free tutorials, free templates, free addons, prestashop modules MndfRK4.png


mahkali

    PrestaShop Newbie

  • Members
  • Pip
  • 3 posts

Posted 13 October 2013 - 06:41 PM

I can see the whole error code and I figured the problem, and created a solution but I don't think it is a real solution, I don't know all the PrestaShop code and its whereabouts, so please forgive me if I am going the wrong direction here.

I see that the SQL has a where clause for column a.`active`, this refers to the tax rule table where there is indeed no `active` column. 

 

Adding a `active` column with this query: 
ALTER TABLE `ps_tax_rule` ADD `active` TINYINT( 1 ) NOT NULL DEFAULT '1'

removes the error, but now the tax rule edit screen is looking funny.

All in all, it kind of helps, but I don't think this is a real solution.
 
Perhaps someone can shed some light on this matter.
 
*edit
I did a lookup and PS 1.5.4.1 did not have an `active` column in table ps_tax_rule so I'm afraid there might be a better solution for this 
 
*edit
I also see people reporting this error in 1.5.4.1

Edited by mahkali, 13 October 2013 - 06:51 PM.


lock-e73

    PrestaShop Newbie

  • Members
  • Pip
  • 6 posts

Posted 11 February 2014 - 06:02 AM

Please help!  Standard install of PS 1.5.6.2 with default theme. Australian localisation pack installed and all 'Taxes' disabled except 'GST AU 10%'. The only 'Tax Rule' enabled is 'AU Standard rate (10%)'.  I'm in the same boat as all above. See below screenshots:

http://www.bizbenefi...om.au/Taxes.JPG | http://www.bizbenefi...m.au/Taxes2.JPG

http://www.bizbenefi...u/Tax_Rules.JPG | http://www.bizbenefi.../Tax_Rules2.JPG

 

You can see in the last screenshot above 'Tax_Rules2.JPG' the SQL error code:

  • Bad SQL query
    Unknown column 'a.active' in 'where clause'

 

Screenshot here shows pricing with the Tax Rule implemented but showing same price both before and after Tax!

http://www.bizbenefi...rrect_Price.JPG

 

Please help me out here as I have no idea what's going on.  If you need any further information let me know!

Cheers, lock-e73


Edited by lock-e73, 11 February 2014 - 06:02 AM.


InformatikMP

    PrestaShop Apprentice

  • Members
  • PipPip
  • 62 posts

Posted 05 March 2014 - 12:59 AM

 

I can see the whole error code and I figured the problem, and created a solution but I don't think it is a real solution, I don't know all the PrestaShop code and its whereabouts, so please forgive me if I am going the wrong direction here.

I see that the SQL has a where clause for column a.`active`, this refers to the tax rule table where there is indeed no `active` column. 

 

Adding a `active` column with this query: 
ALTER TABLE `ps_tax_rule` ADD `active` TINYINT( 1 ) NOT NULL DEFAULT '1'

removes the error, but now the tax rule edit screen is looking funny.

All in all, it kind of helps, but I don't think this is a real solution.
 
Perhaps someone can shed some light on this matter.
 
*edit
I did a lookup and PS 1.5.4.1 did not have an `active` column in table ps_tax_rule so I'm afraid there might be a better solution for this 
 
*edit
I also see people reporting this error in 1.5.4.1

 

 

I have a very simple fix for you:

- In a mysql editor(like PHPMyAdmin), edit your prestashop database table ps_tax_rule

- add a row named active



RedCloudArmory

    PrestaShop Newbie

  • Members
  • Pip
  • 1 posts

Posted 25 March 2014 - 10:28 PM

I'm getting this same error running v1.5.6.2 - is there any chance that one of you guys who is NOT getting this error might do the following for those of us who are getting the error?  That way, we could drop and recreate the ps_tax_rule table to be exactly as yours is.  Even after creating an "active" column, I get the error.

 

 

SHOW CREATE TABLE ps_tax_rule;

 

 

 

This is the result of that query on my system:

 

CREATE TABLE `ps_tax_rule` (
 `id_tax_rule` int(11) NOT NULL AUTO_INCREMENT,
 `id_tax_rules_group` int(11) NOT NULL,
 `id_country` int(11) NOT NULL,
 `id_state` int(11) NOT NULL,
 `zipcode_from` varchar(12) NOT NULL,
 `zipcode_to` varchar(12) NOT NULL,
 `id_tax` int(11) NOT NULL,
 `behavior` int(11) NOT NULL,
 `description` varchar(100) NOT NULL,
 `active` tinyint(1) NOT NULL,
 PRIMARY KEY (`id_tax_rule`),
 KEY `id_tax_rules_group` (`id_tax_rules_group`),
 KEY `id_tax` (`id_tax`),
 KEY `category_getproducts` (`id_tax_rules_group`,`id_country`,`id_state`,`zipcode_from`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8



rvzoest

    PrestaShop Apprentice

  • Members
  • PipPip
  • 27 posts

Posted Yesterday, 01:45 PM

I have a very simple fix for you:

- In a mysql editor(like PHPMyAdmin), edit your prestashop database table ps_tax_rule

- add a row named active

 

Thank you for this info!!! I have no clue about SQL but after fiddling a bit (and making backups all the time) I succeeded in making it work. This is also a problem in Prestashop 1.6. I think this is a MAJOR problem if this isn't standard altered?!?!

 

But still the problem remains, I can not change the country to apply the rule in ?!

 

 

Attached Files


Edited by rvzoest, Yesterday, 01:57 PM.


InformatikMP

    PrestaShop Apprentice

  • Members
  • PipPip
  • 62 posts

Posted Yesterday, 02:55 PM

they have left the problem as it was... sometimes when you create a rule inside a tax rule group, it doesn't appear in the the back-office, it's cause by a lack in the SQL query in the code, causing the sub-rule to be inactive...

 

Once again, you have to go in PhpMyAdmin and do this:

  • go in your shop's db
  • go in the table "prefix"_tax_rule
  • find your tax rule you created, most likely the last(s) one(s)
  • edit the entry, and set the "active" value to 1.

I just remembered the workaround I used: when I created the columt "active", I have set the "default value" to "1", so any rule I create is set active by default.

 

I will re-up this problem in the bugs forum again... Let's see if they care about this...


Edited by InformatikMP, Yesterday, 03:26 PM.






Also tagged with one or more of these keywords: SQL, tax error, tax code

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users