Problema Post Migrazione con Impex da PHPbb3 a VB 4.2.0

Citazione:

---

Originariamente Scritto da Media-Master

grazie per la risposta y2ksw, alla fine non ho letto la tua risposta accidenti e sper di non aver fatto danni.

Ti posto le query che ho utilizzato per pulire dai due tipi di caratteri... adesso sembra andr bene. il problema è che non ho fatto il TRUNCATE TABLE postparsed... spero non succeda niente.

Codice:

---

UPDATE post SET pagetext = REPLACE(pagetext,'ì','ì');
UPDATE post SET pagetext = REPLACE(pagetext,'ù','ù');
UPDATE post SET pagetext = REPLACE(pagetext,'è','è');
UPDATE post SET pagetext = REPLACE(pagetext,'é','é');
UPDATE post SET pagetext = REPLACE(pagetext,'ò','ò');
UPDATE post SET pagetext = REPLACE(pagetext,'ÃÂ','à')

UPDATE post SET title = REPLACE(title,'ì','ì');
UPDATE post SET title = REPLACE(title,'ù','ù');
UPDATE post SET title = REPLACE(title,'è','è');
UPDATE post SET title = REPLACE(title,'é','é');
UPDATE post SET title = REPLACE(title,'ò','ò');
UPDATE post SET title = REPLACE(title,'ÃÂ','à')

UPDATE pmtext SET message = REPLACE(message,'ì','ì');
UPDATE pmtext SET message = REPLACE(message,'ù','ù');
UPDATE pmtext SET message = REPLACE(message,'è','è');
UPDATE pmtext SET message = REPLACE(message,'é','é');
UPDATE pmtext SET message = REPLACE(message,'ò','ò');
UPDATE pmtext SET message = REPLACE(message,'ÃÂ','à')

UPDATE pmtext SET title = REPLACE(title,'ì','ì');
UPDATE pmtext SET title = REPLACE(title,'ù','ù');
UPDATE pmtext SET title = REPLACE(title,'è','è');
UPDATE pmtext SET title = REPLACE(title,'é','é');
UPDATE pmtext SET title = REPLACE(title,'ò','ò');
UPDATE pmtext SET title = REPLACE(title,'ÃÂ','à')

UPDATE thread SET title = REPLACE(title,'ì','ì');
UPDATE thread SET title = REPLACE(title,'ù','ù');
UPDATE thread SET title = REPLACE(title,'è','è');
UPDATE thread SET title = REPLACE(title,'é','é');
UPDATE thread SET title = REPLACE(title,'ò','ò');
UPDATE thread SET title = REPLACE(title,'ÃÂ','à')

UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ì','ì');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ù','ù');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'è','è');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'é','é');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ò','ò');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ÃÂ','à')

---- da eseguire dopo la prima

UPDATE post SET pagetext = REPLACE(pagetext,'á','á');
UPDATE post SET pagetext = REPLACE(pagetext,'ä','ä');
UPDATE post SET pagetext = REPLACE(pagetext,'â','â');
UPDATE post SET pagetext = REPLACE(pagetext,'é','é');
UPDATE post SET pagetext = REPLACE(pagetext,'è','è');
UPDATE post SET pagetext = REPLACE(pagetext,'ê','ê');
UPDATE post SET pagetext = REPLACE(pagetext,'ë','ë');
UPDATE post SET pagetext = REPLACE(pagetext,'ì','ì');
UPDATE post SET pagetext = REPLACE(pagetext,'Ã*','í');
UPDATE post SET pagetext = REPLACE(pagetext,'î','î');
UPDATE post SET pagetext = REPLACE(pagetext,'ïo','ï');
UPDATE post SET pagetext = REPLACE(pagetext,'ò','o');
UPDATE post SET pagetext = REPLACE(pagetext,'ó','ò');
UPDATE post SET pagetext = REPLACE(pagetext,'ô','ó');
UPDATE post SET pagetext = REPLACE(pagetext,'ö','ô');
UPDATE post SET pagetext = REPLACE(pagetext,'ù','ö');
UPDATE post SET pagetext = REPLACE(pagetext,'ú','ù');
UPDATE post SET pagetext = REPLACE(pagetext,'ü','ú');
UPDATE post SET pagetext = REPLACE(pagetext,'û','ü');
UPDATE post SET pagetext = REPLACE(pagetext,'€','û');
UPDATE post SET pagetext = REPLACE(pagetext,'Ã','à');
UPDATE post SET pagetext = REPLACE(pagetext,'€','€')

