MySQL 4.1 upgrade workaround, and new db_mysql.php may be needed.

[AKosygin]

The way MySQL connections are handled with MySQL 4.1 and PHP 5 has changed, because of the extra MySQL 4.1 options, PHP has created a new extension: http://us4.php.net/manual/en/ref.mysqli.php

With MySQL 4.1.x, there are significant changes that affect the way vBulletin (and almost every PHP application/script that uses MySQL) operates. If you plan to upgrade MySQL 4.1.x, it is HIGHLY RECOMMENDED that you ALSO upgrade PHP to PHP 5.x. The reason for this is the mysqli extension in PHP. In php versions before 5, it does not support many of the changes in MySQL 4.1, the most common issues are login failures (due to password hashing changes), and language/collation issues.

Until vBulletin comes up with a new database file to use the PHP's mysqli class in PHP 5.x, you will have to do the following workarounds to make sure you do not run in to problems with using vBulletin:

1.) In your MySQL 4.1 configuration file, ensure that "old-passwords" option is used, the older versions of PHP and the "mysql" (note lack of I) extensions of PHP 5.x, cannot authenticate using the new MySQL 4.1 password method! Authentication will fail the moment you change your database password without the old-passwords option.
2.) Set your "default-character-set" to "latin1", and make sure that all tables created or restored from backup are "latin1_swedish_ci" as the collation set. Otherwise, vBulletin will fail in key ALTER TABLE or CREATE TABLE statements.

It would be a good idea if vBulletin can create a new database interface class like "db_mysqli.php" so that we can use the new functions of PHP 5, and MySQL 4.1.

[Gary Fischman]

How would one change the default character set?

[AKosygin]

How would one change the default character set?

In the my.ini file you set the default-character-set = latin1
(Note: For any windows installation, the location of the my.ini file has been moved to the installation directory and no longer resides in the WINDOWS directory.)

OR

If you must keep the default character set to something else and you create the tables manually... refer to this page:
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

