Restoring old gallery in Bulgarian Cyrillic

[usmivkata]

Hello,
I am trying to revive an old gallery that we used many years back. The version is 1.4.10 (stable). It has not been used for a while and now am trying to bring it back to life. Unfortunately I meet all kind of problem.
First the error on the top:
Notice: Undefined offset: 8192 in .../include/debugger.inc.php on line 112

This, as I see is due to the outdated version, and an update is resolving it, however the comments on the front end disappear after the update, they still exist in the db though.

Second issue:
The gallery is running mainly in Bulgarian Cyrillic and every user text in it is completely broken.
Even after update to the latest version when I try to add Cyrillic text, it will only show question marks, it will do this even when editing the site description.

Third, probably the worst problem:
The categories are broken, could be due to the language issues and the albums are probably completely missing, can not find any trace of them.

This is probably related to the fact that even though all the user's pictures are untouched in the album folder, they do not appear anywhere in the gallery.

The db collation is cp1251_bulgarian_ci, I tried changing it to UTF8, but this is only breaking the Cyrillic text in it.

Shortly I got an empty looking gallery with some question marks for site description, however the pictures look fine in the file folders and the db is looking good too from the inside.

Hopefully someone can help me with a good suggestion.

[gmc]

Undefined offset: 8192 in .../include/debugger.inc.php on line 112

This, as I see is due to the outdated version, and an update is resolving it,

correct. The upgrade will fix this

however the comments on the front end disappear after the update, they still exist in the db though.

a link to the gallery will help me see what you mean

The gallery is running mainly in Bulgarian Cyrillic and every user text in it is completely broken.
Even after update to the latest version when I try to add Cyrillic text, it will only show question marks, it will do this even when editing the site description.

The rest of your issues appear to be character set related - which can be very tricky...
What value is specified in your CPG config for Character Encoding? (we recommend this AND your database be set to UTF-8, but you indicate that isn't the case below for your database. These need to match to display properly.)

Third, probably the worst problem:
The categories are broken, could be due to the language issues and the albums are probably completely missing, can not find any trace of them.
This is probably related to the fact that even though all the user's pictures are untouched in the album folder, they do not appear anywhere in the gallery.

again a link would help.. I would expect the albums to still be there even if a charset issue is not allowing proper text to display.

The db collation is cp1251_bulgarian_ci, I tried changing it to UTF8, but this is only breaking the Cyrillic text in it.

You can't just change the db collation without converting the data to the new format... Several threads exist explaining how (I'm only on my tablet now and can't easily search... Search for 'convert cast')
PLEASE BACKUP YOUR DB before attempting any charset changes!

[usmivkata]

Thanks for helping.

This is the link to the updated gallery.

http://is.gd/JLNkGI

I did now the following conversion

ALTER TABLE cpg_vote_stats CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

but could not do the following, as I am getting errors. Shall I alter it somehow for Cyrillic, not sure?

UPDATE cpg_categories SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);

This fixed the Cyrillic to some point and now I can add a site description. However, no albums are visible the only image that you can see there I uploaded now just for testing.

I renamed the old categories to Latin names but still no pictures are shown.
---------------
The following link is to a version with no changes. As you can see the Cyrillic is broken and nothing visible on the front, however the comments are still there but only with thumbnails, no full sized pictures are visible.

http://is.gd/Wqas3C

[usmivkata]

Are everybody on vacation? No one here can help?

[Αndré]

I suggest to fix the album issue in your old gallery. I guess there have been some trouble with the albums database table (crashed or whatever) and it got flushed, as I can see your pictures at least with the lastup meta album: http://racingpigeons.net/gallery1/thumbnails.php?album=lastup

Please use a tool like phpMyAdmin to have a look at the albums table. If it's empty, we need to restore the albums. If you have a database backup/dump (even a very old one, it's better than noting), you could try to restore the albums table. If not, we'd need to re-create all albums. Depending on the number of albums you used to have in your gallery, this might be a quite annoying and time consuming task if you try to do that manually. A simple SQL query could also re-create all albums, let me know if you need help with this.

As this is a quite complex issue, it would be helpful to have a look at your database. If you want, I'll send you my contact details where you can give me access to your server, or send me a database dump of your old gallery. Then I'll try to fix the issues for you and also update your gallery to the latest stable release.

