Strange characters after upgrade from 2.8.3 to 2.12.1

[svsanchez]

Hello!

I just upgraded www.distribuidoraal cance.com from version 2.8.3 rev 1645 to 2.12.1 and the upgrade went very well, BUT we are having a problem with strange characters in some of the pages names and on Bakery products:

The site is in Spanish, and so we use some special characters like á, é, í, ó, ú and ñ. These characters have been changed to strange combinations, for example:

Crédito <--- Should be Solicitud de Crédito
Útiles <--- Should be Útiles
Presentación <--- Should be Presentación
Johnnie Walker® <--- Should be Johnnie Walker® (I think! )

How can I fix these characters both in the pages and in the Bakery products titles?

You can see in this link that the Bakery product's title has some strange characters (Años <--- should be Años) but the extended characters are correctly displayed in the description:

https://www.distribuidoraalcance.com/pages/bakery/whisky-chivas-regal-12-anos-1662.php

Thanks for your help!

[hgs]

This link might help
https://forum.WebsiteBaker.org/index.php/topic,30402.msg212078.html#msg212078

[jacobi22]

is it possible, to send me the backup-sql as ZIP or GZ or to send me a downloadlink for this? i will do my best, to convert everything in a short time

my email: uwe@jacobi22.com

[svsanchez]

Hell Jacobi, thank you for your offer. I have uploaded the mysql backup to Google Drive and shared the file with your email.

I hope it's something easy to fix.

I do have some other sites that I have to upgrade from 2.8.3 to 2.12.1, is there something we should do first to avoid this from happening?

Kind regards,

[jacobi22]

I do have some other sites that I have to upgrade from 2.8.3 to 2.12.1, is there something we should do first to avoid this from happening?

there are some possible problem's
- if you use utf8 from the beginning in this project's, there are no problem's with special characters

- some people start with latin1, but the server change the default charset in the past sometime's - in this case, you have a mix between latin and utf8-characters. Also possible: a WB-Upgrade to WB 2.10.x and up - WB use UTF8 since WB 2.10.0 as default charset and as reaction of the php-changes (UTF8 as default_charset) in PHP 5.6 and up

- a clear latin-project from the beginning - that's simple and normalize no problem, to display it without errors in WB or other systems

in small project's, you can search and replace, but never use a batch-replacement, only step by step, letter by letter, but you have also to change the table collations and field collations inside of the tables.
I use a php-script for this, but i have to add every character in a array before i start this converter. at the moment, i have ~90 characters in this array, only latin and special characters like ñ, Ë or ©, no cyrrilic or others.
Maybe, it's not enough for your project, but it's only a question of time

feel free to send me more SQL-Backup's, if you have to convert it

[svsanchez]

Hello Jacobi, thank you for your reply. I will try upgrading the other projects first to 2.10 as you said.

Regarding this project, I understand you will execute a php script to "clean" my DB, then you will send it back to me and I will have to "restore" the DB from Cpanel, is that right?

[svsanchez]

Hello Jacobi, I just read your email and sent you the DB backup as an attachment.

[jacobi22]

the replay is on the way

Regarding this project, I understand you will execute a php script to "clean" my DB, then you will send it back to me and I will have to "restore" the DB from Cpanel, is that right?

yes, that's right
you have to delete the old tables from this backup in phpmyadmin before you import the converted file from the mail, but be carefull and detele only the tables with the same prefix, in this special case, the prefix was wb_  (prefix are the leading letters on every table name, like wb_addons, wb_settings etc)

[svsanchez]

Hello Jacobi22, thank you for your reply and email, unfortunately I don't understand, I hope my following remarks make sense:

1) In your email you begin by saying that the new file I sent you was not corrupt but that you couldn't use it because it was too big and your editor can't handle files larger than 2 MB.

2) However, you sent me a "converted" file. I opened the "converted" converted file in a text editor and noticed that the strange characters are all still there, so it seems you probably sent me the same file again 

3) Finally, you tell me that the best way to use the converted file is to delete all the tables with the prefix wb_ in my database and then import the "converted" file. However, ALL my database tables begin with the prefix wb_ as you can see on the following picture!!!



So, could you please confirm to me that:

1) You were able to "convert" the strange characters in the database?

2) You sent me the right "converted" DB?

3) It's OK to delete ALL my database tables -even if not one remains- and replace them with the correct "converted" DB?

Thank you!!!

[jacobi22]

nice, to found content from my private mails here in a public forum

[jacobi22]