UPDATE post SET title = REPLACE(title,'á','á');
UPDATE post SET title = REPLACE(title,'ä','ä');
UPDATE post SET title = REPLACE(title,'â','â');
UPDATE post SET title = REPLACE(title,'é','é');
UPDATE post SET title = REPLACE(title,'è','è');
UPDATE post SET title = REPLACE(title,'ê','ê');
UPDATE post SET title = REPLACE(title,'ë','ë');
UPDATE post SET title = REPLACE(title,'ì','ì');
UPDATE post SET title = REPLACE(title,'Ã*','í');
UPDATE post SET title = REPLACE(title,'î','î');
UPDATE post SET title = REPLACE(title,'ïo','ï');
UPDATE post SET title = REPLACE(title,'ò','o');
UPDATE post SET title = REPLACE(title,'ó','ò');
UPDATE post SET title = REPLACE(title,'ô','ó');
UPDATE post SET title = REPLACE(title,'ö','ô');
UPDATE post SET title = REPLACE(title,'ù','ö');
UPDATE post SET title = REPLACE(title,'ú','ù');
UPDATE post SET title = REPLACE(title,'ü','ú');
UPDATE post SET title = REPLACE(title,'û','ü');
UPDATE post SET title = REPLACE(title,'€','û');
UPDATE post SET title = REPLACE(title,'Ã','à');
UPDATE post SET title = REPLACE(title,'€','€')

UPDATE pmtext SET message = REPLACE(message,'á','á');
UPDATE pmtext SET message = REPLACE(message,'ä','ä');
UPDATE pmtext SET message = REPLACE(message,'â','â');
UPDATE pmtext SET message = REPLACE(message,'é','é');
UPDATE pmtext SET message = REPLACE(message,'è','è');
UPDATE pmtext SET message = REPLACE(message,'ê','ê');
UPDATE pmtext SET message = REPLACE(message,'ë','ë');
UPDATE pmtext SET message = REPLACE(message,'ì','ì');
UPDATE pmtext SET message = REPLACE(message,'Ã*','í');
UPDATE pmtext SET message = REPLACE(message,'î','î');
UPDATE pmtext SET message = REPLACE(message,'ïo','ï');
UPDATE pmtext SET message = REPLACE(message,'ò','o');
UPDATE pmtext SET message = REPLACE(message,'ó','ò');
UPDATE pmtext SET message = REPLACE(message,'ô','ó');
UPDATE pmtext SET message = REPLACE(message,'ö','ô');
UPDATE pmtext SET message = REPLACE(message,'ù','ö');
UPDATE pmtext SET message = REPLACE(message,'ú','ù');
UPDATE pmtext SET message = REPLACE(message,'ü','ú');
UPDATE pmtext SET message = REPLACE(message,'û','ü');
UPDATE pmtext SET message = REPLACE(message,'€','û');
UPDATE pmtext SET message = REPLACE(message,'Ã','à');
UPDATE pmtext SET message = REPLACE(message,'€','€')

