Setting charset and collation on every query, is it neccessary and can I suppress it?

[eluser]OwenM[/eluser]
Hi,

Im using mysql 5.1 and have a site running maybe 5k requests an hour.

I recently logged out the querys sent to mysql and found

Code:

Query       SET NAMES 'utf8' COLLATE 'utf8_general_ci'

preceeding nearly every query, or set of querys.

This means about a third of the traffic between my php server and my db server is the statement above, can I stop it happening. I read about modifying mysql_driver.php to return True; but i'm not sure if that will stop these querys.

Tks,
Owen.

[eluser]InsiteFX[/eluser]
MySQL will handle it for you if you create your databse and tables like this:

Code:

DROP TABLE IF EXISTS `ci_sessions`;

CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id`    varchar(40)          DEFAULT '0' NOT NULL,
  `ip_address`    varchar(16)          DEFAULT '0' NOT NULL,
  `user_agent`    varchar(50)                      NOT NULL,
  `last_activity` int(10)     unsigned DEFAULT 0   NOT NULL,
  `user_data`     text                             NOT NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; COMMENT <-- This is the collation settings

When you create a new database in phpMyAdmin click on the Operations tab and at the bottom you will see the collation setting it defaults to lantin1_swedish_ci change it utf8_uncode_ci

InsiteFX

[eluser]OwenM[/eluser]
Tks, will try this on monday and let you know.

[eluser]OwenM[/eluser]
Hi,

Tried that, I converted the tables to utf8 and utf8_general_ci using:

Code:

ALTER TABLE tz_inner_polygons CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

for each table, restarted to mysqld service but I still get the following in my query log?

Code:

110328  8:41:50         14 Init DB     myDB
             14 Query       SET NAMES 'utf8' COLLATE 'utf8_general_ci'
110328  8:41:51          9 Init DB     myDB
              9 Query       SET NAMES 'utf8' COLLATE 'utf8_general_ci'
              9 Query       select * from tz_outer_polygons where min_lat<=52.400000 and max_lat>=52.400000 and min_lon<=-8.480000 and max_lon>=-8.480000
              9 Query       select * from tz_info where location_id="IE"

Any suggestions? It's the two SET NAMES statements that I think are unneccessary and want to prevent.

Tks,
Owen.

[eluser]InsiteFX[/eluser]
I do not what OS you are running, but with phpMyAdmin there is a default of latin1_swedish_ci when creating databases!

to see the database collation:
1) Click on the database then on the Operations tab
2) Look at the very bottom and see what the database collation is set to
3) If it is set to latin1_swedish_ci change it to either utf8_general_ci or utf8_unicode_ci
4) Click on the GO button on the far right to update your database collation.

InsiteFX

[eluser]OwenM[/eluser]
mysql 5.1 on CentOS. phpmyadmin is not installed, I'm using sqlyog (third party app). It does report the tables and the database are using UTF8 and utf8_general_ci and more importantly the current records are in this charachter set.

If you have any more pointers please?

Rds,
Owen.

[eluser]InsiteFX[/eluser]
A very lengthly read but well worth it!
Character Sets / Character Encoding Issues

InsiteFX

[eluser]OwenM[/eluser]
My DB server now shows:

Code:

"character_set_client=    "utf8"
"character_set_connection    "utf8"
"character_set_database=    "utf8"
"character_set_filesystem=      "binary"
"character_set_results=      "utf8"
"character_set_server=      "utf8"
"character_set_system=          "utf8"
"character_sets_dir=             "/usr/share/mysql/charsets/"

But Im still getting all the SET NAMES and Collation statements? I have also restarted the mysql server. As far as I can tell everything about my server is UTF8 but CI still wants the set the parameters. Any suggestions?

[eluser]InsiteFX[/eluser]
You can try the below, not tested.
But this is still telling me that your database is using the latin1_swedish_ci collation, this is the number one problem that causes this error for SET NAMES.

Run it before you do other queries.

Code:

$this->db->query('SET NAMES '.$this->db->escape_str("utf8");

InsiteFX