So, i compare the original sql with my work and i think, that i replace 98% - 99% of the strange characters  like
Baterías AAA 2*1 vs Baterías AAA 2*1
Scotch Brite Paño Absorbente vs Scotch Brite Paño Absorbente

but not
TOTONICAPÁN vs TOTONICAPÁN
bacause, that is a char, that i cannot display on my local machine. In real UTF8, it must be this character -> for the lower á and Á for the upper Á, but it isn't in your SQL-File
It's very easy, if you know, what you want, but dont forget, it's a foreign language for me. If i see the content in this project, i think, guatemala is the right location, and Totonicapán is the word, what you need - but it is, what i think, not, what i know

somewhere in the top, i write a description, how i work, a list with replacements - ich have only ~90, but in utf8, it's possible to display every character from all over the world, these are not hundred's, not tousend's

[jacobi22]

the original


my converted text


the only unconverted character (what i found to this moment) is the upper Á, but see in the source, it's a unknow sign



now say it again, that i send you the same file back.....

[jacobi22]

theme closed for me here - if you have a problem with a private mail, send a answer as private mail!

[jacobi22]

not closed, one more, to explain it

screenshot 1 come's from the Netbeans-Editor, one of the best editors, he work's without problems in UTF8



but i wondering about the unknown char there, so i try it with another editor, this one show the real SQL-Code - what we see in PHPmyAdmin is the translation like the output on the webpage

the same line in the code - it show's a double converted special char Á - i try to post it here -> Á -

this is not a valid char and i dont have a combination like this in my array - but it will show the effect, when you use the "wrong" editor. The language is (i think) spain and in this language, you have a lot of strong characters


two screenshots more

here the original and real SQL-Code in the converted file



and the same line in Netbeans



or from Notepad



or Notepad++



but not in a editor, that show's real SQL-Code

it is, what i write in the private mail

1# delete from the database all tables with the prefix wb_
2# import the converted file from my email as zip-file, choose UTF8 as Format in the Import-Dialog
3# search in the editor for special wrong words, like TOTONICAP (without the wrong char!!) or use the search in PHPmyAdmin
4# for the Word "TOTONICAP" i get 23 Hits, go to the beginn of the line from every hit, there you found the table (like mod_bakery_items) and the item_id or section_id.
Go to this table in phpmyadmin and this item and replace the wrong char with the real letter directly in the database. PHPmyAdmin makes the translation for you

Important!!!: dont try to replace the wrong chars directly in the sql-file, if you use the wrong editor for that, it will destroy the complete converted file

P.S.: 23 Hits in a file with 21 Million chars, i think, thats not so bad   

[svsanchez]

Hello Jacobi, thanks a lot for your excellent and long reply!

I am sorry if posting this to the forum wasn't the right thing to do (I thought it made more sense to post it here so that the solution remains posted for the future, both for my reference and anyone else having the same problem).

Just one last thing I need clarification on:

ALL my tables have the wb_ prefix. Does that mean that I have to delete ALL my tables before I import the converted DB?

Thanks again!

[jacobi22]

ALL my tables have the wb_ prefix. Does that mean that I have to delete ALL my tables before I import the converted DB?

YES, thats correct - delete then all tables in this database.

P.S.: if i need a test in a subfolder, i use often the same databese, but different prefix. Other customers / users have only one database, but different installations, maybe also different CMS - thats why, i say: delete (only) the tables with this prefix, in your case, prefix "wb_"

[svsanchez]

BAD NEWS!

Unfortunately, after deleting the tables and importing the converted DB, the site broke:

First, there was a message "There was an uncatched exception: CAPTCHA-Settings not found" but I was able to fix that thanks to your SQL injection solution provided here: https://forum.WebsiteBaker.org/index.php?topic=29275.0

Once that worked, I could see that the strange characters were gone, but the whole site wasn't working anymore as it should:

1) The [MINICART] didn't show the minicart, instead it shows the text [MINICART]
2) There was an error saying that the column "ORDER" didn't exist
3) For some reason the site was all messed up, showing things in places it wasn't supposed to, as if the template was messed up!

I had to restore my DB backup and the site is working again, BUT the strange characters are back 

[svsanchez]

So, since I had offered to have this fixed by today, I tried doing the following via phpMyAdmin:

UPDATE wb_mod_bakery_items SET title = REPLACE(title, 'á', 'á');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, 'é', 'é');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, 'í', 'í');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, 'ó', 'ó');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, 'ú', 'ú');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, 'ñ', 'ñ');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, '®', '©');
UPDATE wb_mod_bakery_items SET title = REPLACE(title, '®', '©');

