• Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Smartys wrote:

A. What was the charset used to insert that data into the database/present it in 1.2..

UTF-8 Unicode (utf8)

Smartys wrote:

B. What was the collation of the database tables in 1.2.

utf8_general_ci

Smartys wrote:

C. Did you choose to have the data converted to UTF-8? If so, what collation did you give to convert from?

Tested both ways. Used the default collation. Will redo it again to double check the collation.

Smartys wrote:

If you're using the mysql dblayer, make sure you're using the latest copy from SVN.

Yes, using the latest copy.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Aha!
My guess: you weren't using SET NAMES UTF8. So the UTF-8 characters were converted by MySQL from latin1 to UTF-8 to be put in the database. When outputting them, it "unencoded" them. Now, since SET NAMES UTF8 is called, the database isn't unencoding the text.
The simplest (but incorrect) way to fix it is to add the correct constant to disable calling SET NAMES (I don't remember it off the top of my head).
Another way that *might* work is to modify db_update.php so it calls SET NAMES (we currently don't if you're upgrading) and ask the script to convert from UTF-8. I don't know exactly how that will work tongue

  • From: France
  • Registered: 2004-11-06
  • Posts: 695

Re: Feedback on the update process

Smartys wrote:

I was referring to your old config file, not the one that was generated.

Yup you're right, the 1.2's file had indeed no cookie name set.

My bad, my humble deepest bad wink

Jeu de rôle en ligne sur table virtuelle ⁂ Shadowrun France ⁂ Chez Blacky

Jérémie's Website

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Smartys wrote:

Another way that *might* work is to modify db_update.php so it calls SET NAMES (we currently don't if you're upgrading) and ask the script to convert from UTF-8. I don't know exactly how that will work tongue

It appears to not work. I may not be doing it correctly. Here is what I did. In db_updated.php, changed PUN_NO_SET_NAMES from 1 to 0. In the script, forced the conversion.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

You need to remove the define() line entirely, changing the value to 0 isn't enough.
And when you forced the conversion, you said it was converting from UTF-8?

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Smartys wrote:

You need to remove the define() line entirely, changing the value to 0 isn't enough.

Removed define().

Smartys wrote:

And when you forced the conversion, you said it was converting from UTF-8?

Enable conversion is checked.
ISO-8859-1 is the current character set.

Sorry for being dense. wink

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

The current character set is a text field. It's something you set. Change it to UTF-8 and then run it (with the define removed).

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Smartys wrote:

The current character set is a text field. It's something you set. Change it to UTF-8 and then run it (with the define removed).

Done. Problem persists.

On the test server:
MySQL charset:  UTF-8 Unicode (utf8)
MySQL connection collation: utf8_unicode_ci

Does this make a difference?

Thanks.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

MySQL connection collation: utf8_unicode_ci

Is that without calling SET NAMES?

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Smartys wrote:

Is that without calling SET NAMES?

Yes, this line is removed.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Any chance you could give me access to this server to investigate it?

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Do you need access to the live server or the test server? The test server is a local server using XAMPP.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

The server where you're doing this and having the issue.

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

The issue is on the test server. This may not happen on the live server when upgrading. For now, I will try a backup of it on the live server and report back if any issues. Thanks.

  • Registered: 2005-05-11
  • Posts: 275

Re: Feedback on the update process

Smartys wrote:
Mpok wrote:
Rickard wrote:

Because those tables don't have any text/varchar fields, only integers.

Yep, i know, that's why i said "it's not a big deal", but for consistency only, i think it can be done anyway...

Agreed, for consistency we should have it (since new tables will have it).

With our setup on a new install, the Online table is the only table that is not utf8_general_ci. It is latin1_swedish_ci. Is this something we have to get our hosting provider to change?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Looks like it was just an oversight in install.php, I corrected it.

67 Reply by Mpok

  • Mpok
  • Member
  • Offline
  • Registered: 2007-08-11
  • Posts: 35

Re: Feedback on the update process

Smartys wrote:

For anyone curious, the issue was not with the conversion process but with the MySQL dblayer. smile

Thanks for the info (as i searched a long time, without figuring it could be there...).

So, IT (refer posts #5 and #21 of this topic) now works well.. (at least on my primary tests)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Wonderful! big_smile

  • Registered: 2006-10-19
  • Posts: 34

Re: Feedback on the update process

Replying to this, since there's apparently no guest posting on the Trac?

SQLite should actually be the easiest, because it does not enforce type checking. The column types are basically for the benefit of anyone reading the schema -- you can put any type of data in any column. That includes storing strings of any length. See this page, under "Manifest typing" (and I suppose the "Variable-length records" bit is also relevant).

So, as far as I can tell, there shouldn't actually be any updating needed for SQLite.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Hmm, it appears you're right. This is one of the perils of developing for a database system you're not familiar with. smile
So for IPv6 we shouldn't have an issue, other than that the column lengths will appear incorrect. However, we still need the ability to add columns to a database table: if such a solution allows us to come up with a setup similar to the way we handle PostgreSQL, all the better.

And yeah, we're still waiting on Trac/PunBB integration unfortunately. wink

71 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Feedback on the update process

Smartys wrote:

if such a solution allows us to come up with a setup similar to the way we handle PostgreSQL, all the better.

Do you just need to alter the column from varchar(15) to varchar(39)?

MattF's Website

72 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Feedback on the update process

Looking at that patch, and assuming all you want to do is go from 15 to 39 for PgSQL, (and possibly set a default too), you should only need, (straight command line syntax, btw), big_smile:

ALTER TABLE [db prefix]posts ALTER COLUMN poster_ip TYPE VARCHAR(39);
ALTER TABLE [db prefix]posts ALTER COLUMN poster_ip SET DEFAULT '0.0.0.0';

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Alter column type only exists for PostgreSQL 8, I believe.
I do have a solution, you can see it in the patch in the ticket he linked to.

74 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Feedback on the update process

Smartys wrote:

Alter column type only exists for PostgreSQL 8, I believe.

It's be hoped not. big_smile

http://www.postgresql.org/docs/7.4/inte … table.html

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Feedback on the update process

Uh, the page you linked me to is the documentation for 7.4, which does not have ALTER TABLE ... ALTER COLUMN ... TYPE.

As opposed to here, the documentation for 8.1:
http://www.postgresql.org/docs/8.1/stat … table.html