New PMs not showing after collation change

Arantor · December 23, 2010, 07:36:03 PM

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² · December 23, 2010, 07:53:50 PM

Code Select

<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² · December 23, 2010, 08:36:16 PM

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² · December 24, 2010, 10:26:25 PM

Any help please?

Deaks · January 10, 2011, 02:38:47 PM

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

News:

New PMs not showing after collation change

Arantor

s²

s²

s²

Deaks