SQL Error setting up tax codes

[BigDude]

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]

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?

[BigDude]

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.

[Arnel]

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:

1. Clicked LOCALIZATION menu, then clicked on TAX RULES.
   
2. Clicked on Add New (screen cap 1). Named the new rule.
   
3. 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 May 18, 2013 by arncus (see edit history)

[BigDude]

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]

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.

[Arnel]

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]

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 [spam-filter], 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]

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]

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]

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]

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]

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);

[mahkali]

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 October 13, 2013 by mahkali (see edit history)

[lock-e73]

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.bizbenefits.com.au/Taxes.JPG | http://www.bizbenefits.com.au/Taxes2.JPG

http://www.bizbenefits.com.au/Tax_Rules.JPG | http://www.bizbenefits.com.au/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.bizbenefits.com.au/Incorrect_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 February 11, 2014 by lock-e73 (see edit history)

[Informatik MP]

 

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]

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]

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 ?!

 

 

Edited April 18, 2014 by rvzoest (see edit history)

[Informatik MP]

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 April 18, 2014 by InformatikMP (see edit history)

[msp1982dk]

Hi guys,

 

I got this error too.

I think it's caused by filtering the tax rules based on the "Enabled" field.

At least I don't get the error when I clear the filters...

[SeanYi]

Hi guys.

 

I'm using 1.6.0.6 on inMotion Hosting service by automatic installation service(Softaculous??)

I have same problem.

 

I think it might be some kind of javascript bug. 

Whenever I load the 'adding new tax rule' or 'editing specific tax rule' page, some fields(I think it might be shown on normal phase.) are shown and disappear suddenly. I cannot access that fields after disappearing.

 

I think some javascript helps to display the lists and fields on tax_rule class table. but, It might be failed.

 

Anyway, the tax is very important point to run the shop. I hope it will be fixed soon!

[qhe]

I think it's a problem of cookies.
I deleted all cookies from the site and it works.
 

Regards

 

Quim