New PMs not showing after collation change

[s²]

I recently changed my database tables to utf-8 from latin-swedish.  Now any new PMs are not showing up at all, and they affect the number of labels as well.


http://***/index.php?action=pm

8: Undefined index: name

File: /home/pnet/***/Themes/default/GenericMenu.template.php (folder sub template - eval?)
Line: 616

http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Themes/default/GenericMenu.template.php (folder sub template - eval?)
Line: 616

This guy had a similar problem but his solution didn't work for me.

[Illori]

please apply http://custom.simplemachines.org/mods/index.php?mod=2054 and then post the new error message.

[s²]

I should note I'm using RC4 so that option is built in.  Here's the new error messages.


http://***/index.php?action=pm

8: Undefined index: name

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 616
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 616
   
http://***/index.php?action=pm

8: Undefined index: name

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 611
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 611
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 609
   
http://***/index.php?action=pm

8: Undefined index: name

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 457
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 457

http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Themes/default/PersonalMessage.template.php
Line: 455
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 375
   
http://***/index.php?action=pm

8: Undefined index: name

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 374
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 373
   
http://***/index.php?action=pm

8: Undefined index: id

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 366
   
http://***/index.php?action=pm

8: Undefined index: unread_messages

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 242
   
http://***/index.php?action=pm

8: Undefined index: messages

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 240
   
http://***/index.php?action=pm

8: Undefined offset: 0

File: /home/pnet/***/Sources/PersonalMessage.php
Line: 240

[Arantor]

Did you tell SMF you'd moved from UTF-8 to latin-swedish?

Better question, why make the move at all? There is a reason SMF doesn't let you convert back from UTF-8 once you move to it...

[s²]

You definitely misread me.

I changed to UTF-8 in the admin a long time ago but just realized the collation of my tables were still latin swedish.

I switched from latin swedish to UTF-8.  After I did that my initial problem occurred.

[Arantor]

Ah, sorry.

If the collation of the tables was still latin-swedish, that means the data was physically being stored in ISO-8859-1, rather than UTF-8. Changing the collation changes the character set, but it may not change the data itself, which means you potentially have ISO-encoded data being treated as UTF-8 when it isn't, which will make very invalid UTF-8, so the browser ignores the entire element (i.e. the PMs)

Change the collation back, what happens? (Oh, and I hope you had a backup from before altering the tables)

[s²]

That doesn't make sense though, as I changed the data set to UTF-8 in the admin console a while ago.  Not changing the collation screwed up my data when I upgraded.  And no I don't make backups.  My database has been screwed over so many times with char set problems that it's marred up the most important threads on the site, based on the fact that they were written in Word.

[Arantor]

You changed the data in the admin panel except all it actually changed was one setting in SMF. If the tables have a collation of latin_swedish_ci, they still have character encoding ISO-8859-1.

It isn't possible to have the data in UTF-8, treated properly, and have the latin_swedish collation. Reason: collations are lists of characters, which tell MySQL how to order letters for sorting purposes. Approximately half the characters in the Latin/Swedish character set (i.e. ISO-8859-1) are not legal UTF-8 characters - UTF-8 uses the most significant bit (the value 128 bit) to indicate whether a given character is part of a multi-byte character, while Latin1 is a single byte character set and instead uses the most significant bit to select different characters within itself.

[s²]

Is there such thing as a script that will change the character encoding to UTF-8?

[Arantor]

There isn't a script here for it but it should be possible to do, trouble is it must be done on every column individually. http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html has some examples of scripts that might be suitable though.

[s²]

Nope, converting the data to a new charset didn't work.

[Arantor]

So what did you convert to what charset, and what do you mean by 'didn't work'. How so?

[s²]

I converted to utf8 and recent PMs are still not showing up.

[Arantor]

All the tables? All columns in all tables? What collations do they have now? (And how, exactly, did you do it? I have a feeling you haven't updated the content, but merely the table specification, which means you will have to revert the collations to latin-swedish, then convert the columns with charset conversion as well as collation conversion)

[s²]

All tables and their columns have been converted.  The collation is utf8_general_ci.

[s²]

I used the php script post at the bottom of the link you gave me.

[Arantor]

I would ask how you converted them but I already know the answer - you didn't used the CHARACTER SET option, which means the table was simply reflagged without updating the data itself. That means you have NON UTF-8 DATA in MySQL which is now thoroughly confused.


EDIT:
Which one, exactly? Did you change anything in it, since I don't recall that any of those scripts exactly would have helped you.

To be honest, though, I'm done here. I don't want to have to keep making posts that go over the same basic things again and again because you don't provide enough detail the first time. I really hope someone fixes your data, it's now thoroughly messed up.

[s²]

Wow, didn't know I was wearing you out.  I was trying to be simple, but whatever.  Thanks for the help?

Through reading your other posts, it seems you are constantly pissed off at something.  Don't yell at me when I'm honestly trying to help myself.  You provided a link and I used it.  If you knew those scripts weren't going to help then why didn't you say so?  Sounds like you need to take your own advice.

[Arantor]

If you knew those scripts weren't going to help then why didn't you say so?

I said there were examples that might be helpful. I didn't say "use script X", but relied on you taking a look and seeing if it was directly suitable.

Through reading your other posts, it seems you are constantly pissed off at something.

If you want help, provide as much detail up front as possible, instead of too little. I still don't know exactly what you did to your database since it was in an inconsistent state even before you modified the collation manually.

[s²]

I used this script:

Code Select Expand

<?php
// Script written by Vladislav "FractalizeR" Rastrusny
// http://www.fractalizer.ru

//MySQL connection settings
$db_server = 'localhost';
$db_user="root";
$db_password="";

mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());