Then I repeated the same commands but used "description" instead of "title".

This allowed me to change the name and description of the products in the Bakery store, however when I go a product's page, the page's <title> is still showing with the strange character. I searched on the different wb_mod_bakery tables but couldn't find where it's taking the <title> tag content?

[jacobi22]

There was an uncatched exception: CAPTCHA-Settings not found

correct!
if there is no table for the captcha-module inside the backup, the converter cannot build this

Hello!

I just upgraded www.distribuidoraal cance.com from version 2.8.3 rev 1645 to 2.12.1 and the upgrade went very well, BUT we are having a problem with strange characters in some of the pages names and on Bakery products:

and why did you send me the backup from the old WB 1611??



the backup was for WB 1611, of course, you run in problems, if you use this in WB 2.12.1
to make it short..... ask me never again for a help. if it's not possible, to send me the complete package (files & database-backup). it make's only trouble for everyone, if it's not possible to test it. trouble for nothing........... 
The user from the thread about the WB 2.6.7 version send's me the complete project and has two hours later the latest upgrade finish, wb works, template and something more is repaired - and everybody is happy. - that's the right way

[svsanchez]

Mein Gott!!!

I'm so sorry for that, I really didn't think about it, I didn't think about making a new backup after I upgraded to 2.12.1 until I imported the file you sent me! Lesson learned! (But why were the strange characters also in the WB 1611 version?)

So sorry to have bothered you for nothing Jacobi, I hope you can help me converting the right file if I send it to you by mail 

[hgs]

To be on the safe side, jacobi22 send the whole project including the database. And everything will be fine.
 

[svsanchez]

Hello, you need to understand that I am in Guatemala and we don't have broadband. I would have been very angry if someone sent me something bigger than 40 MB by mail and therefore I would have never thought about doing the same!

At this point, I manually changed all the strange characters from the products, however the <title>Product Name</title> STILL shows the strange characters and I can't find where to change this.

Could someone please help me, thank you!

[hgs]

Okay, sorry, I didn't know that.
You were very busy, the page looks very good at first sight.
Do you have a link to the problem cases for us?

[jacobi22]

only as info

the real database backup (SQL-File) has a size = 21 MB
as 7z-File, it has 330 kb 

the same for the project files... it is not needed, to send a zip-file with 250 mb - the old Backup-Modul can build a zip from the project and add this to the root directory of wb, a link to this file is maybe 1kB, not more

the question was very simple - you need the help, not i. it is not my intention here, to be the bad boy, i work here for some nice words at the happy end of the job. But if this work brings only trouble, where is the sense for me, to spend more and more time in job's like this. It was not my fault with the wrong backup, but nobody read this - everybody read, the jacobi22 make's s***, dont trust him

now, you ask for more help, but nobody know's the exact situation. i can see the link in the old backup file, but i have to search over the whole project, where is the exact place for this problem. i saw maybe 50 products, but no one of this with an error or a char-problem. Possible, that i have to spend more time, but also possible, that the problem is already solved in the last week, also possible, that i search on the wrong place, for example: product overview vs product detail page - that's different.

So again the words: you need the help and the supporters here the informations, to help. Give the info's and you get the help

[svsanchez]

Hello Jacobi, I don't know why you talk about yourself being the 'bad boy', I'm sorry if at any moment you felt I was complaining but in all honestly it wasn't my intention, to the contrary: I am very thankful for the help that you and other forum members have provided. Maybe my real feelings are more difficult to express since English is not my main language... Sometimes it's a little difficult for me to understand what you are trying to say too but I always trust you are trying to help and I appreciate!

For example, see how many times I asked for confirmation if I had to delete all my tables with the wb_ prefix since I wasn't sure because it meant deleting ALL of my tables! You are the expert and I try to follow the steps you provide the best I can. Please don't ever think I'm being rude or not polite towards any of you!

Now regarding the problem with the strange characters, I was able to find the problem while looking for an example URL to post here. The <title></title> tags were NOT using the product's name but the page's name! I hadn't noticed this since the browser tabs were only showing me the first word, so for example when I accessed the product 'Azúcar Caña Real 1 Lb' the tab showed only 'Azécar' making me believe it was using the product's name, when in fact the names of the subpages had also been messed up. I modified the names of the pages and now everything works as it should!

Again, thank you all and sorry for any misunderstandings.