EllisLab text mark
Advanced Search
     
CI 1.6 - MySQL “SET NAMES” on MySQL 4.0.17
Posted: 01 February 2008 05:23 AM
Joined: 2007-06-26
22 posts

My MySQL Version 4.0.17 does not support the “SET NAMES” command, so trying to connect to the db with 1.6 fails. I had to substitute line 97 in mysql_driver.php by “return true;” to make it work. How about checking the mysql version or suppressing the error and continueing, if the db doesn’t support the command?

Regards,
Marc

 
Posted: 01 February 2008 10:39 AM   [ # 1 ]   [ Rating: 0 ]
Joined: 2007-01-15
11 posts

Here is reply from Derek Jones to simmilar question:

Sorry about that MikeD - I’ll update the docs in moment, but yes, CI will require MySQL 4.1+ starting with version 1.6 to work with MySQL.  I would personally recommend that you upgrade MySQL.  MySQL 4.0 has not been supported by MySQL since 2006, and this year it will even stop receiving security fixes.  MySQL doesn’t even have versions prior to 4.1 available for download any longer, and the benefits you would gain are very real.  If for some reason you cannot do so, you can modify the MySQL driver’s db_set_charset() method to just return TRUE, and everything else should continue to work.

I have additional question. MaDe you solved this by simple “hack”. But the question is if this is the only noncompatibility issue (set names) with old mysql versions? Because the problem is that if this is the only issue and it is so simply solved then I think checking the mysql version should be done . But if there are more issues ,not only this then i dont think it is good practice to “hack” the mysql_driver.php like you did since ther might be other problems which maybe will not be so easily solveable like this one.

So apart from this SET CHARACTERS problem - are there any other possible issues with mysql versions prior 4.1? Because imagine getting some error like this when you built almost entire site or application.That is the only thing stoping me from applying the “hack” you did MaDe - what if at some point in the development similar issue with “old” mysql will pop up..

But if there is only this one issue then I wonder why not inlucde the simple mysql version testing as MaDe did? Just wondering smile

I agree the best idea would be to upgrade to mysql 4.1 as Derek proposed but it is not always possible smile

 
Posted: 02 February 2008 06:12 AM   [ # 2 ]   [ Rating: 0 ]
Joined: 2007-06-26
22 posts

Hi MikeD,

I use CI very often for customers with the most different server settings. I found this issue on a customers server with IIS and MySQL 4.0. The application running on this server is very basic, so I just use some active record methods to UPDATE, INSERT and SELECT some basic data. I don’t do any JOINS or some weird rstuff and so the SET NAMES issue was the only thing (related to MySQL) I found in this project. The website is online and productive now and I did not experience any other problems (except for memory limitations, write permission issues and PHP inheritance bugs).

I would really suggest to make this little change (as it would be only one line to change). It took me about a hour to find this problem, so with a little change others could save this hour smile

Regards,
MaDe

 
Posted: 02 February 2008 10:07 AM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2002-06-03
6547 posts

It is presently our intention to only support MySQL 4.1+.  We have additional features planned for future versions of CodeIgniter that will also share that requirement.  The product lifecycle completely terminates for MySQL 4.0 in December, when it will no longer even receive security updates.  Unlike PHP 4’s surprise end of life announcement, this change is neither arbitrary nor unanticipated as there is a clear lifecycle policy that developers, hosts, and server vendors are all aware of.

For 1.6.0, this change is all that is necessary to continue using the MySQL driver with older versions, however that is not likely to be the case with future releases.  In the interim while working on servers you cannot control, you may wish to keep a Wiki article up to date on the necessary code changes for quick reference.

 Signature 
 
Posted: 07 March 2008 02:56 PM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2007-05-19
32 posts
Derek Jones - 02 February 2008 03:07 PM

It is presently our intention to only support MySQL 4.1+.  We have additional features planned for future versions of CodeIgniter that will also share that requirement.  The product lifecycle completely terminates for MySQL 4.0 in December, when it will no longer even receive security updates.  Unlike PHP 4’s surprise end of life announcement, this change is neither arbitrary nor unanticipated as there is a clear lifecycle policy that developers, hosts, and server vendors are all aware of.

For 1.6.0, this change is all that is necessary to continue using the MySQL driver with older versions, however that is not likely to be the case with future releases.  In the interim while working on servers you cannot control, you may wish to keep a Wiki article up to date on the necessary code changes for quick reference.

We cannot say MySQL 4.0 or PHP 4.x are not important nowadays. There is no problem to install the latest version of PHP and MySQL on our test machines. But when projects are put online, especially on a web hosting server, it is not your call. Many web hosting providers are still using PHP 4.x and MySQL3.23. They think the old version is very stable.

BTW: Besides the “SET NAMES” issue, is there any other conflicts with MySQL3.x and 4.0? If there is not, I suggest to change db_set_charset() like

function db_set_charset($charset$collation)
{
    
if (!$charset || !$collation)
    
{
        
return TRUE// SET NAMES requires MySQL 4.1.x or later...
    
}
    
return @mysql_query("SET NAMES '".$this->escape_str($charset)."' COLLATE '".$this->escape_str($collation)."'"$this->conn_id);

So that users, who are still using an old version MySQL can use CodeIgniter without modifying mysql_driver.php by themselves.

 Signature 

http://stanleyxu2005.blogspot.com/

 
Posted: 07 March 2008 06:43 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2002-06-03
6547 posts

Comparing MySQL 3.23 to PHP 4 in terms of stability, age, and support is misleading.  MySQL has clear product lifecycles with known ends of support well in advance, a far cry from PHP’s sudden one year announcement of the end of life for PHP 4.  Further, our own studies show that while PHP 4 still has as much as 70-80% of the market, MySQL 3.23 has less than 5%.  I will concede that increasing that limitation to MySQL 4.0 is a larger leap, but we feel that the feature benefit is worth it, and in harmony with our future goals for the Database class.  Your concerns are noted and appreciated, however, and we will consider including an official “old” driver in future releases.

 Signature 
 
Posted: 07 March 2008 07:10 PM   [ # 6 ]   [ Rating: 0 ]
Avatar
Joined: 2007-05-19
32 posts
Derek Jones - 07 March 2008 11:43 PM

Your concerns are noted and appreciated, however, and we will consider including an official “old” driver in future releases.

Well, this sounds really great. Thanks for considering my suggestion.

The reasons, why mysql 3.x make sense, are:
1. Encoding problem. It is painful to confirm the consistence of all datas. Maybe you will say, “This problem has to be solved sooner or later. Just download it, convert it and upload it.” However, when data is more than 100MB, it is almost impossible to do this.
2. The negative performance of InnoDB.

So long as the old MySQL works, most people (at least me) will not upgrade, won’t they?
If I have to upgrade, I would rather use PostgreSQL. ^^)

 Signature 

http://stanleyxu2005.blogspot.com/

 
Posted: 07 March 2008 07:17 PM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2002-06-03
6547 posts

1) If you’re migrating to 4.1 - 5, there’s actually a simple way to convert it live, that is remarkably performance friendly.

For each field:

UPDATE `{$table}SET `{$field}` = CONVERT(CONVERT(`{$field}USING binaryUSING utf8

Then on the table itself:

ALTER TABLE `{$table}CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

I’ve done this fairly recently on large (150MB-ish) databases, and it only took about 15 seconds.

2) I agree, for read-heavy apps, which are the most common, that InnoDB has some severe drawbacks.  There’s no need to use InnoDB, though, MyISAM is still the default on most hosts with 4.1 - 5, and you can of course set this implicitly in your table creation queries.

 Signature 
 
Posted: 07 March 2008 07:24 PM   [ # 8 ]   [ Rating: 0 ]
Avatar
Joined: 2007-05-19
32 posts
Derek Jones - 08 March 2008 12:17 AM

1) If you’re migrating to 4.1 - 5, there’s actually a simple way to convert it live, that is remarkably performance friendly.

For each field:

UPDATE `{$table}SET `{$field}` = CONVERT(CONVERT(`{$field}USING binaryUSING utf8

Then on the table itself:

ALTER TABLE `{$table}CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

I’ve done this fairly recently on large (150MB-ish) databases, and it only took about 15 seconds.

2) I agree, for read-heavy apps, which are the most common, that InnoDB has some severe drawbacks.  There’s no need to use InnoDB, though, MyISAM is still the default on most hosts with 4.1 - 5, and you can of course set this implicitly in your table creation queries.

Thanks for the tips. I will try this tomorrow.

 Signature 

http://stanleyxu2005.blogspot.com/