//Put here a list of databases you need to change charset at or leave array empty to change all existing
$dblist=array();

//If changing at all databases, which databases to skip? information_schema is mysql system databse and no need to change charset on it.
$skip_db_list = array('information_schema', 'mysql');

//Which charset to convert to?
$charset="utf8";

//Which collation to convert to?
$collation="utf8_general_ci";

//Only print queries without execution?
$printonly=true;

//Getting database names if they are not specified
$skip_db_text = '"'.implode('", "', $skip_db_list).'"';
if(count($dblist)<1) {
    $sql="SELECT GROUP_CONCAT(`SCHEMA_NAME` SEPARATOR ',') AS FRST FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` NOT IN ($skip_db_text)";
    $result = mysql_query($sql) or die(mysql_error());
    $data = mysql_fetch_assoc ($result);
    $dblist=explode(",", $data["FRST"]);
}

//Iterating databases
foreach ($dblist as $dbname) {
    $sql="SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET $charset COLLATE $collation;') as FRST FROM `information_schema`.`TABLES` t WHERE t.`TABLE_SCHEMA` = '$dbname' ORDER BY 1";

    $result = mysql_query($sql) or die(mysql_error());
    while ($row = mysql_fetch_assoc($result)) {
        echo $row["FRST"]."\r\n";
        if(!$printonly) {
            mysql_query($row["FRST"]) or die(mysql_error());
        }
    }
}
?>
It outputs the needed SQL query, which I then used.
This script was on the page you linked to.  I changed the values to utf8 and utf8_general_ci respectively.

Now I know that I need to change the charset to blob to turn it to binary and then back to utf8.  Sounds reasonable?

[Arantor]

I'm still curious how you did the original conversion that actually broke everything since until the collation change it was working fine.

I don't think switching to binary and then to UTF-8 is going to solve the problem because it will likely still think you're using UTF-8 originally, so I think you're going to have to switch it back to latin1 first before switching to utf8. And if you haven't, PLEASE do a backup before tampering with the DB any further. As a rule, back up before any potentially destructive operation, which includes any table changes other than new rows. Any changes at this point could make it even more damaged than it already is.

[s²]

Code Select Expand

<html>
<head>
<title>Convert MySQL Database to UTF-8</title>
</head>
<body>

<?php
// Fill in your configuration below
(db info)

// Do not change anything below this
set_time_limit(0);

$connection = mysql_connect($db_server, $db_user, $db_password) or die( mysql_error() );
$db = mysql_select_db($db_name) or die( mysql_error() );

$sql = 'SHOW TABLES';
if ( !($result = mysql_query($sql)) )
{
   print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
}

// Loop through all tables in this database
while ( $row = mysql_fetch_row($result) )
{
   $table = mysql_real_escape_string($row[0]);
   $sql2 = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
   
   if ( !($result2 = mysql_query($sql2)) )
   {
      print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
      
      break;
   }
   
   print "$table changed to UTF-8 successfully.<br>\n";

   // Now loop through all the fields within this table
   $sql3 = "SHOW COLUMNS FROM $table";
   if ( !($result3 = mysql_query($sql3)) )
   {
      print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
      
      break;
   }

   while ( $row3 = mysql_fetch_row($result3) )
   {
      $field_name = $row3[0];
      $field_type = $row3[1];
      
      // Change text based fields
      $skipped_field_types = array('char', 'text', 'blob', 'enum', 'set');
      
      foreach ( $skipped_field_types as $type )
      {
         if ( strpos($field_type, $type) !== false )
         {
            $sql4 = "ALTER TABLE $table CHANGE `$field_name` `$field_name` $field_type CHARACTER SET utf8 COLLATE utf8_bin";
            if ( !($result4 = mysql_query($sql4)) )
            {
               print '<span style="color: red;">SQL Error: <br>' . mysql_error() . "</span>\n";
               
               break 3;
            }
            print "---- $field_name changed to UTF-8 successfully.<br>\n";
         }
      }
   }
   print "<hr>\n";
}

mysql_close($connection);
?>

</body>
</html>

This was the first script I had to do the initial change before the PM problems happened.  It was because a member of my forum posted a link from here (SMF community) about someone who had a similar problem with charsets that had certain characters turning into ?'s.  SOmeone replied saying the collation needed to be in utf8, hence why I changed it.

[s²]

Okay, I used the last script I posted to change my charset back to latin1 and the collation back to latin_swedish.  The recent PMs are still not showing.  Will those have to be deleted?

[s²]

Any help please?

[Deaks]

s2 do you still need help with this? as you have not bumped this I am marking it as solved until you get back to us