and note the section: [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

OR

If you need to alter an existing table to the correct character set, you should refer to this page:
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

and note the section: CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

WARNING! Changing the character set while there are data in the tables MAY RESULT IN DATA LOSS OR CORRUPTION! It is best to have done it right the first time, as the definition of the width of the columns may truncate data.

While the change in COLLATION will not cause problems other than the ordering of entries and how results are sorted (see MySQL Charset Collation Effect (http://dev.mysql.com/doc/mysql/en/Charset-collation-effect.html)), the CHARACTER SET setting will affect how the data is stored as some languages treat each character as occupying different space. Number of characters IS NOT EQUAL to number of bytes a field is sized. If my memory serves me right, the char(15) is to denote 15 bytes not 15 characters. It just so happens that in "latin1_swedish_ci", 1 character = 1 byte; but in "sjis" 1 character = 2 bytes.

I.E. Japanese requires two bit width for their characters, hence if your column will be (for all practical purposes) be shorter than what you have defined in the CREATE TABLE statement. And because the coversion may not be what YOU want, it is done according to MySQL's programming, it is recommended that you read this page: http://dev.mysql.com/doc/mysql/en/Charset.html to ensure you know what character set you want.

As for the exact values, it is USUALLY...

You want to put:
CHARACTER SET latin1 COLLATE latin1_swedish_ci

[Zachery]

At this time in present we do not support nor recomend php5 or mysql 4.1.X

[Gary Fischman]

At this time in present we do not support nor recomend php5 or mysql 4.1.X

I beg your pardon?
Can we get some clarification on this (http://www.vbulletin.com/forum/showthread.php?t=124428) then?

Do you think I asked the question just to help keep the post counts up?

[Zachery]

As of the features.php page

System Requirements
MinimumPHP 4.1.0
MySQL 3.23.33
Recommended
PHP 4 (latest)
MySQL 4.0.16 (minimum)

[Gary Fischman]

Yeah, and read the thread that goes with it. "Latest stable public release". I specifically asked about MySql 4.1.8. No one said "Don't do it." So I did.

As far as I'm concerned, the left hand doesn't know what the right hand is doing!

Get your act together, and get me some valid answers!

[Zachery]

I am telling you right now,

We DO NOT recomend mysql 4.1.X and php5 at this time.

[Gary Fischman]

And I'm telling *you* that I don't accept that!
I want to know why I was told that vB recommends using the latest stable public release!

I went down a path that *YOU* people recommended. Now you tell me you don't support it?

Sorry, pal - I don't buy it.

Do your colleagues concur with your statements? Lets get 'em in here and straighten this out.

[Floris]

We do support php5 and mysql 4.1.x

[Gary Fischman]

Now that we've gotten that out of the way, what is the recommended way to deal with this character set problem?

I installed 4.1.8 from the MySQL rpm distribution. Can we get specific instructions on the best way to ensure that there are no character set related issues?

What I've done so far is discussed here (http://www.vbulletin.com/forum/showthread.php?t=123449).

[Wayne Luke]

Set the collation for all your tables to the proper character set that you want to use. My test forum is only in English so it is set to Latin collation. There are no problems or errors. If you use a different character set then set it to the proper collation.

This issue is a MySQL issue not a vBulletin one, as we do not force a collation in any query. You should refer to the MySQL Manual on how to deal with Collations in the newer versions of their products.

[Gary Fischman]

Mine is all English as well, but the out-of-the-box MySQL distribution was evidently set to Swedish or something. I just wish someone would tell me if I have a problem or not after running:

ALTER TABLE `phrase` CHANGE `varname` `varname` VARCHAR( 250 ) NOT NULL


Do I need to do anything else? Did I hose myself up?

[soopa]

I am telling you right now,

We DO NOT recomend mysql 4.1.X and php5 at this time.

We do support php5 and mysql 4.1.x

:|

brilliant support.

[AKosygin]

In their defense, while they are obligated to support PHP 5.x and MySQL 4.1.x because they did specifically say "latest stable release" (Which PHP and MySQL said those are the latest stable releases), they still do not recommend installing it.

"Support" IS NOT EQUAL TO "recommend". They will still help you, but they do not recommend using it. Currently vBulletin does not use the "Improved MySQL" extension of PHP, and while they are working on it (hopefully) it is not recommended to install PHP 5.x or MySQL 4.1.x, at least until they have added additional functionality to make use of php_mysqli.dll.

[Freddie Bingham]

Yes, you're good to go.Mine is all English as well, but the out-of-the-box MySQL distribution was evidently set to Swedish or something. I just wish someone would tell me if I have a problem or not after running:

ALTER TABLE `phrase` CHANGE `varname` `varname` VARCHAR( 250 ) NOT NULL


Do I need to do anything else? Did I hose myself up?

[Freddie Bingham]

In their defense, while they are obligated to support PHP 5.x and MySQL 4.1.x because they did specifically say "latest stable release" (Which PHP and MySQL said those are the latest stable releases), they still do not recommend installing it.

"Support" IS NOT EQUAL TO "recommend". They will still help you, but they do not recommend using it. Currently vBulletin does not use the "Improved MySQL" extension of PHP, and while they are working on it (hopefully) it is not recommended to install PHP 5.x or MySQL 4.1.x, at least until they have added additional functionality to make use of php_mysqli.dll.
Don't worry, we will support mysqli, just allow us time to test the changes.

[AKosygin]

This issue is a MySQL issue not a vBulletin one, as we do not force a collation in any query. You should refer to the MySQL Manual on how to deal with Collations in the newer versions of their products.
As for whether this is a MySQL issue or a vBulletin issue.... COLLATION is a user option defined on MySQL, however, the vBulletin staff needs to severely read up on how COLLATION is affected by CHARACTER SETS which CAN affect how vBulletin operates. This, the vBulletin staff must take in to consideration when creating table definition statements! If you have a Japanese website running vBulletin attempting to get a Japanese COLLATION, they MUST switch CHARACTER SETS, which means the size of the fields/columns assigned will be affected as the Japanese character set is defined as a TWO BYTE character.

This is an issue that should not be passed, for this is not a bug of MySQL as it is something that is BY DESIGN, and thus vBulletin must adapt to it either through the code or through the documentation (warning against such thing OR instructing on proper handling of such situations).

"Do not pass the buck."

[AKosygin]

Don't worry, we will support mysqli, just allow us time to test the changes.

Thank you, it is greatly appreciated. I am happy with the vBulletin team that effort is being applied towards this.

[Gary Fischman]

In their defense, while they are obligated to support PHP 5.x and MySQL 4.1.x because they did specifically say "latest stable release" (Which PHP and MySQL said those are the latest stable releases), they still do not recommend installing it.

"Support" IS NOT EQUAL TO "recommend". They will still help you, but they do not recommend using it. Currently vBulletin does not use the "Improved MySQL" extension of PHP, and while they are working on it (hopefully) it is not recommended to install PHP 5.x or MySQL 4.1.x, at least until they have added additional functionality to make use of php_mysqli.dll.

...except when the question specifically asks for a recommendation. (http://www.vbulletin.com/forum/showthread.php?t=124428)

[MotoUp]

I'm on a shared server with my provider. The server I'm on is using PHP 4.3.2, and MySQL 4.1.9-standard.

My FAQ returns the same error as stated above. Does that mean those of us that don't run our own servers to have control over those settings can't do anything about it right now, or am I just going to have to wait and hope they update it sometime?

[MotoUp]

Nevermind, I'm an idiot, I just ran that Query from myphpadmin, and it's working fine now.

[Stadler]

Maybe I've missed something, but I'm using MySQL 4.1.x without the MySQLi-extensions on my local Testbox along with PHP 5. New passwords are working just fine. You just have to make sure, that the newest client-libraries are being used. At least under Linux it's more or less just a matter of recompiling PHP linking to the newer librarys. Under windows it doesn't work with the precompiled binaries for PHP 4 or with older precompiled versions of PHP 5.

[KarmaPoliceT2]

This may be jumping the gun, but has anyone run into this problem with MySQL 5.0?

I know it's still in alpha, but i'm thinking of just upgrading anyway since i'm just getting my sites started...

Thanks

[Zachery]

MySQL 5 seems way to early... at least to me

[KarmaPoliceT2]

MySQL 5 seems way to early... at least to me
It probably is too early to be worried about this, i just thought i'd see if anyone has tinkered with it, and if this problem still exists.

I assume it will since it's now a "feature" of mysql... but was just curious

[HeadRat]

I still don't see the solution to this problem.

I'm on a server that I don't think I can go into the MySQL to edit, am I just **** up a creek?

[telc]

You can use PHP 4.x and Mysql 4.1 without enabling old_passwords.

If you compile php 4.x --with-mysql=PATH_TO_YOUR_MYSQL_4.1_INSTALL

Then it will compile the 4.1 mysql client into php and you will not have to enable 'old_passwords'

If you compile php with "--with-mysql" then you will not get the 4.1 client you will get "Client API version 3.23.49"

When you specify the full path to Mysql it will compile the 4,1 client into php and phpinfo() will show "Client API version 4.1.0"

[Marc Smith]

Thanks for the tip!

[Sergio68]

Set the collation for all your tables to the proper character set that you want to use. My test forum is only in English so it is set to Latin collation. There are no problems or errors. If you use a different character set then set it to the proper collation.

This issue is a MySQL issue not a vBulletin one, as we do not force a collation in any query. You should refer to the MySQL Manual on how to deal with Collations in the newer versions of their products.
Pardon me, you said set the proper character for all your tables, any risk to corrupt data into the tables ?
If I still have problems with a MySql version > 4.1.13 these problems shouldn't be related to Vbulletin but to MySql couse theoretically I could use mixed collation without any problem...

[Floris]

This thread is from 2005, please don't bump it. If you have a support question please open a new thread and optionally link to this one.