please help site down

I have posted on the zen cart light box page, but have not had a reply yet (inpatient) as my store is down at the moment, would really appreciate some help on this,

Copy of post

Hi, I copied all the contents of the install.sql file in to the install sql page, box, and then hit send, it said 9 something had been sent, but now non of my products show on the site and I have an erorr message on the bottom of my site

please help,

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX_RANDOM_SELECT_REVIEWS' at line 8
in:
[select r.reviews_id, r.reviews_rating, p.products_id, p.products_image, pd.products_name from zen_reviews r, zen_reviews_description rd, zen_products p, zen_products_description pd where p.products_status = '1' and p.products_id = r.products_id and r.reviews_id = rd.reviews_id and rd.languages_id = '1' and p.products_id = pd.products_id and pd.language_id = '1' and r.status = 1 limit MAX_RANDOM_SELECT_REVIEWS]

sorry to keep posting but I reall need help with this and the site is down at the moment

powelli:

sorry to keep posting but I reall need help with this and the site is down at the moment
did you backup your database before installing this addon??
roll back if u did

also look in your database for the table mentioned and look for line 8 maybe??

hi nope didnt do the back up :(

spid3r1987:

did you backup your database before installing this addon??
roll back if u did

also look in your database for the table mentioned and look for line 8 maybe??

actually there are two of these errors

one on the main page of the store:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX_DISPLAY_UPCOMING_PRODUCTS' at line 6
in:
[select p.products_id, pd.products_name, products_date_available as date_expected, p.master_categories_id from zen_products p, zen_products_description pd where p.products_id = pd.products_id and p.products_status = 1 and pd.language_id = '1' and p.products_date_available >20100115235959 order by date_expected desc limit MAX_DISPLAY_UPCOMING_PRODUCTS]

and then the admin section has its own too, whic changes depending on the section you go to..products has this one:

Warning: Division by zero in /home/elfefcom/public_html/store/admin/includes/classes/split_page_results.php on line 39
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-0, MAX_DISPLAY_RESULTS_CATEGORIES' at line 12
in:
[select p.products_type, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_model, p.products_quantity_order_min, p.products_quantity_order_units, p.products_priced_by_attribute, p.product_is_free, p.product_is_call, p.products_quantity_mixed, p.product_is_always_free_shipping, p.products_quantity_order_max, p.products_sort_order from zen_products p, zen_products_description pd, zen_products_to_categories p2c where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = '0' order by p.products_sort_order, pd.products_name limit -0, MAX_DISPLAY_RESULTS_CATEGORIES]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

ideas of what to do? I replaced all the store files and it did not help any at all. He has a custom theme and can not access the template section to get to the classic template...but I did change it in database to no avail for the different 1064 messages are in the classic template too.

If I get rid of his tables the items will still be on the server but he will need to re-enter all info into the store again. He had done the lightbox sql installation about 3 times and those have been removed...will remove the files too.

DarkAngel:

ideas of what to do? I replaced all the store files and it did not help any at all.
.

Firstly, DON'T attempt to delete any database tables or records. THERE IS RARELY EVER A REASON that this needs to be done (even after a failed modules installation/removal). Additional/unused DB tables or records are not likely to cause any problems.

Secondly, replacing the store files will only help if the store files have been modified or changed in any way. If the module requires additional files to be installed (as most do) these won't be affected by replacing the original store files, unless you delete all the files and start afresh.

At this stage I wouldn't even contemplate such an action.

OK, so now I've told you what NOT to do, it is time to actually DIAGNOSE the problem and THEN take action to fix it. This is actually going to be quite easy :smile:

Firstly, the 'problem' as indicated is: "1064 You have an error in your SQL syntax"

The SYNTAX error(s) being related to undefined CONSTANTS, namely,
MAX_DISPLAY_UPCOMING_PRODUCTS,
MAX_DISPLAY_RESULTS_CATEGORIES

CONSTANTS can/will originate from one of two places, the SQL Database (which is why deleting tables/records is a bad idea), or, the language/php files.

The fact that you/we are seeing the CONSTANT NAMES rather than the CONSTANT values is telling us that the actual data is missing from the database or the files. This would suggest that either the data wasn't correctly uploaded in the first place, or has since been removed (as a result of reverting back to older files).

Solution. Ensure that these constants get defined, OR remove any code that uses these constants.

If you review the files that came with the module that you tried to install you should be able to identify whether these defines are DB based or File based (perform a word seach/find) . If file based just copy the relevent file over to your store (and assume it wasn't copied the first time for some reason). If DB based then run the SQL setup script that came with the module again (ensure you set/use the correct table prefix).

To remove the module you'll need to delete any FILES that it added to your store (and revert any that were modified). As previously mentioned, you will NOT need to remove any database records that may have been added.

In my experience it us usually easier to 'finish' a failed module installation that it is to try to remove them.

Cheers
Rod

ps. A possible "dirty fix" would be to add the following to your english.php file:

MAX_DISPLAY_UPCOMING_PRODUCTS = 10
MAX_DISPLAY_RESULTS_CATEGORIES = 10

welllllll, I actually have replaced all the store files with fresh untouched ones from the store install zip.

I did go through prior to that and looked for any of the lightbox files in the wrong places but found none.

I, as I said found that lightbox had been installed (sql rather was ran 3 times because that was the only thing that showed up under the configurations drop down menu). So I ran the uninstaller to get rid of it...but just in case I will re run the uninstaller via the db instead of store admin section.

I shall come back with results. Thanks

RodG:

Firstly, DON'T attempt to delete any database tables or records. THERE IS RARELY EVER A REASON that this needs to be done (even after a failed modules installation/removal). Additional/unused DB tables or records are not likely to cause any problems.

Secondly, replacing the store files will only help if the store files have been modified or changed in any way. If the module requires additional files to be installed (as most do) these won't be affected by replacing the original store files, unless you delete all the files and start afresh.

At this stage I wouldn't even contemplate such an action.

OK, so now I've told you what NOT to do, it is time to actually DIAGNOSE the problem and THEN take action to fix it. This is actually going to be quite easy :smile:

Firstly, the 'problem' as indicated is: "1064 You have an error in your SQL syntax"

The SYNTAX error(s) being related to undefined CONSTANTS, namely,
MAX_DISPLAY_UPCOMING_PRODUCTS,
MAX_DISPLAY_RESULTS_CATEGORIES

CONSTANTS can/will originate from one of two places, the SQL Database (which is why deleting tables/records is a bad idea), or, the language/php files.

The fact that you/we are seeing the CONSTANT NAMES rather than the CONSTANT values is telling us that the actual data is missing from the database or the files. This would suggest that either the data wasn't correctly uploaded in the first place, or has since been removed (as a result of reverting back to older files).

Solution. Ensure that these constants get defined, OR remove any code that uses these constants.

If you review the files that came with the module that you tried to install you should be able to identify whether these defines are DB based or File based (perform a word seach/find) . If file based just copy the relevent file over to your store (and assume it wasn't copied the first time for some reason). If DB based then run the SQL setup script that came with the module again (ensure you set/use the correct table prefix).

To remove the module you'll need to delete any FILES that it added to your store (and revert any that were modified). As previously mentioned, you will NOT need to remove any database records that may have been added.

In my experience it us usually easier to 'finish' a failed module installation that it is to try to remove them.

Cheers
Rod

ps. A possible "dirty fix" would be to add the following to your english.php file:

MAX_DISPLAY_UPCOMING_PRODUCTS = 10
MAX_DISPLAY_RESULTS_CATEGORIES = 10

readded the files for the lightbox...ran lightbox installer and nope...

the lightbox was the only thing listed in configuration drop down menu and error is still showing.

re-ran the uninstaller sql and the lightbox config is gone --- then removed all the files and still the error is there.

added the "dirty fix" to the english.php file and was greeted with no main page at all but the errors were still in admin...lol

so I still have a sick site, I have no idea why the configuration drop down menu is not showing either.

later today when I get up, I will go see if there is a place in the db that I can set the amount of images to be viewed but really do not think it will help the pages any.

any more ideas????

DarkAngel:

welllllll, I actually have replaced all the store files with fresh untouched ones from the store install zip.

Yes, and what I was saying that if a module installs new files, eg /includes/modules/newmofule.php then replacing the store files isn't going to remove this file, so it could still cause a problem.

DarkAngel:

I did go through prior to that and looked for any of the lightbox files in the wrong places but found none.

Not even the one you missed ;-)

DarkAngel:

but just in case I will re run the uninstaller via the db instead of store admin section.

Fsck. You didn't read a word I wrote did you?
This was the first thing I told you to NOT do. :frusty:

I'm not sure which way to tell you to go from here. You probably just dug yourself into a deeper hole. :-( I guess I can only wait for you to post your results.

Rod

DarkAngel:

added the "dirty fix" to the english.php file and was greeted with no main page at all but the errors were still in admin...lol

I'll wager that you simply copied my text (which was meant as a guide only). You probably needed to add a couple of quotes, or terminate the lines with a semi colon.

DarkAngel:

so I still have a sick site, I have no idea why the configuration drop down menu is not showing either.

You have a seriously sick database.

DarkAngel:

any more ideas????

To be perfectly frank, no I don't. You did the one thing a zencart user should never do, namely run an uninstall script on your database. This is always going to cause more problems than it will ever solve. A well behaved uninstall script is unlikely to cause a problem directly, but a badly written one can cause irreparable damage. I have no idea which category the lightbox uninstall script fits into. Not running an SQL uninstall script at all, even if the relevent module files are deleted will never cause a problem, directly or indirectly.

About the only way I can see you recovering now is with a fresh install.

Sorry to be the bearer of this sad news.

Rod

LOL, yes it is sick and the uninstaller that comes with the zen lightbox is a good one since I have used it before...the funny thing is that it all happened when the owner ran the installer for the lightbox...that is when everything disappeared.

The running of the uninstaller was done previously to this post...I have tried all sorts of things from making sure the files were uploaded correctly to installing the script again then completely removing everything concerning the lightbox.

The store files--since there was no other addons--I just uploaded the entire directories, minus the config files, completely replacing any and all files within them so essentially I have pristine files on the server with no added files/codes.

I fear it is a problem that the template may have contributed to...since it is like another one I helped out that would not use the lightbox due to its own restructured files and pngscript.

soooo, thank you very much for your help and I will try installing it into a different directory with a new db and see if that helps any at all.

To be perfectly frank, no I don't. You did the one thing a zencart user should never do, namely run an uninstall script on your database. This is always going to cause more problems than it will ever solve. A well behaved uninstall script is unlikely to cause a problem directly, but a badly written one can cause irreparable damage. I have no idea which category the lightbox uninstall script fits into. Not running an SQL uninstall script at all, even if the relevent module files are deleted will never cause a problem, directly or indirectly.

About the only way I can see you recovering now is with a fresh install.

Sorry to be the bearer of this sad news.

Rod

DarkAngel:

LOL,
I'm not sure I really want to help someone that thinks their problem is nothing but a laughing matter.

DarkAngel:

yes it is sick and the uninstaller that comes with the zen lightbox is a good one since I have used it before...

This is pretty flawed logic.

DarkAngel:

the funny thing is that it all happened when the owner ran the installer for the lightbox...that is when everything disappeared.

Are you simply taking the owners word that this was the only thing that s/he did? What makes you so sure that s/he didn't do other things that could have caused the problem?
Call me cynical if you like, but end users have a nasty habit of neglecting to reveal vital information about things that they have done. Usually it is along the lines of "I didn't do anything, It just stopped working".

DarkAngel:

I have tried all sorts of things from making sure the files were uploaded correctly to installing the script again then completely removing everything concerning the lightbox.

Yet you still have a problem. Clearly you haven't yet done the things you needed to do, else it would be working just fine.

DarkAngel:

The store files--since there was no other addons--I just uploaded the entire directories, minus the config files, completely replacing any and all files within them so essentially I have pristine files on the server with no added files/codes.

Tell me more about the config files. You state that you uploaded entire directorys minus these config files, so exactly what config files are in place now? How did you exclude them from the upload (or from being overwritten)?

DarkAngel:

I fear it is a problem that the template may have contributed to...

I fear that we don't have the full story.

DarkAngel:

since it is like another one I helped out that would not use the lightbox due to its own restructured files and pngscript.

This doesn't explain the missing database records, which will only occur by running a badly behaved SQL uninstall script, or direct manipulation of the database. Not that this changes the situation at all, but having an explanation for it will help prevent the same situation from re-occuring.

Cheers
Rod

RodG:

I'm not sure I really want to help someone that thinks their problem is nothing but a laughing matter.

If one does not inject a bit of humor then one will go mad. Help if you wish but do not tell me how to react, that is not anything to do with the matter at hand. I did what was instructed, I am not an expert but neither am I a novice.

Yes I did everything I was told to do, you are not hear so do not assume anything. I did what was instructed by you and others...and it simply did not work for the errors.

I have since gotten it to behave, it would seem that the owner has learned the hard way not to install the store with a program but to do it manually to ensure problems do not arise.

I created a new database with totally new tables and did the installation the proper way.

I came asking for help and ideas not to be lectured jsut because what you said to do did not work.

Thank you for the help you did give and I don't need you at this time anymore.

This problem is resolved if not solved.

Rod

I hope that this unthankful user will not put you off of helping others including me as you have done so many times. If asking valid questions is lecturing then feel free to lecture any time :smile:

Dark Angel,

it is not often that I jump in like that neither I intend to reply any further in this post but you do seam/act like a novice wishing for a quick fix rather than try to understand the comments. More than that your final post comes across as dismissive, unthankful and condescending (sorry to the Zen Cart team if I offend anyone). The fact is that if you have spend a bit more time to understand the comments made by Rod then you may have found the problem and solved it.

Now you run the risk of the problem coming back (I hope not ) because you still do not know what caused it in the first place. That's exactly the behaviour of novices which does not help anyone else on the forum because if someone has the same problem in the future there is still no solution.

Sorry if I offend you but when you attack someone like Rod who has helped plenty of people as well as provided for free one of the best modules on Zen Cart then someone needs to speak up (not that he needs it)

Good luck

first off I am thankful for help...however yes, when I am told I did not do what was instructed when I had already stated it had been done---and done before the advice is when I get defensive.

not looking fo quick fixes...I myself help a lot on this forum and ask questions and even explain in detail to others that do not realize when it states...

place the includes folder in your server does not mean the one from the zip into your server---or that will overwrite the one there.

I have never told anyone that they did not do what was instructed, that is rude to assume that becasue it did nort work that they did not follow the instructions.

As I said I found out that the owner uploded the store with frontpage or fantastico, can't remember which now...so no matter what the database would not let go.

I ended up creating a new db, uploading the store fresh and it is now behaving like it should.

YES, by all means give advice, reword it if necessary to help the enduser understand what you are telling them (just like standing right in front of them sometimes the person does not understand what you think they do)...but don't assume they did not follow the instructions.

I have been here and other forums both helping and being helped and if I appeared unappreciative then sorry for that for IF I knew what to do everytime I would have created my own addons or program or software.

Rod has created addon, ty maybe I have used it who knows, I ask the questions as easily as possible for two reasons:

1. so that whoever helps knows what I might need

2. IF by chance a really new novice comes along with the same problem will understand what I was asking and come back for help.

It comes from helping to write faq manuals at the hospital I worked at...speak in words as clearly as possible so even a child may understand it...to help the enduser as much as possible.

I am no expert and neither a novice, intermediate user but when told things in a certain way I get defensive and his method of "talk" in the post just hit the right nerve so yes he was dismissed. I need help at times, just like everyone that comes here does when asking for help. I have been fortunate to have others help and not accused (that is what it appeared like) of not doing what was told.

I am grateful for this store software, it is the easiest one to install and manage and add on to...and never tell others to install any other one...recommending this one above all others.

So forgive this old woman for spanking you young'ns when she gets accused of things. I would do it in real life too. Ask valid questions, there is no other way to finid out what is needed but do not accuse the user of things....if that is how you feel then try asking your questions in a different manner.

Now if you shoule both decide never to help me ---for I am sure I will return with questions about both my own and my clients stores then so be it.

Nothing personal just did not like the "attitude" I perceived as getting...then returned in fashion.

Mary AKA DarkAngel,

I don't usually continue a thread that has deteriorated into little more than a slanging match, but this time I'm going to make an exception.

DarkAngel:

first off I am thankful for help...however yes, when I am told I did not do what was instructed when I had already stated it had been done---and done before the advice is when I get defensive.

How about getting your facts right. I NEVER said that you DIDN'T do what I instructed you to do, I did however (correctly) state that you did the one thing I said you should NOT do.

DarkAngel:

I have never told anyone that they did not do what was instructed,

Neither have I (well, not without being able to prove it). This was not the case here though... I told you to NOT run the SQL uninstall scripts. There is RARELY EVER a need to do that, and it will ALWAYS cause more problems than it solves .... so what did you do? You ran the uninstall script AGAINST my advise. Not once, but twice.

DarkAngel:

that is rude to assume that becasue it did nort work that they did not follow the instructions.

I assumed nothing. I told you at least twice to NOT run the uninstall script, yet by your own admission you ran it anyway.

DarkAngel:

YES, by all means give advice, reword it if necessary to help the enduser understand what you are telling them

Tell me oh wise one, how could I have reworded the following sentence that would have made it any clearer for you?

"Firstly, DON'T attempt to delete any database tables or records. THERE IS RARELY EVER A REASON that this needs to be done"

DarkAngel:

(just like standing right in front of them sometimes the person does not understand what you think they do)...but don't assume they did not follow the instructions.

Again, I assumed nothing. I very clearly stated "DON'T attempt to delete any database tables or records" and you responded with "but just in case I will re run the uninstaller"
<huge sigh>

DarkAngel:

It comes from helping to write faq manuals at the hospital I worked at...speak in words as clearly as possible so even a child may understand it...to help the enduser as much as possible.

Again, I request that you explain how "DON'T attempt to delete any database tables or records" can be worded in such a way so that people such as yourself won't misinterpret to mean "yes, go ahead and run the uninstaller, and if that doesn't work, run it again" ?

THAT is the issue we are dealing with here. Nothing is/was assumed.

Incidently, As well as my (many) computer skills (and certifications) , I am also a certified Trainer & Assessor, which I am sure will trump your limited experience at helping to write faq manuals for a hospital.

DarkAngel:

I am no expert

Well, I am, and this is where many people have an issue with me - I have great confidence in most of my postings and suggestions, so much so that it is often confused with arrogance. No biggy, I can live with that. :-)

Occasionally I'll respond to a request for help without being 100% sure of my facts, and in these cases I let it be known that I'm taking little more than educated guesses (else I'd end up looking like a fool).

DarkAngel:

and neither a novice, intermediate user but when told things in a certain way I get defensive and his method of "talk" in the post just hit the right nerve so yes he was dismissed.

To your detriment... you not only had to reinstall the database (and reconfigure), but because you refuse to heed my sage advise you will almost certainly make the same mistake again at some time in the future.

Perhaps after you have made the same mistake 2 or 3 times (as I have done) you will finally come to appreciate the wisdom of my words, but until then I just know that you will consider me an arrogant SOB.

Unfortunately experience tells me that there is nothing that I can do or say that will change this prediction.

DarkAngel:

I need help at times, just like everyone that comes here does when asking for help. I have been fortunate to have others help and not accused (that is what it appeared like) of not doing what was told.

But you didn't do what you was told - you did the very thing you was told you should NOT do.

DarkAngel:

So forgive this old woman for spanking you young'ns when she gets accused of things. I would do it in real life too.

What a joke, you falsely accuse me of making assumptions, and then proceed to do the same thing yourself.

BTW, thank you for this, it has been MANY years since anyone has called me a young'n.

For the record I'm 54.

DarkAngel:

Now if you should both decide never to help me ---for I am sure I will return with questions about both my own and my clients stores then so be it.

What a petty comment to make. You may be immature enough to refuse to continue to help someone in spite of their false accusations, but I'm always willing to give people a second, and even 3rd chance before considering them a lost cause.

DarkAngel:

Nothing personal just did not like the "attitude" I perceived as getting...then returned in fashion.

Sorry deary, I know at your age (whatever that may be) the old brain cells don't work as well as they should, but there is no way that I will ever pander to your bullshot (or anyone elses for that matter). I consider myself too old to play such juvenile games. The basic facts are all that I am ever interested in dealing with - Anything else, such as your unfounded allegations simply makes me respond without pulling any punches, and if you don't like my 'attitude' that is your problem, not mine.

Perhaps you should spend a little less time in your fantasy land and a bit more time in the real world???

Rgds
Rod

ps. If you are offended by any of my comments please understand that offense can only be taken, never given.

pss. Dr.Byte ..... ok, no need to say anything. As far as I'm concerned this thread is now closed, and as DarkAngel stated earlier "If one does not inject a bit of humor then one will go mad". This is my idea of humor/fun :smile:

Hi, as I am the culprit of this I thought Id better say what happened,
After I had pretty much finished the site, I thought id have a go at installing zen cart light box, I unzipped the files and uploaded them to the correct parts on the site via the site c-panel.
I then opend the install.sql file and copied the contents of it in the install sql patches in the admin and hit the send button, I then went to the site and the products had gone and the error message as on my first post was showing, before this the site was working well.
As this is my first attempt at using zen, I thought id best get help form you guys (who have been amazingly helpful) even more so than a lot of other fourms (not zen) and for that I really appreciate your help.
A massive thumbs up to dark angel who has been talking to me via email and is doing a great job getting it fixed keeping me updated and discussing with me what the problem is.
Owain

Perhaps you should spend a little less time in your fantasy land and a bit more time in the real world???

you are right, my fantasy family of sites is my reality break. the reality of a mother in the final stages of Alheimer's needs to have the break to keep sane.

I have been having a similar problem with only my site not my admin. Does anyone know what the solution was? I get the same error when someone empties their shopping cart:
1064 You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
in:
[select p.products_id, pd.products_name, products_date_available as date_expected, p.master_categories_id from products p, products_description pd where p.products_id = pd.products_id and p.products_status = 1 and pd.language_id = '1' and p.products_date_available >20100225235959 order by date_expected desc limit ]

Looking back I believe the error did come right after installing light box.
thanks!

Check your Configuration ... Maximum Values ... and make sure that none are blank or zero for the New, Specials, Featured, Upcoming ...