After Upgrade To SMF 2 My Member Names Became "????"!

[SAFAD]

Hi All
I Don't Know Why& How I Did This
I Just DId It
No Back Ups Nothing More Than My Dead Forum

All My Members Upgraded To ? Only The English Ones Didn't (Arabic )
But The Categories Topics Evrything Else was Good
I Entred Phpmyadmin
Then i Found Not All The Tables Are UTF8 They Was Latin1
i thinked About It A Lil
Then I Dicided To Convert To UTF8
There Was 76 Table
So I Mad A Script And Converted Em All In No Time :d

But At The End It Is All The Same
Please AnyOne Help Me
Best Regards

[Arantor]

How exactly did you perform the conversion?

[SAFAD]

uploaded full upgrade package from the download section
run upgrade.php
PS : I Didn't Find "Use UTF-8 Charset "Option When Converting
Then Delete Upgrade Files And See The New World

[Arantor]

You haven't answered my question: you've said you wrote a script to do the conversion:

So I Mad A Script And Converted Em All In No Time :d

What did this script have in it?

[SAFAD]

no
i didn't make a script to upgrade
i did it to convert charset to utf-8
and this after i did all the possible to fix it from the forums

this is the content

<?php
//connection code
mysql_connect("localhost","usr","pw");
mysql_select_db("db");

// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
    foreach ($row as $key => $table)
    {
        mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
        echo $key . " =&gt; " . $table . " CONVERTED<br />";
    }
}
?>

thats all

[Arantor]

From phpMyAdmin, go into your database, and execute the following query and please tell me what it says.

Code Select Expand

SELECT * FROM smf_settings WHERE variable = 'global_character_set'

Of course if your table isn't called smf_settings, please edit the query to reflect the settings table.

[SAFAD]

From phpMyAdmin, go into your database, and execute the following query and please tell me what it says.

Code Select Expand

SELECT * FROM smf_settings WHERE variable = 'global_character_set'

Of course if your table isn't called smf_settings, please edit the query to reflect the settings table.

ok ok
but bro
i can handle silly things like

Of course if your table isn't called smf_settings, please edit the query to reflect the settings table.

:d:d:d

[SAFAD]

O
It Shows a table

variable is global_charcter_set
and value is UTF-8

[Norv]

Please check, for the tables that were utf8, what character set is set, for each of their text columns.
If there's a chance to recover the data, then each of the columns must also be converted.

But before doing this and any other operations, please, make a backup of the database, to avoid further losses.

[SAFAD]

I Didn't Understand You Correctly
There Was Alot Of Clomuns character set : latin1_swidish_ci
and some else : utf8_general_ci

Is This What Did You Mean ?

[Norv]

I'm afraid that is exactly what I meant, you still have columns that are latin1.

All these three must be utf8: the database itself, the tables (you made a conversion script for them), and the columns in the tables.

But please, before anything else, make a backup of the database.
Also, I understand you have no recent backup. But do you have an older backup? When was the last backup made?

[SAFAD]

hmmmm
i don't remember exact
but far long much that i can't back it up
more than 100 member registred (really rarly to hapen to me)
and maybe another more 200 topic

and don't think they are till now latin1
i converted em to UTF-8

I Will Make A BackUp & Wait The Next Step From You

[SAFAD]

done with the backup
whats next ?

[Norv]

Well, honestly I'm afraid the data that doesn't appear anymore in this database is already corrupted and cannot be recovered. So please keep that old backup at hand. You may need it.

But before giving up, I propose to try to make sure the conversion to utf8 is complete. (and maybe, just maybe there will be something that will prove the pessimism to be wrong.)

Please tell what the following query results in:

Code Select Expand


show create database your_database_name;


[SAFAD]

Its Allways On Hand
AnyWay
I Can't Create DB From Any PHP Script
Cuz My Acoun't Doesn't Have Previligies
I Can By My CPanel (Confixx)

[Norv]

Please, try running the statement. It only shows what "create database" did when the current database was created. It doesn't try to create another. (please replace "database_name" with your current database)

[SAFAD]

ah ok

CREATE DATABASE `:)` /*!40100 DEFAULT CHARACTER SET utf8 */

UTF-8

[Norv]

Okay.
Now, please select your database, and look at its "structure" in phpMyAdmin: you will see the list of tables.
You said before they are all utf8, can you please say though, which were latin1 ?

For each of them, please look at the table's structure. You will see the column definitions. Or, you can run:

Code Select Expand


show full columns from table_name;

Are there any of the columns latin1?

[SAFAD]

Members table was Latin1

there is no latin1
there is NULL
and UTF8_generale_ci

Here Is A Pic

[Norv]

Ah okay, thank you.

The data in this table, in phpMyAdmin, does it also show as " "? For all text-based columns, or only member names?

[SAFAD]

yep it shows them

[Norv]

All of the text columns?

---

Please right click the phpMyAdmin page, and "view source": in the html source, there might be a meta like

Code Select Expand


<meta http-equiv="Content-Type"
    content="text/html; charset=utf-8" />

What charset is it actually, if any?

[SAFAD]

utf-8
this is the charset

[Norv]

I think the data from this particular table was lost.

Does your host offer anything like automatic backup of the databases? Many do.

[SAFAD]

naaaaaaah
it doesn't

[SAFAD]

so this means
member lost ?

[Norv]

Unless someone else has a better idea (please?),
I would say that:
- the data about members from the old database backup could be recovered, and merged with the current database. It seems hard though. But, if successful, that will mean that all members data (from the members table) that was saved a long time ago will be available again, and any edits to this data meanwhile will be lost.

