• Registered: 2021-01-20
  • Posts: 6

Topic: Charset on PunBB, 1.2.15 vs MySQL 5.6

Hello people,

First of all, thank you for this forum and to read my request.
I apologize for the possible english mistakes, this is not my native language, i'm French (it also happens to good people big_smile).

Also, I'm really sorry to bring back this section from the dead, but i need help. I hope i don't post in the bad section... If so, please apologize me.

Here's my case :

I have an old PunBB, 1.2.15 version related to a website, with a long story (10 years up), a community involved, etc. You can find it here.

For many reason, i have to migrate all my hosting (website + board) on a new server, with MySQL 5.6.
So i've installed a new board, but i'd like to keep archives from the old one, in a read only mode.
You can see this installation here (not in read only for the moment)

I had multiple problems, but i have corrected many of them. However, i'm stuck with 2 lasts bugs related to charset (i believe so).

The first one is about special characters, like é, è, ê, à, etc. When they came from my DB, all these characters appears like this : �
The second one is the post message doesn't appears when you consult a topic. The message block is empty. But i've noticed the message appears when i do a search. You can see example here : empty topic & search results.

So it means that the problem for the "empty message" does not provide from my DB.

I've been on many websites, i've been reading a lot, but i don't find the solution.

I've converted all my files in UTF-8 Format.
I've fully converted my DataBase in utf8_general_mysql500_ci for the "collations" parameter.
I've added <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> in all my files.
And many other stuffs stuffs to resolve previous problems (before these actions, all the messages were truncated after the first special character as "é" "à" "œ", etc.)

Does any one of you has an idea to help me ?

2 Reply by Visman (edited by Visman 2021-01-21 03:50)

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

If I'm not mistaken, punbb 1.2.x will not be able to work with the utf-8 base, since in this version of the engine all string functions are designed for single-byte character encoding.

You need to make a one-to-one duplicate of the old forum database.

And then try to convert the copy to utf-8:

