Category Archives: mysql

migrating old typo3 instances from (mysql 5.1 / php 5.3) => (mysql 5.5 / php 5.4)

apache, mysql, typo3

in typo3conf/localconf.php append

$TYPO3_CONF_VARS['SYS']['exceptionalErrors'] = E_ALL ^ E_NOTICE ^ E_DEPRECATED ^ E_WARNING ^ E_USER_ERROR ^ E_USER_NOTICE ^ E_USER_WARNING ^ E_STRICT;
$TYPO3_CONF_VARS['BE']['forceCharset'] = 'utf-8';
$TYPO3_CONF_VARS['SYS']['setDBinit'] = 'SET NAMES utf8';

convert tables containing content to utf-8


ALTER TABLE pages CONVERT TO CHARACTER SET utf8;
ALTER TABLE tt_content CONVERT TO CHARACTER SET utf8;
ALTER TABLE pages_language_overlay CONVERT TO CHARACTER SET utf8;

you might need to convert others too like tt_news, tt_products, etc…


ALTER TABLE tt_news CONVERT TO CHARACTER SET utf8;
ALTER TABLE tt_products CONVERT TO CHARACTER SET utf8;

do not forget to clear cache …

Do you encounter the follosing strange behavious after: page looks ok if loaded after cache clear but special chars are displayed wrong when loaded from the cache? This can help:


ALTER TABLE cache_pages CONVERT TO CHARACTER SET utf8;

restart mysql master/slave synchronisation after an invalid SQL query

mysql

you may encounter stuff like this:

a SQL clause that returns different error codes on master and slave, for instance if you use different versions of mysql:

[ERROR] Slave SQL: Query caused different errors on master and slave. Error on master: ... Error on slave:

To restart synchronisation simply skip that SQL clause this way:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Of course the 1 may be replaced with “n” if you want to skip multiple SQL clauses.