Jump to content

Archived

This topic is now archived and is closed to further replies.

Kjolebutikken

1064 - You have an error in your SQL syntax

Recommended Posts

Hi, I have not worked on this webstore for two years, but now I have started working on it again and will open the store in the beginning of 2013. But when I start working on it again, I get some errors. I have tried to read in my log for the changes I did two years ago and have also uploaded an older backup to get rid of some of them, but still there are some errors that I can not find out the reason for. Hope some of you can help me with those :-)

 

In admin, when I try to update the product information for a product I am not able to save. I get an 1046 syntax error and it seems like it is because of the norwegian letters Æ, Ø and Å. The productdescription is saved only until one of these letters appear in the description:

 

 

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 'Century Gothic'">ór, og er meget forseggjort og pyntet med både perler, paljette' at line 1

 

UPDATE products_to_products_extra_fields SET products_extra_fields_value = '

 

Nydelig ballkjole med "prinsesse-fasong". Kjolelivet er av flortynn, gjennomsiktig cherisefarget silke over cherisefarget fór, og er meget forseggjort og pyntet med både perler, paljetter og små stoffblomster. Toppen har innlagt brystcup, noe som gjør bh unødvendig. Skjørtet er klokkeformet og av to lag flortynt, glinsende og gjennomsiktig cherisefarget stoff. Kjolen er stroppeløs og helfóret med cherisefarget fór. Tilsammen tre lag i skjørtet. Glidelås i rygg.

 

' WHERE products_id = 44 AND products_extra_fields_id = 4

 

 

This is the part of the description saved:

 

 

Nydelig ballkjole med "prinsesse-fasong". Kjolelivet er av flortynn, gjennomsiktig cherisefarget silke over cherisefarget f

 

 

I am not sure if this is because my database does not handle these letters? Or if there is something with my files. But I can not see in my log that I had these problems when I installed the contributions. I have previous installed the contributions (latest installations, two years ago):

 

 

Product extra fields

TinyMC

Product attribute sort order

Attribute sort

Advanced sort attributes

Not finding product attributes

PDF Datasheet maker

 

 

Only contribution installed recently is an update of printable catalog.

 

 

I also get this problem in admin for the payment modules, F.ex. for the bank transfer module. text that have the letters Æ, Ø or Å disappears from where the letter appears.

 

 

Is there f.ex. an sql I can run to solve this problem?

 

 

Also, in the webstore, on the product specification page, for some of the products the with of the store is increased by opening this page, whereas it is the normal with on the categories page or on the index.php page which shows all products. The strange thing is that this happens for only some of the products. For other products the page with is normal, but the attribute listing has moved into the middle of the page!

 

 

I addition, when I try to open the pdf data sheet for the product, I also get an syntax error:

 

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 ' manufacturers m on p.manufacturers_id = m.manufacturers_id, products_descriptio' at line 1

 

select p.products_id, pd.products_name, cd.categories_name, p.products_image, p.products_model, pd.products_description, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.products_status, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, s.expires_date, m.manufacturers_name from products p left join categories_description cd, manufacturers m on p.manufacturers_id = m.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id where p.products_id = '44' and p.products_id = pd.products_id and pd.language_id = '4'

 

[TEP STOP]

 

 

And when trying to open the printable catalog, it shows no products at all, but no error message!

 

 

Could some of these probloem be related maybe?

 

 

I'm a little rusty here and have tried fixing this for several days now. I would really appreciate if someone can help me out with some of these problems :-)

Thank you so much!

 

 

Kjolebutikken


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

What character encoding is your database using? What character encoding are your pages outputting in? What is the source of the text with the offending letters/dipthongs? Was it typed directly into osCommerce, was it imported via Easy Populate, or was it cut and pasted from another document? I suspect that you have a character encoding conflict, and invalid characters are in the database. At any point did this text go through Microsoft Word? I don't think that MS "Smart Quotes" is going to affect those particular characters (which should be found in Latin 1 as well as various Nordic encodings), but let's get all the information we can.

 

This sounds vaguely familiar. I seem to recall helping you with this a year or two ago. Have you done a search on this forum to see previous discussions?

Share this post


Link to post
Share on other sites

Regarding the Norwegian characters and encoding you can check these 3 fixes from Gergely in this post. I just applied them to 2.3.3 to get all functions to work with Norwegian characters.

 

You may have to check that mysql is UTF-8, and also all your language files in in utf-8 format without character faults.

Share this post


Link to post
Share on other sites

This sounds vaguely familiar. I seem to recall helping you with this a year or two ago. Have you done a search on this forum to see previous discussions?

 