With the new members, there are two ways:
First:
- the new members can be preserved, but the signatures etc., all other columns from the members table, for new members, will be empty ("").
- the names of the new members ... I'm not sure about the names. At first sight it seems to me there is no reasonable enough way to try setting them back. (The member names are saved in other places, for example, in the smf_messages table when they post. But I have to think a little to see if it can be copied from there to the members table in a reasonable way.). But there are passwords too, which cannot be recovered, nor reset properly. And more issues.
Second:
- there may be less work if the new members remake their account, with the same name they used to have. And then, try merging the new accounts with the older posts and topics and so on.

I can try to do some of these.

But after this, I'd say you will still have some manual editing to do. I will be back with details.

Anyway, if you agree that I do the try to recover all that it's reasonably possible, please let me know. I will need both databases, and in case you agree, I will PM you an account to use for it.

[SAFAD]

well certenly its a long message to read
what i get i will lost my new ARABIC Members Only
Thats Might be simple to fix
I Upgrade My Old DB
Then Delete From The Current Table Arabic Members
Put The Old DB Upgraded Member Table In The New One
Then IT Must Work For Sure
What You Think About This Idea ?

[SAFAD]

NOOOOOOOOOOOO
There Is No Old BackUp

[Norv]

How so?

[SAFAD]

There Is No Old Back UP
IT IS DELETE After Hard Disk Crash
O Man

[Norv]

First, please allow me to answer the previous point (so that it is no misunderstanding):

I Upgrade My Old DB
Then Delete From The Current Table Arabic Members
Put The Old DB Upgraded Member Table In The New One
Then IT Must Work For Sure
What You Think About This Idea ?

No, delete is not a solution. The idea is the old members exist in the new database and in the old database, and they have the same ID, and IDs are important, if you delete them then after importing the old ones you have new IDs, so all kinds of things will not work, like attachments will not "belong" to them, or other broken links. No, delete is not a solution.
A series of updates, instead. As I said, it's tedious, but it can be done.
(if two such backups exist)

[SAFAD]

ok ok i understand
but
there is no BackUp !

[Norv]

Yes, I'm really sorry to hear that. Are you sure there is no other? From SMF it is so easy to make a backup, it's in Admin > Forum maintenance, it's just a click and press a button, save to disk, done.
Well, anyway, there are also software applications that make automatic backups.

[Norv]

But now. What can be done: can you please tell how many members you have ? with non-english names, as you mentioned.

[SAFAD]

OK OK
They Are About 105

[SAFAD]

No BackUps
Alot Of Members

[Norv]

Please run:

Code Select Expand


select concat('UPDATE smf_members SET member_name = ', quote(poster_name), ', real_name=', quote(poster_name), ', personal_text=\'\', location=\'\', email_address=', quote(poster_email), ', member_ip=', quote(poster_ip), '  WHERE id_member = ', CAST(id_member AS CHAR), ';') from smf_messages order by id_member;

The query is not tested, it might have some silly syntax errors. But the idea is: there's actually a lot of data to be recovered from smf_messages at least.
The query results are UPDATE statements., like the attached screenshot. Please run then "Print view (full texts)", and ignore the printing: it also makes a listing in browser. Save the listing as .sql file, or .txt file. Then open it in a text editor, and remove the first lines (they are not SQL statements).
Then, run the rest of the SQL script (the UPDATE statements).

I will probably be not online for the next ten hours or so. But please keep us informed. Thank you!

[SAFAD]

UPDATE smf_members SET member_name = ' ?? ?', real_name=' ?? ?', personal_text='', location='', email_address='[email protected]', member_ip='192.168.1.66'  WHERE id_member = 3;

still the same

[Norv]

Please, make a screenshot with the results of running the first code, the select concat( ... etc.

[SAFAD]

This Is The Screen

[Norv]

Ah. But then, not only smf_members was affected, but at least smf_messages as well.

Please let's see what is intact:
Please check each table structure, to see if there is any column latin1.
Post here which are or were latin1, at lest we should know exactly what was damaged.

[SAFAD]

Now Its Impossible to remember
76 Table

Look I Remember Important things

Messages And Boards Were UTF8

Members And Member Groups Were Latin 1

Thats What I Remeber For Now

[Norv]

What character set has now smf_messages.poster_name? The column, not the table.
Please run:

Code Select Expand


show full columns from smf_messages;


[SAFAD]

Null
Utf8

[Norv]

I see, thank you.
Please, restore the copy made at the beginning of these tests.

On another note, the usernames are missing in any table.
You still have the emails, it seems, and the IPs, but not the usernames, nor the passwords, or it will look that way.
I would propose that we try inserting the email addresses in the name field, and write some warning on the login page about it. 
Like this: "Please reset your password", and after you receive the notification for the password, log in in the forum using your email address, not username".

Users can always set a different "display name", to be used, so that their email is not displayed anymore.

[SAFAD]

a great idea
but a failure success For The Ones Who Use Arabic On Pass !!!
ok ok thx for this bro
it took alooooot of time
thank u very much

[Arantor]

It shouldn't change their password at all; it's not stored in Arabic.

[Norv]

Note that the rest of the table must be at least cleaned up (from the "" in other fields) even if the email trick works for the moment. And then, usenames (the new usernames, well) need to be updated in posts as well.

[N3RVE]

I'm going through old 'unresolved' topics, do you require any further support?
If they are none, please mark this topic as solved to indicate

-[n3rve]