[usmivkata]

Andre, thanks a lot for suggesting to help. It seems like the albums are gone from all the backups that I have, this must have happened long ago. Unfortunately we stopped using this gallery years back.
So probably the only way is to recreate the albums, if you have some instruction how this shall be done I can give it a try.
Also I can give you access or send you a copy of the db. What do you prefer? Let me know.

[Αndré]

I just sent you a PM.

[usmivkata]

Andre, thanks a lot for helping.
I have been working on recovering (renaming) the albums to their previous condition, however still have some problems with the encoding. I have converted the db to utf8, but the code below is giving me errors and I am not able to fix the already broken Cyrillic.

UPDATE cpg_categories SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);

I would assume there shall be a way to fix it since it looks kind of ok in the db (something like this СЃРїРѕСЂС,ен), but only question marks are visible on the front end.

[Αndré]

Honestly, this would be also a trial & error approach for me. I just tried to access your phpMyAdmin interface, but unfortunately the credentials you sent me aren't valid anymore.

[usmivkata]

Thanks for helping again. I just sent you an email with login details.

[Αndré]

I just used one of your messed up titles:

Командо СЃ наградаС,Р° Р"РёРєРёРЅ

and put it into this tool: http://2cyr.com/decode/?lang=en

According to this tool the source encoding is utf-8 and it used windows-1251 to display the text correctly. Unfortunately I currently don't know how we need to use that information to fix your issue. But good news is: the data is still there

[usmivkata]

Yes, I saw that. Source encoding utf8 displayed as windows-1251, the thing is that it is selected utf-8 in the control panel and also the db is converted, however the old db used to be windows-1251.
Its a lot of work to fix this manually, any idea what can be done?
I see someone used the following code to fix broken sings, but it does not work for Cyrillic. Do you know how it can be re-worked?
UPDATE cpg_categories SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);

[gmc]

I see someone used the following code to fix broken sings, but it does not work for Cyrillic. Do you know how it can be re-worked?
UPDATE cpg_categories SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);

The first USING is the old char set from the database - the second USING is the new char set...
A SHOW CHARSET in MySQL includes:

Code Select Expand


Charset Description Default collation Maxlen
cp1251 Windows Cyrillic cp1251_general_ci 1

So I would expect something like:

Code Select Expand

UPDATE cpg_categories SET title= CONVERT(CAST(CONVERT(title USING cp1251) AS binary) USING utf8);
should work...

Backup first please...

[usmivkata]

Thanks
I tried that but get the error

#1054 - Unknown column 'title' in 'field list'

[gmc]

Oops... 'title' is from album...  'name' is the field to use for categories...
Thought I fixed my old post (was a typo when I first did it) - but must be other places as well.

So try this:

Code Select Expand

UPDATE cpg_categories SET name= CONVERT(CAST(CONVERT(title USING cp1251) AS binary) USING utf8);

[usmivkata]

It worked now, but all the signs in the cpg_categories name field turned into question marks.

UPDATE cpg_categories SET name= CONVERT(CAST(CONVERT(name USING cp1251) AS binary) USING utf8);

[gmc]

all the signs in the cpg_categories name field turned into question marks.

In phpMyAdmin (or equiv)?  or in CPG??
If CPG, what CONFIG setting do you have for CHARSET?  That gets put in the html to tell the browser how to translate...

[usmivkata]

Sorry my mistake for the previous post, I did it wrong there.

Now it works, but it fixes just the signs in the db, so they become readable, however on the front end nothing changes, so I only see question mark where it is supposed to be Cyrillic.

[gmc]

If by the 'front end' you mean CPG - one additional change to try...
You changed the tables to UTF-8... but sometimes we have seen that MySQL doesn't correctly open the connection (varies by host, MySQL defaults, and maybe other reasons...)

In include/config.inc.php add the following line (location doesn't matter)

Code Select Expand

$CONFIG['dbcharset'] ='utf8';
This will force CPG to explicitly tell MySQL the data is encoded as should be treated as UTF8 when the connection is opened...

If that doesn't work - I would like a copy (backup) of your categories table from BEFORE any changes were made to do some testing in my sandbox...  (unless Αndré has another approach since he was already working this...)

[Αndré]

unless Αndré has another approach since he was already working this...

No, but using $CONFIG['dbcharset'] would have been also my next advice