Hi, you might have. I don't remember, but if so, I had to upload an older backup just to get the store visible so maybe an old problem is back. Anyway, I have searched for my old posts but can not find them.

 

Thank you for your suggestions, I will check the encoding. In some cases, for the product descriptions, the text is copied from some old HTML files and have origin from MS Word. But for the instance with the poayment module, in admin, where I have the same problem, the text is written directly in the admin fields.


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

Regarding the Norwegian characters and encoding you can check these 3 fixes from Gergely in this post. I just applied them to 2.3.3 to get all functions to work with Norwegian characters.

 

You may have to check that mysql is UTF-8, and also all your language files in in utf-8 format without character faults.

 

Thank you for your suggestions. I will check the encoding of the language files :-) And also the post you are refering to :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

OK, You are right, Some of the lfiles are in windows-1252 encoding. Mysql is using utf8_general_ci. Should it be utf8_unicode_ci instead?

I have started the work of changing all the language files manually in notepad++. many files so this will take time. Is there an easy way of changing all the Ø's simultanusly, all the Æ's and so on?

Thanks :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

utf8_* are both using the same encoding. The collation used for sorting is a little different, and may cause MySQL to get upset if it needs to deal with inconsistent collations.

 

Windows-1252 (CP-1252) is the single byte encoding used by Microsoft for Windows PCs in Western Europe and North America. Non-ASCII characters such as Ø and Æ will definitely be a problem when mixed with UTF-8, where they are two-byte sequences instead of a single byte.

 

If you're on a Linux PC, there would be the 'tr' command that might let you do character translations on a whole file at a time. If your server is Linux and you can access the command line, you might be able to use it. Don't forget that this would affect only files, not database text. If you have bad character data (wrong encoding) in your database, too much to fix by hand in phpMyAdmin, the only solution would be to dump the database to an .sql file and fix the file by hand or something like 'tr', then reload the database.

Share this post


Link to post
Share on other sites

OK, I have to start with the files then. If I have understood this correct, I need to change all my language files locally on my computer, fix the errors and save them as utf8. Then upload them again. From the post that www.in.no refers to above, it seems like I should save the files in encoding UTF-8 without BOM. I will keep the encoding utf8_general-ci on my database for now and see if this is ok with the updated files. If not, I will try with utf8_unicode-ci for mysql. I really hope that my database data is correct :-) I have a new pc with windows 8. But my server at my webhost is Linux. But I'm not sure where to look for the "tr" command. Is it in my filemanager that I open from my host cpanel?

Thank you :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

"tr" would be a Linux system utility (command), assuming that your host didn't remove it to keep customers out of trouble. You would have to access it via a command-line interface on the server, such as SSH or writing a "cron" command. You might ask your host about it, before investing a lot of time. I don't know if something comparable exists on Windows. The first thing is to understand exactly what and where your problems are with mismatched character encodings.

 

What you are aiming for is having all your text -- database, language support files, and all page output -- to be in exactly the same character encoding. Depending on what's been done with your system, and its history, this may be a little work or it may be a lot. As long as the encodings are consistent, it doesn't matter if you use UTF-8, Latin-1, or some other encoding, so long as it supports all the characters you want to use (including customer names and addresses,whatever country they might be in). Certainly, UTF-8 is the most universal character set, but you have to take into consideration what most of your site is already in. If you use UTF-8, be very careful that your editor doesn't drop Byte Order Marks into the files.

Share this post


Link to post
Share on other sites

Hi again, I'm sorry but I'm not sure what Byte Order Marks really is, but I will be doing all the editing of the files myself. The problem might be if I install contributions?

I have no edited all my language files and saved them as UTF-8 files and uploaded them to my site again. Now the problem with updating the products spesifications are solved :-))) But a new peoblem has occoured. I have no strange caracters instead of the Ø, Å and Æ in text on the website.See the introduction text here and also thye box heading for the search box at the top left on my index.php file:

 

www.kjolebutikken.no/butikk/index.php

 

Before I had problems with editing text in admin and this text not showing in the store if an Æ, Å or Ø was in the text. Also I had problems with text in the admin panel itself. But no strange caracters showing in the text displayd in the store. Now it is the other way around.


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

Regarding the Norwegian characters and encoding you can check these 3 fixes from Gergely in this post. I just applied them to 2.3.3 to get all functions to work with Norwegian characters.

 

Hi, did you do all the changes to all files mentioned in these fixes?

 

Thanks :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

I did it with notepad++ and search for the lines mention and compare them before i changed. But i i am not sure witch version you are running, and for 2.3.3 it was not that much work. Just a tip in GIT when copy text, stop before the line numbers when marking so you don't get the numbers with you when copy the code.