1. Or with punbb 1.4.4 (my variant https://github.com/MioVisman/punbb should work up to php 8.0.1 and mysql 8+). Although I did not check the database conversion script for punbb. To update, you need to run the admin/db_update.php file.

2. Or using FluxBB_by_Visman (https://github.com/MioVisman/FluxBB_by_Visman). The result is a FluxBB forum, but there are still old styles from punbb 1.2. In this assembly, I improved the conversion from punbb 1.2, since I converted my old forum. At the beginning of the conversion, you will need to select the correct encoding of the source data. The conversion should start on its own.

In both cases, you need to configure the config.php file specifying the correct connection to convertible database:

1. An example config.php file for punbb 1.4.4 (the file is in the root of the forum):

<?php

$db_type = 'mysqli_innodb';   // here is your type of connection to the database
$db_host = '...';    
$db_name = '...';    
$db_username = '...';   
$db_password = '...';  
$db_prefix = '...'; 
$p_connect = false;

$base_url = 'http://...';

$cookie_name = 'forum_cookie_...';
$cookie_domain = '';
$cookie_path = '/';
$cookie_secure = 0;

define('FORUM', 1);

// Enable DEBUG mode by removing // from the following line
define('FORUM_DEBUG', 1);

// Enable show DB Queries mode by removing // from the following line
define('FORUM_SHOW_QUERIES', 1);

// Enable forum IDNA support by removing // from the following line
//define('FORUM_ENABLE_IDNA', 1);

// Disable forum CSRF checking by removing // from the following line
//define('FORUM_DISABLE_CSRF_CONFIRM', 1);

// Disable forum hooks (extensions) by removing // from the following line
//define('FORUM_DISABLE_HOOKS', 1);

// Disable forum output buffering by removing // from the following line
//define('FORUM_DISABLE_BUFFERING', 1);

// Disable forum async JS loader by removing // from the following line
//define('FORUM_DISABLE_ASYNC_JS_LOADER', 1);

// Disable forum extensions version check by removing // from the following line
//define('FORUM_DISABLE_EXTENSIONS_VERSION_CHECK', 1);

// SQLite3 busy timeout -> after waiting for that time we get 'db is locked' error (in msec)
//define('FORUM_SQLITE3_BUSY_TIMEOUT', 10000);

// SQLite3 WAL mode has better control over concurrency. Source: https://www.sqlite.org/wal.html
//define('FORUM_SQLITE3_WAL_ON', 1);

2. Sample config.php file for FluxBB_by_Visman (located in the include/ directory of the forum directory):

<?php

$db_type = 'mysqli_innodb';   // here is your type of connection to the database
$db_host = '...';
$db_name = '...';
$db_username = '...';
$db_password = '...';
$db_prefix = '...';
$p_connect = false;

$cookie_name = 'pun_cookie_...';
$cookie_domain = '';
$cookie_path = '/';
$cookie_secure = 0;
$cookie_seed = '...'; // random string
$cookie_samesite = 'Lax'; // Strict, Lax or None

$salt1 = '';

define('PUN', 1);

define('PUN_DEBUG', 1);
define('PUN_SHOW_QUERIES', 1);
define('PUN_MAX_POSTSIZE', 65535);
//define('FORUM_EOL', "\r\n"); // possible values can be PHP_EOL, "\r\n", "\n" or "\r"
//define('FORUM_UA_OFF', 1);
define('FORUM_AJAX_JQUERY', 'https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js');
//define('FORUM_SQLITE3_BUSY_TIMEOUT', 10000);
//define('FORUM_SQLITE3_WAL_ON', 1);

//error_reporting(E_ALL);
//ini_set('display_errors', 1);
//ini_set('log_errors', 1);

P.S. More information about FluxBB by Visman: https://fluxbb.org/forums/viewtopic.php?id=4941
P.P.S. Don't forget about backups wink

ForkBB
I speak only Russian  :P

Visman's Website

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

https://punbb.informer.com/forums/topic … sion-help/
https://punbb.informer.com/forums/topic … from-1215/
https://punbb.informer.com/forums/topic … s-1221-14/

ForkBB
I speak only Russian  :P

Visman's Website

  • Registered: 2021-01-20
  • Posts: 6

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

Thanks a lot for your quick reply smile
I'll test your suggestions and those in the others topics ASAP !

5 Reply by PeteBondurant (edited by PeteBondurant 2021-01-21 22:41)

  • Registered: 2021-01-20
  • Posts: 6

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

Thanks a lot, it's near to perfect for read only smile

In read only :

Last mistake : topic titles are truncated when a special character is in the title.

For example : "Qu'écoutez-vous en ce moment" is now "Qu'".
I had a look in the DB and all the titles have been truncated in the DB itself, not only in the client view.

Except that, all is OK.

But in writting mode, it isn't so good :

I've got an error when i try to post :

"Sorry! The page could not be loaded.

This is probably a temporary error. Just refresh the page and retry. If problem continues, please check back in 5-10 minutes."

If i go back on the board, the new post or the new topic are available (but when i use special characters as "à" "&" "œ" etc., the messages/posts are truncated.

See here (new install)

I've checked the files permissions, all seems to be OK.
I've no idea where to check for a normal behavior in writting mode (new posts and no problemes with characters set).

If you have any idea ?
If not nevermind, you've helped me a lot with your previous post !

Merci beaucoup smile

edit :

I've made more test about special characters :

If i took my native base of my board in 1.2.15 and convert it with db_update.php, all data are truncated if there is a special character as "à" "œ" "&" etc.
If i took the db after i've change the collations to utf-8 (using phpmyadmin to do so) and after use db_update.php to update it. In this case, only the post titles and nicknames/usernames are truncated.

I think this script could be my solution, but it is not more available sad

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

I've got an error when i try to post :

Enable debug mode in config.php:

// Enable DEBUG mode by removing // from the following line
define('FORUM_DEBUG', 1);

and look in the server error log error.log.

I think this script could be my solution, but it is not more available

The Internet archive remembers this script:

~ PunBB 1.2 -> PunBB 1.3 database preparation ~
(tested successfully during a real forum migration)

Converts latin1 (or whatever) colums to utf8 manually, and prevents loss of data due to special chars.
This does NOT convert 1.2 database to 1.3 format, it only alters the charset, you still need to run db_update.php script to convert your db.
>> After executing this script, you MUST NOT SELECT "Enable conversation", or you will lose data if sone of your entries contain special chars.

<?>    This script assumes your data is in the same format as your headers (which is the case most of the time)
    Please backup/dump your database before using my script for safety.

<!> Don't forget to change the USE statement at line 25

Read this topic for more information:
http://punbb.informer.com/forums/post/140765/#p140765


    tOnI0 (punBB official forum)
    contact: tonio.cotten@gnail.com
    03/08/2011

*/

USE myDbName --your punBB 1.2 Database name

--bans

ALTER TABLE bans

DEFAULT CHARSET=utf8,

MODIFY COLUMN `username` varchar(200) CHARACTER SET utf8,
MODIFY COLUMN `ip` varchar(255) CHARACTER SET utf8,
MODIFY COLUMN `email` varchar(50) CHARACTER SET utf8,
MODIFY COLUMN `message` varchar(255) CHARACTER SET utf8;

--categories

ALTER TABLE categories

DEFAULT CHARSET=utf8,

MODIFY COLUMN `cat_name` varchar(80) CHARACTER SET utf8 not null DEFAULT 'New Category';

--censoring

ALTER TABLE censoring

DEFAULT CHARSET=utf8,

MODIFY COLUMN `search_for` varchar(60) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `replace_with` varchar(60) CHARACTER SET utf8 not null DEFAULT '';

--config

ALTER TABLE config

DEFAULT CHARSET=utf8,

MODIFY COLUMN `conf_name` varchar(255) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `conf_value` text CHARACTER SET utf8;

--forums

ALTER TABLE forums

DEFAULT CHARSET=utf8,

MODIFY COLUMN `forum_name` varchar(80) CHARACTER SET utf8 not null DEFAULT 'New Forum',
MODIFY COLUMN `forum_desc` text CHARACTER SET utf8,
MODIFY COLUMN `redirect_url` varchar(100) CHARACTER SET utf8,
MODIFY COLUMN `moderators` text CHARACTER SET utf8,
MODIFY COLUMN `last_poster` varchar(200) CHARACTER SET utf8;

--perms

ALTER TABLE forum_perms

DEFAULT CHARSET=utf8;

--groups

ALTER TABLE groups

DEFAULT CHARSET=utf8,

MODIFY COLUMN `g_title` varchar(50) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `g_user_title` varchar(50) CHARACTER SET utf8;

--online

ALTER TABLE online

DEFAULT CHARSET=utf8,

MODIFY COLUMN `ident` varchar(200) CHARACTER SET utf8 not null DEFAULT '';

--posts

ALTER TABLE posts

DEFAULT CHARSET=utf8,

MODIFY COLUMN `poster` varchar(200) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `poster_ip` varchar(15) CHARACTER SET utf8,
MODIFY COLUMN `poster_email` varchar(50) CHARACTER SET utf8,
MODIFY COLUMN `message` text CHARACTER SET utf8,
MODIFY COLUMN `edited_by` varchar(200) CHARACTER SET utf8 not null DEFAULT '';

--ranks

ALTER TABLE ranks

DEFAULT CHARSET=utf8,

MODIFY COLUMN `rank` varchar(50) CHARACTER SET utf8 not null DEFAULT '';

--reports

ALTER TABLE reports

DEFAULT CHARSET=utf8,

MODIFY COLUMN `message` text CHARACTER SET utf8;

--search_table

ALTER TABLE search_cache

DEFAULT CHARSET=utf8,

MODIFY COLUMN `ident` varchar(200) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `search_data` text CHARACTER SET utf8;

--search_matches

ALTER TABLE search_matches

DEFAULT CHARSET=utf8;

--search_words

ALTER TABLE search_words

DEFAULT CHARSET=utf8,

MODIFY COLUMN `word` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin not null DEFAULT '';

--subscriptions

ALTER TABLE subscriptions

DEFAULT CHARSET=utf8;

--topics

ALTER TABLE topics

DEFAULT CHARSET=utf8,

MODIFY COLUMN `poster` varchar(200) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `subject` varchar(255) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `last_poster` varchar(200) CHARACTER SET utf8;

--users

ALTER TABLE users

DEFAULT CHARSET=utf8,

MODIFY COLUMN `username` varchar(200) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `password` varchar(40) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `email` varchar(50) CHARACTER SET utf8 not null DEFAULT '',
MODIFY COLUMN `title` varchar(40) CHARACTER SET utf8,
MODIFY COLUMN `realname` varchar(40) CHARACTER SET utf8,
MODIFY COLUMN `url` varchar(100) CHARACTER SET utf8,
MODIFY COLUMN `jabber` varchar(75) CHARACTER SET utf8,
MODIFY COLUMN `icq` varchar(12) CHARACTER SET utf8,
MODIFY COLUMN `msn` varchar(50) CHARACTER SET utf8,
MODIFY COLUMN `aim` varchar(30) CHARACTER SET utf8,
MODIFY COLUMN `yahoo` varchar(30) CHARACTER SET utf8,
MODIFY COLUMN `location` varchar(30) CHARACTER SET utf8,
MODIFY COLUMN `signature` text CHARACTER SET utf8,
MODIFY COLUMN `language` varchar(25) CHARACTER SET utf8 not null DEFAULT 'English',
MODIFY COLUMN `style` varchar(25) CHARACTER SET utf8 not null DEFAULT 'Oxygen',
MODIFY COLUMN `registration_ip` varchar(15) CHARACTER SET utf8 not null DEFAULT '0.0.0.0',
MODIFY COLUMN `admin_note` varchar(30) CHARACTER SET utf8,
MODIFY COLUMN `activate_string` varchar(50) CHARACTER SET utf8,
MODIFY COLUMN `activate_key` varchar(8) CHARACTER SET utf8;
ForkBB
I speak only Russian  :P

Visman's Website

  • From: Siberia
  • Registered: 2013-08-05
  • Posts: 408

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

An article on your topic (not punbb): https://nicj.net/mysql-converting-an-in … n-to-utf8/

Although you have the page encoding iso-8859-1 set, the actual database may be ISO-8859-15, Windows-1252 https://en.wikipedia.org/wiki/ISO/IEC_8 … e_coverage

ForkBB
I speak only Russian  :P

Visman's Website

8 Reply by PeteBondurant (edited by PeteBondurant 2021-01-23 10:05)

  • Registered: 2021-01-20
  • Posts: 6

Re: Charset on PunBB, 1.2.15 vs MySQL 5.6

Visman : Thank you so much !!!!

All works perfectly on Punbb 1.4.

Here's what i did :

I've uploaded my old db, without any modifications (even on charset or collations)
I used the script manually, table by table, as described by ml here.
I've launched the script of punbb 1.4 db_update.php whitout selecting "Enable conversation" and with  ISO-8859-15 as native DB status.
After conversion all is OK : posts, nicknames, topic titles, etc. are not truncated anymore ; all forums functions are up and OK.

A real big thanks, merci de tout cœur smile

Спасибо / Spassiba (I hope this is correct smile)