UPDATE pmtext SET title = REPLACE(title,'á','á');
UPDATE pmtext SET title = REPLACE(title,'ä','ä');
UPDATE pmtext SET title = REPLACE(title,'â','â');
UPDATE pmtext SET title = REPLACE(title,'é','é');
UPDATE pmtext SET title = REPLACE(title,'è','è');
UPDATE pmtext SET title = REPLACE(title,'ê','ê');
UPDATE pmtext SET title = REPLACE(title,'ë','ë');
UPDATE pmtext SET title = REPLACE(title,'ì','ì');
UPDATE pmtext SET title = REPLACE(title,'Ã*','í');
UPDATE pmtext SET title = REPLACE(title,'î','î');
UPDATE pmtext SET title = REPLACE(title,'ïo','ï');
UPDATE pmtext SET title = REPLACE(title,'ò','o');
UPDATE pmtext SET title = REPLACE(title,'ó','ò');
UPDATE pmtext SET title = REPLACE(title,'ô','ó');
UPDATE pmtext SET title = REPLACE(title,'ö','ô');
UPDATE pmtext SET title = REPLACE(title,'ù','ö');
UPDATE pmtext SET title = REPLACE(title,'ú','ù');
UPDATE pmtext SET title = REPLACE(title,'ü','ú');
UPDATE pmtext SET title = REPLACE(title,'û','ü');
UPDATE pmtext SET title = REPLACE(title,'€','û');
UPDATE pmtext SET title = REPLACE(title,'Ã','à');
UPDATE pmtext SET title = REPLACE(title,'€','€')

UPDATE thread SET title = REPLACE(title,'á','á');
UPDATE thread SET title = REPLACE(title,'ä','ä');
UPDATE thread SET title = REPLACE(title,'â','â');
UPDATE thread SET title = REPLACE(title,'é','é');
UPDATE thread SET title = REPLACE(title,'è','è');
UPDATE thread SET title = REPLACE(title,'ê','ê');
UPDATE thread SET title = REPLACE(title,'ë','ë');
UPDATE thread SET title = REPLACE(title,'ì','ì');
UPDATE thread SET title = REPLACE(title,'Ã*','í');
UPDATE thread SET title = REPLACE(title,'î','î');
UPDATE thread SET title = REPLACE(title,'ïo','ï');
UPDATE thread SET title = REPLACE(title,'ò','o');
UPDATE thread SET title = REPLACE(title,'ó','ò');
UPDATE thread SET title = REPLACE(title,'ô','ó');
UPDATE thread SET title = REPLACE(title,'ö','ô');
UPDATE thread SET title = REPLACE(title,'ù','ö');
UPDATE thread SET title = REPLACE(title,'ú','ù');
UPDATE thread SET title = REPLACE(title,'ü','ú');
UPDATE thread SET title = REPLACE(title,'û','ü');
UPDATE thread SET title = REPLACE(title,'€','û');
UPDATE thread SET title = REPLACE(title,'Ã','à');
UPDATE thread SET title = REPLACE(title,'€','€')

UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'á','á');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ä','ä');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'â','â');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'é','é');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'è','è');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ê','ê');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ë','ë');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ì','ì');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'Ã*','í');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'î','î');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ïo','ï');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ò','o');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ó','ò');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ô','ó');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ö','ô');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ù','ö');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ú','ù');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'ü','ú');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'û','ü');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'€','û');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'Ã','à');
UPDATE sigparsed SET signatureparsed = REPLACE(signatureparsed,'€','€')

---

Per quanto riguarda le tabelle in generale purtroppo me le da tutte in latin_swedish, anche se il database è in utf8, e non riesco a trovare il modo per cambiarle di massa.

---

Si cambiano una per una con phpMyAdmin. Però quando converti il primo ti suggerisce la query, che la prendi e con un copia incolla e sostituzione globale i sistemi abbastanza in fretta.

Con

Codice:

---

SHOW TABLES

---

ti elenca tutte le tabelle da copiare in un file di testo, ad esempio tabelle.txt. Se selezioni la prima e trascini il mouse verso il basso, prendi tutte :)

Allegato 1625

Se sei in Linux, allora puoi usare uno script per generare le query, ad esempio:

Codice:

---

#!/bin/sh

[ -f miequery.sql ] && rm miequery.sql

for i in `cat tabelle.txt`
do
    echo "ALTER TABLE $i DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;">>miequery.sql
done

exit 0

---

Se non hai Linux, fatti fare da qualcuno il file miequery.sql con questo script e la lista delle tabelle.

Il contenuto del file è quindi costituito da 200+ query che si possono incollare nella finestra SQL di phpMyAdmin ed eseguire in blocco.