And check line endings so you dont breake or move the last line when copy in code.

 

In the post i linked to, in there are also code to run on MySql to change the db to the correct encoding.

 

If you have strange characters in your text, the "define language" tools in admin is easy and quick to use also. Took me 5-10 min to go through all languages files and check for bad characters and removing spaces outside of php closing tags. I found out that ftp and cpanel editors are not all utf-8 ready and they mess up the files and showing correct æ ø å in there but on webpage they show as garbage.

 

For 2.3.3 i uploaded Norwegian for catalog not long ago, the admin i still kept in English. Just copy english to norwegian.

Share this post


Link to post
Share on other sites

I see that you are running 2.2 series and then the changes may be different. But post a question in the other tread to Gergely and he may be able to tell you if it is to much different or not. Maybe a upgrade to 2.3.3 is better. If not you have to check all security updates to 2.2 to keep it safe.

Share this post


Link to post
Share on other sites

A Byte Order Mark is a group of 3 bytes added by editors to the beginning of UTF-8 files. They are an inverse ?, double-right guillemet, and i-umlaut (that may not be quite the right order) in Latin-1, but invisible in UTF-8. You do not want them in Web files. Any decent editor will give you a choice of whether or not to write a BOM to a file (always choose "no"), but Microsoft editors usually insist on writing a BOM. A BOM tells whoever is reading the file what order bits are in a byte, and whether bytes are swapped around (byte order). The problem on websites is that a BOM is outside of a PHP block <?php ... ?>, and so gets sent right away to the browser as text to be displayed. Besides giving 3 strange characters in the upper left corner of the screen (if not in UTF-8 mode), when the first text of a page is sent to a browser, it triggers the flushing of all accumulated "headers" to the browser. If this is too early, and the program (like osC) still wants to update some other headers, it's now too late and you get the "headers already sent" error message.

 

As for why you're having problems with characters, it's obvious that you still do not have consistent encoding throughout your site. You still have, say, Latin-1 for some text, and UTF-8 in other places. Don't forget that simply changing the database (the default is Latin-1) to UTF-8 may not change the actual bytes in any stored text -- you need to double check (in phpMyAdmin) that all text in the database is now actually UTF-8. If all your text is still Latin-1, when you tell phpMyAdmin to change to UTF-8, it should convert all the bytes, but maybe it didn't. Check all table fields to make sure each one (for text) is UTF-8. Language support files need to be inspected to make sure they are now completely UTF-8. And of course, check that the page actually claims to be UTF-8 on output (also check with your browser View > Character Encoding [or similar] to make sure your server isn't overriding your UTF-8 setting and forcing Latin-1).

Share this post


Link to post
Share on other sites

I see that you are running 2.2 series and then the changes may be different. But post a question in the other tread to Gergely and he may be able to tell you if it is to much different or not. Maybe a upgrade to 2.3.3 is better. If not you have to check all security updates to 2.2 to keep it safe.

 

Thank you for your feedback :-) I will check if maybe I should update my oscommerce version to the newest version before doing more with the encoding problem :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

As for why you're having problems with characters, it's obvious that you still do not have consistent encoding throughout your site. You still have, say, Latin-1 for some text, and UTF-8 in other places. Don't forget that simply changing the database (the default is Latin-1) to UTF-8 may not change the actual bytes in any stored text -- you need to double check (in phpMyAdmin) that all text in the database is now actually UTF-8. If all your text is still Latin-1, when you tell phpMyAdmin to change to UTF-8, it should convert all the bytes, but maybe it didn't. Check all table fields to make sure each one (for text) is UTF-8. Language support files need to be inspected to make sure they are now completely UTF-8.

 

Thank you for your feedback :-) I see that all my databasetables have encoding utf8_general_ci while the mysql is set to utf8_unicode.ci, so maybe this is the problem. I have received an sql from my host to run to change the encoding for all the tables, so I will try that (taking a backup first ofcourse :-)). All languagefiles (I have english and norwegian) have been checked and converted to utf8.

 

And of course, check that the page actually claims to be UTF-8 on output (also check with your browser View > Character Encoding [or similar] to make sure your server isn't overriding your UTF-8 setting and forcing Latin-1).

 

How do I check this?

 

 

Do you have an opinion of wheater it is best to upgrade the oscommerce version first or solve these encoding problems first? Will an upgrade solve part of these problems or will it not have any influence?

 

Thanks :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

In your browser, do a View > Character Encoding (or whatever they call the character set used). If it says "Latin-1" (a.k.a. ISO-8859-1, Western European) even though your page contains <meta .... charset=UTF-8">, it means the server is configured to override your meta tag and force Latin-1. I've seen that happen from time to time. Just one more thing to check when things are weird.

 

I think you should fix the encoding problems first, so at least you have some idea of what went wrong, plus you're starting the upgrade process with a clean, working system. Even if you make a clean install of 2.3.3 and upgrade the database, at least you'll know that the database is cleanly UTF-8 from one end to the other.

Share this post


Link to post
Share on other sites

In your browser, do a View > Character Encoding (or whatever they call the character set used). If it says "Latin-1" (a.k.a. ISO-8859-1, Western European) even though your page contains <meta .... charset=UTF-8">, it means the server is configured to override your meta tag and force Latin-1. I've seen that happen from time to time. Just one more thing to check when things are weird.

 

I think you should fix the encoding problems first, so at least you have some idea of what went wrong, plus you're starting the upgrade process with a clean, working system. Even if you make a clean install of 2.3.3 and upgrade the database, at least you'll know that the database is cleanly UTF-8 from one end to the other.

 

Hi and thank you :-)

 

I tested the output in google chrome browser and also explorer 10, for two of the pages I am having trouble with, my index.php and my product_info.php. Both files are saved as UTF-8 files but in the broser they show output as Western European (ISO-8859-1) and not UTF-8, but this might be the same more or less?. Would I need to contact my webhost to make them change the settings for the server? This might be a problem as I guess I share server with other people.

 

I will follow your adwise and get these problem fixed before doing the upgrade :-)

 

Thanks!


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

@@Kjolebutikken

 

Try to add this in your htaccess file, and check if there are other setting like this enabled with other character set.

 

AddDefaultCharset utf-8

Share this post


Link to post
Share on other sites

@@Kjolebutikken

 

Try to add this in your htaccess file, and check if there are other setting like this enabled with other character set.

 

AddDefaultCharset utf-8

 

Wow! This actually fixed the problem with the letters :-) I just have to change in some more files in addition to the language files, where there are norwegian language added. The generating of product decsription also works, though I can not copy text from word, but have to enter it in oscommerce admin.

 

Then there is only the other problems to worry about still :-)

 

Thank you so much! So happy I do not have to change anything in the database :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

By the way, code files such as index.php and product_info.php should be identical no matter what encoding you edited them in. Once in a while you'll find some idiot has hard coded a non-ASCII character in code (>> right double guillemet in breadcrumb trails is a favorite), but good programmers use HTML entities for anything that's not ASCII in code files. Language support files (prompts, labels, error messages, boilerplate text) should be in a designated character encoding, the same one as the database and page display.

 

The purpose of having only ASCII characters in code files is so they will work the same no matter what character encoding you use for language files, database, and page output. They are then encoding-independent.

Share this post


Link to post
Share on other sites

By the way, code files such as index.php and product_info.php should be identical no matter what encoding you edited them in. Once in a while you'll find some idiot has hard coded a non-ASCII character in code (>> right double guillemet in breadcrumb trails is a favorite), but good programmers use HTML entities for anything that's not ASCII in code files. Language support files (prompts, labels, error messages, boilerplate text) should be in a designated character encoding, the same one as the database and page display.

 

The purpose of having only ASCII characters in code files is so they will work the same no matter what character encoding you use for language files, database, and page output. They are then encoding-independent.

 

I guess one can never be sure and for us that don't know so much about programming, like myself, there will always be a risk installing the add-ons.

Anyway, thank you so much for all your help with this problem :-)) Really appreciate it!


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

Seems like I'm still having problems with the UTF8 encoding :-( The problem now seem to be only for text from the database and only for display in the admin section. Text from files show OK. When I edit text in the database tables it displays with strange caracters in admin tool. When I edit the text again in admin tool, suddenly it gets wrong again in the database, f.ex. with the product attributes and also for the payment modules! Any idea what I can try next? Do you think the settings for the encoding of my database is correct? There are several UTF8 types to choose from.

 

Thanks :-)


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

You can use this to alter the database, but you still may have wrong characters in the DB that you have to change after.

You might have to export the database and check it for wrongly encoded characters and alter them in the sql file. Then drop the db and reimport the fixed backup.

ALTER DATABASE `oscommerce_example_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Share this post


Link to post
Share on other sites

You can use this to alter the database, but you still may have wrong characters in the DB that you have to change after.

You might have to export the database and check it for wrongly encoded characters and alter them in the sql file. Then drop the db and reimport the fixed backup.

ALTER DATABASE `oscommerce_example_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 

Thanks :-) But the existing database tables are already UTF8_general_ci. I was wondering if I should change them to UTF8_unicode_ci since mysql is UTF8_unicode_ci?


Best regards

Kjolebutikken

Share this post


Link to post
Share on other sites

×